☰

MySQL 复制性能

tag-icon • tag-icon mysql performance replication myisam
MySQL 复制性能

我在两台机器之间遇到了 MySQL 5.5 复制性能的严重问题，主要是基于语句的复制的 myISAM 表。二进制日志和 mysql 数据目录都位于同一个 Fusion ioDrive 上。

这个问题最近成了大问题，我们需要暂停复制约 3 小时。在没有其他负载的情况下，大约需要 10 小时才能再次赶上。

10 小时赶上

我如何提高复制的性能？机器 B 基本上处于空闲状态（很少，IO，16 个核心中有 2 个已满，有大量可用 RAM），因为只有 1 个 mySQL 线程正在写入数据。以下是我的一些想法：

  • 切换到基于行的复制。在测试中，这只带来了 10-20% 的性能提升
  • 升级到具有多线程复制功能的 mySQL 5.6。我们可以轻松地将数据拆分到单独的数据库中，基准测试似乎表明这会有所帮助，但代码似乎还未准备好投入生产。
  • 一些有助于加速复制的配置变量

主要问题是如果暂停 3 小时后需要 10 小时才能赶上，这意味着复制在 10 小时内写入了 13 小时的数据，或者能够以数据传入速度的 130％ 进行写入。我希望在不久的将来在主机上至少实现双倍写入，因此迫切需要一种方法来提高复制性能。

机器 A：

  • 掌握
  • 24GB 内存
  • 1.2TB Fusion ioDrive2
  • 2 个 E5620
  • 千兆互连

my.cnf：

[mysqld]
server-id=71
datadir=/data_fio/mysqldata
socket=/var/lib/mysql/mysql.sock
tmpdir=/data_fio/mysqltmp

log-error = /data/logs/mysql/error.log
log-slow-queries = /data/logs/mysql/stats03-slowquery.log
long_query_time = 2
port=3306

log-bin=/data_fio/mysqlbinlog/mysql-bin.log
binlog-format=STATEMENT
replicate-ignore-db=mysql

log-slave-updates = true

# Performance Tuning
max_allowed_packet=16M
max_connections=500
table_open_cache = 2048
max_connect_errors=1000
open-files-limit=5000

# mem = key_buffer + ( sort_buffer_size + read_buffer_size ) * max_connections
key_buffer=4G
max_heap_table_size = 1G
tmp_table_size = 4G
myisam_sort_buffer_size = 256M
sort_buffer_size=4M
read_buffer_size=2M
query_cache_size=16M
query_cache_type=2
thread_concurrency=32

user=mysql

symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
socket=/var/lib/mysql/mysql.sock

[client]
socket=/var/lib/mysql/mysql.sock

机器B：

  • 奴隶
  • 36GB 内存
  • 1.2TB Fusion ioDrive2
  • 2 个 E5620
  • 千兆互连

my.cnf：

[mysqld]
server-id=72
datadir=/data_fio/mysqldata
socket=/var/lib/mysql/mysql.sock
tmpdir=/data_fio/mysqltmp

log-error = /data/logs/mysql/error.log
log-slow-queries = /data/logs/mysql/stats03-slowquery.log
long_query_time = 2
port=3306

# Performance Tuning
max_allowed_packet=16M
max_connections=500
table_open_cache = 2048
max_connect_errors=1000
open-files-limit=5000

# mem = key_buffer + ( sort_buffer_size + read_buffer_size ) * max_connections
key_buffer=4G
max_heap_table_size = 1G
tmp_table_size = 4G
myisam_sort_buffer_size = 256M
sort_buffer_size=4M
read_buffer_size=2M
query_cache_size=16M
query_cache_type=2
thread_concurrency=32

user=mysql

symbolic-links=0

plugin-load=archive=ha_archive.so;blackhole=ha_blackhole.so

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
socket=/var/lib/mysql/mysql.sock

[client]
socket=/var/lib/mysql/mysql.sock

答案1

哇，你拥有非常强大的硬件来解决这个问题。硬件方面，你能投入的资源不多了，除非升级到 Sandy/Ivy Bridge CPU，这样 Btree 搜索的性能会提高 20-50%，等等。

请注意，我的强项是 Innodb，所以我将

  1. 忽略您是 myisam 并且表现得好像它不会造成任何影响。
  2. 假设这个问题足以促使你升级。是的，这是一次升级。

Innodb 可以通过将这些频繁访问的行存储在缓冲池中来充分利用所有内存。您可以将其调整到所需的大小（例如 80% 的内存），并且新的读取/写入仍会保留在记忆中直到需要将它们推送到磁盘以便为最新访问的数据腾出更多空间。内存中的速度比 FusionIO 快一个数量级。

Innodb 还有许多其他功能，例如自适应哈希、自动增加锁定机制等，这些功能可以为您的环境带来好处。但是，您比我更了解您的数据。

