I need to count impressions of a .php script that is accessed via web. Right now I do this in a 1 line MySQL table and just increment the value. This works fine usually but when the server has traffic bursts (hundreds of requests/second) everything gets delayed and the increment SQL statements start to pop up in MySQL's slow queries log.
I tried to count via APC but it "loses" values, that is, when I run a simple ab test command like
ab -n 100 -c 10 http://example.com/script.php
only ~50 new impressions are counted while on MySQL 100 are counted.
I also tried to count impressions via memcache, it works better (that is, its able to keep up with more simultaneous connections) but it also fails to count everything after 50 or 100 simultaneous connections.
I was wondering if there is anybody aware of a good solution to this problem?
In the end I chose to use a MEMORY table instead the usual INNODB one. While the solution isn't perfect it works much better than just using INNODB for this. I also applied this solution on a number of other tables where data integrity wasn't crucial and the results were positive.