How to use show profiles to analyze sql performance in mysql
This article mainly introduces how to use show profiles to analyze sql performance in mysql, which has a certain reference value, and interested friends can refer to it. I hope you can learn a lot after reading this article.
Show profiles was added after 5.0.37, and to use this feature, make sure the version is later than 5.0.37.
Check my version.
> Select version ()
+-+
| | version () |
+-+
| | 5.0.82-community-nt |
+-+
Www.2cto.com
1 row in set (0.00 sec)
The version supports the show profiles feature. Next, enter the world of mysql performance tracking diagnostics.
Check to see if the profiles function is enabled. It is off by default.
Mysql > use test
Database changed
Mysql > show profiles
Empty set (0.00 sec)
The display is empty, indicating that the profiles feature is turned off. Open below
Mysql > set profiling=1
Query OK, 0 rows affected (0.00 sec)
Execute the following query
Www.2cto.com
Mysql > explain select distinct player_idfrom task limit 20
Mysql > select distinct player_id from task
Then execute show profiles
Mysql > show profiles
+-- +
| | Query_ID | Duration | Query | |
+-- +
| | 1 | 0.00035225 | explain select distinct player_id from task limit 20 |
| | 2 | 1.91772775 | select distinct player_id from task |
+-- +
At this point, you can see that it took 1.91772775 seconds to execute select distinct player_id from task
It takes time to view the details of a query according to query_id
Mysql > show profile for query 2
Www.2cto.com
+-+ +
| | Status | Duration |
+-+ +
| | starting | 0.000052 | |
| | Opening tables | 0.000009 | |
| | System lock | 0.000003 | |
| | Table lock | 0.000007 | |
| | init | 0.000013 | |
| | optimizing | 0.000003 | |
| | statistics | 0.000009 | |
| | preparing | 0.000008 | |
| | Creating tmp table | 0.000074 | |
| | executing | 0.000002 | |
| | Copying to tmp table | 1.916551 | |
Www.2cto.com
| | Sending data | 0.000667 | |
| | end | 0.000004 | |
| | removing tmp table | 0.000065 | |
| | end | 0.000002 | |
| | end | 0.000002 | |
| | query end | 0.000003 | |
| | freeing items | 0.000245 | |
| | closing tables | 0.000006 | |
| | logging slow query | 0.000002 | |
| | cleaning up | 0.000003 | |
+-+ +
You can see that the red font part takes a lot of time because temporary tables are used for distinct viewing
So can you check the occupation of cpu, io and other information?
Mysql > show profile block io,cpu for query2
+-
-+
| | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block |
_ ops_out |
+-
Www.2cto.com
-+
| | starting | 0.000052 | NULL | NULL | NULL | |
NULL |
| | Opening tables | 0.000009 | NULL | NULL | NULL | |
NULL |
| | System lock | 0.000003 | NULL | NULL | NULL | |
NULL |
| | Table lock | 0.000007 | NULL | NULL | NULL | |
NULL |
| | init | 0.000013 | NULL | NULL | NULL | |
NULL |
| | optimizing | 0.000003 | NULL | NULL | NULL | |
NULL |
| | statistics | 0.000009 | NULL | NULL | NULL | |
NULL | www.2cto.com
| | preparing | 0.000008 | NULL | NULL | NULL | |
NULL |
| | Creating tmp table | 0.000074 | NULL | NULL | NULL | |
NULL |
| | executing | 0.000002 | NULL | NULL | NULL | |
NULL |
| | Copying to tmp table | 1.916551 | NULL | NULL | NULL | |
NULL |
| | Sending data | 0.000667 | NULL | NULL | NULL | |
NULL |
| | end | 0.000004 | NULL | NULL | NULL | |
NULL |
| | removing tmp table | 0.000065 | NULL | NULL | NULL | |
NULL |
| | end | 0.000002 | NULL | NULL | NULL | |
NULL |
| | end | 0.000002 | NULL | NULL | NULL | |
NULL |
| | query end | 0.000003 | NULL | NULL | NULL | |
NULL |
| | freeing items | 0.000245 | NULL | NULL | NULL | |
NULL |
| | closing tables | 0.000006 | NULL | NULL | NULL | |
NULL |
Www.2cto.com
| | logging slow query | 0.000002 | NULL | NULL | NULL | |
NULL |
| | cleaning up | 0.000003 | NULL | NULL | NULL | |
NULL |
+-
In addition, you can see information such as memory,swaps,context switches,source.
Thank you for reading this article carefully. I hope the article "how to use show profiles to analyze sql performance in mysql" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!
