List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:June 22 1999 1:55pm
Subject:Update,Insert and Select
View as plain text  
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 (home) (work)
Update,Insert and SelectSasha Pachev23 Jun
  • Re: Update,Insert and SelectDavid Johnson23 Jun
    • Re: Update,Insert and SelectDan Nelson23 Jun
  • Re: Update,Insert and SelectSasha Pachev23 Jun
  • Re: Update,Insert and SelectJim Faucette23 Jun
  • Update,Insert and SelectMichael Widenius29 Jun