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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]