Thursday, September 11, 2008

big sort_buffer_size causes slow filesort

I spent several hours tonight trying to track down why a 12-row filesort was taking upwards of 30ms on our production DBs. After installing 4 different versions of mysql and not figuring anything out, I finally took a guess and decreased sort_buffer_size from 64M to 256K.

I'm not entirely sure why I thought a 64M sort buffer was a great idea, but it turns out it's a terrible one! After setting it to 256K the query returns in 0.00sec as expected.

Looks like the guys at Percona have noticed this too.

No comments: