Re: Sort_merge_passes increasing at 38 per minute...

[phil yune]

I actually do have a query that's acting weird. The transaction below usually takes < 1s, but it will hit > 2s 1-2 times per minute. The table is really small - 4000 rows or so. InnoDB with REPEATABLE READ. The same row gets read 10 times per minute or so. I need some help figuring out why it spikes to > 2s when it's only a 4000 row table. Thanks!!

START TRANSACTION;
SELECT end_time INTO currentEndTime FROM auctions WHERE id=var_auction_id;

IF (ADDTIME(currentEndTime , var_time_increment) < NOW()) THEN
UPDATE auctions SET end_time = ADDTIME(NOW(), var_time_increment), price = price+var_price_increment, leader_id = var_leader_id, modified = NOW() WHERE id = var_auction_id AND closed = 0;
ELSE
UPDATE auctions SET end_time = ADDTIME(end_time, var_time_increment), price = price+var_price_increment, leader_id = var_leader_id, modified = NOW() WHERE id = var_auction_id AND closed = 0;
END IF;
SELECT ROW_COUNT() INTO myRowCount;

IF (_error) THEN
ROLLBACK;
ELSE
SET var_return = myRowCount;
COMMIT;
END IF;