CODEWITHSUNDEEP
mysqlperformancestored-procedurestransactionsinnodb
Kyra
Kyra

Reputation: 31

MySQL transaction with 1 update statement takes >4s in a 4000 row table

Most of the time this transaction takes <1s, average of 200ms or so. But occasionally, it takes >4s! This sproc gets run 5-6 times per second or so.

My sproc is pretty simple (innoDB - REPEATABLE READ):

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;

I want to figure out what's causing the 4s spikes, things I have tried:

  1. I thought it might be concurrent, but I've seen this sproc called 5 times in 1 second, and those transactions take <100ms. And for the 4s ones, it's not that many concurrent transactions

  2. Index is set properly on id

  3. Table is small... 4000 rows or so.

  4. Can't really run slow query log since it's MySQL 5.0, want to avoid rebooting the server to turn on the slow query flag unless it's the last resort.

I need some suggestions on the cause or what else to investigate.

Upvotes: 3

Views: 197

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Not sure if this will help but these lines:

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;

can be rewritten as:

    UPDATE auctions 
    SET end_time 
        = ADDTIME( CASE WHEN ADDTIME(end_time, var_time_increment) < NOW()
                        THEN NOW()
                        ELSE end_time
                   END
                 , var_time_increment
                 ) 
      , price = price + var_price_increment
      , leader_id = var_leader_id
      , modified = NOW() 
    WHERE id = var_auction_id 
      AND closed = 0;

or:

    UPDATE auctions 
    SET end_time 
        = ADDTIME( CASE WHEN end_time < ADDTIME(NOW(), - var_time_increment)
                        THEN NOW()
                        ELSE end_time
                   END
                 , var_time_increment
                 ) 
      , price = price + var_price_increment
      , leader_id = var_leader_id
      , modified = NOW() 
    WHERE id = var_auction_id 
      AND closed = 0;

A compound index on (closed, id) should also help the UPDATE statement to avoid reading from the table when closed <> 0.

Upvotes: 1

Related Questions