Analyzing Slow query in MySQL

Sometimes It becomes necessary to analysis the performance of application. One of the major area that need to  study for performance analysis is how our sql queries are performing in term of time. We can follow these steps for identifying the slow queries:-

1. Put entries in the mysqld section in the file /etc/my.cnf (on CentOs or Red Hat)  or /etc/mysql/my.cnf (on Debian, Ubuntu etc.)

[mysqld]
long_query_time         = 2
log-slow-queries        = /var/log/mysql-slow.log

 

This makes logging of queries taking time greater than  2 second and the slow log file as mentioned with log-slow-queries.

2.Create file /var/log/mysql-slow.log and provide the ownership same as of /var/log/mysqld.log, generally it would be mysql mysql or execute the below command

 chown mysql.mysql /var/log/mysql-slow.log

3.Restart mysql
/etc/init.d/mysqld restart

4.Check the log for no error /var/log/mysqld.log. After restarting mysql the log file should contain some tags :-> specifically the size would be > 0.

5.Use mysqldumpslow a tool from mysql that parses and summarizes the slow query log file.

By executing the below command

mysqldumpslow -s c -t 10 /var/log/mysql-slow.log

we get the top 10 queries (-t 10) sorted by the number of occurrences in the log (-s c).

Hope this information will be beneficial. Let me know if any issue is coming in doing the same.

You can leave a response, or trackback from your own site.

11 Responses to “Analyzing Slow query in MySQL”

  1. Dave Shappel says:

    would you rent ad space (for a banner ad) for my site?

  2. Mark Man says:

    Hi I was wondering if you’re up for selling your site? Please get in touch whenever you read this. Cheers. Mark

    • promignavi says:

      Good idea Mark but this makes me unhappy :-( because I like to share the knowledge and invite other people to do the same..

  3. Free wordpress themes says:

    Great blog, if your interested in free themes go to http://wpthemes4free.org

  4. totally free stuff says:

    Great blog

  5. wp themes free says:

    fantastic blog, if your interested in some free wordpress themes go to http://wpthemes4fre.org

  6. nootropic says:

    Greeting from over the world. Great article I shall return for more.

  7. rift platinum says:

    Rattling excellent info can be found on this website.

  8. Cammie Madrid says:

    Thorn of Girl…

    Superb info can be identified on this internet website….

  9. REALIZARE SITE says:

    I don’t even know the way I stopped up right here, however I thought this publish used to be great. I do not recognise who you are but certainly you are going to a well-known blogger should you are not already. Cheers!

Leave a Reply

Designed for Ares Galaxy in Collaboration with Hostgator coupons and Popular Wordpress Themes and unlock iphone 5