log

MySQL Profiling

MySQL allows you to profile queries, enabling you to see whats being run on your server. They are stored in a special profile table in the INFORMATION_SCHEMA database.

You can drill right down into the query to see how much CPU time, etc was used.

To see if it is switched on,

SELECT @@PROFILING;

This will return 1 or 0 to let you know if its on.

Turn on MySQL profiling:

SET PROFILING = 1;

Show the list of queries

SHOW PROFILES;

If you want more information about how long a query took, and which parts took longer:

SHOW PROFILE FOR QUERY 1;

If you want to get uber geeky, you can drill down into certain deep parts of the query:

SHOW PROFILE CPU FOR QUERY 1;

This will show you the CPU timing, etc. You can use the following options to view different parts:

ALL, BLOCK IO, CONTEXT SWITCHES, CPU, IPC, PAGE FAULTS, SOURCE, SWAPS

I’m not sure you will ever need these, but the CPU one /might/ be helpful to see slow queries.

Read the last x lines of a log file

If like me you need to check out the PHP error log, but its bloody massive, you might want to take the last few lines of it.

tail -x log_file > new_log

That will take the last x number of lines from log_file and save into new_log

tail -10000 /var/log/httpd/error_log > ~/short_error_log

The above example will take the last 10,000 lines of the Apache error log, and save it into a new file ‘short_error_log’ in your home folder.

Logging MySQL Queries

I often set up mysql query logging when I am diagnosing a problem.

In the my.cnf file, enter (or update the log parameter)

log=/path/to/logfile.log

Remember to restart the mysql service:

sudo service mysql restart (ubuntu)
sudo service mysqld restart (centos)

And, ensure that the log file has the correct permissions for mysql to write to it.

After that you can tail the file to see what’s going on:

tail -f /path/to/logfile.log