The way to Use Easy MySQL Monitoring Instruments for Free to Discover the Slowest Queries that You Ought to Optimize First – 2 minutes – These days in PHP Podcast Episode 93 Half 3

On this article you may study:

The way to Enhance the Velocity of An Software that Makes use of a MySQL Database Server

1. Earlier Article: Discover MySQL Sluggish Queries by Activating the Sluggish Question Log

4. Subsequent Article: The way to Use EverSQL SQL Question Optimizer Software to Optimize Particular SQL Queries

Contents

Monitor the MySQL Sluggish Question Log to Discover the Most Essential Sluggish SQL Queries to Optimize First

Now that we’ve got the sluggish question log enabled, it’s good to monitor that sluggish question log.

How can we monitor this sluggish question log?

You should use this easy command within the Linux system referred to as the tail.

And on this case, it will get a parameter to get solely the final hundred traces. So that you wish to verify solely the most recent queries which can be sluggish.

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

This output seize beneath is an edited display screen, so I abbreviated it to indicate one question.

mlemos@improvement:~> tail -100 /var/log/mysql/mysqld_slow.log
#.
#.
#.
# Time: 220904 21:35:04
# Consumer@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 entry.publish AS publish, weblog.kind AS kind, entry.date AS date, entry.subscriber AS subscriber, entry.ip AS ip, entry.agent AS agent, entry.id AS id, publish.posted AS posted, publish.writer AS writer FROM blog_post_view entry, publish, weblog WHERE entry.processed='N' AND entry.date<'2022-09-01' AND entry.publish=publish.id AND publish.weblog=weblog.id ORDER BY date;
#.
#.
#.
mlemos@improvement:~> 

What’s necessary right here is that this question took 538 seconds. That could be a lot. It is nearly 10 minutes the consumer is ready, or perhaps some course of operating within the background is being slowed down by a sluggish question.

So this question must be optimized. As you might even see, it exhibits that 29 thousand traces, greater than that, was the variety of rows that the question despatched to the script or utility you’re operating.

After which it additionally exhibits that it queried over 3 million rows of the database desk. This truth implies that the database server examined all these traces, which explains why this question is sluggish.

So beneath right here is the precise question; that is necessary as a result of it’s good to know what question it’s good to optimize.

Present notes