How do I queue inserts to create a bulk insert

I am going to change my application so that it does bulk inserts instead of individ开发者_开发技巧ual ones to ease the load on my server. I am not sure the best way to go about this. Thoughts so far are:

1. Use a text file and write all the insert / update statements to this file and process it every 5 mins - I am not sure of the best way to handle this one. Would reading from one process (to create the bulk insert) cause issues when the main process is still trying to add more statements to it? Would I need to create a new file every 5 mins and delete it when its processed.

2. Store the inserts in a session and then just process them. Would this cause any problems with memory ect?

I am using PHP and MySQL with MyISAM tables. I am open to all ideas on the best way to handle this, I just know I need to stop doing single inserts / updates.

Thanks.

The fastest way to get data into the database is to use load data infile on a text file.

See: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

You can also use bulk inserts of course, if you want them to queue behind selects, use a syntax like:

INSERT LOW PRIORITY INTO table1 (field1, field2) VALUES (1,1),(2,2),(3,3),...
or
INSERT DELAYED INTO .....

Note that delayed does not work with InnoDB. Also note that low priority is not recommended when using MyISAM.

See:
http://dev.mysql.com/doc/refman/5.5/en/insert.html
http://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html

1. I think you should create a new file for each 5 minutes fro insert and update separately and remove the ones after processing.

2. For bulk insert

   1. You can use LOAD DATA INFILE with disabled keys on table.
   2. If you use innodb you should run all inserts in transaction for preventing flushing indexes on each query and use form with multiple VALUES(),(),().
   3. If you use MyIsam you should insert with DELAYED option. Also if you don't remove rows from table it's possible to have concurrent read/write.

3. For bulk updates you should use transaction because you will get the same effect.