MySQL Tuning

This article deals with some of the steps to tune MySQL performance and scalability in multi-user environments. In today’s IT, databases are playing an important role in storing our valuable data.  It is  important for applications using databases to read and write into the database as fast as possible to provide good performance.

MySQL installed without any tweaking or tuning may be inefficient to provide high performance. Performance of databases can also depend on the hardware or your application itself. But we can try out tweaking of database as a first step to improve performance of our application.

Finding the trouble makers:

Logs provide us with the information about system performance and user access patterns, and mySQL has different kind of logs. Here we are looking into “slow query log” which logs details of queries that have exceeded a specified threshold execution time. We need to enable slow query log to identify slow running queries on the server.

Enabling Slow Query Log:

Open MySQL Configuration file ( /etc/my.cnf ) with your favourite editor (vi,nano,…) and add the following statements to it.


========================================


[mysqld]
slow_query_log=/var/log/slow_query.log
long_query_time=1


=========================================

long_query_time refers to the threshold time beyond which queries will be logged as slow, and thus controls the extend of logging. Make sure that the /var/log/slow_query.log, exists on the server. If it doesn’t create this file , then restart MySQL server.

If there are slow queries running on the server, the detailed information about them will be logged into the slow_query_log. The following information will be displayed for each and every query that is logged.


====================


Query Execution Time
Lock Time
Total rows examined
Total rows sent


=====================

# Time: 151500 14:30:24
# User@Host: root[root]@localhost[]
# Query_time: 7.587645 Lock_time: 0.053456 Rows_sent: 75284 Rows_examined: 75284
SET timestamp=1712789086;

As the information in the slow_query_log is too long, we may need some tool that can summarize this information. “mysqldumpslow” is one such utility that comes bundled with the MySQL, which summarizes the results, grouping queries based on similarity, and excluding differences in the values of number of string data

mysqldumpslow [options][log_file….]

We need to summarise the log, extracting the top 5 slow queries based on total execution time. (  Total execution time = Avg. Query execution time * No of times query was logged )

mysqldumpslow -s t -t 5 /var/log/slow_query.log

Few lines from the output :


Reading mysql slow query log from /var/log/slow_query.log
Count: 1348 Time=5.34s (5374s) Lock=0.25s (246s) Rows=75284.0, root[root]@localhost

-s – defines the sort order, and sorting can happen with six different ammeters.
t – indicates the total execution time on which we are sorting

Store the worst-performing queries in terms of the execution time in any file ( say test.txt) for further analysis.  Once we have this we need to tune these queries individually.  We need to run these queries through a profile that will list the break-up query execution time, and indicate the hot spots.

(to be continued ….)

Leave a Reply