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)
The biggest problem now is if the indexes are created when the server is
very busy. If there was a way of telling MySQL to delay creating the
indexes when it is busy, then a big obstacle would be out of the way.
Is this possible? We could not find anything in the MySQL documentation
Any suggestions would be greatly appreciated.