Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is the counter table InnoDB ??? –  RolandoMySQLDBA Sep 6 '13 at 1:07
    
That's the plan. I've got a blank canvas, just being restricted by clients with shared hosting. –  QuaffAPint Sep 6 '13 at 2:45

1 Answer 1

up vote 1 down vote accepted

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

the Book

I have the book and refer to it well.

share|improve this answer
    
Seems like an interesting idea and work around for locking. My only concern is how to run that periodic compilation and how much overhead it would cause when it runs. I can't rely on people being able to setup cron jobs to do this. –  QuaffAPint Sep 6 '13 at 3:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.