• Call A Developer Google+
  • Like Us on Facebook
  • @calladeveloper on Twitter
  • Subscribe to our Blog
  • Skype: calladeveloper
  • Call us on Gtalk

Saturday, March 23, 2013

Enabling the Slow Query Log in MySQL without service restart - Database Optimization Basics

This is the first article in a series on MySQL Database Optimization Basics. Subscribe to be notified when new articles are added!

Today we're going to cover how to log slow queries in mysql so that you can identify problems and refactor the correct queries in order to improve the performance of your website or application. Slow queries can cause errors when people use your website or application and having them will make your code difficult to scale. The first step toward fixing them is seeing which queries are slow in your code, so let's start by learning how to enable slow query logging without restarting the mysql service:

How to enable slow query log in MySQL without restarting mysqld

In MySQL 5.1.12 and later, this is fairly straightforward and can be done without restarting mysql as follows:

*Note: this requires command line access over SSH as root, so it is only applicable to VPS and Dedicated Servers. I am working on a Centos 5 VPS with cPanel, but this will work on most Linux and UNIX versions (paths may be different).

root in [~]# touch /var/log/slow
root in [~]# chown mysql:mysql /var/log/slow
root in [~]# mysql -e 'SET GLOBAL slow_query_log=1;'
root in [~]# mysql -e 'SET GLOBAL slow_query_log_file="/var/log/slow";'
root in [~]# mysql -e 'SET GLOBAL long_query_time=2;'

If you want to leave slow query logging on (recommended), you will need to add 3 lines to my.cnf under the [mysqld] section:


