Announcement

Collapse
No announcement yet.

3.5.x to 3.6.0, large boards

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #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


    • #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


      • #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


        • #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


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

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

            arn

            Comment


            • #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


              • #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


                • #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


                  • #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


                    • #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


                      • #26
                        Originally posted by Mirical Bernd View Post
                        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


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

                          Comment


                          • #28
                            Originally posted by telc View Post
                            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


                            • #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...
                              X