How does transactional isolation affect autocommit performance in MySQL?
I have a VBulletin 4.x forum running on my server. Some forum tables have been converted to InnoDB for performance reasons as per this guide . The Forum itself does not use transactions at all (no START TRANSACTION or BEGIN WORK in the source code) and InnoDB tables are only used to prevent table locks in UPDATE queries. The forum, of course, operates in auto-commutation mode.
Am I correct in understanding that in this case I can change the default transaction isolation level to READ UNCOMMITED and get such a performance gain that way?
a source to share
TL; DR: If your forum is slow, the OPERATION ISOLATION LEVEL is most likely not the cause, and setting it to anything other than the default is unlikely to help. Setting innodb_flush_log_on_trx_commit = 2 will help, but has consequences for crashes.
Long version:
What is the PICTURE LEVEL OPERATION I wrote at http://mysqldump.azundris.com/archives/77-Transactions-An-InnoDB-Tutorial.html . Check out all 3 InnoDB overview articles at http://mysqldump.azundris.com/categories/32-InnoDB .
As a result, the system should be able to ROLLBACK anyway, so even READ UNCOMMITTED doesn't change anything that needs to be done when writing.
For transaction reads, reads are slower when the chain of undo logs leading to the view for the read transaction is longer, so READ UNCOMMITTED or READ COMMITTED may be very slightly faster than the default REPEATABLE READ. But you have to keep in mind that we are talking about memory access here and it is disk access that slows you down.
On the AUTOCOMMIT question: this syncs every write to disk write. If you've used MyISAM before and that was good enough, you can tweak
[mysqld]
innodb_flush_log_on_trx_commit = 2
in my.cnf file and restart the server.
This will write the commit from mysqld to the filesystem cache buffer, but delay flushing the filesystem cache buffer to disk so that it only happens once a second. You will not lose any mysqld crash data, but you can lose up to 1 second of writing on hardware crash. However, InnoDB will automatically recover, even after a hardware failure, and the behavior is still better than before with MyISAM, even if it is not a full ACID. It will be much faster than AUTOCOMMIT without this setting.
a source to share
Yes . This will certainly give some performance boost. But then you have to manually commit if you do any updates.
What I know about AutoCommit mode is that it will automatically commit transactions after db operations, which forces the indexes on the tables to rebuild on every commit, which slows down performance.
a source to share