query

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.

MySQL Query Profiling

This inbuilt part of MySQL allows you to look closely at queries run – per session. And you can use this information to find bottlenecks. To enable profiling, run this command in the MySQL client:

SET profiling = 1;

This will turn on the profiling. Then for each query you run, MySQL will log all the queries and extra information about them.

Run yourself some queries, and then run this command:

SHOW PROFILES;

It will show you a standard MySQL recordset of the queries you have run, each with an individual ID, in the order that you ran them.

+----------+-------------+--------------------------------------------------------+
| Query_ID | Duration    | Query                                                  |
+----------+-------------+--------------------------------------------------------+
|        1 |  0.00009260 | SELECT fieldname FROM table WHERE field = 'value'      |
+----------+-------------+--------------------------------------------------------+
There will be as many rows as queries that you ran. And that’s not it, you can drill down even more, to see what the duration is made up with… To drill down on a query, make a note of the Query_ID and use the following statement:
SHOW PROFILE FOR QUERY 1;
The resultset will be a list of operations that MySQL ran in order to complete the query. For example:
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000033 |
| checking query cache for query | 0.000073 |
| Opening tables                 | 0.000013 |
| System lock                    | 0.000007 |
| Table lock                     | 0.000035 |
| init                           | 0.000032 |
| optimizing                     | 0.000014 |
| statistics                     | 0.000016 |
| preparing                      | 0.000014 |
| executing                      | 0.000009 |
| Sending data                   | 0.331296 |
| end                            | 0.000016 |
| end                            | 0.000003 |
| query end                      | 0.000005 |
| storing result in query cache  | 0.000105 |
| freeing items                  | 0.000012 |
| closing tables                 | 0.000007 |
| logging slow query             | 0.000003 |
| logging slow query             | 0.000048 |
| cleaning up                    | 0.000006 |
+--------------------------------+----------+
20 rows in set (0.00 sec)
So you can see all the different operations performed by the server for that one query. This is invaluable in finding problems I would say in slow queries and bottlenecks in the database.
There is also a CPU profile you can use to see the CPU’s timings, and to do this, you just add on the CPU keyword:
SHOW PROFILE CPU FOR QUERY 1;
There are others you can use, MEMORY, BLOCK IO, SWAPS, etc. They are all available in the MySQL reference for the SHOW PROFILE syntax, http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html
Profiling was introduced in MySQL version 5.0.37 Community Server.