Hi Team,

We are facing one production issue after enabling replication.

Production Description:

We have one MySQL server with DRBD after enabling MySQL Replication
the server is getting slow and application request queue is
increasing. If we disable MySQL replication application works fine and
application request queue is not increasing.

I checked OS level like CPU, RAM and Disk I/O, everything works well.

How to debug this issue?

Is a problem with MySQL replication with DRBD?

Why Bin-logs file size very huge(220 GB) for 10 GB Database(per day)?

Addition details after enabling replication:

CPU utilization: 8% max

Ram: 30 % out of 32 GB

Disk I/0: Nrmarl

CPU details:

RAM : 32 GB

CPU : 16 Cores

OS : Linux Red Hat 5.0

Mysql Deatil:

Version: 5.1

Database size : 100 GB

Datebase size per day: 10 GB

Bin-log size: 1 GB bin-log file creating for every 6 minutes/ 9 GB per
hour

Binlog_format: Statement

DMC on MySQL server: 80 % insert statements , 18% update statements
and rest are select statements.

sudheer <g.sudheer2000 (AT) gmail (DOT) com> wrote:
What are the binlog related settings?
Are you writing the binlog to the DRBD device?

Obviously not.

There is no fundamental problem with that. But writing the binlog
creates I/O load. And if you use sync_binlog!=0 (which makes kind
of sense) then this will be synchronous write load, which is
especially demanding.

DRBD already adds a penalty for writes, due to write barriers and
the added latency for protocol C. Also one has to put InnoDB table
spaces and logs onto the same DRBD device and cannot split I/O to
several spindles. Adding the binlog to that can make the difference.

Have you run iostat -x ? Normally this gives good diagnostics in
the last field (percent utilization). Approaching the 90% region
means your workload saturates your I/O system.

If you have battery-backed cache RAM on your disk controllers,
then you can

a) enable write-back write caching on the controller and
b) configure DRBD to be more relaxed about flushing (RTFM)

Both will help to manage heavy synchronous write load.

How do you measure that? And what exactly means "10GB per day"?
Increase in (table space) size?

Depends. But i.e. an UPDATE will not increase table space size, but
still needs to be logged. Same goes for DELETE followed by INSERT.

Could be worth to switch to ROW. This will be especially helpful if
you have many DML statements that hit no rows. Those will be logged
in STATEMENT format, but not in ROW format.

So you have only 2% SELECT? Overall? Or are there SELECTs going to
the replication slave(s)? Are you aware that replication makes no
sense for such write-mostly workload? You need at least 50% reads
or you won't have any benefit from replication at all.


XL