Tuesday, March 19, 2013

Tuning Mysql Performance with Mysql tuner

Tuning Mysql Performance with Mysql tuner

MYSQL Tuner :-  It is a perl script that analyzes the MYSQL performance and based on the statistics , it gathers and give us an idea about the parameters need to be changed in the my.cnf file to increase the MYSQL performance.

Download Mysql Tuner script as follows:

# wget http://mysqltuner.com/mysqltuner.pl

Then, make the script executable by running

# chmod +x mysqltuner.pl

Run the script

# ./mysqltuner.pl

This is how you can run the mysql tuner script. Then, you will obtain the output as:


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
    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
    Increase table_cache gradually to avoid file descriptor limits
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_size (>= 8M)
    sort_buffer_size (> 8M)
    read_rnd_buffer_size (> 256K)
    tmp_table_size (> 20M)
    max_heap_table_size (> 20M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 29M)


By adjusting the parameters listed in " Variables to adjust ", we can increase the performance of mysql

No comments:

Post a Comment