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

This article will explain in detail how to use Mysql profile for you. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

How to discover the cost of statements through profile

Construct the scene:

Open a reply and execute the statement

Lock table film_text read

Open another reply and execute the following statement:

Root@sakila 07:51:14 > show variables like'% profil%'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_profiling | YES |

| | profiling | OFF |

| | profiling_history_size | 15 |

+-+ +

3 rows in set (0.01sec)

Root@sakila 07:52:38 > set profiling=1

Query OK, 0 rows affected, 1 warning (0.00 sec)

Root@sakila 07:52:45 > update film_text set title='test' where film_id=1003

Query OK, 0 rows affected (11.02 sec)

Rows matched: 1 Changed: 0 Warnings: 0

Root@sakila 07:53:18 > show profiles

+-- +

| | Query_ID | Duration | Query | |

+-- +

| | 1 | 11.02395150 | update film_text set title='test' where film_id=1003 |

+-- +

1 row in set, 1 warning (0.01sec)

Root@sakila 07:53:26 > show profile for query 1

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000793 | |

| | checking permissions | 0.000032 | |

| | Opening tables | 0.000087 | |

| | init | 0.000026 | |

| | System lock | 0.000025 | |

| | Waiting for table level lock | 11.018648 | |

| | System lock | 0.000770 | |

| | updating | 0.002947 | |

| | end | 0.000077 | |

| | query end | 0.000069 | |

| | closing tables | 0.000062 | |

| | freeing items | 0.000253 | |

| | cleaning up | 0.000163 | |

+-+ +

13 rows in set, 1 warning (0.00 sec)

You can see that the cost is in this waiting table lock.

It seems that show profiles can only show the sql statements executed by this reply, even if global profiling=1 is set.

So if you want to tune a set of sql, put the set of sql into a reply, then execute show profiles to see the execution time of each statement, and further analyze where the overhead is.

This is the end of the article on "how to use Mysql profile". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.