2

I am working on a replication problem that completely baffles me! This client has two MySQL replication clusters on HUGE bare metal HW. See Environment below.

The IO_Tread of the slave is WAY behind, several hours or more. Yes, the IO_tread, not the SQL_tread. Why is it so hard to download not all that large binlog records and write them to disk. I tried finding a resource bottleneck, but given the massive HW none can be found.

The only strange observation is that the slave has 8x the IO OPS than the master. But even this does not really overload the SSD disk. Packet traces show the SLAVE often setting the TCP window to zero. Why, there are plenty resources?

Anyone with ideas what could be causing this strange behaviour? Why do I have more IO on the slave? What can cause the IO_tread to slow down?

Environment: Both machines: Bare metal DELL, MySQL 5.6.30, 12CPUs, 128GB mem, datadir on SSD, Net I/F: Emulex 10Gb, ROW based binlog FMT

Symptoms:

MASTER: CPU: 67% 1 processor lightly used, MEM: 70% used, 30% free, IO OPS: ~2500 tps, 30% util on SSD, slave client tread: Send binlog to slave.

SLAVE:

CPU: 40% 1 processor lightly used, MEM: 70% used, 30% free, IO OPS: ~16000 tps, 70% util on SSD, Error counters on net I/F are 0 (zero), TCP window is often set to 0 on IO_tread, Slave IO_tread is VERY slow. Lags more than an hour!

Another slave on SAME master has no trouble at all! This slave has much lower HW spec!

Trouble downloading master binlog. Why this insanely high IO rate?

Stopping the slave also stops the IO OPS. (As expected, the OPS are from MySQL)

Copying large amounts of data from master to slave over the network (using ncat) show performance as expected.

Other observations:

When reversing the roles the problem stays the same.

Another replication cluster with same HW has no trouble. IO OPS in this cluster on slave are slightly less than on master. This cluster uses STATEMENT based binlog

1

If the I/O thread is behind, then the network is slow.

If the SQL thread is behind, then it could be the serial nature of replication (unless you have a new version) or contention from SELECTs, or disk I/O, or hardware differences (generally, a slave should be at least as powerful as the master), etc.

If you are doing huge UPDATEs or DELETEs, then Row Based Replication puts lots of stuff in the replication stream (binlog). Could this be relevant?

"MASTER: CPU: 67% 1 processor lightly used" -- Even 67% of one Core is rather high. Perhaps missing some needed composite indexes?

Is the Slave doing lots of SELECTs that could be interfering with replication?

What are the values of SHOW VARIABLES LIKE 'query_cache%'; on both machines? Don't set `query_cache_size bigger than, say, 50M, regardless of the amount of RAM.

How many GB of binlogs are created per hour?

1

Sorry, I need a 50 rep to comment, so... Here's a couple of things to start.

  1. Are you by chance using ZFS for your filesystem (it can throttle the disk unexpectedly)?
  2. Are your config files (nearly) identical (with exception of replication master/slave settings)? The config should be the same as the other slave except maybe memory buffers (sounded like the server HW was different). https://www.percona.com/doc/percona-toolkit/2.2/pt-config-diff.html
  3. (Run on the slave, and post) SHOW SLAVE STATUS\G
  4. (Run on the master, and post) SHOW MASTER STATUS\G
  5. Check the Slave Status a few times (maybe a minute apart). Are both Exec_Master_Log_Pos and Read_Master_Log_Pos (or Retrieved_Gtid_Set and Executed_Gtid_Set), moving (ideal is if they are catching up)?
  6. Are the relay logs being stored as tables or files?

Edit After reading Rick's post, I went back and noticed (thought at first both were SBR) your cluster that runs faster is running SBR (statement based replication), and the slow one is RBR (row based). Rick's question about the type of queries and how many binlogs you generate in say an hour is important.

Sometimes RBR is faster, and sometimes SBR wins. Always important to test and benchmark all scenarios.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.