Profiling

Profiling permits you to benchmark information that indicates resource usages during a session. This is used when we want to get information on a specified query. Here are the types of information:

• Block I/O
• Context switches
• CPU
• IPC
• Memory
• Page faults
• Source
• Swaps
• All

First of all, you need to know that profiling on a production server is not recommended because of the performance degradation it can cause.

To enable profiling, use the following command:

MariaDB [none]> SET PROFILING=1;

Perform all the query tasks you want to profile and then list them:

MariaDB [none]> SHOW PROFILES;
+----------+------------+-------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------+
| 1 | 0.30798532 | select * from s_explain |
| 2 | 0.25341312 | select * from s_explain |
+----------+------------+-------------------------+

In the preceding command-line output, you can see that we've two query IDs. To get information related to the first Query_ID, with extra columns for the CPU, use the following command:

MariaDB [none]> SHOW PROFILE CPU FOR QUERY 1;
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting | 0.000034 | 0.000000 | 0.000000 |
| Waiting for query cache lock | 0.000009 | 0.000000 | 0.000000 |
| init | 0.000008 | 0.000000 | 0.000000 |
[...]
| init | 0.000016 | 0.000000 | 0.000000 |
| optimizing | 0.000011 | 0.000000 | 0.000000 |
| statistics | 0.000050 | 0.000000 | 0.000000 |
| preparing | 0.000017 | 0.000000 | 0.000000 |
| executing | 0.000008 | 0.000000 | 0.000000 |
| Sending data | 0.007369 | 0.004001 | 0.000000 |
| Waiting for query cache lock | 0.000020 | 0.000000 | 0.000000 |
| Sending data | 0.003420 | 0.004000 | 0.000000 |
[...]
| Sending data | 0.271156 | 0.272017 | 0.000000 |
| end | 0.000020 | 0.000000 | 0.000000 |
| query end | 0.000010 | 0.000000 | 0.000000 |
| closing tables | 0.000015 | 0.000000 | 0.000000 |
| freeing items | 0.000009 | 0.000000 | 0.000000 |
| updating status | 0.000041 | 0.000000 | 0.000000 |
| cleaning up | 0.000029 | 0.000000 | 0.000000 |
+--------------------------------+----------+----------+------------+

You will find a lot of interesting information in the preceding command-line output. Here is an overview:

• init: This gives information of the starting process for the storage engine
• optimizing: This gives the query plan information as given in the slow query logs
• statistics: This shows the engine locking and optimization
• executing: This shows the execution time (as in Query_plan)

In the preceding command line, we've just specified the CPU type and got all the extra columns related to it. If we want maximum information, replace CPU with ALL.

So, now you're able to compare multiple requests, see their evolution, and track the used resources with them.