How do I determine maximum transaction size in MySQL?

Question

Saw the same question posited for PostgreSQL here; wondering if anyone knows (a) the MySQL flavour of the response and (b) which MySQL options I would examine to determine/influence the answer.

I don't need an absolute answer btw, but if I were to propose inserting, say, 200,000 rows of ~2Kb each would you consider that very straightforward, or pushing the limit a bit?

Assume MySQL is running on a well specced Linux box with 4Gb of RAM, shedloads of disk space, and an instance tuned by someone who generally knows what they're doing!

Cheers

Brian

Answer 1

There are no transaction limits built inside SQL servers. The limit is the hardware running it, physical RAM, free space on the hard disk.

We run successfully imports of millions of data.

Answer 2

For Innodb the transaction size will be limited by the size of the redo log (ib_logfile*), so if you plan to commit very large transactions make sure you set innodb_log_file_size=256M or more. The drawback is that it will take longer to recover in case of crash.

But for the record Innobase employees recommend keeping you transactions short