mysql query become very slowly when binlog rotate
Posted by:
weity ho
Date: July 11, 2017 12:32AM
Mysql version is 5.7.13.
Everytime when binlog file rotate,all queries and dml become extremely slow.the server "freezes" for about 40 seconds.
In the slow query log, I just see commits and system/session variable queries (like select @@session.tx_read_only; ) that take about 20 secs.
==============master server my.cnf============
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
innodb_buffer_pool_size = 2048M
innodb_log_file_size = 360M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 50
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log-bin=/alidata1/binlog/mysql-bin
expire_logs_days=7
binlog-do-db=mydb
sync_binlog=6
server_id=10
slow_query_log=on
slow_query_log_file=/data/slow_query/slow_query.log
log_queries_not_using_indexes=off
long_query_time=1
wait_timeout=10800
max_connections=800
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#character_set_server=utf8
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
==============binlog files=============
[root@iZ94aem4qr5Z binlog]# ll
total 15873308
-rw-r----- 1 mysql mysql 629162145 Jul 10 16:15 mysql-bin.000253
-rw-r----- 1 mysql mysql 629160790 Jul 10 21:11 mysql-bin.000254
-rw-r----- 1 mysql mysql 629148120 Jul 11 08:35 mysql-bin.000255
-rw-r----- 1 mysql mysql 629167412 Jul 11 13:12 mysql-bin.000256
-rw-r----- 1 mysql mysql 143362983 Jul 11 14:25 mysql-bin.000257
-rw-r----- 1 mysql mysql 782 Jul 11 13:12 mysql-bin.index
==============mysql slow log==============
# User@Host: user[user] @ [10.24.165.8] Id: 130132
# Query_time: 14.186255 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1499749959;
select @@session.tx_read_only;
# User@Host: user[user] @ [10.25.208.208] Id: 130317
# Query_time: 13.943930 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1499749959;
select @@session.tx_read_only;
# User@Host: user[user] @ [10.24.165.8] Id: 130204
# Query_time: 13.848498 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1499749959;
select @@session.tx_read_only;
# User@Host: user[user] @ [10.24.165.8] Id: 130197
# Query_time: 12.988434 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1499749959;
select @@session.tx_read_only;
Subject
Views
Written By
Posted
mysql query become very slowly when binlog rotate
2065
July 11, 2017 12:32AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.