Database size question

[Dodgeboard]

My database size was 263 mb in vBulletin, before importing to XF.
After import to Xenforo, 541 mb.

I expected it to drop because of it being "clean", without all the addons and stuff.

Why did it double in size? Same data. Less junk.

 

[Dean]

xf_search_index table...

 

[Dodgeboard]

So...there's nothing that I can do about the size then...is that what you are saying?

 

[Dean]

Is it causing a problem? Maybe overloading your automated backup systems you may have in place? (that is an issue I am addressing at the moment for my forum).

I will not profess to be a database expert, but in my vb database I see no huge search index, but I do in xf. I do know it has been disscussed at length a while back... but I have extremely limited internet connection at the moment so I cannot search at the moment.

 

[Dodgeboard]

Longer to download a sql backup and I can no longer do an entire site backup via cpanel as it says it's too large.

 

[Walter]

The growth is pretty normal due to the search index.

 

[Mike]

Generally speaking, you'll see more size usage from InnoDB than MyISAM, as well.

 

[Floris]

Regardless of how big the size is, I prefer to use shell, rather than the browser - to ensure complete back ups.

ssh2 into your box and


mysqldump --opt -Q -u dbusername -p databasename > backupname.sql

it will then ask for your password, this is the then the dbuserPassword

 

[Keith]

My apologies for the thread necromancy.

But could someone explain why the search table is so much larger than the ipb one I had previously.

It's not an issue (presuming its supposed to be the largest table in the database (20% larger and the xf_post table)) just curious as to why the big jump when everything else is smaller than in IPB.

 

[Jake Bunce]

But could someone explain why the search table is so much larger than the ipb one I had previously.

XenForo duplicates all posts in the xf_search_index table. That is where the fulltext indexes are stored.

There must be a technical reason for offloading these indexes to another table. Maybe to speed up post operations.

 

[p4guru]

does IPB use innodb table for that search table ? if not , then as Kier explained difference in disk space consumption is due to using innodb instead of myisam tables for xenforo. MyISAM tables used in vB compresses it's indexes while InnoDB doesn't. That's partially the reason why InnoDB tables are larger.

InnoDB tables can be between 50-300% larger in size than MyISAM tables.

You can test this out actually, just take a MyISAM table and convert it to InnoDB and note differences in sizes. Or take a InnoDB table and convert it to MyISAM and compare. You can convert via ALTER TABLE command

Backup database BEFORE testing!

to convert to InnoDB

alter table tablename engine=InnoDB;​

to convert to MyISAM

alter table tablename engine=MyISAM;​

​