1

Why mysql rotates the relay logs at 100mb when max_relay_log_size = 1G ?

Is mysql should rotate the log file only if the size to exceed the value of max_relay_log_size variable?

-rw-r----- 1 mysql mysql        255 Jun 30 19:14 mysqld-relay-bin.000011
-rw-r----- 1 mysql mysql  104858982 Jun 30 19:18 mysqld-relay-bin.000012
-rw-r----- 1 mysql mysql        255 Jun 30 19:18 mysqld-relay-bin.000013
-rw-r----- 1 mysql mysql   22305643 Jun 30 19:20 mysqld-relay-bin.000014
-rw-r----- 1 mysql mysql        208 Jun 30 19:20 mysqld-relay-bin.000015
-rw-r----- 1 mysql mysql   84483918 Jun 30 19:23 mysqld-relay-bin.000016
-rw-r----- 1 mysql mysql        255 Jun 30 19:23 mysqld-relay-bin.000017
-rw-r----- 1 mysql mysql  104981742 Jun 30 19:26 mysqld-relay-bin.000018
-rw-r----- 1 mysql mysql        255 Jun 30 19:26 mysqld-relay-bin.000019
-rw-r----- 1 mysql mysql  104862635 Jun 30 19:30 mysqld-relay-bin.000020
-rw-r----- 1 mysql mysql        255 Jun 30 19:30 mysqld-relay-bin.000021
-rw-r----- 1 mysql mysql  104862297 Jun 30 19:35 mysqld-relay-bin.000022
-rw-r----- 1 mysql mysql        255 Jun 30 19:35 mysqld-relay-bin.000023
-rw-r----- 1 mysql mysql  105150321 Jun 30 19:38 mysqld-relay-bin.000024
-rw-r----- 1 mysql mysql        255 Jun 30 19:38 mysqld-relay-bin.000025
-rw-r----- 1 mysql mysql  104861615 Jun 30 19:43 mysqld-relay-bin.000026
-rw-r----- 1 mysql mysql        255 Jun 30 19:43 mysqld-relay-bin.000027
-rw-r----- 1 mysql mysql    2467221 Jun 30 19:43 mysqld-relay-bin.000028
-rw-r----- 1 mysql mysql      87680 Jun 30 19:43 mysqld-relay-log.index
-rw-r----- 1 mysql mysql         84 Jun 30 19:43 relay-log.info



binlog_format = ROW
log-bin = /data/binlogs/mysql-bin
log-bin-index = /data/binlogs/mysql-bin.index
innodb_log_file_size = 1G
max_binlog_size = 2G
relay-log-info-file = /data/binlogs/relay-log.info
relay-log-index = /data/binlogs/mysqld-relay-log.index
relay-log = /data/mysql/mysqld-relay-bin
max_relay_log_size = 1G
relay_log_space_limit = 0
expire_logs_days=14
innodb_locks_unsafe_for_binlog=1



Variable_name              Value                                 
-------------------------  --------------------------------------
max_relay_log_size         1073741824                            
relay_log                  /data/binlogs/mysqld-relay-bin        
relay_log_basename         /data/binlogs/mysqld-relay-bin        
relay_log_index            /data/binlogs/mysqld-relay-log.index  
relay_log_info_file        /data/binlogs/relay-log.info          
relay_log_info_repository  FILE                                  
relay_log_purge            ON                                    
relay_log_recovery         OFF                                   
relay_log_space_limit      0                                     
sync_relay_log             10000                                 
sync_relay_log_info        10000    

mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper

update

Now there are almost no relay logs, is it because the relay logs are purged automatically when the slave has finished applying the data from them, meaning second behind master = 0 ?

drwxr-xr-x 2 mysql mysql       4096 Jul  1 20:59 ./
drwxr-xr-x 7 mysql mysql         98 Feb 19 14:57 ../
-rw-r----- 1 mysql mysql        177 Jul  1 13:41 mysql-bin.000001
-rw-r----- 1 mysql mysql 1073979508 Jul  1 14:14 mysql-bin.000002
-rw-r----- 1 mysql mysql 1122063688 Jul  1 14:36 mysql-bin.000003
-rw-r----- 1 mysql mysql 1073760829 Jul  1 15:21 mysql-bin.000004
-rw-r----- 1 mysql mysql 1073747080 Jul  1 16:11 mysql-bin.000005
-rw-r----- 1 mysql mysql 1076996640 Jul  1 17:02 mysql-bin.000006
-rw-r----- 1 mysql mysql 1073742632 Jul  1 17:51 mysql-bin.000007
-rw-r----- 1 mysql mysql 1073742448 Jul  1 18:42 mysql-bin.000008
-rw-r----- 1 mysql mysql 1073951285 Jul  1 19:29 mysql-bin.000009
-rw-r----- 1 mysql mysql 1073741986 Jul  1 20:17 mysql-bin.000010
-rw-r----- 1 mysql mysql  911962461 Jul  1 20:59 mysql-bin.000011
-rw-r----- 1 mysql mysql        341 Jul  1 20:17 mysql-bin.index
-rw-r----- 1 mysql mysql        255 Jul  1 20:56 mysqld-relay-bin.000754
-rw-r----- 1 mysql mysql   45170435 Jul  1 20:59 mysqld-relay-bin.000755
-rw-r----- 1 mysql mysql         76 Jul  1 20:56 mysqld-relay-log.index
-rw-r----- 1 mysql mysql         86 Jul  1 20:59 relay-log.info

update

I see the following warning in mysql.log file option 'max_binlog_size': unsigned value 2147483648 adjusted to 1073741824.

10
  • Have you tried writing it as max_relay_log_size = 1073741824 and see if it makes a difference? Is there any possibility this setting is dynamically changed? It is actually dynamic, so it can be changed via SQL.
    – joanolo
    Jul 3, 2017 at 23:33
  • @joanolo, I am using max_relay_log_size = 1G, but I wrote an update to my question, did you see ?
    – Berlin
    Jul 4, 2017 at 10:06
  • That's a huge amount of data being shipped through -- please verify that server-id is different on each machine.
    – Rick James
    Jul 7, 2017 at 21:51
  • I wonder what caused the "255" byte files. Any change someone is doing a FLUSH?
    – Rick James
    Jul 7, 2017 at 21:51
  • 1
    I guess this is a limitation based on old 32-bit days. As soon the file is bigger than the max, it will stop adding. This means it will go a little past 1G. If it went past 2G there would be issue with signed/unsigned in a 32-bit compiler. 1G is plenty for a binlog; the new default seems to be 100M, which is also fine.
    – Rick James
    Jul 22, 2017 at 14:07

1 Answer 1

1

I guess this is a limitation based on old 32-bit days. As soon the file is bigger than the max, it will stop adding. This means it will go a little past 1G. If it went past 2G there would be issue with signed/unsigned in a 32-bit compiler. 1G is plenty for a binlog; the new default seems to be 100M, which is also fine.

As for the max_relay_log_size = 1G became a 100M limit, I don't know. But, again, it should not matter. The additional 255-byte files make me wonder if your code is doing some sort of FLUSH LOGS every so often.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.