List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 1 2005 2:24pm
Subject:Re: Possible to delay index writes until server is less busy?
View as plain text  
In the last episode (Jul 01), Mathias said:
> Dan Nelson wrote:
> >In the last episode (Jun 30), Mathias said:
> >>We've been benchmarking a database that in real-life will have a
> >>huge write load (max peak load 10000 inserts/second) to the same
> >>table (MyISAM).
> >>
> >>We will need about 4 indexes for that table. However, from our
> >>benchmark tests, it is clear that writing indexes takes too many
> >>resources and impedes the speed of inserting new records.
> >>
> >>To overcome this, we are thinking of:
> >>1 -  using several smaller tables (instead of one big one) by creating 
> >>and writing to a new table every x hours,
> >>2 -  wait with writing the indexes until a new table has been created 
> >>where the next inserts will be (i.e, not write indexes until the table 
> >>has been closed)
> >
> > You want the delay_key_write flag.  You can set it per-table, or
> > globally.  You can use the "FLUSH TABLE mytable" command to force
> > mysql to update the on-disk copy of the indexes.
> 
> Yes, that is something we are considering doing. Any suggestions
> though how to best decide when to do this? As far as we know, there
> is no way of determinining from within MySQL whether it is very busy
> or not. I guess we need to determine that externaly before running
> the queries

If you don't mind the extra time required to check/repair damaged
indexes after a system crash, you don't really need to flush at all.

Or, if you aren't using hardware raid, consider getting one with
battery-backed RAM (which will let it cache writes).  That way your
index writes will return immediately even with delay_key_write unset,
and the raid card will flush to disk at its leisure.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Possible to delay index writes until server is less busy?Mathias30 Jun
  • Re: Possible to delay index writes until server is less busy?gunmuse30 Jun
    • Re: Possible to delay index writes until server is less busy?Mathias1 Jul
    • Re: Possible to delay index writes until server is less busy?Mathias1 Jul
  • Re: Possible to delay index writes until server is less busy?Dan Nelson30 Jun
    • Re: Possible to delay index writes until server is less busy?Mathias1 Jul
      • Re: Possible to delay index writes until server is less busy?Dan Nelson1 Jul