Logrotate implementing MySQL Slow Log segmentation

Source: Internet
Author: User
Tags mysql slow query log

MySQL slow log?

MySQL's slow query log is a log record provided by MySQL, which is used to record a statement in MySQL that has a response time exceeding the threshold, which refers to SQL that runs longer than the Long_query_time value, and is recorded in the slow query log. The default value of Long_query_time is 10, which means to run statements above 10s.
By default, the MySQL database does not start the slow query log, we need to manually set this parameter, of course, if not tuning needs, it is generally not recommended to start this parameter, because the slow query log on more or less will have some performance impact. The slow query log supports writing log records to a file, and also supports writing log records to a database table.

Slow log generation situation?

The first execution time is too long (greater than the set Long_query_time threshold), the second unused index, or an optimal index is not used.

Both logs are turned off by default, especially those that do not use indexes, because there may be a lot of logs in this category, so there is a special switch log_throttle_queries_not_using_indexes to limit the number of logs that do not use indexes per minute.

Slow log output

MySQL slow query log by default is all written to a file, the file will be more and more large, very inconvenient for us to analyze. Use logrotate to cut MySQL slow query log, error log, to facilitate analysis and find problems.

Define the log theory roll strategy 

[Email protected]_db_40 ~]# Cat Mysql-log-rotate
/opt/mysql/data/idc_db_40-slow.log {
Create MySQL MySQL
Dateext
Notifempty
Daily
MaxAge 60
Rotate 30
Missingok
Compress
olddir/opt/mysql/data/
Postrotate
# just if Mysqld is really running
If Test-x/opt/mysql/bin/mysqladmin && \
/opt/mysql/bin/mysqladmin ping-uroot-p123qwe,./-s/tmp/mysql.sock &>/dev/null
Then
/opt/mysql/bin/mysqladmin flush-logs-uroot-p123qwe,./-s/tmp/mysql.sock
Fi
Endscript
}

Set up Scheduled Tasks

* * * */usr/sbin/logrotate-f mysql-log-rotate

#每天23:59 for log splitting

Effect Show

A compressed package is generated every day very convenient management!!!

Logrotate implementing MySQL Slow Log segmentation

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.