'MariaDB - Replication. Slave Lagging Behind For an Interval
So I have 1 Master Server and 2 Slave servers running (CentOS-7). I have installed MariaDB-10.1.48 on all the servers. I implemented replication using GTID. All things were working well but an issue arose when I tried to add the 2nd Slave to replication. I am monitoring the servers through Maxscale. Both servers sync the GTID correctly but as soon as Master gets a lot of writes instantly (both users + bots are running), Slave 1 catches up quickly with Master but Slave 2 takes time, when I run show slave status - it says Seconds_behind_master = seconds(up to 50-60) then suddenly it drops to (0). The cnf file of both S1 and S2 are the same and in fact, S2 has a lot more hard disk and CPU power than S1. I have tried looking at things but could not find a solution. I am attaching the cnf file. This file is the same for master and both slaves except the server_id which is of course unique. Any suggestion is greatly appreciated.
slave-skip-errors = 1062
socket=/var/lib/mysql/mysql.sock
datadir=/var/lib/mysql
gtid_strict_mode=1
log_slave_updates=1
sql-mode=""
slow_query_log
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=2.0
log-error = /var/log/mysql/mysql-error.log
binlog_format=MIXED
max_connect_errors=2000
server-id = unique_for_every_server
bind-address = 0.0.0.0
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = some DBs (main + mysql + informmation_schema)
binlog-ignore-db= (some DBs)
replicate_do_db= some DBs (main + mysql + informmation_schema)
replicate-ignore-db= some DBs
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
default-time-zone='-04:00'
max_allowed_packet = 1024M
wait_timeout = 60000
interactive_timeout= 60000
connect_timeout= 60000
table_cache = 2048
sort_buffer_size = 100M
thread_concurrency = 500
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
query_cache_size = 100M
query_cache_limit = 150M
tmp_table_size = 100M
innodb_buffer_pool_size = 500M
innodb_additional_mem_pool_size = 20M
join_buffer_size=500M
table_open_cache = 7000
key_buffer_size = 50M
thread_cache_size = 50
key_buffer_size = 150M
read_buffer_size = 100M
read_rnd_buffer_size = 50M
key_buffer = 100M
max_connections = 2000
Solution 1:[1]
I just added innodb_flush_log_at_trx_commit = 2
in /my.cnf/
file and the problem was solved.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | richardec |