Scaling an incrementing counter in MySQL (for keeping track of pageviews)

Question

I have an integer MySQL column that is incremented each time a page is viewed. The SQL query looks something like this:

UPDATE page SET views = views + 1 WHERE id = $id

We began to run into scaling problems when the same page (same id) was viewed many times per second (record would lock in MySQL) and the query would grind MySQL to a halt. To combat this we've been using the following strategy:

Each time the page loads we increment a counter in Memcache and put a job in a queue (Gearman) that would update the counter in MySQL in the background (amongst 3 worker machines). The simplified code looks like this:

On page view:

$memcache->increment("page_view:$id");
$gearman->doBackground('page_view', json_encode(array('id' => $id)));

In the background worker:

$payload = json_decode($payload);
$views = $memcache->get("page_view:{$payload->id}");
if (!empty($views)) {
    $mysql->query("UPDATE page SET views = views + $views WHERE id = {$payload->id}");
    $memcache->delete("page_view:{$payload->id}");
}

This has worked well. It allows us to cut down on the queries to the DB (as we aggregate the views in memcache before writing to the DB) and the DB write occurs in the background, not holding up the page load.

Unfortunately, we are starting to see MySQL locks again. It seems that very active pages are still getting ran at nearly the same time, causing MySQL to lock again. The locks are slowing down the writes and often kill our workers. This is causing the queue to grow very large, often having 70k+ jobs that are "behind"

My question: What's should we do next to scale this?

Answer 1

I don't know much about Gearman, so I may be wrong.

You're enqueueing a gearman task each time you increment the counter. I guess that it would be better to enqueue a task only if the result of $memcache->increment is 1. My rationale is that when the next update will arrive after the gearman task clears page_view:$i, you will not have a long queue of gearman tasks eager to update this new value in the DB. This should make your code independent of your update rate, and capped at how fast gearman picks new tasks (which will be, hopefully, slow enough). In a perfect world you could just ask gearman to delay this task ~1s. This will ensure that you only update this counter at a rate of 1 qps.

Independently of gearman, if you can accept slower READs and assuming you're using InnoDB, you can shard this counter.

To do that just add a shard column and make it part of the primary key, like

CREATE TABLE page (
     id INTEGER,
     shard INTEGER,
     views INTEGER,
     PRIMARY KEY (id, shard)
)

When you update this counter, choose randomly a shard between 1 - 10. When you read it, SUM over all shards of the id that you want to read. This will make reads 10x slower, but it will allow you to scale 10x on writes. (Of course it doesn't need to be 10, you can pick any number that you want.)

Answer 2

Not sure about what you are using page counts for and how essential it is that all of them get recorded. Perhaps you could cache the counts in memory on each server then only persist them on some fixed schedule. That way you would control the number of accesses you have to the database.

Granted this obviously won't guarantee that the counts get persisted in the event the server goes down for any reason. So, if it's for any important audit logging or anything where losing some of the page views would be a problem this won't work.