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
