mysql replication(复制)中 io 线程成为瓶颈的情况

cenalulu

摘要：
在用sysbench测试mysql replication时，发现大量的小语句（insert），导致replication io线程的relay，猜测：mysql 复制io线程可能对于大量小段信息的传输有瓶颈。
因此，作者之后采用 SET GLOBAL
slave_compressed_protocol=ON 的方法，启用压缩模式，进行replication。验证后发现io 效率提升，此时sql线程再次成为系统瓶颈。
I was debugging some repl delay monitoring metrics and I noticed
that the test I was doing (sysbench --test=oltp prepare) to generate
replication data was far outstripping the slave.  The SQL thread was
caught up to the IO thread, but the IO thread was way behind the master.

    Replicating from:           a2.db.bcp.re1.yahoo.com
    Master:                     a2_db_bcp_re1.000166/138395515
    Slave I/O:          Yes     a2_db_bcp_re1.000165/802640907  ???
    Slave Relay:        Yes     a2_db_bcp_re1.000165/802030586  596K
  198 secs


  In this case, the I/O thread was getting further and further
behind as sysbench did bulk inserts into my master.  My theory is that
a lot of relatively small binary log records simply don't transfer
efficiently.  That leaves the SQL thread idle some of the time waiting
for the IO thread, and leads it inefficient replication.

   I poked around the replication options manual page, looking for something to help and found this:
slave_compressed_protocol

  Hmm, looks promising.  I did 'SET GLOBAL
slave_compressed_protocol=ON' on both the master and slave, did a
'SLAVE STOP; SLAVE START' and suddenly things looked a lot better:

    Replicating from:           a2.db.bcp.re1.yahoo.com
    Master:                     a2_db_bcp_re1.000166/145629546
    Slave I/O:          Yes     a2_db_bcp_re1.000165/1014388288 ???
    Slave Relay:        Yes     a2_db_bcp_re1.000165/819116830  186M
    Replicating from:           a2.db.bcp.re1.yahoo.com
    Master:                     a2_db_bcp_re1.000166/148889364
    Slave I/O:          Yes     a2_db_bcp_re1.000166/125999252  22M
    Slave Relay:        Yes     a2_db_bcp_re1.000165/829490621  ???
    Replicating from:           a2.db.bcp.re1.yahoo.com
    Master:                     a2_db_bcp_re1.000166/152763939
    Slave I/O:          Yes     a2_db_bcp_re1.000166/152634478  126K
    Slave Relay:        Yes     a2_db_bcp_re1.000165/841084858  ???


Suddenly the IO thread is on the same binlog and close to the same
position as the master, while the SQL thread is behind.  This doesn't
catch up the slave, since the INSERTS still need to run, but at least
the SQL thread is running as fast as possible and not bumping into the
I/O thread.


Now this does come at a cost of some CPU on the master and slave,
but it doesn't seem like a huge amount.  How does the compression
algorithm work?  I have no idea.  But it does seem to work.  
               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/90603/showart_2077916.html