MYSQL5.7.9 Tips for turning on slow query logs

MYSQL 5.7.9 is used as the monitoring database for ZABBIX 2.4.7. I started the slow query log some time ago, but later I found that the slow query log had swelled to 700M

set global slow_query_log=1; set global slow_query_log=1; The way.

Then you want to configure slow queries directly with the configuration file /etc/ my.cnf

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at % of total RAM for dedicated server, else %.
innodb_buffer_pool_size = M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = /LANMP/mysql
datadir = /MYSQLDATA/data
port = 
# server_id = .....
socket = /tmp/mysql.sock
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = M
sort_buffer_size = M
read_rnd_buffer_size = M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character_set_server=utf
init_connect='SET NAMES utf'
slow_query_log = 
slow_query_log_file = /MYSQLDATA/mysql_slow_query.log
long_query_time = 
log_queries_not_using_indexes = ON

service mysqld restart was started several times

mysql> show variables like '%query%';
+------------------------------+---------------------------------+
| Variable_name | Value |
+------------------------------+---------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 5.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /MYSQLDATA/mysql_slow_query.log |
+------------------------------+---------------------------------+
13 rows in set (0.00 sec)

Always the OFF state and the other LONG_QUERY_TIME gets changed for 5 seconds

Set up error reporting dynamically

mysql> set global slow_query_log=1;
ERROR 29 (HY000): File '/MYSQLDATA/mysql_slow_query.log' not found (Errcode: 13 - Permission denied)

Yes, I've deleted it, so I'm just going to create an ?

I had to touch 1 by myself

Error #63;

ps-ef |grep mysqld found another mysql user started mysql

Seems to be a user permission issue with mysql users reading slow query logs

Here's how to turn on the mysql slow query log

View configuration:

// Query slow query time 
show variables like "long_query_time"; The default 10s 
// View the slow query configuration 
show status like "%slow_queries%"; 
// View the slow query log path 
show variables like "%slow%"; 

Modify configuration file

Add the following two sentences to ES62en.ini

log-slow-queries = D:\wamp\mysql_slow_query.log
long_query_time=5

The first sentence is used to define the path to the slow query log (since it is windows, no permission issues are involved)

In the second sentence, a query used to define how many seconds it has been checked is considered a slow query. I have defined 5 seconds here

Step 2: View the status of slow queries

Execute the following SQL statement to see the status of the mysql slow query

show variables like '%slow%';

The execution results print on the screen information such as whether the slow query was started, the number of seconds for the slow query, and the slow query log.

Step 3: Perform a slow query operation

In fact, it is difficult to perform a meaningful slow query, because in my own test, even if I query the sea scale with 200,000 pieces of data, I only need 0. A few seconds. We can replace it with the following statement:

SELECT SLEEP(10);

Step 4: Look at the number of slow queries

See how many slow queries were executed by 1 using the following sql statement:

show global status like '%slow%';

Configuration of the mysql logs:

Note: These day files only generate # to record all sql statements when mysql restarts

log=E:/mysqllog/mysql.log
# Record database startup and shutdown information, as well as error messages generated during operation 
log-error=E:/mysqllog/myerror.log
#  Records in addition to select Anything other than a statement sql Statement to the log, which can be used to recover data files 
log-bin=E:/mysqllog/bin
# Record queries that are slow sql statements 
log-slow-queries=E:/mysqllog/slow.log 
# Slow query time 
long_query_time=0.5