PHP Classes

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

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

Author:

Viewers: 273

Last month viewers: 6

Categories: PHP Tutorials, Lately in PHP Podcast

When you want to optimize the performance of a MySQL-based application, you should try to optimize first the slowest queries that affect the application's performance.

The MySQL slow query log file provides a list of slow queries as they happen. So you need to monitor that file to determine the most critical slow SQL queries you need to optimize first.

Read this article, watch a 2-minute video, or listen to part 3 of episode 93 of the Lately in PHP podcast to learn how to use a free tool to monitor the MySQL slow query log and find the slowest SQL queries.




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

2. Previous Article: Find MySQL Slow Queries in a Production Server by Activating the Slow Query Log

3. This Article: How to Use Simple MySQL Monitoring Tools for Free to Find the Slowest Queries that You Should Optimize First

4. Next Article: How to Use EverSQL SQL Query Optimizer Tool to Optimize Specific SQL 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 3 Video

Play Video

What was said in the podcast

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
This output capture below is an edited screen, so I abbreviated it to show one query.
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




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

1,616,405 PHP developers registered to the PHP Classes site.
Be One of Us!

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 Use Simple MyS...   Post a comment Post a comment   See comments See comments (0)   Trackbacks (0)