1

I have 2 MySQL servers:
Master server: mysql version 5.7.14
Slave server: mysql version 5.7.14 in Docker container (from official docker hub).
GTID-replication.

I have two problems:

  1. Binlogs growth very fast. I set 2 days limit rotation, but this not help - every day binlog folder increases at least twice (first day 25Gb, second 50, third 80 etc).
  2. Slave server "seconds behind master" increases.

Servers in local network (100 mbit/s), SSD disks, Database size near 40Gb.
I clone db for slave with Percona Xtrabackup.

Maybe configuration of servers is not correct & I need to fix something?

This is Master my.cnf file:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

max_allowed_packet = 256M

log-error       = /var/log/mysql/error.log

symbolic-links=0

!includedir /etc/mysql/conf.d/

innodb_buffer_pool_size=15000M
innodb_buffer_pool_instances=1
sql_mode = ''

slow_query_log = 1
slow_query_log_file     = '/var/log/mysql/slow.log'
long_query_time = 1
log_queries_not_using_indexes = 0

#skip-grant-tables

default_week_format = 1

skip-name-resolve
sort_buffer_size=4M
join_buffer_size=4M
innodb_sort_buffer_size=4M
tmp_table_size=5000M
max_heap_table_size=5000M

[mysqld]
server-id = 1
binlog-format = row
gtid_mode=ON
enforce-gtid-consistency=ON
log-slave-updates
log_bin = /var/log/mysql/mysql-bin.log
performance_schema_max_digest_length = 8192
max_digest_length = 8192
max_binlog_size= 1G
expire_logs_days = 2
binlog-ignore-db=check_passport
replicate-ignore-db=check_passport

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

key_buffer_size             = 16M
innodb_flush_method         = O_DIRECT
max_connections             = 200

#innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:5G

This is my.cnf of slave server

[client]
port                        = 3306
socket                      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                      = /var/run/mysqld/mysqld.sock
nice                        = 0

[mysqld]
sql_mode                    = ""
character_set_server        = utf8
collation_server            = utf8_general_ci
user                        = mysql
pid-file                    = /var/run/mysqld/mysqld.pid
socket                      = /var/run/mysqld/mysqld.sock
port                        = 3306
basedir                     = /usr
datadir                     = /var/lib/mysql
tmpdir                      = /tmp
language                    = /usr/share/mysql/english
old_passwords               = 0
bind-address                = 127.0.0.1
skip-host-cache
skip-name-resolve

skip-external-locking

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

max_allowed_packet          = 256M
#key_buffer_size             = 16M
innodb_buffer_pool_size     = 2048M
innodb_log_file_size        = 256M
innodb_file_per_table       = 1
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 1

max_connections             = 136

query_cache_size            = 0

slow_query_log              = /var/log/mysql/mysql-slow.log
long_query_time             = 1

expire_logs_days            = 2
max_binlog_size             = 1G

[mysqldump]
quick
quote-names
max_allowed_packet          = 16M


[mysqld]
server-id = 2
binlog-format = row
gtid_mode=ON
enforce-gtid-consistency=ON

relay-log = /var/log/mysql/mysql-relay-bin.log

log_bin = /var/log/mysql/mysql-bin.log

skip_slave_start

log_slave_updates = 0
read_only = ON

innodb_file_per_table = ON
#innodb_buffer_pool_size = 3G
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 1

max_binlog_size = 1G

#max_relay_log_size = 1G
#relay_log_space_limit = 20G
relay_log_recovery = ON

expire_logs_days = 2

#slave-parallel-workers = 0

binlog-ignore-db=check_passport
replicate-ignore-db=check_passport

replicate-ignore-table=gfk.application_insurance
replicate-ignore-table=gfk.archive_client_building
replicate-ignore-table=gfk.comments_passwords
replicate-ignore-table=gfk.date_interval

