Shulou(Shulou.com)06/02 Report--

This article focuses on "MySQL slow log related configuration", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "MySQL slow log related configuration" bar!

Whether to enable slow log in slow_query_log #. 1 means to enable slow log, and 0 means to disable the storage path of slow log in MySQL database of the previous version of log_slow_queries # (version 5.6 or below). Do not set this parameter, the system will default to a default file host_name-slow.log (parameter is out of date) slow_query_log_file # new version (version 5.6 and above) MySQL database slow query log storage path. If you do not set this parameter, the system will default to a default file host_name-slow.log (replace log_slow_queries) long_query_time # slow query threshold, which defaults to 10s. When the query time exceeds the set threshold, log _ queries_not_using_indexes # queries without indexes will also be recorded in the slow log (optional) log_output # log storage. Log_output='FILE' means to save the log to a file, and the default value is' FILE'. Log_output='TABLE' means to store the log in the database so that the log information is written to the mysql.slow_ log table. MySQL data

The library supports two log storage methods at the same time, which can be separated by commas when configured, such as log_output='FILE,TABLE'. Logging to the dedicated log table of the system consumes more system resources than recording files, so for slow query logs that need to be enabled, you also need to

To achieve higher system performance, it is recommended to log to file first. By default, the value of slow_query_log is OFF, indicating that slow log is disabled. You can enable it by setting the value of slow_query_log, as shown below: mysql > show variables like'% slow_query_log%' +-+ | Variable_name | Value | +-+- -- + | slow_query_log | OFF | | slow_query_log_file | / usr/local/mysql/data/localhost-slow.log | +- -- + 2 rows in set (0.00 sec) mysql > set global slow_query_log=1 Query OK, 0 rows affected (0.00 sec) mysql > show variables like'% slow_query_log%' +-+ | Variable_name | Value | +-+- -- + | slow_query_log | ON | | slow_query_log_file | / usr/local/mysql/data/localhost-slow.log | +- -- + 2 rows in set (0.00 sec) # use set global slow_query_log=1 to enable slow query logs that are only valid for the current database The MySQL will fail when it is restarted. If you want to take effect permanently, you must modify the configuration file my.cnf (as do other system variables), modify the my.cnf file, add or modify the parameters slow_query_log and slow_query_log_file, and then restart the MySQL server, as follows: slow_query_log = 1slow_query_log_file=/usr/local/mysql/data/localhost-slow.logmysql > show variables like 'slow_query%' +-+-+ | Variable_name | Value | +-+-+ | slow_query_log | ON | | slow_query_log_file | / usr/local/mysql/data/localhost-slow.log | +-+-+ rows in set (0.00 sec) mysql > # slow query parameter slow_query_log_file | It specifies the storage path of slow log files. By default, the system will give a default file mysqldumpslow-- helpUsage: mysqldumpslow [OPTS...] that comes with slow log analysis tool host_name-slow.logmysql. ] [LOGS... ] Parse and summarize the MySQL slow query log. Options are-- verbose verbose-- debug debug-- help write this text to standard output-v verbose-d debug-s ORDER what to sort by (al, at, ar, c, l, r, t) 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time-r reverse the sort order (largest last instead of first)-t NUM just show the top N queries-a don't abstract all numbers to N and strings to's'- n NUM abstract numbers with at least n digits within names-g PATTERN grep: only consider stmts that include this string-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard) Default is'*' I.e. Match all-i NAME name of server instance (if using mysql.server startup script)-l don't subtract lock time from total time####-s Indicates how to sort c: access count l: lock time r: return records t: query time al: average lock time ar: average number of records returned at: average query time-t, which means top n That is, how many pieces of data are returned. -g, which can be followed by a regular matching pattern, which is case-insensitive; for example, get the 10 SQL with the most returned recordsets. Mysqldumpslow-s r-t 10 / database/mysql/mysql06_slow.log get the 10 most visited SQLmysqldumpslow-s c-t 10 / database/mysql/mysql06_slow.log get the first 10 query statements with left join in chronological order. Mysqldumpslow-s t-t 10-g "left join" / database/mysql/mysql06_slow.log is also recommended to use these commands in combination with | and more, otherwise screen brushing may occur. Mysqldumpslow-s r-t 20 / mysqldata/mysql/mysql06-slow.log | more#### cleans up mysql-bin command line: reset master;reset slave Configure my.cnf:log-bin = mysql-bin # for master / slave scenarios to comment out binlog_format = mixed # binlog recording mode expire_logs_days = 7 # set retention days to restart mysql takes effect. I believe you have a better understanding of "configuration of MySQL slow logs". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.