Introduction
For applications you are building where you've designed your own database schema, it is common to need to debug performance issues when databases grow very large. Often due to a lack of indexes, queries that were extremely fast when a database table had only ten thousand rows will become quite slow when the table has millions of rows.
The MySQL slow query log is where the MySQL database server registers all queries that exceed a given threshold of execution time. This can often be a good starting place to see which queries are slowest and how often they are slow.
You will need to understand the details of your application to determine whether a logged query is of any concern. For example, a query that takes 20 seconds to run but is only run once a month by an administrative script is probably not a source of concern. A query that takes 2 seconds but runs thousands of times an hour as a result of users interacting with your application may be a concern.
Reading the MySQL Slow Query Log
The slow query log is located at:
/var/log/mysql/mysql-slow.log
For security reasons, you can only access this file as root using sudo. You will not be able to read that file when SSH'd in as the user sellcloud.
Entries in the slow log file look like this:
# Time: 140905 6:33:11 # User@Host: dbuser[dbname] @ hostname [1.2.3.4] # Query_time: 0.116250 Lock_time: 0.000035 Rows_sent: 0 Rows_examined: 20878 use dbname; SET timestamp=1409898791; ...SLOW QUERY HERE...
Using mysqldumpslow
Rather than reading the slow query log directly, it is often better to use the command mysqldumpslow to parse and summarize the slow query log.
You can run the command like this while SSH'd in as root:
sudo -i mysqldumpslow /var/log/mysql/mysql-slow.log
See the mysqldumpslow documentation for more information.
Alert: For Control Panel Help & Tutorials, click here: Panel Tutorials
