Speed up MySQL for inserts (for testing purposes)

Question

I have a bit of software that needs to do a lot of INSERTs. In production environment there'll be some serious tweaking and testing and stuff like that, but now when I need to test it I'd like to speed up inserts as much as possible. Hence my question - is there a way to tweak mysql such that it doesn't do much disk I/O but keeps everything in RAM and syncs with disk rarely(like once n-seconds say?)

Answer 1

Insert Delayed can help (MyISAM only), because it allows to insert in bulks (which is what MyISAM is good for).
Partitioning of your table will also help.

If your memory allows it (and that what you are asking for), then you can use a memory table. Just make sure that the settings max_heap_table_size and tmp_table_size are enough for the data.

A little more trickery, could be (on Linux) to use TMPFS and place your data file there (using Symlinks)

Answer 2

See the MySQL manual's answer here. To answer your specific question, check out INSERT DELAYED which does exactly what you want. You can tune its behavior with delayed_insert_limit and delayed_queue_size.

If you can batch the INSERTs, though, that can be even faster; either use the bulk insert tool, or supply multiple VALUES lists, eg: INSERT INTO table (column) VALUES (val1),(val2),(val3)

Answer 3

as daniel says in 2nd part of his answer - batch-inserts will give you a lot. check this presentation. your scale is probably much smaller but i think you'll find it interesting anyway.