we've found part of the issue. I never even gave it a thought that my thread and post tables in my vbulletin database are innodb not myisam. that is causing it to be slower than expected.
making progress again though
Yeah that would be a problem. lol...nice catch.
Yeah, sorta forgot how long it takes to convert the tables though. My 31GB post table has been converting for 8.5 hours now and isn't finished. Then I've gotta do the thread table, which should be only a couple of hours to do as it is a lot smaller (about 0.5 GB-ish). Luckily there are only the two tables that are InnoDB that need converting to MyISAM.
I'll just need to remember to do the same for the other boards to convert before starting.
I never gave it a thought because they have been InnoDB for so long that I had completely forgotten about it.
It should be a lot nippier when they are back to MyISAM tables though.
woohoo! It only took 20 hours to convert the two database tables, but it has finally finished and the import is running again. Its not running badly either. It certainly seems faster.
It took 1h 15m with the InnoDB table to do 10k posts, and it is bouncing along quicker now without killing the server too.
I'm leaving it to run for a bit and I'll report back, but it looks to me like we might just be in business again!
update... just 40 mins for 10k posts now. That's pretty much halved the time it was taking. Result!
Still got 1mil posts to go, but it is going the fastest it has gone so far, so it is all good!
* MHryano is happy
Another update: This is doing so much better now! 400k posts imported in under 12 hours. I was lucky to get 40k done in the same time before, so it is averaging at 10 times quicker.
Got just over 600k posts left to go, so at this rate, I should be able to get things finished up and my site re-opened within the next 24-36 hours
I'm holding back a bit, because I don't want to jinx anything, bur Ryan G has been a fantastic help and has had an incredible amount of patience with dealing with all the hassle that I've had. Hopefully the next import will go a lot smoother as the issues that I'm going to hit have already been identified and addressed. The next import will give beta5 of the importer a good test run too
@ Ryan - Do you want me to do a big board test run of beta 5 on vb3.8? If you do, I can do an upgrade of one of my dev boards which is a copy of one of the 2 big live boards and give it a whirl on there.
(2009-05-31, 07:54 AM)MHryano Wrote: [ -> ]@ Ryan - Do you want me to do a big board test run of beta 5 on vb3.8? If you do, I can do an upgrade of one of my dev boards which is a copy of one of the 2 big live boards and give it a whirl on there.
Sure thing - That would be great
No probs
I'm getting the tables converted back to MyISAM in advance as I know they take a while and I'm down to just 500k posts left to import on the first one now.
Actually, it stopped working and just gave a blank page, so not sure what happened there but a refresh kicked it back to life. I think we can let it off though as it had done just short of 500k posts without any issues in about 13.5 hours.
The next one to be done will be the vB 3.8, which is around 5mil posts, followed by the actual live version of the same site which is rapidly approaching 6mil. Then the last one will be my other big board which is around 5.2mil posts. The only thing I haven't got is any attachments, but I can rectify that by uploading a few to the vb3.8 and to one of the live sites.
(2009-05-31, 12:03 PM)MHryano Wrote: [ -> ]Actually, it stopped working and just gave a blank page, so not sure what happened there but a refresh kicked it back to life. I think we can let it off though as it had done just short of 500k posts without any issues in about 13.5 hours.
Usually it's just a quirk in apache - If it refreshed to a blank page then yeh, just refresh and you should be a-okay.
Ok, a problem came up again and I couldn't get it to do the last 250k or so posts. I've aborted and done all the cleanup etc thats involved, but what i need to do now is to find a way of letting the merge script know that that import has finished then I can prune the source database (I know where it stopped) and continue as a fresh import into my MyBB database.
I can't get back to the main merge script screen as it thinks it should be importing posts still.
Open to suggestions.
Might not be a good idea to have aborted that. I'm extremely busy until Wednesday, but if I find some time, I'll try and look into it before then
Cheers Ryan,
I didn't have much choice in aborting it unfortunately. Left it running for 48 hours and it hadn't progressed and when I looked into the only way around it was to get a dedicated box just for MySQL. I'm going to be a bit more proactive with the next imports and limit it to a pruned copy of the database with no more than 2mil posts in it as it is after the 2.5mil mark that it starts to struggle and at the 2.9mil mark it grinds to a halt due to hardware limits.
I'm gonna go through the code tonight as well and see if I can find a way to trick it into thinking that it has finished so that it will go into the cleanup stage so that I can do another import with the remaining data only.
Some things that I have noticed though, and that I'm gonna need to write up as plugins/code hacks to do/fix are:
- Mass move threads
- Inline moderation for moderated threads only
- Mass ban users (I need a one time run script for this to reban my banned users)
- Rebuilding counters requires manual clicking of a button to continue. With a small board this isn't an issue, but with a big one, it means that you can't set it running overnight and leave it to finish (I'm used to rebuilding counters taking anything from 2 hours to 12 hours depending on the counter being rebuilt)
I'm sure there are other things too, but I've not found them yet, so those are things that I will come across as I go to use them.
Other than that, everything seems ok. Having a few issues with the updating of users post counts, but nothing that is too major and it can be sorted out easily enough once I've done the second import.
[Edit]
Just to show how close it actually got before it died, heres the stats as displayed on the bottom of my MyBB forum
Our members have made a total of 3,004,402 posts in 351,729 threads.
We currently have 44,086 members registered.
The stats from my vBulletin (from about 7-10 days before starting the import at which stage I closed down the board) are in the first post and the merge system declares There are 219,372 posts left to import so it did get nearly all the way there.