XF 1.5 vBulletin 3.8 Import table size increase

[MagicalMidge]

Having just finished our test imports we noticed that the xenforo database is much larger than the original vBulletin database, with the same data.

A small size increase would be understandable, however the table "post" has increased from 9.3GB to 17.2GB, this is almost doubled in size.

Is there any cleaning up we must perform to knock the size back down?

Any help would be appreciated

Regards,
Matthew

 

[Chris D]

That seems like a large increase but it is likely just due to differences in how we store data and what additional data we cache. For example we store a cache of the like users for each post in this table, which could be a significant amount of data overall. If that overall size includes the size of any indexes, too, that could account for it.

 

[Mike]

Also, InnoDB tables tend to be larger than a MyISAM version.

 

[MagicalMidge]

Well in total the forum used to be 14GB, with xenforo it is now 51GB... As I said we were expecting a size increase but this is massive. Now the reason I didnt mention this earlier is because a lot of that is the search indexes which we are going to sort out at a later date but surely just the posts table basically doubling in size isnt normal?

What could possibly double the size of each post?

 

[Chris D]

vBulletin using MyISAM vs InnoDB slipped my mind, earlier, but that is likely to be the significant factor, along with differences in the data we store that vBulletin may not and perhaps a larger overall index size to ensure good performance when querying for posts.

There are a few technical explanations with regards to the InnoDB vs MyISAM table size:
http://stackoverflow.com/questions/2914492/why-is-innodb-table-size-much-larger-than-expected

Certainly if the bulk of the database size is the search index, you may benefit from using XenForo Enhanced Search and Elastic which doesn't store its search index in the database.

 

[MagicalMidge]

vBulletin using MyISAM vs InnoDB slipped my mind, earlier, but that is likely to be the significant factor, along with differences in the data we store that vBulletin may not and perhaps a larger overall index size to ensure good performance when querying for posts.

There are a few technical explanations with regards to the InnoDB vs MyISAM table size:
http://stackoverflow.com/questions/2914492/why-is-innodb-table-size-much-larger-than-expected

Certainly if the bulk of the database size is the search index, you may benefit from using XenForo Enhanced Search and Elastic which doesn't store its search index in the database.

Yeah thats why I didnt bring up the search index before, we are going to use the Elastic search.

Would you recommend any particular column in posts to be the primary key?

As always you guys have been amazing You have a great team!

 

[Chris D]

There is already a primary key on the xf_post table. I highly recommend you never directly alter the structure of the database, certainly not where things like primary keys are involved.

 

[MagicalMidge]

Fair enough.

So minus the search index, which we will sort a bit later, the forum size went from 14.2GB (vBulletin complete total) to 24.7GB in xenForo (thats without the search index) you guys think thats about right?

There's nothing you could recommend to shrink this down a little?

Like I said the main offender is the post table, basically doubling in size which has caused the extra 10gb.

 

[Mike]

That's probably just generally down to how InnoDB's storage system works. There's cost to things like crash resiliency, transactions, etc. You can consider compression if disk usage is a more significant concern: https://dev.mysql.com/doc/refman/5.6/en/innodb-compression.html Though you'll be trading some CPU usage to decompress.

 

[janslu]

I think this may be due to a conversion to utf-8...