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

// #####################################################################
// Determines which mysql engine to use for high concurrency tables
// Will use InnoDB if its available, otherwise MyISAM
function get_high_concurrency_table_engine($db)
{
	if (defined('SKIPDB'))
	{
		return 'MyISAM';
	}
 
	$set = $db->query('SHOW ENGINES');
 
	while ($row = $db->fetch_array($set))
	{
		if (
			strcasecmp($row['Engine'], 'innodb') == 0 AND
			(
				(strcasecmp($row['Support'], 'yes') == 0) OR
				(strcasecmp($row['Support'], 'default') == 0)
			)
		)
		{
			return 'InnoDB';
		}
 
	}
	return 'MyISAM';
}

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 Error : Unknown table engine 'InnoDB'

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.

[root@master ~]# mysql vb413dbname
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 5.2.5-MariaDB-mariadb99-log (MariaDB - http://mariadb.com/)
 
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [vb413dbname]> show table status like 'searchcore'\G
*************************** 1. row ***************************
           Name: searchcore
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 141645
 Avg_row_length: 70
    Data_length: 9977856
Max_data_length: 0
   Index_length: 26312704
      Data_free: 0
 Auto_increment: 141319
    Create_time: 2011-04-26 07:09:51
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.35 sec)

Auto_increment value is at 141,319. Now to run OPTIMIZE on searchcore table.

MariaDB [vb413dbname]> OPTIMIZE TABLE searchcore;
+------------------------+----------+----------+-------------------------------------------------------------------+
| Table                  | Op       | Msg_type | Msg_text                                                          |
+------------------------+----------+----------+-------------------------------------------------------------------+
| vb413dbname.searchcore | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| vb413dbname.searchcore | optimize | status   | OK                                                                |
+------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (3.28 sec)

Then to double check if auto_increment value remains intact.

MariaDB [vb413dbname]> show table status like 'searchcore'\G
*************************** 1. row ***************************
           Name: searchcore
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 140922
 Avg_row_length: 70
    Data_length: 9977856
Max_data_length: 0
   Index_length: 26312704
      Data_free: 0
 Auto_increment: 141319
    Create_time: 2011-04-26 12:09:27
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

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.

Pages: 1 2 3


Previous:
Next:



MaxCDN Site Acceleration

Get Cost Effective Content Delivery
Unbeatable speed, stats, & price
http://www.maxcdn.com



No Comments

Trackbacks/Pingbacks

  1. vBulletin 3.x to vBulletin 4.x/4.1.x upgrade process for large vBulletin forums | vbtechsupport.com - [...] Read the reasons of how and why you’d want InnoDB support disabled in my blog post at http://vbtechsupport.com/675/.Step 2. Backup…

Leave a Comment