Skip to main content

Server optimization

nunoxico
Hi, This is my first tread in this forum, hopping being at the right place. I"m trying to optimize my new VPS cPanel purchase: AMD Opteron(tm) Processor 4284 2Vcores 2GB RAM Speed 3000.000 MHz Cache 2048 KB After install MySQLTuner I get this results: [OK] Key buffer hit rate: 99.5% (404K cached / 2K reads) [OK] Query cache efficiency: 92.3% (735K cached / 797K selects) [!!] Query cache prunes per day: 7655 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 15K sorts) [OK] Temporary tables created on disk: 22% (5K on disk / 23K total) [OK] Thread cache hit rate: 99% (12 created / 17K connections) [!!] Table cache hit rate: 4% (665 open / 15K opened) [OK] Open file limit used: 2% (304/10K) [OK] Table locks acquired immediately: 99% (188K immediate / 189K locks) [!!] InnoDB buffer pool / data size: 8.0M/23.1M [!!] InnoDB log waits: 18 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Increase table_cache gradually to avoid file descriptor limits Read this before increasing table_cache over 64: [http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/ Variables to adjust: query_cache_size (> 32M) table_cache (> 5000) innodb_buffer_pool_size (>= 23M) innodb_log_buffer_size (>= 1M)
Can anyone guide me through to optimize my plan? Thanks in advance, Francisco

Comments

7 comments

