When doing a mass insert like INSERT INTO SELECT FROM the speed benefit of doing that in chunks of 10k records can be enormous, in my tests a query that was running for 5 days took only 2 hours using the chunk method.

To make things worse: if you let such an operation run for a few hours and KILL the job (or crash) then it can take days or weeks until ROLLBACK is finished.
Rollback is significantly slower than insert, no optimizations here.

The same counts for doing mass UPDATEs.

My question: How can I completely disable transaction log for such a transactions ?

I tested this: SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; But it showed no difference, when killing the job transactions start rolling back.

I really did my homework and tried to find anything about it, no luck.

P.S.
Of course I am aware that importing without transactions does not give a unique snapshot to rely on if anything is changed in meantime.

You cannot turn off transactions.

It is assumed that ROLLBACK is the exception, not the rule. Are you trying to make it the "normal" path?

5 days vs 2 hours -- this is far more extreme than I would expect. Perhaps you are running out of RAM? Or swapping?

Please provide more details on the task; we may be able to help you 'solve' the problem in a way other than "turning off transactions".

  • Well it's half a billion records I was testing this with, and I have larger tables than that. 64GB of ram was used when I had that tested, no swapping but that table was half a terrabyte, I don't have that RAM. The problem is really the transaction. I wrote a stored procedure that does it in 10k rows "parts" and it was finished after 1.5 hours. The full dataset would take a week without separating it into parts. It's the transaction log causing this. – John Jul 24 at 22:24
  • innodb_log_file_size may also be a factor. – Rick James Jul 24 at 23:13
  • Hmm, a small innodb_log_file_size would stop the rollback problem right ? But the performance problem probably not, correct ? I don't know what mysql is doing when it creates a transaction of 500 million rows. But whatever it is, it's roughly 100 times slower in comparison to forcing small transactions. – John Jul 24 at 23:36
  • @John - it would not stop the rollback, but changing the size might speed it up. – Rick James Jul 25 at 0:29
  • @John - It starts by putting rollback stuff in the log file. But you have too much for that, so it overflows and starts putting it in with the rows. This backup strategy is more costly, hence slower (than if you chunked, COMMITting after each chunk). But 60x diff in speed is far more than I would expect. – Rick James Jul 25 at 0:31

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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