List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 28 1999 11:22pm
Subject:Update,Insert and Select
View as plain text  
>>>>> "Sasha" == Sasha Pachev <sasha@stripped> writes:

Sasha> I have been messing around with the following problem ( for educational
Sasha> purposes, not a production application):

Sasha> We have a table ip (ip int unsigned not null primary key, n not null).
Sasha> We generate random ips from a given range - if the record is already in
Sasha> the table we increment the counter - otherwise we perform an insert. We
Sasha> are trying to simulate a web counter database, so we do not lock tables
Sasha> to improve insert speed. The goal is to maximize performance ( number of
Sasha> queries per second).

Sasha> I have noticed that unlike selects, updates and inserts are more disk
Sasha> I/O than CPU bound. Setting key_buffer to 0 in this situation actually
Sasha> improved performance. Regardless of the buffer settings, disk I/O is
Sasha> very intensive and mysqld is using only a very small fraction of CPU.

Sasha> Does that mean that if someone is doing a lot of inserts and updates
Sasha> they need to focus on a fast disk? Or maybe there is a way to tweak the
Sasha> settings to avoid it (other than mounting ramdisk on
Sasha> /usr/local/mysql/data/db_name) What do you guys think?

Hi!

The problem is that MySQL flushes the keys to disk between each query
to makes things more safe.

In many case this is however not strictly needed (as isamchk can
repair a table even if the keys are not up to date)

If anyone wants to experiment with this (and you are NOT running to
mysqld daemons on the same data), you can simple comment the following
rows out from isam/_locking.c

 
   if (info->lock_type == F_WRLCK && !share->w_locks &&
        flush_key_blocks(share->kfile,0))
 

Mail me if you get any better timings with this :)

Regards,
Monty

Thread
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