Part 2 vB4mance: vBulletin4 Optimization - Basic Guide to InnoDB Conversion

[IBxAnders]

Project Difficulty: Advanced Users
Requirements: Major database changes, database backup is a must!
Version: vBulletin4 only! Do not run on vB 3!

In an earlier blog post (here) we've discussed the basic benefits of converting your vBulletin forum database from the default MyISAM storage engine to InnoDB.

To recap, a typical vBulletin installation will use the MyISAM table engine. MyISAM engine is a popular and efficient table storage engine. Under most typical conditions, MyISAM tables are fast to serve both "reading" and "writing" request commands. When the server is highly utilized, for example your forum is on a highly shared server and/or has a high number of posts and users online, the MyISAM tables will begin queuing MySQL queries and "locking" tables. This is because MyISAM engine uses "table level locking" instead of "row locking". This is problematic for vBulletin as such condition causes several issues not limited to general site slowness and database errors. Database slowness due to table locking is best visualized as a road traffic jam where database updates block the entire highway. It boils down to this: During the update, all other sessions that want to access this particular table must wait until the update is done, meaning that users have to wait until the database processes queued requests.

Knowing the general issue - our focus becomes avoiding "table level locking" in MyISAM. A free, easy to implement alternative to MyISAM engine is called InnoDB. The advantage of InnoDB to MyISAM is that during rush-hour conditions, InnoDB only closes one lane of traffic and allows the rest of the traffic to go through. In the case of vBulletin, InnoDB will still serve data from the database table even if portion of that table is "locked". Because of its row-locking feature InnoDB is said to thrive in high load environments. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

So, what is different in vBulletin 4 and why should you consider using InnoDB? Prior to vBulletin4, the main "post" table was impossible to convert to InnoDB without breaking the default fulltext search engine. The same problem also affected the "thread" table, making optimization hard - and requiring a third party search engine. One of the great benefits introduced in vBulletin4 is a search engine that is segmented away from the "post" and "thread" tables in the database schema. This alteration allows for an easy conversion to InnoDB without requiring any third party products to re-enable searching. Everything is optimized - and all of the default features work!

Getting down to business now.

IMPORTANT: Please make sure you have a valid backup copy of your database prior to any database changes! vBulletin, nor anyone else is responsible for your database, please back it up!

Notes: MySQL queries can be executed via many different applications, including command line access to the mysql server directly. This guide will assume that you are executing these queries via the command line interface while logged directly into your mysql server.

Turn off your vBulletin forum in AdminCP options.

1. Log into your mysql server using the credentials and host information from your vBulletin config.php file.
   
   mysql -u'mysqlusername' -p'mysqlpassword'
   
   or
   
   mysql -h'serverhostname' -u'mysqlusername' -p'mysqlpassword'
2. Go into the vBulletin database, this database name is also found in config.php file.
   
   USE vb_database_name;
3. Now is a great time to check if your database server configuration supports InnoDB; if it does not - you should install the necessary components or contact your hosting provider for support. After executing the command below, look for the "YES" next to InnoDB engine support.
   
   SHOW ENGINES;
4. After confirming that your database server supports InnoDB you can begin the conversion process. Enter and execute only one conversion command query at a time. Depending on your server and database size - some tables could take a really long time to finish converting. You can always verify the progress by opening a new database sesssion and executing "SHOW PROCESSLIST;" command; this will show you the duration of the conversion query and current action. POST table is usually the longest.
   
   alter table attachment engine=InnoDB;
   
   alter table datastore engine=InnoDB;
   
   alter table deletionlog engine=InnoDB;
   
   alter table forum engine=InnoDB;
   
   alter table pm engine=InnoDB;
   
   alter table pmreceipt engine=InnoDB;
   
   alter table pmtext engine=InnoDB;
   
   alter table post engine=InnoDB;
   
   alter table postparsed engine=InnoDB;
   
   alter table searchcore engine=InnoDB;
   
   alter table searchgroup engine=InnoDB;
   
   alter table searchlog engine=InnoDB;
   
   alter table sigparsed engine=InnoDB;
   
   alter table subscribethread engine=InnoDB;
   
   alter table thread engine=InnoDB;
   
   alter table threadviews engine=InnoDB;
   
   alter table user engine=InnoDB;
   
   alter table usertextfield engine=InnoDB;
5. Almost done. Now, to verify that the tables are indeed InnoDB, execute this command and look for the InnoDB engine type in an appropriate row.
   
   SHOW TABLE STATUS;
   
   or
   
   SHOW TABLE STATUS WHERE ENGINE='InnoDB';

EDIT 4/30/2010: Adding COMMAND LINE unix script that will tell you which tables can be converted to innodb on your database server! Take the guess work out of it. Script for advanced users; must fill out server details and be able to execute from command line in shell.

[developer294]

IBxAnders, what about blog_text table?

It looks like vBulletin team forgot to refactor dependecies for blog_text and it still contains FULLTEXT index. (like vBulletin 3.x did for all its important tables)
Should I create a bug/feature request for this in JIRA?

[David Karol]

I'm trying to run the alter table command on mySQL 5.1.58. I'm running vBulletin 4.1.10, and currently all the tables are MyISAM.

InnoDB is available, and I can change tables one at a time to InnoDB through the GUI in phpmyadmin.

When I try to run those queries manually though, as it's much faster, they run but the storage engine does not change. I don't get any errors either. Any ideas?

[Rostor]

Hello, I'm doing everything described.
I use your script and I have a lot of tables that could be converted, but I would like to understand if it's necessary or not.
To reach the best performance the most converted tables are the one you suggest right ?
Thanks