MySQL performance is slow. How to improve it?

Symptoms

• MySQL server consuming too much CPU time.

• Websites working with MySQL database are slow.

• Plesk and websites that do not use MySQL are working fast.

• If login to client's database and open site in a browser the following query will show that query is being executed and slow mysql response(Time column):

  mysql> SHOW FULL PROCESSLIST;
  +-----+---------+-----------------+-----------+---------+------+--------------+-------------------------------------------------------------------------+
  | Id  | User    | Host            | db        | Command | Time | State        | Info                                                                    |
  +-----+---------+-----------------+-----------+---------+------+--------------+-------------------------------------------------------------------------+
  | 829 | USERNAME| localhost:56224 | NEEDED_DB | Query   |    0 | NULL         | SHOW FULL PROCESSLIST                                                   |
  | 832 | USERNAME| localhost:56228 | NEEDED_DB | Query   |  112 | Sending data | SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' |
  +-----+---------+-----------------+-----------+---------+------+--------------+-------------------------------------------------------------------------+
  

Manual attempt to execute this query shows the same result - slow mysql response:

    mysql> SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
    238 rows in set (95.27 sec)

Other queries are also slow:

    mysql> SELECT * FROM wp_example;
    140 rows in set (25.16 sec)

• Database dump takes too much time.

Cause

MySQL instance is not tuned for optimal performance.

Resolution

1. Analyze and optimize all tables in all databases:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -uadmin --optimize --all-databases
   

2. Use mtop MySQL monitoring tool in Linux servers in order to find the queries which are taking the most amount of time to complete. Also the utility has 'zooming' in on a process and show the complete query, 'explaining' the query optimizer information for a query and 'killing' queries. In addition, server performance statistics, configuration information, and tuning tips also are provided by utility.

3. Install and run mysqltuner utility, it will analyze current MySQL server instance and will recommend you to adjust appropriate parameters in MySQL config file.

4. Linux version is available at: http://mysqltuner.com
5. Windows version is available at: https://mysqltuner.codeplex.com

In addition, general recommendations for MySQL settings are:- innodb_buffer_pool_size - should have size great or equal to the sum of all InnoDB databases. Tune the innodb_buffer_pool_size as large as possible without using swap when the system is running the production workload.

• query_cache_type = 1 - enable SQL queries cache, it will increase a performance especially on a WordPress sites. Query cache is invalidated for an entire table even if any value is modified.

• query_cache_size - the amount of memory allocated for caching query results. By default, the query cache is disabled.

• query_cache_limit - do not cache results that are larger than this number.