Forum Moderators: open
arran.
I have a MySQL application online running at around 50 update operations per second on an InnoDB table of over 4 million rows, with slow queries and inserts going on around that all the time. Works great :)
50 update operations per second
Good job.
Other than row level locking, what steps have you taken to make the system scale up this well?
arran.
what steps have you taken to make the system scale up this well?
Embarssingly; almost nothing, other than moving the site onto its own dedicated server as it was bringing my other apps down - but that was a "quick fix", it wasn't until moving servers that I actually sat down and looked at what was going on, and realised that MyISAM's full table locking was the problem.
I will admit however that it is a blindingly simple app. The 50 updates per second is incrementing a counter of type INT(11) based on a single WHERE clause against an indexed VARCHAR(255), e.g.
UPDATE table SET count = count + 1 WHERE this='that';
With that happening 50 times a second load average is hanging around about the 0.2 mark on a 1GHz AMD processor with 512MB RAM.
Why do you need to update a counter field 50 times per second?
It's not one single counter that is being updated at that frequency. The application is recording events, and each event either applies to an existing item (row) - in which case the counter for that row is incremented - or it is a never seen before value in which case a new row is inserted (much less frequent).
