Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to minimize the number of queries that are executed just for updating a counter field.

One of my example counter fields is raised by 1 everytime a page is visited, I was wondering what could be the best method to update it with just one occasionally query, maybe a can raise it by 100 with 1 query instead of raising it 100 times by 1.

How could I do that?

I think I need a place where to store by how much I need to raise that specific counter field, and this place should be the fastest possible, I thought about memcached but I think there will be problems with concurrent accesses to the same resource.

share|improve this question
    
Which database/programming stack are you working with? –  jynus Jul 30 at 11:03
    
I'm working with a MySql database, the counter field I'm talking about is stored in a MyIsam table with around 400k rows. –  Fabio Spampinato Jul 30 at 11:05

1 Answer 1

MyISAM is the worst offender for updating individual rows on concurrency. First thing you want to do is use InnoDB to be able to execute UPDATEs in parallel. InnoDB may have lower insertion rate, but that can be solved by reducing its durability setting (innodb_flush_log_at_trx_commit = 2). That, combined with a large enough buffer pool, will make most of your updates on-memory only.

If that is still too slow for a single row, memcached is certainly one solution to buffer queries, and it is even integrated into MySQL 5.6. Operations in memcached are guaranteed to be done atomically, so on that side that wouldn't be a concurrency problem. However, reading, adding plus 1 on client code, and updating the result is not guaranteed to be atomic, so you should control concurrency on application side (syncronization/locking)- Java has very good support for that, PHP doesn't, although it can be done. That can be too much overhead if the cache server is over the network.

One trick to avoid concurrency problems (works for DB tables and any other system) is to do partitioning: instead of having one counter, having several per value to prevent blocking problems on write. When calculating the final number, just add up the different rows into one.

share|improve this answer

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.