Home / server / Questions / 450048
Accepted
blacksoul
Asked: 2012-11-20 02:45:04 +0800 CST

MySQL master-slave lag replication

  • 772

Our company is using TokuDB on production and we are having a lot of problems trying to mitigate lag on our slave. Is very strange, because we're talking about very few rows... but with a few data it gets lagged.

Slave is a read-only DB.

For more information, we are using:

CPU: Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz (4 cores)

RAM: 16Gb

HDD: 2Tb ST2000DM001 (EXT4 filesystem)

Here you can see some I/O performance outputs. I paste it outside of this post, because I think that of this way it will easier for reading.

iostat -x 1 output, when we have a lag situation http://paste.laravel.com/bjv

fio, for disk I/O: http://paste.laravel.com/bjG

We did a few disk tweaks, extracted from Steven Corona's book http://www.scalingphpbook.com:

  • Changed I/O scheduler to noop.
  • Turned off file system access times, noatime and nodiratime in /etc/fstab
  • Increased the number of open files, in /etc/security/limits.conf:
* soft  nofile  999999
* hard  nofile  999999

Some tweaks we have made in config:

MASTER

# * Query Cache Configuration
query_cache_limit               = 0
query_cache_size                = 0
query_cache_type                = 0
innodb_file_per_table           = 1

innodb_file_format              = barracuda
innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 1
innodb_log_file_size            = 128M

innodb_buffer_pool_size         = 13500M
innodb_read_io_threads          = 16
innodb_write_io_threads         = 16
innodb_io_capacity              = 180
innodb_thread_concurrency       = 4

SLAVE

# * Query Cache Configuration
query_cache_limit               = 0
query_cache_size                = 0
query_cache_type                = 0
innodb_file_per_table           = 1

innodb_file_format              = barracuda
innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 2
sync_binlog                     = 1

innodb_log_file_size            = 128M
innodb_buffer_pool_size         = 13500M
innodb_read_io_threads          = 16
innodb_write_io_threads         = 16

innodb_io_capacity              = 180
innodb_thread_concurrency       = 4
linux

1 Answers

  1. Best Answer

    I think this is already solved, due to @symcbean's help.

    I disabled barriers, set data=ordered, used async commit and checksums. Finally, we migrated to MariaDB, but still having TokuDB

    I forget to mention that we also set tokudb_cache_size = 8G, as TokuDB recommends, 50% of physical memory

    • 1