• Register
    • Help

    striker  0 Items
    Currently Supporting
    • Home
    • News
    • Forum
    • Wiki
    • Support
      • Manage Subscriptions
      • FAQ
      • Support For
        • VaultWiki 4.x Series
        • VaultWiki.org Site
    • What's New?
    • Buy Now
    • Manual
    • 
    • Support
    • VaultWiki 3.x Series
    • Task
    • VaultWiki and altering of POST table

    1. Welcome to VaultWiki.org, home of the wiki add-on for vBulletin and XenForo!

      VaultWiki allows your existing forum users to collaborate on creating and managing a site's content pages. VaultWiki is a fully-featured and fully-supported wiki solution for vBulletin and XenForo.

      The VaultWiki Team encourages you to join our community of forum administrators and check out VaultWiki for yourself.

    Issue: VaultWiki and altering of POST table

    • Issue Tools
      • View Changes
    1. issueid=1570 May 15, 2010 3:45 AM
      kmike kmike is offline
      Junior Member
      VaultWiki and altering of POST table

      As you may imagine, any messing with the POST table is a huge pain for the big forums, so I winced when I saw VaultWiki adding a new column to it in 3.0.0 RC2.

      I see that the new field is used to indicate the usage of Wiki BB code in a post. I'd imagine the rate of Wiki/standard BB code usage will be very low, so why not use a separate table (which will be very small) for that flag and JOIN it when needed?
    Issue Details
    Issue Number 1570
    Issue Type Task
    Project VaultWiki 3.x Series
    Category Performance
    Status Completed
    Priority 3 - Loss of Functionality
    Target Version 3.0.0 RC 3
    Resolved Version 3.0.0
    Milestone VaultWiki 3.0.0
    Software DependencyAny
    Votes to perform 1
    Votes not to perform 0
    Attachments 0
    Assigned Users (none)
    Tags (none)




    1. May 15, 2010 3:56 AM
      pegasus pegasus is offline
      VaultWiki Team
      In the short-run (the upgrade process itself), this may seem desirable because it won't take a few minutes for large post tables to update. In the long run, joining an additional table would be less efficient than having the column there all the time:
      • This value is needed whenever the post is parsed, which is almost any time you see the post,
      • Joins have additional overhead, and we'd have to do the join for every query to the post table.
      • Save actions would become more complicated and require more plugin code (at least 5 more plugins).

      Perhaps there's a way to alter the post table without locking it up during that period?

      EDIT: After reviewing the MySQL reference on ALTER TABLE, it seems that the problem is that performing an ALTER actually creates a temporary copy of the table (which will take a while for multi-gigabyte tables), and then completely re-indexes the table (which takes even longer). This is certainly a lot more intensive than I originally thought adding an non-indexed INT(3) column would be.

      While my points above do have some merit - it may be better to avoid potentially crashing MySQL completely than to have the most efficient implementation. There is precedence in creating additional tables rather than altering in VaultWiki (i.e. the social group implementation that was done to simplify the upgrade process).

      So the problem really lies in the fact that yourself and a number of other big boards that have already upgraded have the altered post table. Should this column be left meaninglessly for boards with a certain number of posts (how many?), or should dropping the column be risked?
      Reply Reply  
    2. May 15, 2010 4:21 AM
      kmike kmike is offline
      Junior Member
      Thank you for the prompt response. The support you provide here is amazing.

      I don't agree to some of your points though.

      This value is needed whenever the post is parsed, which is almost any time you see the post
      Yes, it's needed when the post is parsed. However the parsed post content is cached, so the flag value is generally needed just at the first post view.

      Joins have additional overhead, and we'd have to do the join for every query to the post table.
      I already noted that the cardinality of the Wiki bbcode flag column will be very close to zero (99.999% of flag column values will be zero). So the additional table will be tiny, and joins against a tiny table are very fast.

      The denormalized approach chosen by you inflates the post table data size which results in more I/O and will have a significant performance impact for the I/O bound db servers.

      Save actions would become more complicated and require more plugin code.
      That's true, it can't be helped.
      Reply Reply  
    3. May 16, 2010 1:20 AM
      pegasus pegasus is offline
      VaultWiki Team
      Moving this to the project area so that it can be addressed for the gold release.
      Reply Reply  
    4. May 16, 2010 1:54 AM
      kmike kmike is offline
      Junior Member
      So the problem really lies in the fact that yourself and a number of other big boards that have already upgraded have the altered post table. Should this column be left meaninglessly for boards with a certain number of posts (how many?), or should dropping the column be risked?
      We haven't upgraded our production board yet. Even if we did, we'll be all for dropping the additional column.
      Reply Reply  
    5. May 30, 2010 12:31 AM
      pegasus pegasus is offline
      VaultWiki Team
      This behavior has been modified for the next build. In the interest of not modifying the post table itself, it should now store this data in a separate table. To achieve the same effect as before in queries with post.*, the new table is always joined to these queries.
      Reply Reply  
    6. June 1, 2010 2:54 PM
      kmike kmike is offline
      Junior Member
      Thanks!
      Reply Reply  
    + Reply

    Assigned Users
    Loading Please Wait
    Tags
    Loading Please Wait
    • Contact Us
    • License Agreement
    • Privacy
    • Terms
    • Top
    All times are GMT -4. The time now is 9:24 AM.
    This site uses cookies to help personalize content, to tailor your experience, and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Learn more… Accept Remind me later
  • striker
    Powered by vBulletin® Version 4.2.5 Beta 2
    Copyright © 2023 vBulletin Solutions Inc. All rights reserved.
    Search Engine Optimisation provided by DragonByte SEO (Pro) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
    Copyright © 2008 - 2013 VaultWiki Team, Cracked Egg Studios, LLC.