Author: Manuel Lemos
Viewers: 370
Last month viewers: 6
Categories: PHP Tutorials, Lately in PHP Podcast
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.
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
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
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.
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.
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.
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