I have been messing around with the following problem ( for educational
purposes, not a production application):
We have a table ip (ip int unsigned not null primary key, n not null).
We generate random ips from a given range - if the record is already in
the table we increment the counter - otherwise we perform an insert. We
are trying to simulate a web counter database, so we do not lock tables
to improve insert speed. The goal is to maximize performance ( number of
queries per second).
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?
--
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
