MySQL Bugs: #56532: Processing And Output Gets Slow Very Very Slow

Bug #56532	Processing And Output Gets Slow Very Very Slow
Submitted:	3 Sep 2010 11:37	Modified:	3 Sep 2010 13:06
Reporter:	Devang Modi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.0.45-community-log	OS:	Linux (Linux 2.6.18-128.el5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86_64 x86_64 x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	Output gets very very slow

Description:
Dear Supporter;

We are using MySQL on a server with 32 GB RAM and Dual 56XX Xeon Server.

We are sing two types of engine in our databases, one is InnoDB and second one is MyISAM.

Our primary database is having InnoDB Type (1 Database) and all arch. databases (20) are having MyISAM.

We are facing one typical , tedious , critical and annoying issue with MySQL

that is "During weekdays any single day randomly MySQL performance degraded very very badly."

During this degraded performance time some of reports those are issuing big bunch of queries are performing very bad.

Now, big bunch means a report is issuing 16,00,000 select queries withing 45 minutes.

I am not sure what could be the problem.

There could be two issue

1. MySQL Configuration
2. MySQL internal bug.

I took snapshot of various command from Operating System but all are showing is hardware is working fine. Commands those I used are sar -u ,sar -r, netstat -n DEV, strace, pmap, lsof and tcpdump.

Majority this reports are using InnoDB so I also took snapshot of "Show status innodb".

I am sharing some files here with for your reference.

One interesting thing I found today is : during this degraded performance time when we restart MySQL service the performance is dramatically got NORMAL.

Can you please study it and guide me that what could be the issue that is creating trouble for us.

Attached file:
1. my.cnf - MySQL Configuration file
2. degraded.txt - InnoDB Status when Performance is very very degraded
3. normal.txt - InnoDB Status when performance is normal
4. compare.xls - Comparison of Global Status Variable Values during performance is degraded.

Devang

How to repeat:
No guidelines

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.