Tune mysql for better performance

Increase mysql performance with the tool from mysqltuner.com

1. SSH into your server. Make sure you have perl installed already.
2. Run the command to download the mysqltuner script

wget http://mysqltuner.pl/ -O mysqltuner.pl

3. Run the code

perl mysqltuner.pl

Here is a sample out put

root@server [~]# perl mysqltuner.pl

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.37-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 16M (Tables: 243)
[--] Data in InnoDB tables: 6M (Tables: 170)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 16

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned


-------- Performance Metrics -------------------------------------------------
[--] Up for: 17h 22m 37s (81K q [1.308 qps], 7K conn, TX: 124M, RX: 11M)
[--] Reads / Writes: 89% / 11%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (3% of installed RAM)
[OK] Slow queries: 0% (0/81K)
[!!] Highest connection usage: 100%  (152/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/3.0M
[OK] Key buffer hit rate: 99.3% (346K cached / 2K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 11K sorts)
[!!] Temporary tables created on disk: 37% (4K on disk / 11K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 20% (400 open / 1K opened)
[OK] Open file limit used: 47% (489/1K)
[OK] Table locks acquired immediately: 100% (56K immediate / 56K locks)
[!!] Connections aborted: 14%
[OK] InnoDB buffer pool / data size: 128.0M/6.2M
[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
    Reduce or eliminate persistent connections to reduce connection usage
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Your applications are not closing MySQL connections properly
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)

root@server [~]#

4. At the very end, the script gives you recommendations.
5. Next, edit your my.cnf file and add in the parameters. For centOS, it’s located in the folder “etc”.

# vi /etc/my.cnf

Add in the settings:

max_connections = 1000
wait_timeout = 20000
interactive_timeout = 20000
query_cache_size = 32M
tmp_table_size = 512M
max_heap_table_size = 512M
thread_cache_size = 64

We ran it again the following day and added in these two:

query_cache_limit = 1024M
table_open_cache = 512M

Save and exit the file

6. Restart mysql.

# service mysql restart

7. Wait a day and run the perl script again to see what other changes needs adjustment.

admin has written 38 articles