How transaction level isolation affects performace in autocommit mode in MySQL?

Question

Hello.

I have a VBulletin 4.x forum running on my server. Some forum tables were converted into InnoDB for performance reasons according to this instruction. Forum itself does not use transactions (no START TRANSACTION or BEGIN WORK in source code) at all and InnoDB tables are used just to prevent tables from locking on UPDATE queries. Forum is functioning in autocommit mode of course.

Do I understand correctly, that I can change the default server transaction isolation level into READ UNCOMMITED in this case and get some performance gains this way?

Answer 1

Yes. It will surely give some performance gain. But then you have to manually do the commits, if you are doing any updates.

What i know about AutoCommit mode is, it will do the commits automatically after db operations, which causes index on tables to rebuild on every commit, that makes down the performance.

Answer 2

TL;DR: If your forum is slow, the TRANSACTION ISOLATION LEVEL is most likely not the cause of it and setting it to anything else than the default will hardly help. Setting innodb_flush_log_on_trx_commit = 2 will help, but has durability consequences for crashes.

The long version:

What TRANSACTION ISOLATION LEVEL does I have written up in http://mysqldump.azundris.com/archives/77-Transactions-An-InnoDB-Tutorial.html. Check out all 3 InnoDB overview articles in http://mysqldump.azundris.com/categories/32-InnoDB.

The upshot it that in any case the system must be able to ROLLBACK, so not even READ UNCOMMITTED is changing anythat that needs to be done on a write.

For reading transactions, the read is slower when the chain of undo log records leading to the view for the reading transaction is longer, so READ UNCOMMITTED or READ COMMITTED may be very slightly faster than the default READ COMMITTED. But your have to keep in mind that we are talking memory accesses here, and it is the disk accesses that slow you down.

On the matter of AUTOCOMMIT: This will synchronize every single write statement to disk. If you have been using MyISAM before and that was good enough, you may want to configure

[mysqld]
innodb_flush_log_on_trx_commit = 2

in your my.cnf file and restart the server.

That will make the commit write from the mysqld to the file system buffer cache, but delay flushing the file system buffer cache to disk so that it happens only once a second. You will not lose any data on mysqld crash, but you may lose up to 1s worth of writes on hardware crash. The InnoDB will recover automatically, even after hardware crash, though, and the behavior is still better than it was with MyISAM before, even if it is not full ACID. It will be much faster than AUTOCOMMIT without that setting.