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

Question

The way I currently do it in mysql is is

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

This keeps live stats on the site, but as I understand, isnt the best way to go about doing this.

Edit:

let me clarify... this is for counting hits on specific item pages. I have a listing of movies, and I want to count how many views each movie page has gotten. After it +1s, it adds the movie ID to a session var, which stories the ids of all the pages the user viewed. If the ID of the page is in that array, it wont +1 it.

Answer 1

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

Answer 2

Not sure which web server you are using.

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

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

Answer 3

it depends really on if you want hits or views

1 view from 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 quite easily be flooded.

Answer 4

I'm not sure about what you're using, but you could set a cron job to automatically update the count every x minutes in Google App Engine. I think you'd use memcache to save the counts until your cron job is run. Although... GAE does have some stat reporting but you'd probably want to have your own data also. I think you can use memcache on other systems, and set cron jobs on them tool

Answer 5

Use logging software. Google Analytics is pretty and feature-filled (and generates zero load on your servers), but it'll miss non-JavaScript hits. If every single hit is important, use a server log analyzer like webalizer or awstats.

Answer 6

In general with MySQL:

• If you use MyISAM table: there is a lock on the table so you better have to do an INSERT in a separate table. Then with a cron job, you UPDATE the values in your movie table.
• If you use InnoDB table: there is a lock on the row so you can UPDATE the value directly.

That say, depending of the "maturity" and success of your project, you may need to implement a different solution, so:

1st advice: benchmark, benchmark, benchmark.

2nd advice: Using the data from the 1st advice, identify the bottleneck and select the solution for the issue you face but not a future issue you think you might have.

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

Hope this helps. :)

Answer 7

You could take an approach similar to Stack Overflow's view count. This basically increments the counter when the image is loaded. This has two useful aspects:

• Robots often don't download images, so these don't increment the view.
• Browsers cache images, so when you go back to a page, you're not causing work for the server.
• The potentially slower code is run async from the rest of the page. This doesn't slow down the page from being visible.

To optimize the updates: * Keep the counter in a single, narrow table, with a clustered index on the key. * Have the table served up by a different database server / host. * Use memcached and/or a queue to allow the write to either be delayed or run async.

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