Database size is huge

Rob

Well-known member
After successfully importing vB3.8 I was suprised to see that the xF database is approximately 25% bigger in size. I am suprised because the vb database contains thousands of social groups posts and thousands of blog entries not to mention many many mods and their data - non of which have been imported.

I'm not naive enough to think a bigger database is slower because that is always down to the schema and architecture... however i was just shocked at the size difference thats all.
 
The main difference will more than likely be the search_index table, compared to the equivalent in vB.
 
It's correct that it's a bit bigger, because data is more specific. A user and it's data isn't just in the user table for example. This allows XF to be very specific when doing SQL queries with PHP and limit what data it's requesting, and in effect be more efficient than others who have it stuffed in a single place. This can also benefit caching quite a bit in certain situations.
 
Just to add that when I ran MyBB and had over 1000 or more threads listed, not counting replies. I did spot the database backup was much smaller than the XenForo one running alongside it with nothing listed at all "clean install", maybe one thread listed at most. I also then thought, how comes that's the case? Didn't bother me or anything, just something I spotted comparing things. Think I even checked how many tables they both used and saw XF only had a few extra ones, not sure now though.
 
Yup - mine increased from around 1.5Gb to around 3Gb, mostly sue to the search indexing. However it was too big for my then host, so i went on a pruning spree :D
 
The biggest factor with database size is simply the storage engine you are using. It's pretty typical to see a InnoDB use 150-175% the storage space that MyISAM does. For what it's worth, I have my XenForo dev installation using all MyISAM tables for now (undecided what I'll use in production).
Depending on the value of pack_keys for a MyISAM table, InnoDB uses 1.24X, 1.35X or 1.46X the disk space as used by MyISAM...

The overhead for an InnoDB row in MySQL 5.0 and beyond is approximately 20 + (1 * #columns) bytes

http://mysqlha.blogspot.com/2009/01/innodb-myisam-and-disk-space_16.html
 
So, what benefit does InnoDB give over MyISAM?

There must be one - they've used it for a reason I assume?
 
I have always been led to believe that InnoDB is slower at SELECTs but maybe im wrong I guess.
 
I've read a few articles in the past about InnoDB v's MyISAM. From what I read InnoDB is the better of the two because of the locking thing, but suffers slightly in speed performance to MyISAM but offers better stability. Overall InnoDB was said to be the best, especially when coming to big board use. Or so I read.
 
InnoDB is more stable and you have less of chance from database corruption. The #1 issue when converting from vBulletin to "X" (Xenforo, phpBB, ect...) is database corruption. I can't tell you how many times people have sent me a PM asking me why to conversion doesn't work. 1st thing I tell them to do is run the repair & optimize function... All so far have always (100%) lead to a successful resolve.

InnoDB started out slower, this is true. But as development has progressed, it's just a little shy from equallying out to MyISAM. MySQL 5.6 actually breezes by without any noticable speed loss. I suspect as time goes into further development, it could actually become faster than MyISAM. After all, it's still being developed and is already (almost) equal in speed.
 
Aha, so I though I am crazy when a NEW forum (2 months, with around 4K is has a bigger database than a 5 year VB with 60K posts). Well, can't complain about how it runs, am actually pleased with XF, but it's good you guys clarified this. Was about to start a useless thread here and make a fool of myself :D
 
Top Bottom