mySQL for analytics and updating same row

Question

Looking for the best way to handle using mySQL/PHP for analytics and thus having multiple connections trying to update the same counters in the same row at the same time.

The problem being if I have a row like 'year-month-day-hour','hits' and I want to increase hits for every pageview, that's going to be a lot of connections trying to update that same field in the same row. I'm worried about contention and/or writing the wrong values.

Also this will be packaged for many clients on different kinds of hosting. Thanks for any suggestions.

Answer 1

You may need to create the counter table with 250 slots to spread the row locks.

First, here is the table:

CREATE TABLE HourCounter
(
    hr INT,
    slot TINYINT,
    cnt INT UNSIGNED,
    PRIMARY KEY (hr,slot)
) ENGINE=InnoDB;

All you need to do is insert into the random slots like this:

SET @NumberOfSlots = 250;
SET @X = UNIX_TIMESTAMP();
SET @uthr = @X - MOD(@X,3600);
INSERT INTO HourCounter (hr,slot,cnt)
    VALUES (@uthr,RAND() * @NumberOfSlots + 1,1)
    ON DUPLICATE KEY UPDATE cnt = cnt + 1
;

If you want to collect the counts, run this:

SELECT hr,SUM(cnt) HourCount
FROM HourCounter GROUP BY hr;

If you want to collapse the slots into a single number, do this once a week:

UPDATE HourCounter c
    INNER JOIN
    (
        SELECT hr,SUM(cnt) cnt,MIN(slot) mslot
        FROM HourCounter GROUP BY hr
    ) x USING (hr)
SET c.cnt  = IF(c.slot = x.mslot, x.cnt, 0),
    c.slot = IF(c.slot = x.mslot, 0, c.slot);
DELETE FROM HourCounter WHERE slot <> 0 AND cnt = 0;

To reclaim all the disk space for this table, do this once a month:

ALTER TABLE HourCounter ENGINE=InnoDB;
ANALYZE TABLE HourCounter;

Give it a Try !!!

To give credit where is credit is due, I got the answer from page 140 of the Book

I have the book and refer to it well.