MySQL Bugs: #57351: "Sorting Result" is too much slow

Bug #57351	"Sorting Result" is too much slow
Submitted:	10 Oct 2010 5:36	Modified:	10 Oct 2010 7:17
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 MySQL Community Edi	OS:	Linux (Red Hat Enterprise Linux Server release 5.1 (Tikanga))
Assigned to:		CPU Architecture:	Any
Tags:	result, slow, sorting, too

Description:
Dear Supporter;

A day ago we migrated one of our server hardware and new hardware is having 48 GB RAM and Dual Xeon 56x processors. This is a dedicated database server with no extra load.

After migration we found that query is slowing down at the time "Sorting Result" state and start taking too much time.

with this I am attaching my.cnf and out come of "Show engine innodb status" and process status too.

We are using RHEL Red Hat Enterprise Linux Server release 5.1 (Tikanga) and default MySQL received with it.

Devang 

How to repeat:
Difficult

Dear supporter;

I forgot to mentioned few points

1. The query hit rate is too high when the incident occurred.
2. When I tried to issue same query manually it worked perfectly.
3. We recently started using Query Cache which 512MB

Devang

SLOW QUERY log did not have any query to show .

Devang

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.

Your sort_buffer_size = 512M in my.cnf is unrealistically high.