MySQL复制性能

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

最近我们需要暂停复制大约是一个大问题。 3小时。花了大约10个小时才赶上，没有其他的负担。

赶上10个小时

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

切换到基于行的复制。在testing中，这只会使性能提升10-20％
使用multithreading复制升级到mySQL 5.6。我们可以很容易地将我们的数据分成不同的数据库，基准似乎表明这将有所帮助，但代码似乎并没有准备好。
一些configurationvariables将有助于加速复制

主要问题是 ，如果暂停3小时需要10小时才能完成，这意味着复制在10小时内写入13小时的数据，或者能够以130％的数据速度写入数据。在不久的将来，至less会在主机上写两次，所以迫切需要一种方法来提高复制性能。

机器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的Ram
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

哇，这个问题你有一些非常有用的硬件。除了升级到Sandy / Ivy Bridge CPU，Btreesearch等性能提高20-50％之外，没有太多的东西可以在硬件方面投入。

请注意，我的专长是Innodb，所以我要去

忽略你是myisam，并行事，如果它不会有所作为。
假设这个问题有足够的动力让你升级。是的，这是一个升级。

Innodb可以通过将这些经常访问的行存储在缓冲池中来帮助充分利用所有内存。你可以调整它的大小（比如80％的内存），新的读写保留在内存中，直到需要把它们推到磁盘上，为最新访问的数据腾出更多的空间。在内存中比您的FusionIO快一个数量级。

还有更多的Innodbfunction，比如自适应哈希，自动locking机制等等，这些都可以为您的环境带来益处。然而，你比我更了解你的数据。

在innodb世界里，一个好的短期解决scheme是优化你的奴隶 – 你真的需要你的奴隶上的每一个索引，你有你的主人吗？索引是一个插入/更新/删除的球和链，即使与Fusion IO卡。 IOPS并不是一切。 Sandy / Ivy Bridge procs具有更好的内存吞吐量和计算性能 – 它们可以对现在的Westmeres产生巨大的影响。（总体图20-50％）。删除从机上不需要的所有索引！

其次，几乎肯定只适用于innodb，是否mk预取可以知道哪些更新，并在奴隶写入之前。这允许mk-prefetch首先运行读取查询，从而在单个repl运行写入查询时强制数据在内存中。这意味着数据在内存中，而不是在fusionIO中，这是一个快速的数量级性能增益。这是一个巨大的差异，不止一个人可能期望的。许多公司使用这个作为一个永久的解决scheme。通过检查Percona工具包了解更多信息。

第三，也是最重要的是，一旦你升级到Innodb，一定要检查Tokutek。这些人有一些可怕的东西超出了Innodb的写/更新/删除性能的远射。他们认为提高复制速度是一个关键的好处，从基准testing中可以看出，为什么融合疯狂的IOPS 在Btrees的情况下仍然无法帮到你。（注：并非由我独立validation）他们使用一个btree索引的drop-inreplace，虽然比较复杂，但是改进了btree索引的许多algorithm速度限制。

我正在考虑采用Tokutek。如果他们释放了这么多的写入速度，那就允许我添加更多的索引。由于他们压缩数据和索引在这样美妙的比率（他们引用的25倍），你甚至不支付增加数据（性能，维护）的价格。你们支付（$）他们的引擎，但是每个预压缩的GB，IIRC 2500美元/年。如果你有复制的数据，他们有折扣，但你甚至可以只在你的奴隶安装Tokutek，并保持你的主人。查看麻省理工学院开放课件讲座中的技术细节。另外，他们在自己的博客上有很多技术性的东西，对于那些没有1:20观看video的人来说，他们定期写白皮书。我相信这个video也给出了读取速度有多快的Big-O公式。我必须假定读取速度较慢（总是有一个折衷！），但是对于我来说，这个公式太复杂了。他们声称它大致相同，但我宁愿理解math（不太可能！）。你可能比我更好地发现这一点。

Ps我不隶属于Tokutek，我从来没有运行他们的产品，他们甚至不知道我正在看他们。

更新：

我看到你在这个页面上还有其他的问题，

首先，奴隶预取几乎肯定不会为myisam工作，除非你有一个特殊的环境。这主要是因为预取将locking您要写入的表，或者从属线程locking预取守护程序所需的表。如果你的表格非常适合于复制，并且不同的表格是以循环方式写入的，这可能会奏效 – 但是请记住这是非常理论化的。本书“高性能Mysql”在“复制问题”一节中有更多信息。

其次，大概你的奴隶拥有1.0-1.5的负载，如果你有其他的特殊处理或查询运行，但基线为1.0，那么它可能会更高。这意味着你可能会受到CPU的限制，这可能与你的FusionIO板载。正如我前面提到的那样，桑迪/常春藤桥将给予更多的赞扬，但可能不足以让你在最短的时间内完成更为艰难的时期。如果这个从站的负载大部分是只写的（即读取次数不多），那么你的CPU几乎可以肯定是花在计算位置上的时间来进行插入/删除。这应该强化我的观点，关于删除非关键指标 – 您可以随时重新添加它们。禁用超线程将不起作用，更多的CPU不是你的敌人。一旦你获得32GB以上的内存，比如说64GB，你就需要担心内存分配问题，但即使如此，症状也是不同的。

最后，也是最重要的一点（不要跳过这一部分）），我假设你现在正在运行RBR（基于行的复制），因为你在切换的时候提到了不小的性能提升。但是 – 这里可能会有更多的performance。 Mysql错误53375可以清单，如果你有没有主键复制表。从属基本上不够聪明，除了使用主键之外的任何东西，所以没有一个强制复制线程对每次更新都进行全表扫描。一个解决方法是简单地添加一个良性的，代理自动增量主键。如果桌子很大（比如几十几千行或更多），我只会这样做。当然，这是以牺牲桌面上的另一个索引为代价的，这会带来你在CPU中支付的价格。请注意，对此有很less的理论争论，因为InnoDB在幕后增加了一个，如果你不这样做的话。不过，幻影对53375来说并不是一个有用的防御手段。钨也可以克服这个问题，但是当你使用钨的时候，你需要确定你的编码是直的。我最后一次玩它，当任何非UTF8string需要复制时，它会死亡可怕。那是我放弃的时间。

不是一个答案，但你可能会考虑钨复制器和他们的商业产品更灵活。单核是100％的CPU使用率是瓶颈吗？

所以，如果你在slave上做备份，并且你使用myiasm表，你正在locking表来做备份以防止损坏。所以复制不能工作，直到备份完成..然后它赶上。