vBulletin 4.x upgrade / installs – MyISAM and InnoDB tables database size
How to find out storage engine type used ?
To find out what type of vBulletin database tables you are using MyISAM or InnoDB for the above 6 listed tables, you can use these specific queries listed at http://vbtechsupport.com/462/
Example, output from the first query shows no InnoDB tables, data or index in use, only MyISAM data.
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 |
For a break down by tablename of storage engine type and size use this command in SSH2 telnet.
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';" |
where
- mysqlusername = mysql username
- databasename = database name
You’ll be prompted for your mysqlusername’s password. The out put would look like this.
+-----------------------------------------+----------------+----------------+-----------+------------+----------+ | 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 | |
How do I convert InnoDB tables back to MyISAM tables ?
The above 6 listed tables would of only been converted if your MySQL server settings had InnoDB storage engine support enabled. If you had it disabled, your would of stayed with MyISAM tables. To enable or disable InnoDB storage engine support you would have to make changes in /etc/my.cnf (in linux) or C:\my.ini (windows) and then restart MySQL server for changes to take affect.
To disable InnoDB Support
[mysqld] skip-innodb default-storage-engine = MyISAM |
If using MySQL 5.5.x, you may need to replace skip-innodb with innodb=OFF
[mysqld] innodb=OFF default-storage-engine = MyISAM |
To enable InnoDB support comment out the skip-innodb option
[mysqld] #skip-innodb default-storage-engine = MyISAM |
For MySQL 5.5.x, comment out innodb=OFF or remove the line completely.
[mysqld] #innodb=OFF default-storage-engine = MyISAM |
Make sure InnoDB storage engine is supported in SSH2 telnet type:
mysqladmin -u mysqlusername -p var | grep have_innodb | have_innodb | YES |
Now if you find out your have InnoDB tables for your vBulletin 4.x database and want to convert back to MyISAM, you’ll have to follow a process of running a few queries on those 6 listed tables to ALTER the table from InnoDB to MyISAM engine. Basically, the reverse of what is explained here.
[box type=”warning”]Please BACKUP your forum database completely before running these commands[/box]
alter table contenttype engine=MyISAM; alter table searchcore engine=MyISAM; alter table searchgroup engine=MyISAM; alter table searchlog engine=MyISAM; alter table tag engine=MyISAM; alter table tagcontent engine=MyISAM; |
Depending on size of your tables, it can take anywhere from 10-20 minutes for a 10,000s of posts to several hours or days even for 10s of millions of posts. Once converted, edit your /etc/my.cnf or C:\my.ini file and disable InnoDB storage engine support with skip-innodb option and restart your MySQL server.
To disable InnoDB Support
[mysqld] skip-innodb default-storage-engine = MyISAM |
If using MySQL 5.5.x, you may need to replace skip-innodb with innodb=OFF
[mysqld] innodb=OFF default-storage-engine = MyISAM |
SSH2 telnet command:
mysqladmin -u mysqlusername -p var | grep have_innodb | have_innodb | DISABLED |
InnoDB Clean Up
Once InnoDB storage engine support is disabled and all vBulletin 4.x tables are converted from InnoDB back to MyISAM, you’ll have some cleaning up to do in MySQL data directory i.e. /var/lib/mysql for MySQL rpm binary installs or /usr/local/mysql for MySQL source tarball installs.
[box type=”warning”]Important only do below suggestions if your vBulletin database is the only database on the server which was running InnoDB tables. If you have other databases running InnoDB tables, then DO NOT delete the below suggested files[/box]
In the MySQL data directory you will have three InnoDB related files, ibdata1, and 2x ib_log* log files as explained in MySQL.com documentation. You’ll need to delete these (be sure to have checked vBulletin table conversion from InnoDB back to MyISAM is working first). Or you can move them out of the way for safe keeping.
Now you should be back to running vBulletin 4.x forum with MyISAM tables (and the usual Memory tables). If you want to disable this automatic conversion to InnoDB, you apparently can add this undocumented option in your vB 4.x config.php file
define('SKIPDB', true); |
Unfortunately, I tried this on the below test upgrade and it resulted in problems in doing a test upgrade from vBulletin 3.8.5 to vBulletin 4.1.3. With this option in place, when i tried to run /install/upgrade.php I would get this error message complaining of missing class_upgrade_385.php file which never existed in the first place. So couldn’t proceed with the upgrade.
Upgrade page with SKIPDB option in config.php
Upgrade page without SKIPDB option in config.php
With SKIPDB option in config.php hitting the Begin Upgrade button gives this error on next page.
Warning: require_once(/var/www/html/install/includes/class_upgrade_385.php): failed to open stream: No such file or directory in /var/www/html/install/includes/class_upgrade.php on line 373 Fatal error: require_once(): Failed opening required '/var/www/html/install/includes/class_upgrade_385.php' (include_path='.:') in /var/www/html/install/includes/class_upgrade.php on line 373 |
But once you remove that option from vBulletin 4.x config.php file, upgrade completed fine.
