vBulletin 4.x upgrade / installs – MyISAM and InnoDB tables database size

vBulletin 3.8.5 upgrade to vBulletin 4.1.3 – comparing MySQL MyISAM and InnoDB database disk usage

I took a copy of one of my vBulletin 3.8.5 forums’ databases with ~141K posts and 28K threads and 4.6K members and imported it into a new database called vb413dbname. I then recorded the database size and disk usage for this vB 3.8.5 database and for three more subsequent stages did the same. Second stage,  upgraded to vBulletin 4.1.3 with default MyISAM tables. Third stage, converted the 6 above listed vBulletin 4.x tables from MyISAM to InnoDB. Fourth stage, converted more vBulletin 4 tables to InnoDB as per recommendations made here. I used the queries listed here to calculate the index and data sizes.

The results as follows exclude my 218MB attachments which are in filesystem and not in the database. vBulletin 4.1.3 results are after doing post upgrade tasks – rebuilding the search index, adding keywords to threads and rebuilding thread information.

• vBulletin 3.8.5 MyISAM and Memory tables – Index size = 84.29MB, Data size = 561.35MB, on disk size = 650MB
• vBulletin 4.1.3  MyISAM and Memory tables – Index size = 181.358MB, Data size = 650.92MB, on disk size = 834MB
• vBulletin 4.1.3  MyISAM, Memory, 6x InnoDB tables – Index size = 205.99MB, Data size = 665.50MB, on disk size = 912MB
• vBulletin 4.1.3  MyISAM, Memory, 21x InnoDB tables – Index size = 234.24MB, Data size = 727.19MB, on disk size = 1.1GB

End result is upgrading from vB 3.8.5. to vB 4.1.3 resulted in 28% increase in disk usage. Converting vB 4.1.3 MyISAM tables to InnoDB increased disk usage by another 32%. Not quite the 2x to 5x times disk space increase I mentioned above but it will differ from forum database to database.

Interestingly, mysqldump sql file size didn’t change much.

mysqldump sql sizes compared

• vB 3.8.5 MyISAM tables = 591MB
• vB 4.1.3 MyISAM tables = 689MB
• vB 4.1.3 MyISAM + InnoDB tables = 688MB

Converting vBulletin 4.x InnoDB tables back to MyISAM

What if you need to convert the vBulletin 4.x InnoDB tables back to MyISAM ? You can do this easily by first backing up your forum database, then running this command via SSH2 telnet which will print out the ALTER TABLE query commands for you to run to convert each InnoDB table back to MyISAM.

In this example, table_schema is database name = vb413dbname and when prompted enter your mysqlusername’s password.

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

Result is ready made queries for you to run.

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';"

Take each of the printed out custom queries and run them against your vBulletin database name – in this case vb413dbname.tablename to convert vBulletin 4.x InnoDB tables back to MyISAM tables.