Check out this cool INSERT ON DUPLICATE KEY UPDATE feature in MySQL 4.1. This will make some logging code I have much easier and cleaner than it currently is.
Check out this cool INSERT ON DUPLICATE KEY UPDATE feature in MySQL 4.1. This will make some logging code I have much easier and cleaner than it currently is.
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.
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’
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.
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.
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
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