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

 

[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.

 

[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

 

[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

 

[cPanelPeter]

Hello,

I just posted the following sticky thread...

Warning on using mysqltuner on MySQL 5.6.