Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is there a way to execute low priority updates in mysql?

I am running a very high load application 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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

What database engine are you using? MyISAM or InnoDB? If it's MyISAM, you can use the following command to view locks:

SHOW FULL PROCESSLIST;

Source: http://serverfault.com/questions/36260/how-can-i-show-mysql-locks

However, I would recommend that you take a different approach. Instead of having a single point of contention, simply insert a new row for every data-point with INSERT DELAYED. Then at the end of the day, or whenever you need the data, you can batch-process everything. Note that this only works for the MyISAM, MEMORY, and ARCHIVE engine types.

Source: dev.mysql.com/doc/refman/5.1/en/insert-delayed.html

I think this question belongs over at Stack Overflow. You will probably get a better response there.

-Geoffrey Lee

share|improve this answer
    
Thanks for a quick reply. Forgot to mention, that I am using InnoDB, and unfortunately it does not support Insert Delayed :( –  clops Jan 20 '10 at 12:48
    
Well, InnoDB has an "insert buffer" that performs the same function. mysqlperformanceblog.com/2009/01/13/… –  geofflee Jan 20 '10 at 14:31
    
And how do I talk to the insert buffer to see if some table/row is locked? –  clops Jan 21 '10 at 13:56
    
You don't need to check if something is locked. Every time a user views a page, you record the statistics in a new row. At the end of the day, or whenever you need the data, you can run a script to aggregate that data. The InnoDB Insert Buffer exists to batch multiple, independent INSERT requests together so that you avoid contention from multiple INSERT queries. You do not interact directly with the Insert Buffer – it simply works by itself. –  geofflee Jan 21 '10 at 14:16
    
I want to stress that there is no locking involved with my scheme. –  geofflee Jan 21 '10 at 14:18

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.