Issuing low priority updates

Is there a way to execute low priority updates in MySQL, when using InnoDB?

I am running a very high load applicat开发者_运维问答ion where there may easily be literally thousands of users trying to concurrently update the same data records. This is mostly session-based statistical information, much of which could be ignored in case there is a wait time associated with the request. I'd like to be able to check whether some table/row is locked, and if so just not pass an update query to the server. Is that possible?

did you try setting low_priority_updates=1 in your my.cnf file? This should give select queries priority when an update or insert would otherwise lock the table.

If you say so that there is a time limit with the request you could use a Stored Procedure to skip certain updates

Something like this:

DELIMITER $$

DROP PROCEDURE IF EXISTS `updateStats`  $$

CREATE PROCEDURE updateStats ()
BEGIN

DECLARE _B smallint(1) DEFAULT 0;
DECLARE _SECONDS INT DEFAULT 1;

-- http://dev.mysql.com/doc/refman/5.0/en/lock-tables-and-transactions.html
SELECT GET_LOCK('myLabel1',_SECONDS) INTO _B;
IF _B = 1 THEN
    UPDATE table SET ........;
    SLEEP(_SECONDS);
    SELECT RELEASE_LOCK('myLabel1') INTO _B;
END IF;
END

This will make sure that if you got the Lock, that lasts for _SECONDS you make sure no other procedure runs the same code in that time frame. The sleep is needed to keep the lock for 1 second (as if the SP terminates sooner, the lock is released)

You can also add an else node to the if, so it the stored procedure cannot update, to do custom code, like add to queue.

Suppose you want to write into the live table only in interval of 1 second to not load it too much, probably you are having a lot of indexes on it. On the else node you could update a second table that acts as a queue, and the queue is emptied in the IF true node, when you also make the update.

So your user application doesn't wait for the update to complete, and doesn't care if it doesn't complete, might this be a suitable context for using a background processing manager such as Gearman?