How to archive large forum in a separate database

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • mgurain
    Senior Member
    • Jul 2003
    • 270
    • 4.2.X

    How to archive large forum in a separate database

    Hello every one,
    I'm have trouble running very big vbulletin database, since the forum started long time a go it's becoming:
    - very large in size
    - difficult to backup & restore
    and most importantly:
    - performance utilizing the server
    and that makes server stop my account!
    Is there a way to archive old threads (2007 and older) in a separate database that could be run in a different domain and copy of the vbulletin forum, or if possible run from our main vbulletin forum ?
    Or there is a different solution ?
    Thanks in advance,,
    http://www.al-jarodiah.com/vb/
    Arabic Village Social forums
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73981

    #2
    There is no way to archive forums in a separate database as you ask. This simply isn't a feature and would require extensive code modifications to work.

    If you're on a dedicated server than you should visit the Server Configuration forum and ask for help in tweaking your server for better performance. There is a thread stickied that can help with this. You will find it here:



    Other than that, you might want to look into a master/slave configuration where most reads come from the slave database and writes go to the master database. MySQL will replicated the data across the servers.

    For backing up and restoring, you should be using SSH and the commands provided by MySQL.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment

    • kris
      New Member
      • May 2001
      • 7

      #3
      Originally posted by Wayne Luke
      There is no way to archive forums in a separate database as you ask. This simply isn't a feature and would require extensive code modifications to work.

      If you're on a dedicated server than you should visit the Server Configuration forum and ask for help in tweaking your server for better performance. There is a thread stickied that can help with this. You will find it here:



      Other than that, you might want to look into a master/slave configuration where most reads come from the slave database and writes go to the master database. MySQL will replicated the data across the servers.

      For backing up and restoring, you should be using SSH and the commands provided by MySQL.
      Wayne,
      I have same pain and constant problems with very large database that 8 years old and has over 8 million posts. We understand that current version on vB has no real Archive functionality but please could you or somebody else from vBulletin Team gives us some kind of guidelines how can we Archive (close) all old threads (with all posts) wich are more then 2 years old then move them to separate database or even separate server just to make it read only and searcheble.

      I've been optimized my server for long time and I visited Server Optimization forum alot but still time to time I have "table locked" problems if total online users more then 1200. I dont know why, my wait_timeout and table_lock_wait_timeout are set to minimal.

      I think its just right time to Archive old threads and get over it. I want to use my resurces for something else.

      I been searching for solutions for long time and I could not find it anywhere, its too bad that is not too simple to achive. I know it posible you can take a look at AVS Forum Archives, its good way to Archive and I want something like it for my forum.

      Wayne, please help us and your help would be very appreciated !

      Comment

      • Steve Machol
        Former Customer Support Manager
        • Jul 2000
        • 154488

        #4
        Sorry, there is no function to do this. This requires modifying the code. We cannot officially support code modifications or forums running modified code, however you can try searching or asking for help with this over at www.vbulletin.org.
        Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
        Change CKEditor Colors to Match Style (for 4.1.4 and above)

        Steve Machol Photography


        Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


        Comment

        • briansol
          Senior Member
          • Apr 2006
          • 674
          • 3.6.x

          #5
          Looks like avs forum just started fresh.

          they left vb 2.2 on there back in '04 with all the old posts, and then just started fresh on a new install of whatever version it was back then.

          I don't think this is a very good idea, personally.

          If you're locking a lot, you may need more ram to support more concurrent connections.

          What hardware is your db server on?

          Comment

          • 1QuickSI
            Senior Member
            • Oct 2001
            • 881
            • 4.2.5

            #6
            There was a rather large post awhile back that dealt with boards that were considered muuch larger then your typical "big board" and dealt with database structure, changes and how they attacked the problem.
            -----------------------------------------------------------
            Running custom version of vBulletin based on v4.2.5
            PHP 7.4.14 :: MariaDB 10.5.8

            Comment

            • feldon23
              Senior Member
              • Nov 2001
              • 11291
              • 3.7.x

              #7
              Originally posted by briansol
              Looks like avs forum just started fresh.

              they left vb 2.2 on there back in '04 with all the old posts, and then just started fresh on a new install of whatever version it was back then.

              I don't think this is a very good idea, personally.
              There was some very interesting talk 3 years ago that vBulletin might write and include a Perl front-end for a true flatfile vBulletin Archive. I was really looking forward to this fantastic idea, but it never materialized.

              The bigger your vBulletin forum, the more hardware you are going to have to throw at it to keep up. It is a major scaling problem that vBulletin needs to do something about. Right now, you're totally painted into a corner.

              Originally posted by Steve Machol
              Sorry, there is no function to do this. This requires modifying the code. We cannot officially support code modifications or forums running modified code, however you can try searching or asking for help with this over at www.vbulletin.org.
              My favorite boilerplate response. Yum!

              Comment

              • kris
                New Member
                • May 2001
                • 7

                #8
                Originally posted by briansol
                Looks like avs forum just started fresh.

                they left vb 2.2 on there back in '04 with all the old posts, and then just started fresh on a new install of whatever version it was back then.

                I don't think this is a very good idea, personally.

                If you're locking a lot, you may need more ram to support more concurrent connections.

                What hardware is your db server on?
                you right about "avs forum just started fresh" and it not good idea, I agree.

                my hardware is very good , I have 2 Quad core Xeons L5320 @ 1.86GHz with 8 gb memory + raid 5 scsi disks and my board is only thing that running on the server, I have no problem with server load and normally its around 1.5 , but sometime randomly mysql (ver MySQL 5.0.58) begins eat all resources and mytop report shows a lot of locking post table requests, and cure is very simple, I need to stop httpd server for few minutes and then mysql recovers itself because httpd server not sending anymore new requests from forum users.

                I have a lot of hacks plus my own addons, I want to keep them and even create more that means more database requests.

                More people I ask more and more I agree that mysql is not very good database for large datasets and big tables especially when you depend on only one server. If you can afford 2 or 3 servers, it might be working fine, one master mysql server for record updates and others for read only selects.

                Finally I decided to make read only archive for threads that more then 2 years old. I hope after that post table will be small and I hope my locking problem will go away and I can afford more requests to mysql.

                I think is pretty simple to create Archive and I think I can do it myself but I want to make sure that I am going to right direction, I am not sure if I can post and discuss it on this forum, vBulletin team please delete it if its violates something

                Basically, here is what I have in mind :

                - create backup
                - make sure that all attachments on file system
                - create copy of "vb_current" database lets call it "vb_archive" database
                - in "vb_archive" table "thread" delete all records where lastpost >= 1167631200 (timestamp for 01/01/2007) [delete all NEW active threads where last post was made after 2007]
                - in "vb_archive" table "post" delete all records where dateline >= 1167631200 (timestamp for 01/01/2007) [delete all NEW posts in archive]

                [and we have vb_archive database with only old, not active threads and posts (am I right?) now lets clear active vb_current database from old posts and threads that present in vb_archive]

                - in "vb_current" table "thread" delete all records where lastpost < 1167631200 (timestamp for 01/01/2007) [delete all OLD not active threads where NO post was made after 2007]
                - in "vb_current" table "post" delete all records with postids = all postids from database "vb_archive" table "post" [delete all posts in current database that already present in archive]
                - in "vb_archive" table "thread" update open = 0 [close all threads in archive]

                I think thats it ! after this table post in vb_current database will be much smaller and mysql query will be much faster !

                Please correct me if I missed something.

                Comment

                • KrON
                  Senior Member
                  • Jan 2002
                  • 705
                  • 3.6.x

                  #9
                  Somewhere in my post history I've covered how we handle archiving at PbNation. Essentially we move old, inactive posts to post_X tables, then create a MySQL Merge table against all of those post_X tables called post_archived. It's indexed by our Sphinx search so it's searchable and all threads are viewable (but read only).

                  We have something like 10 post_X tables, each containing somewhere around 3 million posts. When all is said and done we have about 51 million posts online, hosted by one database server.
                  Kyle Christensen
                  PbNation.com - one of the biggest and busiest vbulletin forums on the net!

                  Comment

                  widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                  Working...