Hi all,

I wonder if any of you did bulk inserts of 50 - 100k records on mysql. I really would like to know some tips on the best way to do this (for performance) , i.e. if you used any etl tools or just sp.

Thanks in advance!

No tools will give you more performance, as your bottleneck is your server...
what you can do to lower the server load is either to use transactions, or use "insert delayed".

Transaction: [ http://dev.mysql.com/doc/refman/4.1/...-commands.html ]
If your db support it (mysql may need a specific backend to support it, depending of mysql version), you start a transaction by sending "start tansaction", then send all your insert one after the other, and finish the transaction with a "commit" or a "rollback".
This method have the great advantage that the db knows that it don't have to re-compute the indexes before you do the commit.

insert delayed: [ http://dev.mysql.com/doc/refman/4.1/...t-delayed.html ]
This is a special mysql command, that let you give a hint on the db about the importance of the insert.
An insert delayed will always be processed when the db will not be occupied doing something else, it have a low priority comparing to standard insert/update.

Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.

Originally Posted by tripy

No tools will give you more performance, as your bottleneck is your server...
what you can do to lower the server load is either to use transactions, or use "insert delayed".

Transaction: [ http://dev.mysql.com/doc/refman/4.1/...-commands.html ]
If your db support it (mysql may need a specific backend to support it, depending of mysql version), you start a transaction by sending "start tansaction", then send all your insert one after the other, and finish the transaction with a "commit" or a "rollback".
This method have the great advantage that the db knows that it don't have to re-compute the indexes before you do the commit.

insert delayed: [ http://dev.mysql.com/doc/refman/4.1/...t-delayed.html ]
This is a special mysql command, that let you give a hint on the db about the importance of the insert.
An insert delayed will always be processed when the db will not be occupied doing something else, it have a low priority comparing to standard insert/update.

Thanks very much tripy for your reply.
I will try with this technique and see the performance. It sounds like it should do much better with it.

Thanks!