Dedicated table for counters

There are a few ways to implement counters. Even though it's not a complex feature, often I see people having problems around it. This post describes how bad implementation can impact both application and MySQL performance and how to improve it.

A customer asked me for help with performance problem they were facing. I logged into their database and found many client connections waiting for table locks. Almost all threads were stuck on one, small table called hits. What was the reason?

The problem was related to the way they developed a very simple system for counting page views they later used in some reporting. The table structure was:

mysql> SHOW CREATE TABLE hitsG
*************************** 1. row ***************************
Table: hits
Create Table: CREATE TABLE `hits` (
`cnt` int(11) NOT NULL
) ENGINE=MyISAM

mysql> SELECT * FROM hits; +---------+ | cnt | +---------+ | 3823273 | +---------+

The application was updating cnt column on every page load, or sometimes even multiple times per page load (although that was actually a bug), with such obvious query:
UPDATE hits SET cnt = cnt + 1;

It may not scale with traffic. MyISAM storage engine uses table level locking, which means that every UPDATE sets an exclusive lock on the table and forces any other threads accessing the same table concurrently to wait for the lock to be released. When traffic peaks beyond certain threshold, the cascade of locks may simply prevent the application from working at all. This is what happened to the customer during a traffic spike coming from some marketing campaign they launched.

Switching to InnoDB may seem like a natural choice, but does it really help here? Not so much. At the very least, it may not be enough.

InnoDB implements row-level locking mechanism which is better as locks are set on smaller chunks of data - individual records rather than entire tables. It means that concurrent queries updating different rows in the same table would not block each other, unlike in MyISAM. However, the table contains only one row, so that particular advantage does not really work here.

When row-level locking might become a bottleneck, why not use more rows that could be updated instead of just one?

CREATE TABLE `hits` ( `id` tinyint(4) NOT NULL, `cnt` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB

This enables multiple counters, each of which can be incremented independently of others, and what's more important also concurrently.

mysql> SELECT * FROM hits;
+------+------+
| id   | cnt  |
+------+------+
|    1 |   10 | 
|    2 |    8 | 
|    3 |    9 | 
|    4 |    8 | 
+------+------+

How to use the updated structure?

The technique is pretty much the same as used before, but with a small modification in the application code - it has to randomly choose a row that gets updated though the value of id column.
UPDATE hits SET cnt = cnt + 1 WHERE id = 3;

Of course, with such modification the way data is read has to change as well.

mysql> SELECT SUM(cnt) FROM hits;
+----------+
| SUM(cnt) |
+----------+
|       35 | 
+----------+

Even though MySQL has to perform more work, the solution can scale better than the original design.

Often using one table per "counter" will not be so good idea, but of course there's no problem extending this further. For example:

CREATE TABLE `hits` ( `slug` VARCHAR(32) NOT NULL, `slug_id` tinyint(4) NOT NULL, `cnt` int(11) NOT NULL, PRIMARY KEY (`slug`, `slug_id`) ) ENGINE=InnoDB

Some counters could have only one row, while those updated more frequently could have multiple.

Blog