Entry tags:

mysql

Big transactions suck. Small transactions suck. What to do?
One of my favourite topics in MySQL performance talks is the ambiguous description of what size of what your transactions should be. The basic advice is:

Running InnoDB in autocommit, or with short transactions will cause many more fsync()'s which will reduce your write performance.

It seems that if I run entirely transaction-less the import speed of a test I wrote is:

real    0m31.222s
user    0m2.111s
sys     0m1.070s

real    0m30.318s
user    0m2.111s
sys     0m1.070s

real    0m31.744s
user    0m2.108s
sys     0m1.078s

If I run in transactional, committing after approx 10 queries, the time is awesomely better:

real    0m12.154s
user    0m1.771s
sys     0m0.869s

real    0m11.976s
user    0m1.773s
sys     0m0.874s

real    0m12.827s
user    0m1.768s
sys     0m0.872s

I tried hacking my code to commit even less frequently, and I can get the time down to just under 10 seconds. I'm not sure if this is entirely wise though, since:

* In the event of failure I'll loose more information
* The data wasn't necessarily related. If I have to rollback, I'll most likely end up rolling back more data (this is expensive in InnoDB!).

I think the biggest gotcha is that if one transaction stays open long enough that the purge thread falls well behind, the database starts running like mollases; since the size of your table space can really bloat. Unless you are running in innodb_file_per_table, it's also not possible to compact the innodb table space files, and a larger table space file can mean more seeking, less performance.

So where is the tradeoff point? It's probably one of those application dependent things. One of my favourite annoying bugs in MySQL 5.0 was the "optimization" that load data infile committed automatically every 10,000 rows (Bug #11151). I'm glad that behaviour was changed in 5.0.26.