Output of SHOW SLAVE STATUS\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.4
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.004720
          Read_Master_Log_Pos: 518759418
               Relay_Log_File: mysql-relay-bin.000188
                Relay_Log_Pos: 213202356
        Relay_Master_Log_File: mysql-bin.004703
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: check_passport
           Replicate_Do_Table: 
       Replicate_Ignore_Table: gfk.application_insurance,gfk.date_interval,gfk.archive_client_building,gfk.comments_passwords
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 213202143
              Relay_Log_Space: 18773097825
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /var/lib/master_cert/ca.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /var/lib/master_cert/client-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /var/lib/master_cert/client-key.pem
        Seconds_Behind_Master: 14488
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 8ab33cfb-bb00-11e6-84cd-fa163eb352dd
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 8ab33cfb-bb00-11e6-84cd-fa163eb352dd:62276836-70424802
            Executed_Gtid_Set: 8ab33cfb-bb00-11e6-84cd-fa163eb352dd:1-67413143
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
6
  • how many database you set to replicaton ? from one instance ? because I only see variable binlog_ignore_db=check_passport. I assume you doing replicaton all database from one instance mariadb except check_passport
    – febry
    Aug 31, 2020 at 3:47
  • @febry, we replicate 13 databases from 1 instance except check_passport db.
    – Pavel
    Aug 31, 2020 at 10:18
  • remember binary log will be created base on activity from database like insert, update, delete i think this is normal because you have 13 database on one instance
    – febry
    Aug 31, 2020 at 10:21
  • @febry Ok, let's log folder increase more. But how to solve problem with "seconds behind master" increasing. Maybe some params in config not correct?
    – Pavel
    Aug 31, 2020 at 10:23
  • Let's see the big DELETEs and big UPDATEs you are running. There are much better ways to do them.
    – Rick James
    Aug 31, 2020 at 23:44

2 Answers 2

3

Is it practical for the Primary to have binlog_ignore = check_passport? If so, and if there is a lot of traffic about that db, then that would shrink the binlogs "a lot".

Big DELETEs or UPDATEs take a lot of binlog space, hence making the binlog huge. (Example: updating every row of a million-row table.) Provide specifics; the may be significant work-arounds.

How many replica threads are running on the Replica? This impacts Seconds_behind_master. (More threads, up to some limit, is likely to decrease "behind".)

innodb_flush_log_at_trx_commit = 1 --> change to 2. This sacrifices some robustness (across a crash), but improves throughput.

How much RAM in each? I see a bigger buffer_pool in the Primary. Usually the Replica should be the beefier machine.

100Mbs seems low; can you check to see whether it is saturated.

Fresh data daily

Here is an efficient way to replace all the data in a table:

  1. CREATE TABLE new LIKE real;
  2. LOAD DATA INFILE INTO new ... -- or whatever it takes
  3. `RENAME TABLE real TO old, new TO REAL;
  4. DROP TABLE old;

Step #2 is the slowest part.
Step #3 is very fast; it is the only time that your table is not available.
No binlog-intensive UPDATE or DELETE.

3
  • Hi! Thanks for your answer. Yes, this is practical to ignore check_passport because this db recreated every day with data from external service. How to see how many replica threads i am runing (I think default value)? On the primary 23Gb RAM, on the slave 10Gb RAM.
    – Pavel
    Sep 1, 2020 at 7:20
  • @Pavel - I added to my Answer, possibly explaining the binlog bloat.
    – Rick James
    Sep 2, 2020 at 4:40
  • Thanks for your addition. I check local network speed - it's 1Gbit/s. And after set log_slave_updates = 0 my slave catching up master.
    – Pavel
    Sep 2, 2020 at 8:57
0

I found solution for my case

First of all, I search what databases and tables generates so big binlogs (what tables don't have primary or unique keys):

SELECT t.table_schema,t.table_name,engine
FROM information_schema.tables t
INNER JOIN information_schema .columns c
on t.table_schema=c.table_schema
and t.table_name=c.table_name
and t.table_schema not in ('performance_schema','information_schema','mysql')
GROUP BY t.table_schema,t.table_name
HAVING sum(if(column_key in ('PRI','UNI'), 1,0)) =0;

Then:

  1. My network speed already been 1Gbit/s, there is all ok.
  2. I exclude from replication and logging database which recreated every day.
  3. I increase on slave RAM to 23Gb (as on master)
  4. I move binlogs on slave from SSD to HDD - it's ok - speed of HDD is enough.
  5. I set log_slave_updates = 1, because my replication scheme is master>slave>slave.

And this steps solve my problems! For now my binlogs not increase more than 10Gb.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .