Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?)

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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)

share|improve this answer

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.

share|improve this answer

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)

share|improve this answer
    
As that link mentions if you are using tables that support transactions then use them. Transactions will also make a significant difference. –  Zoredache Jun 8 '10 at 6:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.