Date Votes
  • cPanelMichael
    Hello :) You may want to post the full output from the MySQL tuner as opposed to just the final results snippet. Thank you.
    0
  • nunoxico
    Hi Michael, Thanks for your reply. Can you please guide trough the steps. Witch file should I edit? I also try mysqlcheck "optimize -A but doesn't work. Again, thanks for any help, Francisco
    0
  • thinkbot
    copy here full my.cnf and full mysqltuner.pl result for now you can adjust in my.cnf innodb_buffer_pool_size = 50M innodb_log_buffer_size = 16M and restart service mysql restart
    0
  • nunoxico
    Hi Thinkbot, Because I have done some modifications, I prefer show you my actual settings before doing anything else. Please tell me what should I do to improve my settings and bring back memory usage to 2GB = 100% Thanks in advance, Francisco mysqltuner.pl >> MySQLTuner 1.3.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.1.73-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 2M (Tables: 120) [--] Data in InnoDB tables: 29M (Tables: 463) [--] Data in MEMORY tables: 0B (Tables: 17) [!!] Total fragmented tables: 14 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 20h 3m 13s (1M q [10.003 qps], 19K conn, TX: 2B, RX: 372M) [--] Reads / Writes: 62% / 38% [--] Total buffers: 218.0M global + 8.5M per thread (250 max threads) [!!] Maximum possible memory usage: 2.3G (117% of installed RAM) [OK] Slow queries: 0% (0/1M) [OK] Highest usage of available connections: 8% (20/250) [OK] Key buffer size / total MyISAM indexes: 64.0M/1.5M [OK] Key buffer hit rate: 99.4% (227K cached / 1K reads) [OK] Query cache efficiency: 96.4% (1M cached / 1M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13K sorts) [OK] Temporary tables created on disk: 17% (11K on disk / 61K total) [OK] Thread cache hit rate: 99% (20 created / 19K connections) [!!] Table cache hit rate: 0% (666 open / 330K opened) [OK] Open file limit used: 1% (295/20K) [OK] Table locks acquired immediately: 99% (142K immediate / 142K locks) [!!] InnoDB buffer pool / data size: 8.0M/29.8M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Enable the slow query log to troubleshoot bad queries Increase table_cache gradually to avoid file descriptor limits Read this before increasing table_cache over 64: [url=http://bit.ly/1mi7c4C]table_cache negative scalability - MySQL Performance Blog Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** table_cache (> 10000) innodb_buffer_pool_size (>= 29M)
    my.cnf [mysqld] innodb_file_per_table=1 bind-address=127.0.0.1 open_files_limit=3474 [mysqld] local-infile = 0 max_connections = 250 key_buffer = 64M myisam_sort_buffer_size = 64M join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 4M max_heap_table_size = 16M table_cache = 10000 thread_cache_size = 286 interactive_timeout = 50 wait_timeout = 7000 connect_timeout = 15 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 2M query_cache_size = 128M query_cache_type = 1 tmp_table_size = 16M [mysqld_safe] [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M [mysqlhotcopy] interactive-timeout
    Any suggestions will be very welcome.
    0
  • thinkbot
    adjust: max_connections = 100 key_buffer = 25M join_buffer_size = 1M read_buffer_size = 128K sort_buffer_size = 256K table_cache = 6000 thread_cache_size = 100 connect_timeout = 5 query_cache_size = 30M innodb_buffer_pool_size = 50M innodb_log_buffer_size = 16M and restart, memory savings would be huge
    0
  • nunoxico
    Hi thinkbot, I follow your instructions but the results make me a little bit confuse. RAM Before [!!] Maximum possible memory usage: 2.3G (117% of installed RAM) now [OK] Maximum possible memory usage: 313.0M (15% of installed RAM) Key buffer before [OK] Key buffer hit rate: 99.4% (227K cached / 1K reads) and now !!] Key buffer hit rate: 92.1% (355 cached / 28 reads) Query cache efficiency before [OK] Query cache efficiency: 96.4% (1M cached / 1M selects) now [OK] Query cache efficiency: 35.3% (65 cached / 184 selects) Thread cache before [OK] Thread cache hit rate: 99% (20 created / 19K connections) now [OK] Thread cache hit rate: 90% (1 created / 11 connections) Table cache before [!!] Table cache hit rate: 0% (666 open / 330K opened) now [OK] Table cache hit rate: 90% (69 open / 76 opened) Here the report >> MySQLTuner 1.3.0 - Major Hayden >> Bug reports, feature requests, and downloads at MySQLTuner-perl by major >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.1.73-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 2M (Tables: 120) [--] Data in InnoDB tables: 29M (Tables: 463) [--] Data in MEMORY tables: 0B (Tables: 17) [!!] Total fragmented tables: 14 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1m 10s (210 q [3.000 qps], 11 conn, TX: 507K, RX: 42K) [--] Reads / Writes: 100% / 0% [--] Total buffers: 138.0M global + 1.8M per thread (100 max threads) [OK] Maximum possible memory usage: 313.0M (15% of installed RAM) [OK] Slow queries: 0% (0/210) [OK] Highest usage of available connections: 1% (1/100) [OK] Key buffer size / total MyISAM indexes: 25.0M/1.5M [!!] Key buffer hit rate: 92.1% (355 cached / 28 reads) [OK] Query cache efficiency: 35.3% (65 cached / 184 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 38 sorts) [OK] Temporary tables created on disk: 25% (5 on disk / 20 total) [OK] Thread cache hit rate: 90% (1 created / 11 connections) [OK] Table cache hit rate: 90% (69 open / 76 opened) [OK] Open file limit used: 0% (38/12K) [OK] Table locks acquired immediately: 100% (223 immediate / 223 locks) [OK] InnoDB buffer pool / data size: 50.0M/29.9M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries
    and here the Cron Daemon alert /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space' Please, kindly support me on that. Thanks, Francisco
    0
  • thinkbot
    You need to run mysql for a little bit more time than 1min, as recommendation says "MySQL started within last 24 hours - recommendations may be inaccurate" [--] Up for: 1m 10s (210 q [3.000 qps], 11 conn, TX: 507K, RX: 42K) Previous result [!!] Maximum possible memory usage: 2.3G (117% of installed RAM) New one [OK] Maximum possible memory usage: 313.0M (15% of installed RAM) So the maximum mysql usage dropped to acceptable level for your server, at this point, your mysql data doesnt require more In the future adjust only those [OK] Key buffer size / total MyISAM indexes: 25.0M/1.5M [OK] InnoDB buffer pool / data size: 50.0M/29.9M so that MyISAM indexes fits Key buffer size and InnoDB data size fits InnoDB buffer pool, as it does now
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post