How to Use Simple MySQL Monitoring Tools for Free to Find the Slowest Queries that You Should Optimize First - 2 minutes - Lately in PHP Podcast Episode 93 Part 3

In this article you can learn:

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

Contents

Monitor the MySQL Slow Query Log to Find the Most Critical Slow SQL Queries to Optimize First

Now that we have the slow query log enabled, you need to monitor that slow query log.

How can we monitor this slow query log?

You can use this simple command in the Linux system called the tail.

And in this case, it gets a parameter to get only the last hundred lines. So you want to check only the latest queries that are slow.

mlemos@development:~> tail -100 /var/log/mysql/mysqld_slow.log

mlemos@development:~> tail -100 /var/log/mysql/mysqld_slow.log
#.
#.
#.
# Time: 220904 21:35:04
# User@Host: mlemos[mlemos] @ localhost []
# Thread_id: 18289  Schema: phpclasses  QC_hit: No
# Query_time: 538.469179  Lock_time: 0.000132  Rows_sent: 29941  Rows_examined: 3403486
# Rows_affected: 0  Bytes_sent: 5264934
SET timestamp=1662352504;
SELECT access.post AS post, blog.type AS type, access.date AS date, access.subscriber AS subscriber, access.ip AS ip, access.agent AS agent, access.id AS id, post.posted AS posted, post.author AS author FROM blog_post_view access, post, blog WHERE access.processed='N' AND access.date<'2022-09-01' AND access.post=post.id AND post.blog=blog.id ORDER BY date;
#.
#.
#.
mlemos@development:~> 

What is important here is that this query took 538 seconds. That is a lot. It's almost 10 minutes the user is waiting, or maybe some process running in the background is being slowed down by a slow query.

So this query needs to be optimized. As you may see, it shows that 29 thousand lines, more than that, was the number of rows that the query sent to the script or application you are running.

And then it also shows that it queried over 3 million rows of the database table. This fact means that the database server examined all those lines, which explains why this query is slow.

So below here is the actual query; this is important because you need to know what query you need to optimize.

Show notes

• GNU tail command documentation