[Suite] Upgrade 3.71 to latest 4.X release - LONG upgrade step 104

[DiscussAnything]

I'm running an upgrade on a test site in preparation for my sites migration from 3.7.1 to 4.X. the step did note 'this may take a long time' however it has been running for well over 5 hours.

If I run query status this is what I get:

Time Elapsed: 00:396:46, State: Repair with keycache, Query: ALTER TABLE post ADD INDEX threadid_visible_dateline (threadid,visible,dateline,userid,postid)

The server is still showing load, which rises and falls, so I think it is churning. My POST table has 2.1m rows and is about 1.7GB.

Is this normal? I can leave it overnight, but I really don't want my production change to take this long. Is the command line option quicker? Or does that just avoid time outs?

Any mysql tweaks I can do to help?

[Trevor Hannant]

It may be a little quicker but the main advantage is that it will prevent it timing out.

[DiscussAnything]

I know it is a question that you cannot really just answer, but 'how long should it take.' I don't really want to have 12-18 hours of downtime on my forums while I'm doing the upgrade. Is there any way I can speed this up? Any way to do it in phases? I didn't have a time out issue since the server was under load and it completed (that step, I had to refresh the browser for it to go to step 105).

I just need some method I can do this upgrade in a reasonable amount of time.

[Steve Machol]

It's not possible for us to tell you how long it will take, This depends on a lot of factors, including server issues. However I would fully expect this to in in hours instead of minutes.

An alternative upgrade method is via shell. Please see these instructions to upgrade via shell:

http://www.vbulletin.com/docs/html/upgrade_cli

[DiscussAnything]

Steve, I know it is an impossible question 'how long should this take' but I guess the question is more, does 10-12 hours for Step 104 (and I seem to be having a similarly long time on stpe 105) make sense? In the past all of my upgrades have taken 1-2 hours, but nothing this long.

The server itself is a new dedicated box with nothing else running on it. (Production is on another server)

Will the CLI be faster? Any mysql or php tweaks I should to to help performance?

The server itself is:
Pentium E5300
2GB RAM
2GB Swap

So, not the fastest machine in the world, but faster than my current production environment and should be sufficient to work with a 4GB database...

[Steve Machol]

Yes, I can see that step taking that long, depending on the server.

CLI will be faster and less server intensive. In fact, we provide that option specifically for large databases such as yours.

[DiscussAnything]

Steve, thanks for the info. I'll give the CLI a crack and let you guys know how it goes.

[Zachery]

Actually, the quickest way to make the alters on the post table go faster is to drop the vB3's fulltext indecdies on the post table.

[Paul M]

Is the command line option quicker? Or does that just avoid time outs?

The command line option will process the steps quicker as you dont need to wait for all the back and forth http communication, but it will not process the actual mysql commands any quicker, so you will still have a long delay on changes to tables such as the Post or thread tables (due to their size).

[DiscussAnything]

Paul, to me that implies that my issues with Step 104, 105, etc will not be any better as I ran through step 1-103 without issue. (which is where the http communication is occuring). Once I hit step 104, it is basically issuing 1 SQL query and then sitting back for a response, correct?

Any suggestions on what I can do to help this? Should I look to archive old posts or do something else?

[Paul M]

Once I hit step 104, it is basically issuing 1 SQL query and then sitting back for a response, correct?

Correct.

Did you do as Zachery posted, and drop any fulltext indexes on post (and thread) - they will take forever to re-index, and are not needed in vb4 (I suspect a later step will drop them anyway).

[DiscussAnything]

Oh, I had completely missed that post. I will give that a try and rerun my test tonight.

Thanks for all of the advice guys! I will give an update tonight/tomorrow.

[DiscussAnything]

Actually, the quickest way to make the alters on the post table go faster is to drop the vB3's fulltext indecdies on the post table.

Do you mean drop the 'postindex' table from the database, or is there something I should be specifically dropping from the post table? None of the columns on post when I'm browsing using phpMyAdmin appear to be an index, but there is the separate postindex table.

[Zachery]

No, there is a fulltext index on the post, and thread tables.

[DiscussAnything]

Can you help me with the SQL I should run in order to drop the index?

I tried
use 'database_name';
drop index fulltext on post;

But I received an Error 1064 (42000). Same with thread.

Sorry!