18

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

Community
  • 1
  • 1
Brian
  • 6,391
  • 3
  • 33
  • 49
  • 1
    Found an "Overview of Transaction Logging in MySQL" article at http://www.pythian.com/news/1337/ (doesn't answer my question but interesting background) – Brian Feb 19 '10 at 17:16

2 Answers2

22

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

ggiroux
  • 6,544
  • 1
  • 22
  • 23
-3

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.

Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • 1
    Hi, thanks for quick response. Are you sure? Reason I asked initially is that I've worked on "enterprisey" Sybase platforms before where large operations (hundreds of thousands of statements) would fill up the transaction log and prevent further DB writes until we got DBAs to rectify the situation! My instinct is that there's a MySQL transaction log file / memory setting somewhere which can indeed be set very large but which is nevertheless finite in size....? – Brian Feb 19 '10 at 16:55
  • 2
    I suppose this answer is wrong. At least for ORACLE, there are limitations in transaction size, and seems like InnoDB as well has limitations. – hgoebl Jul 24 '17 at 09:52
  • Hardware is not the limit. There are other database-specific considerations to keep in mind: https://dba.stackexchange.com/questions/19569/mysql-transaction-size-how-big-is-too-big – Scott McAllister Jan 06 '22 at 19:45