0

From a MariaDB 10.1 database with the profile enabled.

After making some test with an example query, why the time reports from the query doesn't match the duration time that the profiles report?

I try to know why the same query is sometimes slow.

MariaDB [db1]> select id from sol_demo where id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

MariaDB [db1]> select id from sol_demo where id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.10 sec)

MariaDB [db1]> select id from sol_demo where id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.40 sec)

MariaDB [db1]> show profiles;
+----------+------------+----------------------------------------------+
| Query_ID | Duration   | Query                                        |
+----------+------------+----------------------------------------------+
|      145 | 0.00008608 | select id from sol_demo where id = 1 |
|      146 | 0.00009588 | select id from sol_demo where id = 1 |
|      147 | 0.00011883 | select id from sol_demo where id = 1 |
|      148 | 0.00008926 | select id from sol_demo where id = 1 |
|      149 | 0.00009260 | select id from sol_demo where id = 1 |
|      150 | 0.00008944 | select id from sol_demo where id = 1 |
|      151 | 0.00008301 | select id from sol_demo where id = 1 |
|      152 | 0.00008771 | select id from sol_demo where id = 1 |
|      153 | 0.00010690 | select id from sol_demo where id = 1 |
|      154 | 0.00009748 | select id from sol_demo where id = 1 |
|      155 | 0.00008521 | select id from sol_demo where id = 1 |
|      156 | 0.00009257 | select id from sol_demo where id = 1 |
|      157 | 0.00009136 | select id from sol_demo where id = 1 |
|      158 | 0.00056927 | select id from sol_demo where id = 1 |
|      159 | 0.00008753 | select id from sol_demo where id = 1 |
+----------+------------+----------------------------------------------+
15 rows in set (0.00 sec)
2

Profile shows the (server-side) operating system resource usage by the server process, while the command-line interface reports the (client-side) statement execution elapsed time, which also includes in-client processing and the time spent sending the statement to the server for processing and receiving the results. In other words, elapsed time reported by the CLI is a superset of time measured by the "profiler". The shorter the statement execution time, the higher the proportion of the client overhead in the overall elapsed time.

See also this related question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.