1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.5 vBulletin 3.8 Import table size increase

Discussion in 'Installation, Upgrade, and Import Support' started by MagicalMidge, Sep 13, 2016.

  1. MagicalMidge

    MagicalMidge Member

    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
     
  2. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
  3. Mike

    Mike XenForo Developer Staff Member

    Also, InnoDB tables tend to be larger than a MyISAM version.
     
    Chris D likes this.
  4. MagicalMidge

    MagicalMidge Member

    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?
     
  5. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
  6. MagicalMidge

    MagicalMidge Member

    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!
     

    Attached Files:

  7. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
  8. MagicalMidge

    MagicalMidge Member

    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.
     
  9. Mike

    Mike XenForo Developer Staff Member

    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.
     
  10. janslu

    janslu Member

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

Share This Page