MySQL Bugs: #64121: Data files on slaves are unusually (unexpectedly?) large

Bug #64121	Data files on slaves are unusually (unexpectedly?) large
Submitted:	24 Jan 2012 16:11	Modified:	10 Jan 2014 12:18
Reporter:	Wes Deviers	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5.15, 5.5.23	OS:	Linux (CentOS 6)
Assigned to:		CPU Architecture:	Any
Tags:	replication

Description:
Slaves are using between 10-20% more disk space than masters.  For instance, on the master, we have this:

-rw-rw---- 1 mysql mysql 22196256768 Jan 24 09:38 path_step_scores.ibd
-rw-rw---- 1 mysql mysql 24343740416 Jan 24 09:38 sync_transactions.ibd

whereas, on the first slave, we have:

-rw-rw---- 1 mysql mysql 27179089920 Jan 24 10:41 sync_transactions.ibd
-rw-rw---- 1 mysql mysql 31981568000 Jan 24 10:41 path_step_scores.ibd

and on the second slave:

-rw-rw---- 1 mysql mysql 27187478528 Jan 24 11:01 sync_transactions.ibd
-rw-rw---- 1 mysql mysql 32006733824 Jan 24 11:01 path_step_scores.ibd

So the slave copies are consistently much larger.  I've gone over my settings a bunch of times and searched around for this as much as I can, so now I'm getting more concerned. 

Optimizing the table will generally bring it back in line with expectations.

How to repeat:
We've noticed this on 5.1 and 5.0 installs, but it's much lower (2-5%).  On 5.5.15 installs, using VMware templates, it's been much more consistently in the 10-30% range.  When you're cloning boxes out to make the slaves, this gets really annoying.

Do you update these tables on slave? Please, send my.cnf from master and one of slaves affected also.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Sorry, I must have missed the reply to my original ticket.  Both configs are identical, with the exception of the server ID and skip-slave-start on the master.  We don't do any kinds of changes on the slave DBs. Here's the master config:

[client]
port		= 3306
socket		= /mysql/tmp/mysql.sock
[mysqld]
port		= 3306
socket		= /mysql/tmp/mysql.sock
character-set-server=utf8
default-storage_engine = MyISAM
log-error 			=/mysql/log/error.log
general_log			=1
general_log_file		=/mysql/log/mysql.log
slow_query_log_file             =/mysql/log/mysql-slow.log
slow_query_log                  =1
basedir				=/usr/local/mysql
datadir				=/mysql/data
max_binlog_size			=256M
myisam_data_pointer_size	=7
pid-file 			=/mysql/tmp/mysqld.pid
relay-log 			=/mysql/binlog/mysqld-relay-bin
log-slave-updates		= 0
skip-name-resolve
optimizer_switch='index_merge=off'
skip-slave-start
back_log = 1000
max_connections = 1200
max_connect_errors = 6
table_cache = 4096
max_allowed_packet = 16M
binlog_cache_size = 10M
max_heap_table_size = 128M
sort_buffer_size = 64M
join_buffer_size = 64M
thread_cache_size = 16
thread_concurrency = 8
query_cache_size = 256M
query_cache_limit = 16M
ft_min_word_len = 4
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 128M
log-bin=/mysql/binlog/mysql-bin
long_query_time = 1
tmpdir = /mysql/tmp
server-id = 13016173
key_buffer_size = 256M
read_buffer_size = 16M
read_rnd_buffer_size = 128M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_file_per_table=1
innodb_file_format=barracuda
innodb_strict_mode=1
innodb_autoinc_lock_mode=0
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_size = 4800M
innodb_data_file_path = ibdata1:256M:autoextend
innodb_data_home_dir = /mysql/data
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 512M
innodb_log_files_in_group = 4
innodb_log_group_home_dir = /mysql/data
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqld_safe]
open-files-limit = 8192

Thank you for the feedback.

Do you update your slave by any means other than replication?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No, we never change the slaves via any other method. One is for read-only ops, and the other doesn't have any direct connections, it's only for backups.

Thank you for the feedback.

Verified as described.

Instructions how to repeat.

1. Create sbtest table with sysbench prepare for oltp test.
2. Change table definition, so it does not have MAX_ROWS option
3. Create gypsy query file:

$cat bug64121.query 
n|10000|insert into sbtest (k,c,pad) values(?,'?','?')|int,char,char
n|1000|delete from sbtest where id in(?,?,?,?,?)|int,int,int,int,int

4. Run gypsy:

gypsy --host=127.0.0.1:21178 --user=msandbox --password=msandbox --database=sbtest --queryfile=./bug64121.query --threads=20 --duration=100

5. Watch storage usage:

$ls -l  ~/sandboxes/rsandbox_5_5_23/master/data/sbtest/  ~/sandboxes/rsandbox_5_5_23/node1/data/sbtest
/home/ssmirnov/sandboxes/rsandbox_5_5_23/master/data/sbtest/:
total 791104
-rw-rw----+ 1 ssmirnov wheel        65 Apr 24 22:03 db.opt
-rw-rw----+ 1 ssmirnov wheel      8632 Apr 24 22:18 sb1.frm
-rw-rw----+ 1 ssmirnov wheel 251658240 Apr 24 22:56 sb1.ibd
-rw-rw----+ 1 ssmirnov wheel      8632 Apr 24 22:58 sbtest.frm
-rw-rw----+ 1 ssmirnov wheel 557842432 Apr 25 18:26 sbtest.ibd

/home/ssmirnov/sandboxes/rsandbox_5_5_23/node1/data/sbtest:
total 803394
-rw-rw----+ 1 ssmirnov wheel        65 Apr 24 22:03 db.opt
-rw-rw----+ 1 ssmirnov wheel      8632 Apr 24 22:18 sb1.frm
-rw-rw----+ 1 ssmirnov wheel 251658240 Apr 24 22:56 sb1.ibd
-rw-rw----+ 1 ssmirnov wheel      8632 Apr 24 22:58 sbtest.frm
-rw-rw----+ 1 ssmirnov wheel 570425344 Apr 25 18:26 sbtest.ibd

This can be considered feature request: I think problem happens because order of events executed on master is different from order of events stored in binary log.

[24 Jan 2013 9:07] Sveta Smirnova

This is not repeatable with 5.6 (I tested 5.6.8) As expected: highly concurrent updates on master lead to larger table size. With one thread master's table is slightly larger than slave's which is expected again.