2

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?

clops
  • 213
  • 1
  • 9

1 Answers1

2

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: 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

geofflee
  • 196
  • 1
  • 1
  • 10
  • 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. http://www.mysqlperformanceblog.com/2009/01/13/some-little-known-facts-about-innodb-insert-buffer/ – 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