Learn to read MYSQL's slow query log

In front of the blog "when, how to open the MySql log?" ", we learned how to enable the slow query log for MYSQL.

Today we look at how to read these slow query logs.
Before tracing the slow query log. First you have to make sure that there is a slow query at least once. Suppose you are not able to make one yourself:
[email protected]# mysql-e ' Select SLEEP (8);
There is only one thing you can do with this: "Sleep" (do nothing) for eight seconds. This length should be enough to be recorded in your slow query log (I usually recommend slow query logging for queries longer than 2 or 3 seconds).
First, let's see what a slow query log entry looks like:
[email protected]# tail/var/log/ Slowqueries
# time:130320  7:30:26
# [email protected]: db_user[db_database] @ localhost []
# query_time:4.545309  lock_time:0.000069 rows_sent:219  rows_examined:254
SET timestamp=1363779026;
SELECT option_name, option_value from wp_options WHERE autoload = ' yes ';
Let's take a look at what each line means:

• The first line represents the time when the log was logged. The format is YYMMDD h:m:s. We can see the above query recorded on March 20, 2013 7:30-Note: This is the server time, may be different from your local time
• We can then see the MYSQL user, server, and host name
• The third row represents the total query time, the lock time, the number of rows to send, or the row to return, and the number of rows checked during the query
• The next thing we see is SET timestamp=unixtime, which is the time the query actually occurred. Let's say you want to find some slow queries today. By checking this it will not happen what you are checking is a slow query that happened a few months ago. I'll show you how to turn it into a practical time.
• The last line shows the complete query statement

To turn Unix time into a human-readable time, you can usedate-dCommand.

Enter date-d @(remember to include the @ symbol), and then paste the timestamp in the log after @ (no spaces between them):
[Email protected]# date-d @1363779026
Wed Mar 07:30:26 EDT 2013
In the example above we were able to see that the log was logged at the same time as the query was made-but often not for a server that is overloaded.

So remember:SET timestamp= value is the execution time of the actual query.

Now let me show you how I use the MySql slow query log to solve a real problem on one of my sites.

Your query may not be the same as this, but the principle of solving this problem is interlinked.

I used a plugin called "Tweet Blender" on a blog site to proactively display related tweets to my keyword. Unfortunately, some people find this and use the keyword in my microblog to send some spam tweets-anyway, finally the result is that some spam tweets appear on my blog site.

I did not find any problems on the site because the related microblogging was not long in my site. Until I view the slow query log.
My site has several similar queries for microblogs with spam (spam is often the same), and after a period of time the number of queries is too large to be slow to execute.

When this happens, because the number of requests is very large, some friends of the site is likely to be suspended animation or direct error. However, my server has had very good performance tuning and therefore has no significant impact. Fortunately, I was looking at the slow query log. The situation was discovered in time and the problem was quickly overcome.
The solution to this problem is very easy-tweet Blender has a beautiful filtering function. I just need to add the microblog username and some junk keyword to the "Exclude" list. After that, there is no such problem anymore. So it seems, how important it is to monitor our own sites and logs, even if it's a one-time, high-speed check on every site/server.

Original link: http://calladeveloper.blogspot.com/2013/03/howto-read-mysql-slow-query-log.html.

Learn to read MYSQL's slow query log