Author: Manuel Lemos
Viewers: 235
Last month viewers: 8
Categories: PHP Tutorials, Lately in PHP Podcast
Enabling the MySQL slow query log can help you figure out the slowest SQL queries to optimize first. You can do that in a production environment, but you need to take extra care with how you enable the slow query log to avoid causing harm to the application.
Read this article, watch a 2-minute video, or listen to part 2 of episode 93 of the Lately in PHP podcast to learn how to enable the MySQL slow query log in a production environment in a safe way.
In this article you can learn:
How to Improve the Speed of An Application that Uses a MySQL Database Server
1. Previous Article: Find MySQL Slow Queries by Activating the Slow Query Log
3. Next Article: Find How to Discover Which Are the Slowest Queries
Contents
Listen or download the podcast, RSS feed and subscribe in iTunes
Watch the podcast video, subscribe to the podcast YouTube channel
What was said in the podcast
Show notes
Listen or download the podcast, RSS feed and subscribe in iTunes
Introduction music obtained with permission from: http://spoti.fi/NCS
Sound effects obtained with permission from: https://www.zapsplat.com/
In iTunes, use the Subscribe to Podcast... item of the Advanced menu, and then enter the URL above to subscribe to this podcast.
Watch the podcast video
See the Lately in PHP podcast play list on YouTube and Subscribe to this channel there.
Episode 93 Part 1 Video
What was said in the podcast
Find MySQL Slow Queries by Activating the Slow Query Log in a Production Server
1. How to Find the Path of the Slow Query Log in a Production Server
There is an alternative way to show the slow query log file path. This is important because you need to monitor that file to look for queries that may be slow.
You can use this even in a production system. So if you have a real an application running, it's already in production, you can do this in your production environment. There is an alternative way to show the slow query log file path.
mlemos@development:~> mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'slow_query_log_file';" Enter password: +---------------------+--------------------------------+ | Variable_name | Value | +---------------------+--------------------------------+ | slow_query_log_file | /var/log/mysql/mysqld_slow.log | +---------------------+--------------------------------+ mlemos@development:~>
This is important because you need to monitor that file to look up for queries that may be slow. You can use this even in a production system. So if you have a real application running, it's already in production, you can do this in your production environment.
2. How to Restart a MySQL Server to Update the Slow Query Log in Secure Way
Just be careful because you only restart the database server. You may halt some process that is running. So just be careful and do not restart the server when there are some queries being executed that are important.
So what I advise is that you pause your application somehow. Enable an option that no queries will be executed during the update.
So once you change the database options and restart the server you can resume the application. It's normal to have a maintenance mode that will help you to do these changes in a smooth way.
So let's move on to the next step.
Show notes
You need to be a registered user or login to post a comment
Login Immediately with your account on:
Comments:
No comments were submitted yet.