It is a common database pattern to increment an INT column when an event happens, such as a download. Early last year at GitHub we started encountering issues with slow UPDATES to these types of counters.
You can go far with this pattern until bursts of these types of events happen in parallel and you experience contention on a single row. When multiple transactions are trying to update the counter you are essentially forcing these transactions to run serially which is bad for concurrency.
You can see here a dramatic increase in query time when a burst like this occurred:
To avoid problems like this we had to do this kind of counting differently. We …[Read more...]