profile

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.