2

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.

Improve this question

1 Answer 1

Reset to default
0

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".

Improve this answer
8
  • 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, 2018 at 22:24
  • innodb_log_file_size may also be a factor.
    – Rick James
    Jul 24, 2018 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, 2018 at 23:36
  • @John - it would not stop the rollback, but changing the size might speed it up.
    – Rick James
    Jul 25, 2018 at 0:29
  • 1
    @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, 2018 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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