List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:June 30 2005 7:25pm
Subject:Re: Possible to delay index writes until server is less busy?
View as plain text  
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.

http://dev.mysql.com/doc/mysql/en/create-table.html
http://dev.mysql.com/doc/mysql/en/myisam-start.html
http://dev.mysql.com/doc/mysql/en/flush.html

-- 
	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