monline

A properly tuned MySQL database with a well written application should not experience severe locking (ie. a query locks some tables and other queries have to wait til the locking query finishes). To check whether you've locking problems you should do the following ...

First enable slow query logging on your MySQL server. This log contains the query execution time and the locking time as well. The query execution time includes the locking time too, so the Query_time value will always be larger or equal than the Lock_time value. Use the following configuration parameters in your my.cnf MySQL config file to set up slow query logging:

• log_slow_queries: the path to the slow query log
• long_query_time: the number of seconds as a lower bound on Query_time for including queries in the slow query log
• log-queries-not-using-indexes: this parameter takes no argument. If specified, the server will log queries that do not use indexes too.

On a Debian machine the slow query log is automatically rotated, at least if you use the default path (/var/log/mysql/mysql-slow.log). You can set the rotation parameters in /etc/logrotate.d/mysql-server. By default it rotates daily and keeps 7 logs.

Now wait a day or two (so you've some logs to work with) and execute the following command:

This will go through all your slow query logs and for each file it will print the filename and the count of queries that got locked for more than 1 second. Usually it's not a huge problem if you get a few locks of 1s, but a large number of higher lock_time values means there's some problem, something is not optimal. Using this method you can also easily determine if a change (a new index or some code modification) resolved your problem, since you'll see a significant drop in the count printed by the command.

P.S.: of course some applications might use long locks on purpose. The above method might come handy to detect unintentional locking in your database.
P.S.2: zgrep is a shell script (wrapper) for grep. It automatically decompresses the files/input before feeding to grep. On Debian this script is part of the gzip package.