I’m no DBA or Linux guru, so bear this in mind when reading the MySQL server benchmark results below, that settings and test configurations I chose to run are just based on my own experiences with vBulletin and wordpress usage patterns. Results reflect only the specific versions tested and may not translate to other versions within the same branch i.e. MySQL 5.1.55 vs 5.1.56 or MySQL 5.5.9 vs 5.5.10 or MariaDB 5.2.4 vs MariaDB 5.2.5.
Each MySQL server version particularly MariaDB, Percona and MySQL 5.5.x series have their own specific configuration options used to tune performance so there aren’t any directly comparable my.cnf options available in the benchmark tests.
For vBulletin usage, the benchmark results illustrated here may not directly translate the same for vBulletin usage due to the way Sysbench does the tests.
For vBulletin usage, please bear in mind not all vBulletin tables can be converted to InnoDB with vBulletin 3.8.x series as explained here. For vBulletin 4.x usage, this has changed somewhat as you can read here and here.
For vBulletin users, blindly switching over from MyISAM default tables to InnoDB tables isn’t advised. InnoDB like MyISAM needs understanding on which variables to tune for best performance. InnoDB also has higher hardware resource (disk and memory) resource requirements than MyISAM for optimal performance. InnoDB disk space usage can be up to 2x to 5x times higher than MyISAM disk space usage. That means a 2GB MyISAM table once converted to InnoDB could occupy between 4GB and 10GB of disk space in InnoDB format.
InnoDB also have different database backup, repair and recovery methods from what users are familiar with for MyISAM tables. So make sure to be familiar with all the InnoDB specific tuning and administration end information before your decide to switch. It’s not uncommon for some vBulletin forums to get worse performance when switching from MyISAM to InnoDB tables without properly tuned InnoDB settings and hardware specific requirements in place. Essentially, having enough physically installed memory allocated to InnoDB buffer pool to fit entire size of all InnoDB tables – index + data with room to spare too.
Oracle MySQL has a MySQL 5.5 pdf file which outlines further notes for migration from MyISAM to InnoDB tables – including some already mentioned above as well as their own Sysbench benchmark results and specific application usage scenarios where MyISAM tables is more suited to specific tasks than InnoDB tables. One such case, is that InnoDB tables shouldn’t be used on NFS shares due to possibility of conflicting writes and limitations of maximum file sizes. So the pdf file is worth a read here.
