Clever Geek Handbook

What is the best way to count views on a high traffic site?

The way I do it in mysql is

UPDATE table SET hits=hits+1 WHERE id = 1;

This saves statistics on the site, but as I understand it is not the best way to do it.

Edit:

Let me clarify ... this is for counting hits on certain item pages. I have a list of movies and I want to calculate the number of views of each page of a movie. After + 1, it adds the movie ID to the session var, which displays the IDs of all the pages the user has viewed. If the page id is in this array, it won't +1.

+2


source to share


7 replies


You can do an approach similar to Stack Stack Views . This basically increments the counter when the image is loaded. This has two useful aspects:

  • Robots often don't download images, so they don't magnify the view.
  • Browsers cache images, so when you return to the page, you are not calling work for the server.
  • Potentially slower code runs async from the rest of the page. It doesn't slow down the page's visibility.


To optimize updates: * Keep the counter in one narrow table with a clustered index on the key. * Ask the table to serve another database server / host. * Use memcached and / or a queue to allow writes to either delay or trigger async.

If you don't need to display the number of views in real time, then your best bet is to include the movie id in your url somewhere and use the log of the log to populate the database at the end of the day.

+3


source


If your traffic is high enough, you shouldn't hit the database for every request. Try to keep the count in memory and sync the database on a schedule (for example, refresh the database every 1000 requests or every minute).



+2


source


Not sure which webserver you are using.

If your web server logs requests to the site, say one line for the request in a text file. Then you can just count the lines in the log files.

Your solution has a major problem in that it will block a row in the database, so your site can only serve one request at a time.

+2


source


it really depends if you want to see hits or views

1 view of 1 ip = 1 person looking at a page 1 person refreshing the same page = multiple hits but only one view

I always prefer google analytics etc. for something like this, you need to make sure that this db update is only done once, or you could easily get flooded.

+1


source


I'm not sure what you are using, but you can set up a cron job to automatically update the score every x minutes on Google App Engine. I think you used memcache to keep the counters until the cron job is started. Although ... GAE has some statistical reports, but you probably want to have your own data as well. I think you can use memcache on other systems and set cron jobs on them.

0


source


Use logging software. Google Analytics is beautiful and functional (and generates zero load on your servers), but it will skip non-JavaScript hits. If every single is critical, use a server log analyzer like webalizer or awstats.

0


source


In general, with MySQL:

  • If you are using MyISAM table: there is a lock on the table, so you are better off doing INSERT on a separate table. Then with a cron job, you UPDATE the values ​​in your movies table.
  • If you are using InnoDB table: there is a lock on the row, so you can UPDATE the value directly.

Let's say, depending on the "maturity" and success of your project, you might need to implement a different solution, so:

1st tip: benchmark, benchmark, benchmark.

2nd tip. Using the data from tip 1, identify the bottleneck and choose a solution to the problem you are facing, but not the future issue you think may have.

Here's a great video on this: http://www.youtube.com/watch?v=ZW5_eEKEC28

Hope this helps. :)

0


source






More articles:


All Articles