Incremental update in column value in mysql. Concurrency Issues?

Question

I have a query like

update pageviews set count = count + 1 where pageid = $pageid

This statement is executed each time a webpage is viewed. Therefore, the column count is incremented each time the page is viewed.

My table is of InnoDB type.

Assuming thousands of pageviews can happen per second, is this not scalable? Can i run into concurrency issues? Like query locking etc?

Someone told me that i should be using queues for purposes like this. Why do i need to use a queue? In what sort of condition could my data be corrupted or scalability could become a concern?

Answer 1

Out of the box, on commodity hardware, InnoDB handles about 100 such statements per second. There are no concurrency or locking problems, just performance problems.

• innodb_flush_log_at_trx_commit defaults to 1 for security. But that requires a disk I/O to a log for every transaction. 2 is much faster, and a reasonable compromise. (A crash may lose one second's worth of transactions.)

• Your UPDATE is probably autocommited? Or in a transaction by itself? Can it be batched in any way? If so, that would cut back on the overhead.

• A pageview is handled by a web server, correct? Can it collect some pageids before actually writing to the table? Even if it collected for one second, that could be a significant speedup. If you do this, be sure to sort the IN list in update pageviews set count = count + 1 where pageid IN (...). That will cut back on the likelihood of deadlocks.

• Handle errors from your UPDATE. (Otherwise, your data will be 'wrong' or 'corrupted'.)

Answer 2

In addition to @Rick's answer: I don't know how queue could help you, the only idea that I have is to send all page events to queue where you have a multiple consumers, each of them updates it's own database and results are aggregated later. Also queue allows you to handle some peak loads when there are a lot of loads in a short period of time - in this case instead of blocking on database updates you will put events into memory and they will be processed later, but it will not help if you can't handle events stream at regular load.

And one more suggestion - if you have thousands of pageviews per second, probably you don't need to track them preciesly (if it is not a billing), so you can have a code like this if you have a stateful server:

onPageView() {
    cnt += 1
    if (cnt == 1000) {update table set views = views + 1000; counter = 0;}
}

(don't forget about thread-safety)

or even

onPageView() {
   value = random(0, 1000);
   if (value == 0) {
       update table set views = views + 1000
   }    
}

and you don't need to care about state and you decrease number of writes in 1000 times