PHP Classes

Find MySQL Slow Queries by Activating the Slow Query Log - 7 minutes - Lately in PHP Podcast Episode 93 Part 1

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

Author:

Viewers: 370

Last month viewers: 6

Categories: PHP Tutorials, Lately in PHP Podcast

One of the factors that may make a Web application slow is the speed of execution of database queries executed by the application back-end code.

If you optimize the database queries that are the slowest, it may make a lot of impact on the speed of an application.

The first step in optimizing the slowest database queries is to find which are the slowest queries. Using MySQL as the database server, you can find the slowest queries by activating the slow query log.

Read this article, watch a 7-minute video, or listen to part 1 of episode 93 of the Lately in PHP podcast to learn how to enable the MySQL slow query log.




Loaded Article

In this article you can learn:

How to Improve the Speed of An Application that Uses a MySQL Database Server

1. This Article: Find MySQL Slow Queries by Activating the Slow Query Log

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

Hello, welcome to the Lately in PHP podcast I'm Manuel Lemos from the PHP Classes site. And today, I'm going to present to you an episode that I hope it's interesting to you, which is about MySQL database query optimization.

As you may know, many developers not only of PHP but many languages, use MySQL SQL query database to store information about their applications and it's always interesting to make that database queries faster and for that, you need to have a good criteria not only to discover which are the SQL queries that are worth the effort of optimization and also what you can do to do a good optimization that really works well.

And in the end, I'm going to show you how to test that optimization, so you can verify for yourself if the actual optimization that is suggested really provides faster database queries. So let's move on to this episode.

1. Find Which Database Queries Are Worth Optimization

So the first step that you need to perform when you execute an effort to optimize your databases is determine what are database the queries that are worth of optimization because not all queries are executed as frequently or not all of them are slow.

So you need to find the queries that are slow and also are executed frequently.

2. Check if the MySQL Slow Query Log Is Enabled

So one resource that MySQL database server and other compatible database servers provide is having a slow query log. This helps a lot in finding those queries that are worth some optimization.

So the first thing that you need to do is to determine which are those slow queries. And to achieve that you need to set up MySQL database server to enable the slow query log. To achieve that you can execute a command that I'm showing here.

sudo mysqld 2>/dev/null --verbose --help | grep slow_query_log

Basically, it calls them mysqld command which is the server command,  at least on Linux or other Unix-compatible systems on which MySQL runs.

You can execute this command and use the verbose parameter to output the values of the options that configure the way MySQL server works. Then you filter the result of this command to just get the options that are relevant to the slow query log.

Here below it's the output of this command and you can see the slow query log here below, in red, this option is set to false.

mysqld command to check if the slow query log is enabled

So it is disabled now. You can see also the path to the slow query log file which is also an option that you can configure.

3. Enable the MySQL Slow Query Log

So what you need to do next is to enable slow query log. For that you need to edit the my.cnf file of MySQL.

You need also to figure out where is that located. Usually in Linux systems that is under /etc/my.cnf file. So when you need to change that option of the slow query log you need to edit the my.cnf file and set the slow query log to 1.

You can also configure the path of the slow query log, as you may see here.

Configuration for the path of the MySQL slow query log file

You can change it to a path that is useful for you. I have changed the path here just to show that you can change the path if you want.

You don't need to change the default path if you don't want to. It's up to you to decide that it's an option. So let's move on to the next step.

4. Restart the MySQL Server to Make the Configuration Changes Take Effect

After you change those options, they are not taken over in the MySQL server. You need to restart MySQL servers.

In this case, I use MariaDB. You can use some other compatible database server. And I also use Linux. In the case OpenSuSE Linux but so you need to run the rcmariadb restart command.

rcmariadb restart

If you use other system you need to figure what exactly are the commands maybe you have a user interface, a graphical user interface to do that. That's nice. If you have that. use that. It's up to you to figure that we are going to focus on the steps to optimize here.

5. Test if the Slow Query Log was Enabled Successfully

So once you restarted the server, the new values are the options are enabled. So we'll let's check to make sure that they really loaded the new option values that you execute again the mysqld command. It's the same command.

sudo mysqld 2>/dev/null --verbose --help | grep slow_query_log

And you can see below here that the slow query log is set to true. So it's enabled. And also the new path is set. OK, this is good.

mysqld command to check if the slow query log is enabled

So let's move on next step.

Show notes




You need to be a registered user or login to post a comment

Login Immediately with your account on:



Comments:

1. Using global variables instead - Erik Liljencrantz (2022-10-15 05:06)
set global slow_query_log=ON;... - 3 replies
Read the whole comment and replies



  Blog PHP Classes blog   RSS 1.0 feed RSS 2.0 feed   Blog Find MySQL Slow Queri...   Post a comment Post a comment   See comments See comments (4)   Trackbacks (0)