MySql Query Profiler
MySql is an open source database and till very late it was not very popular database. Recently when open source software picked up in popularity, MySql emerged as first choice for open source database. Features and tools for MySql evolved from very basic set and in present it has very rich set of feature and tools.
In this post, I am trying to give an introduction to MySql query profiler which can be used to check performance of SQL query and tune it accordingly. You can use profiler only if you are using MySQL 5.0.37 or higher. I am trying to list some of the steps to use mysql profiler and how i made use of it.
1. Enable profiling: Profiler can be enabled/disabled dynamically. use following command to enable it.
mysql> SET profiling=1;
2. Run your query : Once profiler is ON, it will track all the queries which are executed in that session and will keep their statistics. You can run your query and check its statistics:
mysql> SELECT count(*) FROM books WHERE auther IN ('a','b','c'); +----------+ | count(*) | +----------+ | 3517 | +----------+
3. Check profiles: Check the profiles which are stored for queries run after enabling profiler.
mysql> SHOW profiles; +----------+------------+-----------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------+ | 0 | 0.00007300 | SET profiling=1 | | 1 | 0.00044700 | SELECT count(*) FROM books WHERE auther IN ('a','b','c') | +----------+------------+-----------------------------------------------+
4. Check statistics:
mysql> SHOW profile FOR query 1; +----------------------+----------+ | STATUS | Duration | +----------------------+----------+ | starting | 0.041406 | | checking permissions | 0.000036 | | Opening TABLES | 1.854385 | | System LOCK | 0.000012 | | TABLE LOCK | 0.000017 | | init | 0.006021 | | optimizing | 0.002854 | | statistics | 0.006014 | | preparing | 0.006364 | | executing | 0.007496 | | Sending DATA | 0.690880 | | end | 0.000015 | | query end | 0.000010 | | freeing items | 0.000099 | | logging slow query | 0.000005 | | cleaning up | 0.000008 | +----------------------+----------+
You can check the area of improvement from above statistics. See more at following link
http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
No comments yet.
Leave a comment