Mysql慢从

Question

背景：

我们在一个数据中心有我们的主要数据库，这些数据库都正常工作。为了备份，我想在我们的办公室添加一个从服务器。我已经将DC (/var/log/mysql、/var/lib/mysql、/etc/mysql/my.cnf)上的所有数据复制到了office服务器上。office服务器还具有与DC上的从服务器相同的规范。4核处理器，4Gb RAM和RAID 10 SSD驱动器。

问题

办公室里的奴隶花了很长时间才赶上主人，而我却在努力找出原因。当我查看/var/ log /mysql文件夹时，它肯定不是逗号，我可以看到，最新的mysql中继文件已经到了其中的第二个数据，尽管当我运行mysql从状态时，它仍然在从包含前一天数据的中继日志中读取数据。

当我运行时，似乎没有任何东西出现，因此似乎查询正在快速执行，而在缓慢的查询日志中也没有显示任何内容。

我用dd测试了硬盘的写入速度，用了6秒的时间将一组数据写入数据中心为5.5的驱动器，所以我看不出这也是个问题。

以下是my.cnf文件的内容。我在rackspace的云服务器上做过完全相同的事情，以前没有遇到过这个问题。

任何帮助都将不胜感激。

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

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]

skip-slave-start
log-bin
expire_logs_days = 0
max_binlog_size = 100M

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
skip-external-locking

# Charset and Collation
character-set-server = utf8
collation-server     = utf8_general_ci
event_scheduler      = 0



#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address            = 0.0.0.0
#
# * Fine Tuning
#
key_buffer_size         = 256M
max_allowed_packet      = 16M
thread_stack            = 256K
thread_cache_size       = 8
sort_buffer_size        = 2M
read_buffer_size        = 128k
read_rnd_buffer_size    = 256k
join_buffer_size        = 128k

auto-increment-increment = 1
auto-increment-offset    = 1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover          = BACKUP
max_connections         = 800
max_connect_errors      = 10
concurrent_insert       = 2
connect_timeout         = 10
wait_timeout            = 180
net_read_timeout        = 30
net_write_timeout       = 30
back_log                = 128
table_cache             = 128
table_open_cache        = 128
tmp_table_size          = 32M
max_heap_table_size     = 32M
bulk_insert_buffer_size = 32M
open-files-limit        = 1024


query_cache_limit       = 1M
query_cache_size        = 16M

log_slow_queries        = /var/log/mysql/slow.log
long_query_time         = 2

server-id = 100
binlog_format           = statement
log-slave-updates       = ON
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_cache_size       = 32K
relay_log               = /var/log/mysql/mysql-relay-bin.log
sync_binlog             = 0
slave_compressed_protocol = 0
innodb_data_home_dir            = /var/lib/mysql
innodb_log_group_home_dir       = /var/lib/mysql
innodb_file_per_table
innodb_table_locks              = true
innodb_lock_wait_timeout        = 60
innodb_thread_concurrency       = 4
innodb_commit_concurrency       = 4
innodb_support_xa               = true

innodb_buffer_pool_size = 128M
innodb_log_file_size    = 2000M
innodb_additional_mem_pool_size = 8M
innodb_data_file_path   = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size  = 8M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[myisamchk]
key_buffer                = 16M

myisam_sort_buffer_size   = 8M
myisam_max_sort_file_size = 2147483648
myisam_repair_threads     = 1
myisam-recover            = BACKUP

Answer 1

从延迟是一个非常常见的问题，正如您已经检查过的，原因通常不是服务器之间通信的带宽或延迟(IO/ Thread需要)，而是涉及到的另一个线程，它将自己的中继日志应用到服务器: SQL thread。

直到最近，在默认配置下，在从服务器上应用主服务器提交的事务的唯一方法是使用单个线程，这意味着，如果主服务器的平均并发性超过1，或者主服务器上有一些长期运行的事务(ALTER，多行更新等)，则该从线程可能会滞后。

为了避免这个问题，您可以做几件事：

• 将主服务器上的binlog_format = STATEMENT更改为binlog_format = ROW。这将减少查询应用的延迟，因为它几乎总是使用带点写的主键写入行。主要的缺点是，如果每个查询修改了很多行，那么所使用的带宽就会更高--而且，它的灵活性也会降低一些。在从服务器上，绑定日志在技术上是不必要的，除非您希望在该服务器上执行实时恢复，否则您将在将来将其提升到主服务器。在这种情况下，您可以禁用binlog以获得某些写性能。
• 如果您正在使用多个模式和MySQL 5.6，则可以启用与slave_parallel_workers并行处理到>1的事务。请注意，只有5.7才允许真正的并行性- -5.6需要在单独的数据库上进行更改才能利用此功能。
• 减少从集innodb_flush_log_at_trx_commit = 2 (或0)的写入开销--这将降低在崩溃情况下从服务器的持久性，但会显着地提高写入速率，并且您已经让主服务器提供了完整的持久性。对于从服务器的写入性能，您可以做更多的事情，例如禁用binlog和慢速日志，确保使用组提交，或者在其单独的驱动器上设置事务日志等。

但是，还有其他非常可疑的事情会导致性能不佳(无论是在主从还是从上)，例如：

innodb_thread_concurrency       = 4
innodb_commit_concurrency       = 4
innodb_support_xa               = true
innodb_buffer_pool_size         = 128M

但这需要对您的系统、应用程序和硬件有更深入的了解。