MySQL Bugs: #68679: sudden increase in disk i/o

Bug #68679	sudden increase in disk i/o
Submitted:	15 Mar 2013 1:58	Modified:	22 Mar 2013 15:42
Reporter:	Kunta Hutabarat	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.91	OS:	Linux (CentOS 5, 2.6.18-238.12.1.el5 #1 SMP Tue May 31 13:22:04 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux)
Assigned to:		CPU Architecture:	Any

Description:
We have a product that consists of two servers that each have a MySQL server using Multi-Master Replication. Only one of those MySQL servers is accessed directly by our application. For clarity, let's call the server being accessed directly the Active server and the other the Passive server. As you may know, with this multi-master replication, the Active is the master to the Passive, the active is the slave the the Passive, the Passive is the master to the Active, and the Passive is the slave to the master.

After some time, there is a sudden increase in disk i/o on the Active server. It got significantly better after we dropped the db and reloaded it. And better still after we reset the replication.

Using atop, atopsar, iostat -x 15, top, we determined that on a normal working system under heavy load, the disk usage is about 1 - 3%. On the affected system which the customer took out of service and hence is under minimal load, the disk usage is about 15%.

After we dropped the db and reloaded it, the disk usage was significantly better. The disk usage dropped to 5 - 8% with occasional spikes up to 90%.

After we reset the replication, the disk usage is somewhat normal in the 1 - 3% range but we are still seeing occasional spikes up to 90%.

To analyze some more, we ran the command SHOW GLOBAL STATUS was repeatedly at about once a minute. We ran this on the Active Affected System, the Passive of the affected system, on the Active Affected System where the Passive was not running, and a Normal Healthy Active System. I will attach a file with those results.

How to repeat:
Create Multi-Master replication setup where you have an Active mysql server and a Passive mysql server. Where Active means the MySQL server that is accessed directly via an application and the Passive only changes because of the replication.

Then run commands on the active one and eventually the disk usage on the passive one will increase.

SHOW GLOBAL STATUS data

Attachment: mysqlBugReportAttachment.txt (text/plain), 13.67 KiB.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.
-----
I simply see no sign of evidence of any bug in MySQL with this report.  This is a support request, not a bug report.

Hi Shane,

The reason we suspected a bug was because the disk i/o was around 15%. After we dropped the database, deleted, and reloaded and then reset the replication, it dropped to around 2 - 3%. The data and the replication setup is exactly the same. 

Is it normal/expected for mysql disk i/o to increase an order of magnitude and then "fix" itself by reloading the DB and resetting the replication? 

Thanks
George David
Polycom
Staff Software Engineer