vBulletin 4.x upgrade / installs – MyISAM and InnoDB tables database size
vBulletin 4.x forum owners maybe wondering why after upgrading their vBulletin 3.x forums that their database size increased dramatically. The reason could be due to vBulletin 4.x upgrade or installation process automatically converting some tables from MyISAM to InnoDB storage engine.
Background
In vBulletin 4.x upgrades or installs, there are some specific circumstances where your MySQL database tables will end up created or converted on install or upgrade to InnoDB storage engine tables instead of the usual default MyISAM tables that most folks are use to with vBulletin 3.x.
If your MySQL server has InnoDB engine support enabled, the following 6 vBulletin 4.x database tables may end up as InnoDB format tables instead of the usual MyISAM format tables.
- contenttype
- searchcore
- searchgroup
- searchlog
- tag
- tagcontent
The code that determines this is located in functions_installupgrade.php
innodb_tablespace_utilization_perc: NULL
total_size_gb: 30.0126212192699
index_size_gb: 10.6734915226698
data_size_gb: 19.3391296966001
innodb_total_size_gb: 0
innodb_data_size_gb: 0
innodb_index_size_gb: 0
myisam_total_size_gb: 29.9825009061024
myisam_data_size_gb: 19.3215826703236
myisam_index_size_gb: 10.6609182357788
perc_index: 35.563343300
perc_data: 64.436656600
innodb_perc_index: NULL
innodb_perc_data: NULL
myisam_perc_index: 35.557134700
myisam_perc_data: 64.442865200
innodb_perc_total_index: 0
innodb_perc_total_data: 0
myisam_perc_total_index: 99.882200800
myisam_perc_total_data: 99.909266700 |
vBulletin folks designed vBulletin 4 this way to accommodate larger post forums which may have table locking issues with the default MySQL MyISAM storage engine based tables. InnoDB is better in this regards with it’s row level locking which has benefits for large post count and high concurrent traffic vBulletin forums as a way to workaround MyISAM table level locking. Full explanation in Mike Ander’s blog post here.
Potential Issues
Now the potential issue is there is no mention in vBulletin documents online that the above 6 listed tables may get converted automatically to InnoDB if InnoDB engine support is enabled. This may cause problems for some folks as InnoDB storage engine tables have their specific know-how and practices that the vBulletin owner/server administrator must be aware of. I’ve touched on this in my blog post here.
Also smaller vBulletin forums with lower post count or less concurrent traffic will run more optimally on the default MyISAM tables. But if you do find that your vBulletin 4 install or upgrade converted your database tables to InnoDB, read further below to see what your options are available – including converting your InnoDB tables back to MyISAM and disabling vBulletin 4.x auto conversion from MyISAM to InnoDB.
InnoDB specific info to be aware of
1. If vBulletin 4 upgraded owner moved their forum database from a web host which InnoDB storage engine support enabled with converted InnoDB tables to a web host which has InnoDB storage engine disabled, you may come across vBulletin errors such as
mysql -u mysqlusername -p -e "SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows,2),' Rows') AS 'Number of Rows',ENGINE AS 'Storage Engine',CONCAT(ROUND(data_length/(1024*1024),2),'MB') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024),2),'MB') AS 'Index Size' ,CONCAT(ROUND((data_length+index_length)/(1024*1024),2),'MB') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'databasename';" |
To fix you’d either have to enable InnoDB storage engine support in MySQL (see below) or convert your InnoDB tables back to MyISAM (see below). Or do both if you don’t have the server resources to handle InnoDB tables, that is temporarily enable InnoDB storage engine so you can convert back the InnoDB tables to MyISAM, then disable InnoDB Storage engine after successfully converting InnoDB tables back to MyISAM tables.
2. Need an understanding InnoDB tuning parameters which aren’t set by default out of the box for MySQL. Different major versions of MySQL 5.0.x, 5.1.x, 5.5.x and forks Percona 5.1.x, Percona 5.5.x and MariaDB 5.1x and MariaDB 5.2.5 all have different InnoDB options and option names specific to their own versions to tune. There are many other InnoDB specific tuning parameters which need understanding and implementing as well. Good place to start is reading the MySQL.com documents for your specific MySQL version. MySQL 5.5 here, MySQL 5.1 here and MySQL 5.0 here.
3. If you plan to use InnoDB tables, then stick with either MySQL 5.5.11+, Percona 5.5.x or MariaDB 5.2.5 versions of MySQL as InnoDB performance is nearly 500% – yes 5x times faster than MySQL 5.0.x/5.1.x versions of MySQL server. See results at http://vbtechsupport.com/606/.
4. Different database backup, repair and recovery procedures compared to MyISAM tables which folks are use to using for their mysqldump, backup and repair procedures. For example, if you use innodb_file_per_table options for individual InnoDB *.ibd table files, you can’t move them individually like you can for MyISAM files – *.frm, *.myi, and *.myd.
5. vBulletin customers who recently upgraded from vBulletin 3.x to vBulletin 4.x may have experienced a dramatic increase in size of your vBulletin 4 database size due to the above 6 listed database tables ballooning in size due to automatic conversion to InnoDB tables when your MySQL server has InnoDB storage engine support enabled.
InnoDB storage engine and tables use more server resources than MyISAM storage engine based tables. InnoDB tables can occupy between 2x to 5x times larger on disk database size than MyISAM tables as InnoDB tables don’t compress indexes while MyISAM does. I decided to a test on one of my vBulletin 3.8.5 based forums upgrading it to vBulletin 4.1.3 with default MyISAM tables and then do InnoDB table conversions and recorded the on disk size for each database state to compare vBulletin 3.8.5 MyISAM/Memory table size vs vBulletin 4.1.3 MyISAM/Memory table size vs vBulletin 4.1.3 MyISAM/Memory + InnoDB table sizes. Read further below for the results.
InnoDB disk space would grow much faster than with MyISAM tables as the forums database grows as by design and out of the box default, InnoDB storage engine uses a shared table space so running OPTIMIZE (which for InnoDB tables is mapped to ALTER TABLE command) on an InnoDB table may not reduce the disk overhead (unused space and fragmented data files) as much. Unless you configure InnoDB to use per-table tablespace configuration option – innodb_file_per_table.
Note this option only works on future created InnoDB tables and not existing InnoDB tables. Quickest way to convert existing InnoDB tables to per-table tablespaces would be to mysqldump them to sql file and reload them into the database. Then each InnoDB database table will have an extension of *.ibd along with the accompanying definition file *.frm of the same name instead of MyISAM’s *.frm, *.myi and *.myd files.
6. InnoDB tables don’t specifically support OPTIMIZE command. You’ll get a message “Table does not support optimize, doing recreate + analyze instead“. You can see restrictions on InnoDB tables here for MySQL 5.1 and for MySQL 5.5.
Important note for InnoDB tables and OPTIMIZE command with MySQL 5.1.55 and MySQL 5.5.9 where both have a bug which can wipe out and reset an InnoDB table’s auto_increment field when OPTIMIZE is run on an InnoDB table see mysql.com bug report and blog entry here. So If you’re using InnoDB I’d make sure you’re not using those versions of MySQL. Since I am using MariaDB 5.2.5 MySQL I thought I’d check to see if my MySQL version is safe so ran OPTIMIZE TABLE on searchcore database table which is InnoDB table.
+-----------------------------------------+----------------+----------------+-----------+------------+----------+ | Table Name | Number of Rows | Storage Engine | Data Size | Index Size | Total | +-----------------------------------------+----------------+----------------+-----------+------------+----------+ | vb413dbname.access | 0 Rows | MyISAM | 0.00MB | 0.00MB | 0.00MB | | vb413dbname.adminhelp | 1744 Rows | MyISAM | 0.09MB | 0.08MB | 0.17MB | | vb413dbname.administrator | 1 Rows | MyISAM | 0.00MB | 0.00MB | 0.00MB | | vb413dbname.adminlog | 23713 Rows | MyISAM | 1.30MB | 0.31MB | 1.61MB | | vb413dbname.adminmessage | 4 Rows | MyISAM | 0.00MB | 0.01MB | 0.01MB | |
Auto_increment value is at 141,319. Now to run OPTIMIZE on searchcore table.
[mysqld] skip-innodb default-storage-engine = MyISAM |
Then to double check if auto_increment value remains intact.
[mysqld] innodb=OFF default-storage-engine = MyISAM |
Looks like Auto_increment values were retained so MariaDB 5.2.5 is safe from this InnoDB OPTIMIZE TABLE bug.
7. For performance optimization reasons, InnoDB storage engine based tables will have to also consume more memory resources due to innodb_buffer_pool_size parameter for optimal InnoDB performance the size of this buffer will ideally be sized and set to hold the entire index + data size of all your vBulletin InnoDB converted tables.
In theory, on vBulletin 4 upgrade if your MySQL server had InnoDB storage engine support enabled and vBulletin 3.x based forum had 2 million posts roughly equating to 2GB in index + data size with a 3GB sized postindex table, the upgrade script then automatically converts the searchcore and searchgroup tables to InnoDB, you could end up with InnoDB tables totally at least 8-16GB in size.
For optimal InnoDB performance, you would then need to allocate exclusively to innodb_buffer_pool_size parameter at least 8-16GB memory + 30-50% extra for overhead. That’s 12-24GB of memory allocated just to InnoDB usage. You would still have the rest of your MyISAM tables need servicing and set aside allocation of memory for them. If you don’t allocate enough memory to innodb_buffer_pool_size to fit the total accumulated size of all InnoDB data + index sizes, then you’ll end up with excessive disk I/O load and performance issues. Read further below to find out how to figure out the size or your individual database tables.
