Large Databases

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • kerrghann
    New Member
    • Jul 2012
    • 14
    • 4.2.x

    Large Databases

    So, I don't normally post or ask questions. Generally I tinker and fiddle with things until I figure them out. This one, however, has mystified me for awhile. I own a website that utilizes Vbulletin 4.2.2 ( http://www.rpgchat.com/ ). We are a website dedicated to creative writing, roleplaying, and other play by post style cooperative writing. This essentially means that most of the posts on my website need to stay there (they hold memories for a lot of people as well as being used in later writings).

    My issue, however, is the fact that my Vbulletin Database is approached 8 GB in size. I've installed VBOptimize, which does wonders for it and really assisted with migrating attachments, avatars, and the like OUT OF my database and into APC (Alternate PHP Caching system).

    I've pruned the members that joined older than 365 days with 0 posts, and I've also deleted members older than 1,825 days with less than 10 posts who haven't logged in since their last post. I've also pruned all the threads that I can get away with without incurring the wrath of my user base.

    With all this in mind, is there any alternative ways for me to continue to shrink my database size? Any tricks, etc?

    As it stands, my site has gotten so big and requires so many resources, that it is rendered unusable on any system with less than 8 GB of RAM (my forum is hosted off of a dedicated server, not a shared server). I was wondering what my options are for making a 13+ year old forum database smaller?

    Thank you in advance.
    Last edited by kerrghann; Sat 25 Apr '15, 6:45pm.
    A Place to create; a place to innovate~
  • kh99
    Senior Member
    • Aug 2009
    • 533

    #2
    I don't know a lot about this, but that seems like a huge database if it's mostly posts and you're not storing attachments in it. If you have phpMyAdmin, if you display the list of tables (click on the db name) and sort them by size, what are the largest tables?

    Edit: Oh, well, 13 years of people sharing their writing, I suppose that could be a reasonable size if it were mostly posts. But it wouldn't hurt to check the table sizes if you haven't already.
    Last edited by kh99; Sun 26 Apr '15, 3:26am.

    Comment

    • Zachery
      Former vBulletin Support
      • Jul 2002
      • 59097

      #3
      vBoptimize doesn't migrate attachments out of the software... those functions are built in. Since moving that data out of the database, have you run an optimize on the attachment/file data tables?

      Are you using InnoDB or MyISAM on your tables?

      Comment

      • kerrghann
        New Member
        • Jul 2012
        • 14
        • 4.2.x

        #4
        I believe the majority of my server is MyISAM. I'm actually in the process of converting everything to InnoDB
        A Place to create; a place to innovate~

        Comment

        • Zachery
          Former vBulletin Support
          • Jul 2002
          • 59097

          #5
          I wouldn't do that if size is a concern, doubly so if you're not using InnoDB tables as files, triply so if you're relying on the default MySQL fulltext searching.

          I would recommend running an optimize on your post, thread, attachment, and filedata tables.

          Comment

          • kerrghann
            New Member
            • Jul 2012
            • 14
            • 4.2.x

            #6
            I see, how would I go about doing that? Would that be a Vbulletin Maintenance Feature, or should I start looking into queries?
            A Place to create; a place to innovate~

            Comment

            • Trevor Hannant
              vBulletin Support
              • Aug 2002
              • 24326
              • 5.7.X

              #7
              If you have phpMyAdmin installed, you will find the ability to optimize tool within there on the table listing. At the foot, there should be an option (depending on version of phpMyAdmin) to select tables with an overhead. If you don't see that, simply check the tables that have an overhead showing in the end column.

              Then, from the dropdown underneath the table list, select 'Optimize'
              Vote for:

              - Admin Settable Paid Subscription Reminder Timeframe (vB6)
              - Add Admin ability to auto-subscribe users to specific channel(s) (vB6)

              Comment

              Related Topics

              Collapse

              Working...