[MySQL]: Purpose of Profiler in MySQL
MySQL Profiler:
The SQL Profiler is built into the database server and can be dynamically enabled/disabled via the MySQL client utility. To begin profiling one or more SQL queries, simply issue the following command:
mysql> set profiling=1;
Two things happen once you issue this command. First, any query you issue from this point on will be traced by the server with various performance diagnostics being created and attached to each distinct query. Second, a memory table named profiling is created in the INFORMATION_SCHEMA database for your particular session (not viewable by any other MySQL session) that stores all the SQL diagnostic results. This table remains persistent until you disconnect from MySQL at which point it is destroyed.
Now, simply execute a SQL query:
mysql> SELECT COUNT(*) FROM t1 WHERE broker_id = 2;
+———-+
| count(*) |
+———-+
| 200 |
+———-+
Once the query completes, you can issue the following command to view the SQL profiles that have currently been stored for you:
mysql> show profiles;
+———-+—————–+———————————————————————–+
| Query_ID | Duration | Query |
+———-+—————–+————————————————————————+
| 0 | 0.00007300 | set profiling=1 |
| 1 | 0.00044700 | SELECT COUNT(*) FROM t1 WHERE broker_id = 2 |
+———-+—————–+————————————————————————+
You get a quick summary of all your captured SQL plus the total duration that the query took to complete. To get the same diagnostic info, you can also query the memory table that holds your statistical information:
mysql> SELECT SUM(duration) FROM information_schema.profiling WHERE query_id = 1;
+——————-+
| sum(duration) |
+——————-+
| 0.000447 |
+——————-+
You can view more detailed diagnostic info about one or more queries that you’ve profiled. The most basic command is one that lists the steps a profiled query went through to satisfy your SQL request, along with each step’s time:
mysql> show profile for query 1;
+————————+—————-+
| Status | Duration |
+————————+—————-+
| (initialization) | 0.00006300 |
| Opening tables | 0.00001400 |
| System lock | 0.00000600 |
| Table lock | 0.00001000 |
| init | 0.00002200 |
| optimizing | 0.00001100 |
| statistics | 0.00009300 |
| preparing | 0.00001700 |
| executing | 0.00000700 |
| Sending data | 0.00016800 |
| end | 0.00000700 |
| query end | 0.00000500 |
| freeing items | 0.00001200 |
| closing tables | 0.00000800 |
| logging slow query | 0.00000400 |
+————————-+—————–+