mysql batch insert

Question

I am doing batch insert for inserting 80k records and 1200 records per batch. The query is executing fine. The problem is that I am getting data from mssql server and creating a batch array out of it. So there are changes are due to some reason one of the batch insert fails.

But when a batch insert fails, it does for all 1200 records of that particular batch.

So, is there any way to find out how to get the exact records from the batch of 1200 records that failed the batch insertion.

And one more question.

While doing these insertion, the web site goes down for the time of updation.

Is there any way that during this time as well the site is not down.

Any help will be appreciated.

Thanks

Thanks for the reply but logs files will show the batch query means insert into table(columns) values (),(),().... . Not which particular insert caused the batch to fail. — Md Jawed Shamshedi
– Md Jawed Shamshedi, Commented Dec 8, 2011 at 7:42
there must be an error line somewhere in the log files, based on that you can hunt down the batch and find the issue. — user497849
– user497849, Commented Dec 8, 2011 at 7:45

user319198 · Accepted Answer · 2011-12-08 09:18:09Z

2

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.

answered Dec 8, 2011 at 9:18

user319198

Sign up to request clarification or add additional context in comments.

4 Comments

Md Jawed Shamshedi Over a year ago

Thanks Wasim for your reply. There is a problem with transaction, I can't used it. And for insert delayed, my site is a product selling and auction site. And insert delay does not guarantee you the time when actual insert will be done, So the changes to product cost and auction will not be reflected instantly. So I didn't go for it.

Md Jawed Shamshedi Over a year ago

Is there any tool, of course free one that show the mysql server load and time etc

user319198 Over a year ago

So do it by script in programming language you are using. Apply appropriated checks and handle errors.

Md Jawed Shamshedi Over a year ago

I am doing scripting and doing batch insert of 2k rows in single query. but how do I come to know that which single records from 2k batch triggered the batch insert.

Collectives™ on Stack Overflow

mysql batch insert

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related