To test if it is working correctly (you should test after changing things, I promise it's a good idea) - simply run a SELECT SLEEP(x) query where x is longer than the long_query_time you have set - the query should be logged to the file you specified for slow_query_log_file (as long as it exists and the mysql user can access it).

# mysql -e 'SELECT SLEEP(5);'

Note: you don't have to have any databases or tables to use SLEEP().

Here is what it looks like if everything worked correctly. If not, make sure that the log file you specified exists and mysql user has access, and also that your GLOBALs are set correctly.

Enable MySQL Slow Query Logging for Optimization and Tuning

Here is a summary of all of the different ways to turn on slow query logging in various versions of MySQL:

  • Before 5.1.6, start mysqld with the --log-slow-queries[=file_name] option.
  • In versions after MySQL 5.1.6, you can log a file or a table, or both. Start mysqld with the --log-slow-queries[=file_name] option and optionally use --log-output to specify the log destination.
  • After MySQL 5.1.12, use --slow_query_log[={0|1}] - 1 is on and the default slow query log file name is used.
  • After MySQL 5.1.29, use --slow_query_log[={0|1}] - the --log-slow-queries option is deprecated.
  • When using slow_query_log=1 (either in my.cnf or starting with mysqld --slow_query_log=1) use slow_query_log_file=/your/log/file or just leave it as the default ($hostname-slow.log in the mysql data folder).

More info about slow query logging at the official MySQL Reference.

If you want to enable slow query logging without restarting mysql on MySQL 5.0, you can - use this patch

Need help with MySQL Performance Tuning or Query Optimization? Call A Developer.

Peter Stolmar is a Linux Systems Administrator and technology optimization specialist. He can help you fix websites and apps, or make then run faster and scale better. Need help with some tech stuff? Find Peter Stolmar on Google+.


  1. This comment has been removed by the author.

    1. IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Projects for CSE It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

      JavaScript Online Training in India

      JavaScript Training in India

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

  2. Hi Peter
    I just wonder if I set log-output=TABLE, what would the table name be by default?

  3. This comment has been removed by the author.

  4. Thanks for sharing! This page was very informative and I enjoyed itwordpress developer company.
    Wordpress Developer

  5. I am always searching online for articles that can help me to improve my knowledge. There is clearly a lot to know about MySQL.I think you made some good points in Features also.By the way you are running a great blog. So, please keep on update like this post.
    Engineering Colleges in Chennai, Mechanical Engineering Colleges in Chennai

  6. I am a regular user of your post, this one also was very interesting and well written. keep sharing the great work
    Web Designing Company Bangalore | Website Designing Company Bangalore

  7. Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way.Thank you so much

    Installment Loans Near Me
    Title loans Near Me
    Cash Advances Near Me

  8. Web Design Sydney: It is a great sharing...I am very much pleased with the contents you have mentioned. I wanted to thank you for this great article.Logo Design Sydney

  9. Netguru Solution India Pvt. Ltd. is one of the leading and developing Website Design and Mobile Application Development Company in Pune. We make website design and development simple by providing answers to address your issues. With our group’s joined involvement in visual communication, site advancement, search engine optimization and activity, you can be sure that whatever your venture requests, our web engineers can convey on time. We offer Website Design, Web Development, Mobile Application Development, Android App Development, iOS App Development, SEO, Internet Marketing, Web Maintenance and Hosting, Web Branding and Vector Art Solutions.
    Website Design Company In Pune
    Web Design Company In Pune
    Website Development Company In Pune
    Mobile Application Development Company In Pune
    Android App Development Company In Pune
    iOS App Development Company In Pune

  10. Excellent comments with interesting topics and very useful resources. Thanks for sharing...
    Top web development services in bangalore
    ECommerce Website developers in bangalore

  11. oh my god... i got a great information from this blogger....

    this is very needful information...thanks for sharing the great article..

    Web Design Company Bangalore
    Website development Company Bangalore

  12. Shraddha IT solutions is professional best website designing and development software company in pune providing top services like Branding Logo Design , Brochure designer , Mobile app development , ecommerce web development web hosting , cms , best web development company in pune that boost your business in pune ,usa ,India.

  13. Hi,

    Thanks for sharing a very interesting article about Enabling the Slow Query Log in MySQL without service restart - Database Optimization Basics. This is very useful information for online blog review readers. Keep it up such a great article like this.

    Best Web Design Company in Bangalore

  14. Really a great post. Appreciate the effort in educating us. We are also same service provides in Bangalore.
    Web Design Company in Bangalore
    Website Designers in Bangalore
    Website Development Company in Bangalore

  15. Hey Nice Blog!! Thanks For Sharing!!!Wonderful blog & good post.Its really helpful for me, waiting for a more new post. Keep Blogging!
    SEO company in coimbatore
    SEO company
    web design company in coimbatore

  16. Thanks for an insightful post.These tips are really helpful. Thanks a lot.Keep it up.Keep blogging.!!
    software development services in lahore 2019

  17. Nice post. It is really interesting. Thanks for sharing the post!
    Buy AC Online | Smart LED TV
    Laptops for Sale | Best Inverter AC

  18. I was looking for this type of valuable content and finally I got this blog. I found it very beneficial for me. You are an excellent blogger and deserves numerous compliments. Thank you so much for sharing this wonderful information.
    Card Game Development

  19. Nice post it is really an interesting article we are also providing the web design services in bangalore. We are the leading
    Web Design Company in Bangalore
    Website Development Company in Bangalore

  20. Nice post really useful information. We are the leading website developer in dubai. Hire our website design company in dubai today for web design services in dubai

  21. Nice post it is really an interesting article.We are also providing the website design services in mumbai. We are
    best website design companies in mumbai
    best website designers in mumbai

  22. Digisol Hub is an advanced tech company, we can help you find solutions with your Website Development problems, Marketing of Social Media, development of applications (Apps), Graphic designing, Google Ads, and development of Software. Our aim is to help people solve their problems regarding digital marketing by using the right strategy and make them feel valued so they progress with their online business.
    Digisol Hub

  23. DigisolHub
    Digisol Hub is a Digital Marketing & Tech. Company, that provides solutions regarding website development, SEO, Social Media Marketing, Google Ads, and Graphics designing, App development, Software development, Website Development. We just want to solve the problems of the people by using the right digital marketing strategy and give them Value so that they can build their business in the Online world

  24. DigisolHub
    Digisol Hub has a long list of SEO and digital marketing services. Being a leading and competitive SEO agency in Lahore, we aim to deliver results and retain long-term financial relations……..read more

  25. This is a good time to make long term plans and it's timely.
    Have fun. I have read this post, and if you will excuse me, I would like to advise you on interesting topics or tips.
    You can write the following articles on this topic.
    I want to read more topics on this topic!
    english stories english short stories with moral value What is the factorial of 100

  26. Hey! This blog is more informative and user friendly
    Thank you so much for giving this information to me..

    best organic face cream
    best shampoo for hair fall control and dandruff 

  27. Need the Azure Interview Questions And Answers? Get this into the blog with Infycle Technologies for having the top interview questions and answers! Call 7504633633 or 7502633633 for having the best software development courses!