PHP Classes

How to Find MySQL Slow Queries in a Production Server by Activating the Slow Query Log - 2 minutes - Lately in PHP Podcast Episode 93 Part 2

Recommend this page to a friend!
  Blog PHP Classes blog   RSS 1.0 feed RSS 2.0 feed   Blog How to Find MySQL Slo...   Post a comment Post a comment   See comments See comments (0)   Trackbacks (0)  

Author:

Viewers: 235

Last month viewers: 8

Categories: PHP Tutorials, Lately in PHP Podcast

When you want to optimize a MySQL database application in production, you should figure out the slowest queries in that environment.

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.




Loaded Article

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

Click on the Play button to listen now.

Introduction music obtained with permission from: http://spoti.fi/NCS

View Podcast in iTunes

Listen on Spotify
Listen on Spotify


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

Play 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.



  Blog PHP Classes blog   RSS 1.0 feed RSS 2.0 feed   Blog How to Find MySQL Slo...   Post a comment Post a comment   See comments See comments (0)   Trackbacks (0)