How to write an efficient hit counter for websites

Question

I want to write a hit counter script to keep track of hits on images on a website and the originating IPs. Impressions are upwards of hundreds of thousands per day, so the counters will be incremented many times a second.

I'm looking for a simple, self-hosted method (php, python scripts, etc.). I was thinking of using MySQL to keep track of this, but I'm guessing there's a more efficient way. What are good methods of keeping counters?

Answer 1

A fascinating subject. Incrementing a counter, simple as it may be, just has to be a transaction... meaning, it can lock out the whole DB for longer than makes sense!-) It can easily be the bottleneck for the whole system.

If you need rigorously exact counts but don't need them to be instantly up-to-date, my favorite approach is to append the countable information to a log (switching logs as often as needed for data freshness purposes). Once a log is closed (with thousands of countable events in it), a script can read it and update all that's needed in a single transaction -- maybe not intuitive, but much faster than thousands of single locks.

Then there's extremely-fast counters that are only statistically accurate -- but since you don't say that such imprecision is acceptable, I'm not going to explain them in more depth.

Answer 2

You could take your webserver's Access log (Apache: access.log) and evaluate it time and again (cronjob) in case you do not need to have the data at hand at the exact moment in time when someone visits your site.

Usually, the access.log is generated anyway and contains the requested resource as well as time, date and the user's IP. This way you do not have to route all trafic through a php-script. Lean, mean counting machine.

Answer 3

Without a doubt, Redis is perfect for this problem. It requires about a minute to setup and install, supports atomic increments, is incredibly fast, has client libs for python and php (and many other languages), is durable (snapshots, journal, replication).

Store each counter to its own key. Then simply

INCR key

Answer 4

There are two really easy ways:

1. Parse it out of your web logs in batch.
2. Run the hits through beanstalkd or gearmand and have a worker do the hard stuff in a controlled way.

Option 1 works with off-the-shelf tools. Option 2 requires just a bit of programming, but gives you something closer to realtime updates without causing you to fall over when the traffic spikes (such as you'll find in your direct mysql case).