Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I determine maximum transaction size in MySQL?

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

like image 879
Brian Avatar asked Feb 19 '10 16:02

Brian


People also ask

How many transactions can MySQL handle?

The maximum theoretical throughput of MySQL is equivalent to the maximum number of fsync(2) per second. We know that fsync(2) takes 1 ms from earlier, which means we would naively expect that MySQL would be able to perform in the neighbourhood of: 1s / 1ms/fsync = 1000 fsyncs/s = 1000 transactions/s .

How big can a SQL transaction be?

About the size, there is no specific size limit in SQL Server, they can theoretically modify any amount of data without problems. The practical limit is really the size of the transaction log file of the target database.

Can MySQL handle large datasets?

MySQL was not designed for running complicated queries against massive data volumes which requires crunching through a lot of data on a huge scale. MySQL optimizer is quite limited, executing a single query at a time using a single thread.

What are the limits of MySQL?

The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.


1 Answers

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

like image 175
ggiroux Avatar answered Sep 27 '22 22:09

ggiroux