Hmm.
Taking a stab in the dark here, but..
If it's purely a 'hit counter' type affair, and you're updating a single row, perhaps performance could be gained by instead inserting into a table (presumably this can be done with single-row level locking - at least in InnoDB?), and running a regular job that aggregates the contents of this table into the counter row?
Data won't be up-to-date instantly, but it might reduce contention. Do you experience contention when the counter is being read from? If so, this could possibly be reduced using a 'dirty read' (no locking on select), so that although slightly outdated information may be read, no updates would be delayed?
Depends on what you're doing, and what you're trying to achieve, really!
If I understand you correctly, you are suggesting to create a temp
table, and update that all the time, then add that sum to the perm table
every once in a while?
In this case, the counter is only checked (for stats purposes) once an
hour, so updating it at 30min increments would be fine (at our hit rate,
that's about 180,000 hits) which would save a fair amount of contention
time on the main table (If I understand it correctly).
I'll have to look into this.
-- Jim Richardson http://www.eskimo.com/~warlock Madness takes its toll. Please have exact change ready.
signature.asc
Description: Digital signature
