Highload web projects using SQL databases

I want to ask a question a开发者_Go百科bout approaches in highload web applications development using SQL databases. Assume we have simple CMS system providing access to different articles. Also we want to store article visits quantity in database. This visits counter increases every time when user watches the article.

In terms of SQL database, we have "visits" integer field in the table "Article" that we need to increase every time when user visits the article. If article has a large number of concurrent visits, it is necessary to correctly modify "visits" field value of current database row.

I use pessimistic locking approach: "SELECT .. FOR UPDATE". Every time, when user visits some article, I make a lock on specific row on "Article" table and increase "visits" counter.

Is this approach correct?

I'm using MySQL database in my projects.

This approach will work for medium load sites, but eventually you will end up with locking problems.

On a very high load website, you'd want to implement a message queue, and send "visited" events to the queue.

A offline process would read the queue and update the columns appropriately. This would allow for just a single process to be accessing that particular column at any time.

I would definitely not recommend storing aggregate data (like "number of visits") in the article data, but rather use a separate table where you log one new record for each visit. Store the timestamp, article_id, IP address and other data there. The rationale behind this is that you will not have to lock the database record for each article for each visit, which would likely result in locking issues / contention.

Now, when you are interested in retrieving the number of views, do a simple select on the log table. For medium-sized sites that should be "good enough"; as the load increases, you will have to calculate the number of views per article on a regular basis and cache the view counters to speed up the access to this data.