StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POScaling an incrementing counter in MySQL (for keeping track of pageviews)
text
Body
copied!<p>I have an integer MySQL column that is incremented each time a page is viewed. The SQL query looks something like this:</p> <p><code> UPDATE page SET views = views + 1 WHERE id = $id </code></p> <p>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:</p> <p>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:</p> <p>On page view:</p> <pre><code>$memcache->increment("page_view:$id"); $gearman->doBackground('page_view', json_encode(array('id' => $id))); </code></pre> <p>In the background worker:</p> <pre><code>$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}"); } </code></pre> <p>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. </p> <p>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"</p> <p>My question: What's should we do next to scale this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload