• MySQL tuning performance
  • Check MySQL config value
  • Debug MySQL slow query log
  • Run query without cache
    • Method 1: Set following config in my.cnf to:
    • Method 2: Inject NO-CACHE clause when running query:


#MySQL tuning performance

In this tutorial, we are going to show you some techniques for tuning MySQL performance.

#Check MySQL config value

First thing first, we need to know how to check MySQL config value from a console to perform various tasks below.

To check MySQL config value from the console, we can use the command below:

mysqld --help --verbose | grep xxxx

Replace xxxx with the desired configuration name, for example, slow_query_log.

#Debug MySQL slow query log

As an application grows, its data size grows along. Sooner or later, the application's query speed will slow down. This is when we can use the procedures below to debug a slow query from MySQL.

• Login with root access:

mysql -u root -p

• Select the target database:

use my_database

• Enable slow query log:

SET GLOBAL slow_query_log = 'ON';

• Set log time in seconds, X here will limit the engine to log query exceeding the 'X' seconds:


SET GLOBAL long_query_time = X;

• Check log location, by running the command below, it will show you the location of the log file:

show variables like '%slow%’;

• Test out the log by running the following in MySQL console:

SELECT SLEEP(X);

• Check out the log file, it should log the above command in the log file.

Now you are ready to debug slow queries from your application. And when the debugging is done, make sure you turn off the slow query log by following the steps below:

• Turn it off after debugging:

SET GLOBAL slow_query_log = 'OFF';

• Verify to make sure the slow query log is turned off:

show variables like '%slow%’;

You will not want to leave the log open as it will result in a performance issue.

#Run query without cache

MySQL cache query results to improve its performance. We want to turn this cache off when debugging an application.

You can use the methods below to turn its cache off:

#Method 1: Set following config in my.cnf to:

query_cache_limit = 0;
query_cache_size = 0;

#Method 2: Inject NO-CACHE clause when running query:


The trick here is the keyword SQL_NO_CACHE when running a query.

select SQL_NO_CACHE * from mytable where 1 limit 1;