Tweak MySQL using MySQLTuner

MySQLTuner is used to analyze the MySQL configuration and we can tweak it based on the suggestions it provides. We need to install and run the script and it will show the exact parameters needed to be changed inside the 'my.cnf' file to improve the performance. Here are the steps for the download and install:

  1. Download the Tuner Script:
    # wget http://mysqltuner.com/mysqltuner.pl
  2. Make the script executable:
    # chmod +x mysqltuner.pl
  3. Run the script:
    # ./mysqltuner.pl

    It will show a result like below:

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 (>= 16M)

    sort_buffer_size (> 16M)

    read_rnd_buffer_size (> 256K)

    tmp_table_size (> 25M)

    max_heap_table_size (> 20M)

    thread_cache_size (start at 4)

    table_cache (> 64)

    innodb_buffer_pool_size (>= 35M)

You need to check on those suggestions and make the changes inside the 'my.cnf' file. Better to take a backup of the old configuration before proceeding. Once done, please restart the 'mysql' service as well and see if the performance is improved. 

  • tweak mysql, mysqltuner
  • 0 Els usuaris han Trobat Això Útil
Ha estat útil la resposta?

Articles Relacionats

How To Install and Use Docker on Ubuntu 16.04

Introduction Docker is an application that makes it simple and easy to run application processes...

Change Main IP of the server :: Vesta Panel

If we are changing the main IP of a server installed with Vesta panel, we can use the following...

cPanel: Apache-FastCGI Data Timeout Error

If you are using a cPanel server and have FastCGI enabled in Apache, you might be facing the...

Domainkey installer in cPanel

You can use the following cPanel script to install domainkey for an account...

Email issues :: Common Troubleshooting Steps

Here I will discuss some common errors and their solution: We are using a mail relay setup for...