In the last episode (Jun 22), David Johnson said:
> Sasha Pachev <sasha@stripped> said:
> > I have noticed that unlike selects, updates and inserts are more
> > disk I/O than CPU bound. Setting key_buffer to 0 in this situation
> > actually improved performance. Regardless of the buffer settings,
> > disk I/O is very intensive and mysqld is using only a very small
> > fraction of CPU.
> > Does that mean that if someone is doing a lot of inserts and
> > updates they need to focus on a fast disk? Or maybe there is a way
> > to tweak the settings to avoid it (other than mounting ramdisk on
> > /usr/local/mysql/data/db_name) What do you guys think?
> We are quickly running into the IO boundary problem. We do allot of
> updates and have recently figured out that we will run into the IO
> wall long before we run into the CPU wall.
> We would be VERY interested in avoiding that problem.
You're pretty much stuck with it, I'm afraid. Inserts and updates have
to modify both the table and any affected indexes, and have to sync the
data to avoid corruption in case of a crash. You can make selects
faster by adding RAM (and caching more of your table/indexes), but more
RAM doesn't help updates much at all.
Bigger databases (like Oracle) overcome this problem by using redo logs
and background database writer processes to optimize writes, and by
using row or block-level locking so that multiple updates can happen
Mysql doesn't have either redo logs or fine-grained locking, so you'll
just have to buy the fastest disks you can find, and/or get a
nonvolatile disk cache like Prestoserve.