Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.