Incrementing values, alternate way of doing this that won't use MySQL

Question

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?

@n8whnp: well, I need the data in real time and also I don't have the logs activated. — Alex Flo, Jan 17 '13 at 09:31
@Alex - you must have an SQL statement that increments the value in the table. What does that look like? — Eric Petroelje, Jan 17 '13 at 13:45
@EricPetroelje it looks like this: UPDATE DailyStatsInMemory SET pdfs=pdfs+1, pdfs_total=pdfs_total+1 — Alex Flo, Jan 17 '13 at 13:55

score 0 · Accepted Answer · answered Jan 21 '13 at 14:32

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.

Incrementing values, alternate way of doing this that won't use MySQL

1 Answers1