List:General Discussion« Previous MessageNext Message »
From:Mathias Date:July 1 2005 8:04am
Subject:Re: Possible to delay index writes until server is less busy?
View as plain text  
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.
>
>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
>  
>

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

  - Mathias

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