3.5.x to 3.6.0, large boards

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • RMS-Chef
    Member
    • Aug 2003
    • 75
    • 3.8.x

    #16
    WOW, I guess server horsepower is a huge factor.
    Just ran upgrade script from v3.5.4 and was expecting a lot of wait time for those two table ALTER steps but the first complete in about 8 min and the second in about 5 min. Running Dual Xeon 3.8GHz, 4GB RAM with dual SCSI drives.

    Comment

    • tpearl5
      Senior Member
      • Jul 2001
      • 547
      • 4.2.X

      #17
      wish I would've seen this earlier as well.

      When looking at the log I'm getting this:
      Query | 4599 | Repair with keycache | ALTER TABLE post ADD infraction SMALLINT UNSIGNED NOT NULL
      At this rate it would've taken a good 8 hours per query! (when I updated the indexes, for some reason it defaulted to 'repair with keycache' as well.

      Anyone have any idea why it's defaulting to 'repair with keycache' ?

      Capture more registrations - Advanced Guest Posting & Registration
      Cell Phone Forums | Nikonites

      Comment

      • telc
        Senior Member
        • Oct 2001
        • 239
        • 3.5.x

        #18
        I pre-ran the quieries on a 3.5.1 to 3.6.2 upgrade:

        Dual Xeon 3.2 / 4G Ram

        mysql> ALTER TABLE post ADD COLUMN infraction smallint(5);
        Query OK, 3767793 rows affected (3 min 6.83 sec)
        Records: 3767793 Duplicates: 0 Warnings: 0

        mysql> ALTER TABLE thread ADD COLUMN deletedcount smallint(5);
        Query OK, 184040 rows affected (7.48 sec)
        Records: 184040 Duplicates: 0 Warnings: 0

        mysql> ALTER TABLE post ADD COLUMN reportthreadid int(11);
        Query OK, 3767793 rows affected (1 min 34.14 sec)
        Records: 3767793 Duplicates: 0 Warnings: 0

        mysql> ALTER TABLE thread ADD COLUMN lastpostid int(11);
        Query OK, 184040 rows affected (9.64 sec)
        Records: 184040 Duplicates: 0 Warnings: 0

        Comment

        • Mike Warner
          Senior Member
          • Jun 2001
          • 521
          • 4.0.x

          #19
          Thanks for posting this - it is exactly what I was looking for. My upgrade is at:
          vBulletin 3.6 Upgrade System
          (Please be patient as some parts may take some time)


          Step 1) Large table alterations (1/2) (Step 1 of 11) XML File Versions:

          vbulletin-style.xml
          vbulletin-settings.xml
          vbulletin-language.xml
          vbulletin-adminhelp.xml

          3.6.2
          3.6.2
          3.6.2
          3.6.2 Step 1) Large table alterations (1/2)
          I have around 3 million posts and it has been doing this for about 30 mins now. I'll go and put the kettle on........
          Mike Warner
          MIGWeb - a Vauxhall Site for Enthusiasts of all Vauxhalls

          Comment

          • macrumors
            Senior Member
            • Jun 2002
            • 215

            #20
            WARNING: These raw mysql calls are not exactly right.

            See: http://www.vbulletin.com/forum/showt...ight=reporting

            arn

            Comment

            • Domenico
              New Member
              • Mar 2024
              • 2

              #21
              540.000 posts and the upgrade script was ready within 4 minutes. Xeon with 2GB ram and 15k SCSI RAID5. I guess sometimes size does matter.

              Webhostingtalk.nl :: For all your Dutch and European hosting quotes

              The best and only hosting forum you need in Europe
              You can ask your quotes and questions in English!

              Comment

              • woodysfj40
                Senior Member
                • May 2004
                • 144
                • 3.8.x

                #22
                size does matter....

                1.5m posts, upgrade from 3.5.4 to 3.6.2 took about 15 minutes...another 40ish to do the two table recounts

                db runs on a DL580 quad processor 900mhz Xeon, four 10k 18GB SCSI's, 6GB memory

                literally done over a lunch hour....

                Comment

                • Mirical Bernd
                  Senior Member
                  • Mar 2002
                  • 397
                  • 3.6.x

                  #23
                  Query OK, 1644236 rows affected (15 min 36.44 sec)
                  Records: 1644236 Duplicates: 0 Warnings: 0
                  Query OK, 46625 rows affected (5.34 sec)
                  Records: 46625 Duplicates: 0 Warnings: 0
                  Query OK, 1644236 rows affected (13 min 22.13 sec)
                  Records: 1644236 Duplicates: 0 Warnings: 0
                  Query OK, 46625 rows affected (3.53 sec)
                  Records: 46625 Duplicates: 0 Warnings: 0

                  The whole update took with backup of the database and counter-update about 1:10 hours. Not that bad, considering the size of our board

                  Running on a AMD XP 2400+ with 2 GB RAM. And having installed a rather huge network of sites too on this server.

                  Comment

                  • telc
                    Senior Member
                    • Oct 2001
                    • 239
                    • 3.5.x

                    #24
                    If you ran those 4 queries they are wrong, they do not create the fields properly. I had to run these 4 additional ones to fix the problem.

                    PHP Code:
                    ALTER TABLE post   CHANGE infraction infraction SMALLINT(5UNSIGNED DEFAULT '0' NOT NULL;
                    ALTER TABLE thread CHANGE deletedcount deletedcount SMALLINT(5UNSIGNED DEFAULT '0' NOT NULL;
                    ALTER TABLE post   CHANGE reportthreadid reportthreadid INT(11UNSIGNED DEFAULT '0' NOT NULL;
                    ALTER TABLE thread CHANGE lastpostid lastpostid INT(11UNSIGNED DEFAULT '0' NOT NULL

                    Comment

                    • Mirical Bernd
                      Senior Member
                      • Mar 2002
                      • 397
                      • 3.6.x

                      #25
                      I took the ones here: http://www.andreas-kraus.net/blog/up...date-timeouts/ seems that they are the right ones, when I see your changes.

                      Comment

                      • telc
                        Senior Member
                        • Oct 2001
                        • 239
                        • 3.5.x

                        #26
                        Originally posted by Mirical Bernd
                        I took the ones here: http://www.andreas-kraus.net/blog/up...date-timeouts/ seems that they are the right ones, when I see your changes.

                        Those are not correct, they set the default to NULL when it should be '0', they do not create an unsigned default '0' NOT NULL. Look at your table and you will see those fields you created do not conform to the way the other fields are in vb tables are created.

                        The vb upgrade script adds those defaults when it runs:


                        See the FIELS
                        $upgrade->add_field(
                        sprintf($upgrade_phrases['upgrade_300b3.php']['altering_x_table'], 'post', 1, 1),
                        'post',
                        'infraction',
                        'smallint',
                        FIELD_DEFAULTS
                        ) ;
                        See the FIELD_DEFAULTS that parameter has it create the 'NOT NULL' and other defaults. Those raw sql queries fail to add this in.


                        Take a look at macrumors post above: http://www.vbulletin.com/forum/showp...6&postcount=20
                        Last edited by telc; Thu 30 Nov '06, 8:25am.

                        Comment

                        • Mirical Bernd
                          Senior Member
                          • Mar 2002
                          • 397
                          • 3.6.x

                          #27
                          ah ok, thanks a lot made the changes now

                          Comment

                          • dareen
                            Member
                            • Mar 2001
                            • 31

                            #28
                            Originally posted by telc
                            If you ran those 4 queries they are wrong, they do not create the fields properly. I had to run these 4 additional ones to fix the problem.

                            PHP Code:
                            ALTER TABLE post   CHANGE infraction infraction SMALLINT(5UNSIGNED DEFAULT '0' NOT NULL;
                            ALTER TABLE thread CHANGE deletedcount deletedcount SMALLINT(5UNSIGNED DEFAULT '0' NOT NULL;
                            ALTER TABLE post   CHANGE reportthreadid reportthreadid INT(11UNSIGNED DEFAULT '0' NOT NULL;
                            ALTER TABLE thread CHANGE lastpostid lastpostid INT(11UNSIGNED DEFAULT '0' NOT NULL
                            so do we just use the above instead of the ones in the link?

                            Comment

                            • Cloud-Warrior
                              Member
                              • Sep 2001
                              • 44

                              #29
                              I haven't run them yet but I presume the correct queries are:

                              PHP Code:
                              ALTER TABLE post ADD COLUMN infraction SMALLINT(5UNSIGNED DEFAULT '0' NOT NULL;
                              ALTER TABLE thread ADD COLUMN deletedcount SMALLINT(5UNSIGNED DEFAULT '0' NOT NULL;
                              ALTER TABLE post ADD COLUMN reportthreadid INT(11UNSIGNED DEFAULT '0' NOT NULL;
                              ALTER TABLE thread ADD COLUMN lastpostid INT(11UNSIGNED DEFAULT '0' NOT NULL
                              ?

                              Comment

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