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

摘要：
在用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.