Database special: MySQL Slow Query Log
There are two different approaches to monitoring your applications. From the outside, in, using a monitoring service like Scout or New Relic. Another way is from the inside out. MySQL has a very convenient tool called the Slow Query Log.
dev.mysql.com states, “The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined.”
You set the variables and then mysql keeps logs on any queries that meet or exceed those parameters.
By default, the slow query logs are disabled. To enable the logs, you need to insert a command into the my.cnf file. On a VPS the path is usually /etc/my.cnf
In the mysql section (labeled [mysqld] add you choice of slow query logging. e.g.
long_query_time=2 #sets (in seconds) what counts as a long query time. (default is 10)
log-slow-queries=/var/log/mysql/log-slow-queries.log #to state where you would like to place the file. If unspecified, The default is (host_name)-slow.log.
you can also monitor unindexed queries or admin statements:
log_queries_not_using_indexes
log_slow_admin_statements
If you are going to monitor all non-indexed queries, you might want to limit the rate that they are put in the logs (as there can often be quite a few).
log_throttle_not_using_indexes=12 #limits the number of logged non-indexed queries per minute to 12 (or whatever number you want). The default is 0 meaning there is no limit and will record each one.
Logging non-indexed queries can be useful in order to determine if a table column starts becoming queried often and/or bogs down and should be indexed.
Make sure you create the folder and file for the slow log to write to:
=> touch var/log/mysql/slow-queries.log
IMPORTANT NOTICE: MySQL will not start using these new commands unless you restart it:
=> service mysql restart
wait for some requests to ping your server and query your database and then open up the logfile to check for and slow query logs.
/root/myapp/var/log/mysql/slow-queries.log, Version: 3.23.54-log, started with:Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument # Time: 032216 15:03:33 # User@Host: ghudson[ghudson] @ localhost.localdomain [127.0.0.1] # Query_time: 13 Lock_time: 0 Rows_sent: 117 Rows_examined: 234 use wsdb; SELECT * FROM users WHERE created_at>'2016-03-10 00:00:00';
It’s up to you at this point to troubleshoot why a query is slow. Is the column indexed (if this isn’t a frequently used query, don’t automatically jump to indexing it)? Is the table deep (has many rows) or wide (many columns)? Are you not allocating enough memory to the SQL server? Are you creating unnecessary many to many joins?
















