Re: Sort_merge_passes increasing at 38 per minute...
> The only other piece of data I have is that in the other times when the
> transactions takes less than 1s, there were still 5 of these transactions per
> second.
And var_auction_id is the same for all these 5 transactions?
Assuming you have an index on auctions.id we need to distinguish between executing that transaction vs executing that transaction using the same var_auction_id. If you execute the transaction with two different var_auction_id values, the transactions should not block each other. If the two transactions run with the same id one will have to wait for the other.
Try the tricks on this page to identify lock waits.
Another possible cause of the problem is that MySQL might, potentially, choose to access the table using the index on id in most cases (where you observer low response time) and using other times (where the response time increases). This may happen if the internal estimates used to find the best way to execute the statement are almost identical. My bet is still on lock waits though.
Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com