Centos – MySQL replication slowing master

centosMySQLmysql-replication

We are currently running MySQL 5.1

Quick Overview

We use 5 web servers ( Apache ) all connected to 1 DB server ( MySQL ) hosted on the cloud. The DB server running on the cloud ( Master ) replicates to a local DB server ( Slave ) here at our office.

Problem

We have noticed when the slave is turned on the masters process list starts loading up with unauthenticated users. Soon as we stop the slave the masters process list slowly goes back down to normal.

RAM/SWAP look fine and there are no slow queries running to explain the buildup in the MySQL process list.

Could this be a network bottleneck? I thought it might be a slow HDD but it doesn't seem to matter what size the bin-log is when its running slow.

Best Answer

You could monitor the thread traffic from MySQL's point-of-view with two status variables

How does Replication affect these status variables ?

  • Bytes_sent : IO Thread requesting binlog entries from the Master
  • Bytes_received
    • IO Thread reading binlogs entries from its Master
    • SQL thread reading its own relay logs

I have discussed this in the DBA StackExchange

The bottleneck is definitely the outgoing traffic from the Cloud Server communicated through the IO Thread.

SUGGESTIONS

Related Topic