MySQL Counters

6 Responses

• 

  Jeff | May 29th, 2006 @ 10:47 pm | Reply

  You can also use it for making RRD like tables, eg, say you have a table with 24 columns for an hourly hit counter ( hour_now, hour-1, hour-2, hour-3, etc) and a single index for, say, the site you’re tracking.

  INSERT INTO hits (`site`, `hour_now`) VALUES ('photomatt.net', 12341123) /* cause you're sooooo popular! */
ON DUPLICATE KEY UPDATE
`hour-24` = `hour-23`,
`hour-23` = `hour-22`,
`hour-22` = `hour-21`
/* etc etc */
`hour_now` = 12341123

  This would keep the most recent hour in the first column, and always move the following columns to the next value every update. I just discovered this neat way of doing things a day or two ago.
• 

  Ozh | May 30th, 2006 @ 10:47 am | Reply

  This is a nice MySQL syntax for sure. What I still want is to UPDATE and get a result at the same time, for instance get current value of ‘hits’ after mysql have updated it to ‘hits + 1′
• 

  MikeT | May 30th, 2006 @ 10:53 am | Reply

  Wouldn’t that kill your MySQL server if you had a heavy load? I could easily see a site like Instapundit ripping its host’s MySQL server a new one doing that.
• 

  Matt | May 30th, 2006 @ 10:59 am | Reply

  It wouldn’t kill MySQL any more than any other query. If your traffic is too high, then just do +5 every randomly every fifth load instead of a +1 every load.

  (I recently did something like this for Akismet.)

  Using that technique you get a reasonable approximation while still controlling how many queries you run each day.
• 

  Jeff | May 30th, 2006 @ 9:36 pm | Reply

  Or, if you’re really worried about the load, put the stat table into a MEMORY table — super fast, and for something like a hit counter they are perfect — transient, fast, and small — then every hour run a cron to pull the memory stats into an hourly format and stuff them into a more permanent myisam/inno table
• 

  Kevin Burton | June 15th, 2006 @ 12:04 am | Reply

  Here’s the best way to handle this case.

  1. Don’t write on every load. Write once every 5 minutes. If you need to keep variables around for that interval use memcached and make sure it won’t delete the key. (I wish there was an easier way to do this than juts ensuring you have enough memory).

  2. RRD files use one value like a circle. Jeff, your above code would cause a lot of extra IO by copying row values. just log the time interval value and keep the other values stale until next time around. Think of a analog clock with the second hand sweeping around the dial. Only update values as you pass thhem.

  Kevin