Skip to main content

MySQL optimization help

nyte_spawn
Hello, can anyone take a look at the following mysql tuner report and tell me what i should add to my.cnf file to make it run smoother or any other things i should be doing to optimize. >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Logged in using credentials passed on the command line -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.34-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 280M (Tables: 185) [--] Data in InnoDB tables: 1M (Tables: 63) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 32 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 11d 14h 3m 48s (874K q [0.873 qps], 78K conn, TX: 13B, RX: 170M) [--] Reads / Writes: 42% / 58% [--] Total buffers: 176.0M global + 2.8M per thread (200 max threads) [OK] Maximum possible memory usage: 726.0M (17% of installed RAM) [OK] Slow queries: 0% (0/874K) [OK] Highest usage of available connections: 4% (9/200) [OK] Key buffer size / total MyISAM indexes: 8.0M/66.6M [OK] Key buffer hit rate: 99.9% (8M cached / 8K reads) [OK] Query cache efficiency: 50.1% (181K cached / 362K selects) [!!] Query cache prunes per day: 694 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 21K sorts) [OK] Temporary tables created on disk: 13% (1K on disk / 12K total) [OK] Thread cache hit rate: 99% (29 created / 78K connections) [OK] Table cache hit rate: 37% (400 open / 1K opened) [OK] Open file limit used: 50% (517/1K) [OK] Table locks acquired immediately: 99% (438K immediate / 438K locks) [OK] InnoDB data size / buffer pool: 1.0M/128.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Variables to adjust: query_cache_size (> 8M)
This is the current my.cnf. [mysqld] max_connections = 200 innodb_file_per_table = 1 query_cache_size = 8M query_cache_limit = 1M table_cache = 400 thread_cache_size = 4 interactive_timeout = 1000 connect_timeout = 1000 wait_timeout = 1000 log-slow-queries=/var/lib/mysql/slow.log
Thanks in advance

Comments

4 comments

Date Votes
  • cPanelMichael
    Hello :) In addition to the MySQL tuner, you can provide some additional statistics as indicated in the following thread: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.
    0
  • thinkbot
    You dont have much queries, so there is not much to optimize You can add key_buffer_size = 100M long_query_time=0.1 query_cache_size change to 30M table_cache change to 1000 besides that, it's all good before restarting remove old slow log file /var/lib/mysql/slow.log, so new one will be created and catch queries longer than 0.1
    0
  • nyte_spawn
    Hi i have a new xen vps now and this is the mysqltuner report >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Logged in using credentials passed on the command line -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.35-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 35M (Tables: 265) [--] Data in MRG_MYISAM tables: 807K (Tables: 6) [--] Data in InnoDB tables: 1M (Tables: 70) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 26 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 3d 11h 51m 42s (209K q [0.693 qps], 14K conn, TX: 3B, RX: 30M) [--] Reads / Writes: 66% / 34% [--] Total buffers: 176.0M global + 2.8M per thread (200 max threads) [OK] Maximum possible memory usage: 726.0M (18% of installed RAM) [OK] Slow queries: 0% (15/209K) [OK] Highest usage of available connections: 3% (6/200) [OK] Key buffer size / total MyISAM indexes: 8.0M/8.7M [OK] Key buffer hit rate: 99.9% (940K cached / 623 reads) [OK] Query cache efficiency: 50.7% (60K cached / 119K selects) [!!] Query cache prunes per day: 211 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 14K sorts) [OK] Temporary tables created on disk: 8% (253 on disk / 3K total) [OK] Thread cache hit rate: 99% (6 created / 14K connections) [OK] Table cache hit rate: 57% (400 open / 694 opened) [OK] Open file limit used: 28% (597/2K) [OK] Table locks acquired immediately: 99% (96K immediate / 96K locks) [OK] InnoDB data size / buffer pool: 1.3M/128.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Variables to adjust: query_cache_size (> 8M)
    The current my.cnf [mysqld] innodb_file_per_table=1 local-infile=0 max_connections = 200 query_cache_size = 8M query_cache_limit = 1M thread_cache_size = 4 tmp_table_size = 16M max_heap_table_size = 16M interactive_timeout = 1000 connect_timeout = 1000 wait_timeout = 1000 slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 open_files_limit=2112
    0
  • cPanelPeter cPanel Staff
    Hello, I just posted the following sticky thread... Warning on using mysqltuner on MySQL 5.6.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post