Admin-Ahead Community

Linux => General Linux => Topic started by: nidhinjo on March 17, 2018, 07:45:44 pm

Title: How to use the "Mysql tuner" in order to Tune the MySQL performance
Post by: nidhinjo on March 17, 2018, 07:45:44 pm

MYSQL Tuner:-  It is used to analyzes the MYSQL performance based on the statistics. This will collect and show an idea about the parameters need to be changed/fixed in the my.cnf file to increase the MYSQL performance. Please follow below steps to download and install and use the MySQL Tuner,

1.) Download Mysql Tuner script as follows:

Code: [Select]

# wget http://mysqltuner.com/mysqltuner.pl
2.) Then, make the script executable by running

Code: [Select]

# chmod +x mysqltuner.pl
3.) Run the script

Code: [Select]

# ./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 easily optimize the MySQL performance on the server