在 innodb 世界中，一个好的短期解决方案是优化你的从服务器 - 你真的需要主服务器上的所有索引吗？索引是插入/更新/删除的束缚，甚至使用 Fusion IO 卡。IOPS 并不是这里的一切。Sandy/Ivy bridge 处理器具有更好的内存吞吐量和计算性能 - 它们可以对您现在拥有的 Westmeres 产生巨大的影响。（图 20-50% 总体）。删除全部在从属服务器上不需要索引！

其次，几乎肯定只适用于 innodb，即 mk-prefetch 可以在从属写入之前知道哪些更新。这允许 mk-prefetch 首先运行读取查询，从而在单个 repl 运行写入查询时强制数据位于内存中。这意味着数据在内存中而不是在 fusionIO 中，这是一个快速的数量级性能提升。这使得巨大的差异可能超出预期。许多公司将此作为永久解决方案。查看Percona 工具包。

第三，也是最重要的一点，一旦你升级到 Innodb，一定要看看 Tokutek。这些家伙有一些非常棒的东西，远远超过了 Innodb 的写入/更新/删除性能。他们宣称提高复制速度是主要优点之一，你可以从他们的基准测试中看到为什么 Fusions 疯狂的 IOPS对于 Btrees 来说，仍然帮不了你。（注：我并未独立验证过。）他们使用 btree 索引的直接替换，虽然非常复杂，但却改善了 btree 索引的许多算法速度限制。

我正在考虑采用 Tokutek。如果他们能释放如此多的写入速度，我就可以添加更多索引。由于他们以如此出色的比率（他们报价的 25 倍）压缩数据和索引，您甚至无需为增加的数据支付（性能、维护）费用。不过，您需要为他们的引擎支付 ($)，每预压缩 GB 2500 美元/年，如果我没记错的话。如果您复制了数据，他们会提供折扣，但您甚至可以只在从服务器上安装 Tokutek，而保持主服务器原样。查看MIT 算法开放课程讲座。另外，他们的博客上有大量技术资料，并且定期提供白皮书，供那些没有 1 分 20 秒时间观看视频的人参考。我相信这个视频还给出了读取速度的 Big-O 公式。我有假设读取速度较慢（总是有权衡！），但公式太复杂了，我无法判断到底有多慢。他们声称大致相同，但我宁愿理解数学（不太可能！）。你可能比我更能发现这一点。

附言：我与 Tokutek 没有任何关系，我从未使用过他们的产品，他们甚至不知道我在看他们。

更新：

我看到你在这个页面上还有其他一些问题，我想我可以插话一下：

首先，除非您拥有一个特殊的环境，否则从属预取几乎肯定不会对 myisam 起作用。这主要是因为预取将锁定您打算写入的表，或者从属线程锁定了预取守护进程所需的表。如果您的表在复制方面非常平衡，并且以循环方式写入不同的表，那么这可能会起作用 - 但请记住，这只是理论上的。《高性能 Mysql》一书在“复制问题”部分提供了更多信息。

其次，假设您的从属服务器的负载为 1.0-1.5，如果您正在运行其他进程或查询，但基线为 1.0，则负载可能会更高。这意味着您可能受到 CPU 限制，这很可能是由于您安装了 FusionIO。正如我之前提到的，Sandy/Ivy Bridge 会提供更多功能，但可能不足以让您以最小的延迟度过艰难时期。如果此从属服务器上的负载主要是只写（即没有太多读取），您的 CPU 几乎肯定会花时间计算 btree 插入/删除的位置。这应该强化我上面关于删除非关键索引的观点 - 您以后可以随时重新添加它们。禁用超线程不起作用，更多的 CPU 不是您的敌人。一旦您的 RAM 超过 32GB，比如 64GB，您就需要担心内存分配，但即便如此，症状仍然不同。

最后，也是最重要的（不要跳过这部分；）），我假设你现在正在运行 RBR（基于行的复制），因为你提到切换到它时性能会有不小的提升。然而 - 可能有办法让更多的在这里表演。Mysql 错误 53375如果您复制的表没有主键，则会出现这种情况。从服务器基本上不够聪明，无法使用除主键之外的任何东西，因此缺少主键会迫使复制线程对每次更新。解决方法是简单地添加一个良性的、替代的自动递增主键。我只会在表很大（比如说有几万行或更大）时这样做。当然，这样做的代价是要在表上再加一个索引，这会增加 CPU 成本。请注意，反对这一点的理论论据很少，因为如果您不这样做，InnoDB 会在后台添加一个。然而，幻影主键并不能有效防御 53375。Tungsten 也可以解决这个问题，但您需要确保在使用 Tungsten 时，您的编码正确。上次我使用它时，当任何非 UTF8 字符串需要复制时，它就会死机。那是我放弃它的时候了。

答案2

不是一个答案，但你可以考虑钨复制器及其商业产品以获得更多灵活性。单核上 100％ 的 CPU 使用率是瓶颈吗？

答案3

因此，如果您在从服务器上进行备份，并且使用 myiasm 表，则在执行备份时会锁定表以防止损坏。因此，复制在备份完成之前无法进行，然后才能赶上进度。

相关内容