Learn to read MySql slow query logs and mysql query logs
When and how to enable MySql logs in the blog at the front end We learned how to enable MySql slow query logs. Today, let's take a look at how to read these slow query logs.
Before tracking slow query logs, you must ensure that at least one slow query occurs. If you do not have one, you can create one by yourself:
Root @ server #Mysql-e 'select SLEEP (8 );
The preceding operations only have one thing: "sleeping" (nothing to do) eight seconds. This length should be enough to be recorded in your slow query log (I usually recommend that you perform slow query records for queries longer than 2 or 3 seconds ).
First, let's look at what a slow query log entry looks like:
Root @ server #Tail/var/log/slowqueries
# Time: 130320 7:30:26
# User @ Host: 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 ';
The meaning of each row is as follows:
- The first line indicates the time when logs are logged. The format is yymmdd h: M: S. We can see that the above query record was recorded at AM, January 1, March 20, 2013-Note: This is the server time, which may be different from your local time.
- Then, we can see the MySql user, server, and host name
- The third row indicates the total query time, lock time, "send" or the number of returned rows, and the number of rows checked during the query.
- Next we will see SET timestamp = UNIXTIME; this is the actual time when the query occurred. If you want to find some slow queries, the slow queries that occurred a few months ago will not happen after you check them. Next I will introduce how to change it into a useful time
- The last line shows the complete query statement.
To convert Unix time to a readable time, you can use the date-d command. Enter date-d @ (remember to include the @ symbol), and paste the timestamp in the log after @ (there is no space:
Root @ server #
Date-d @ 1363779026
Wed Mar 20 07:30:26 EDT 2013
In the above example, we can see that the log is recorded at the same time as the query is done-but this is often not the case for an ultra-load server. Therefore, remember: SET timestamp = value is the actual query execution time.
Now, let's demonstrate how I use MySql slow query logs to solve a real problem on one of my websites. Your query may not be the same, but the solution principle is the same.
On a blog website, I used a plug-in named Tweet Blender to automatically display Weibo to my keywords. Unfortunately, some people have discovered this and used the keywords in my weibo posts to send some spam Weibo posts-no matter what, the final result is that some spam Weibo posts are displayed on my blog website. Because Weibo has not existed on my website for a long time, I did not find any problems on my website until I checked the slow query log.
My website has made several similar queries on Weibo posts with junk information (which is often the same). After a while, these queries are too large to slow down some of them. In this case, due to the large number of requests, some of my friends' websites may be suspended or report errors directly. However, my server has undergone excellent performance tuning, therefore, there is no obvious impact. Fortunately, I was checking slow query logs, found this situation in time, and quickly solved this problem.
The solution to this problem is simple-Tweet Blender has a beautiful filtering function. I just need to add the Weibo user name and some junk keywords to the "exclude" list, and then there will be no more such problems. In this way, it seems that monitoring of our own websites and logs is very important, even if each site/server is checked only once a week.
Original article: http://calladeveloper.blogspot.com/2013/03/howto-read-mysql-slow-query-log.html.
