Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a mysql query that is joining a few tables and then uses order by field for sorting. It sometimes runs quite fast at other times it is slow. While it was slow i profiled the query to find that sorting was taking around 15 secs. But when it is fast the sorting takes only .5 seconds.

Please note it is the same query both the times on the same database. There are other queries running but nothing heavy.

It is bothering me, what might be causing this erratic behaviour

share|improve this question
    
Please to provide more information (table structures, the offending query, explain plans) –  RolandoMySQLDBA Apr 23 at 11:24

2 Answers 2

I don't know exacly what is happening, hard to tell. You may use trace to understand how much cpu it is using each time, try some test, maybe you find some pattern.

Here's a link that tells you how to use trace. http://www.noelherrick.com/blog/mysql-optimizer-trace

I hope helped you.

share|improve this answer

Each sort operation is performed in a memory area called sort buffer (and depending on the scenario, it can also use one or two temporary files). Even though the are other queries that run smoothly in parallel with yours, they might also use the sort buffer (maybe all of it), and this is why your query sometimes performs badly. If the sorting requests require a larger area, the engine might use a disk area (on those temp files) - which will surely impact performance.

You should monitor the sort buffer memory area, then adjust its size using sort_buffer_size or myisam_sort_buffer_size accordingly.

share|improve this answer
    
The sort buffer size looks like a good contender for the issue ... i will try that and let you know if things improve –  Abhi Apr 23 at 12:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.