#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';