ibneko: (Default)
I've been trying to convert a phpBB2 forum to a phpBB3 forum.

One of the biggest problems I keep running into is that posts get lost at a certain point. I'm not entirely sure why, or how.

But the issue I'm going to address here is that upgrading a LARGE forum database (ours is at 3 GB, with around 4 million posts) was really slow, especially at step 16 of 28, filling the phpbb_posts table.

Why? To ensure compatibility with other converters, other databases, different designs, the phpBB developers chose to use a SELECT query with LIMIT x, y. Where x = number of rows at which you want to start querying, and y = number of rows to return.

When x gets really big, the speed at which queries will come back will get really slow, in the tens of seconds range.

So instead, I hacked install_convert.php so that when it starts processing phpbb_posts, it'll disregard the skip_rows parameter (which establishes 'x') and instead set x to 0, while adding to the WHERE part of the query a condition stating that we only want to select posts with post_id greater than the largest post_id we have in our new phpBB3 phpbb_posts table.

Here's a diff.
Read more... )

Mind, I'm still running this right now, so I don't know if it definitely works*, but estimated runtime for my posts table is down to 4 hours and it's holding a steady 330/s row processing rate (as opposed to starting at 330/s and dropping down to less than 100/s row and taking over 14 hours).

*as in, there may be errors down the road?

[edit] As far as I could tell, this worked perfectly.
So if the issue you're experiencing with upgrading phpBB 2.0 to phpBB 3.0 is due to the fact that the upgrade process is too slow at step 17 of 28 (phpbb_posts table), this patch should be safe to use. It will decrease processing time by a significant amount: I think processing step 17 was cut down from 14-16 hours down to a bit over 4 hours.
ibneko: (Default)
http://www.mysql.com/news-and-events/sun-to-acquire-mysql.html

I hope this doesn't change it from being the awesome open source database it is... O.o

---

Also, this is interesting:
http://www.channelregister.co.uk/2008/01/16/mysterious_web_infection_continues/

apparently there's a nasty something spreading... now is a very good time to download NoScript for Firefox and make sure you're using it.
ibneko: (Default)
The two servers I manage run two different versions of MySQL. The newer one acting as the slave of the older one. This is our crappy backup + prep to move to newer server...

the replication error was an odd one: the command apparently ran on the master side without an error, but ran perfectly on the slave side. As a result, the slave side expected... an error. Which I can't exactly replicate, really. And I didn't know how to skip over that transaction. Soo.... Ugh.

Anyhow, just logging what I did to fix it. It's the worst possible way to go about it, I'm sure. XP. Ended up running:
mysqldump -A -F --master-data -f -e --opt -u root --password=******* --result-file=master.sql

on the master side
and then trashing all of the databases on the slave side, doing
mysql> stop slave;
mysql> load data from master;
mysql> source master.sql;
mysql> start slave;

...of course, I tried a lot of other things before that, so uh... it may have been a combination of all the other stuff. :P
ibneko: (Default)
"ENGLISH (n):
A language that lurks in dark alleys, beats up other languages and rifles through their pockets for spare vocabulary."

Spotted in a userpic here: http://syndicated.livejournal.com/sinfestfeed/285922.html?thread=10456034#t10456034. Can't find original source. Am amused.

Am also going to go shower. :: yawns:: sleeping at 4 AM 'cause you wanted to keep talking to someone on the other side of the world, and then getting up at 11 AM, to only poke the internet, and then go back to sleep until 1 PM is probably bad. Mmm, rambling sentences.

I should be productive today.

Yesterday, I set up mysql replication, one-way. 4.1 master on linux, and 5.0 slave on OSX. Took me way too long though - the instructions are lacking. But in rough order:
1) Modify my.cnf files.
2) Restart master server so you have the master table
3) Use mysqldump to dump said database. Or flush and lock the tables and make a copy of the database files themselves (binary copy? with tar?). OR go to the slave table and do a LOAD... something or other (Load from master?). The last one requires the slave to be set up though... so do 4 then then load? Might have to do 4 and 5, then the load?
4) On the slave side, if you didn't specify the master parameters in the my.cnf file, set them now. CHANGE MASTER TO... etc.
5) mysql> START SLAVE; (on the slave side, obviously)

Verification of success:
6) Check the error logs on the slave side for errors. Use SHOW SLAVE STATUS; on the slave side to check connection status. Check the relay.log (looks like relay.[some numbers here] files.) Log files stored in mysql data directory, unless specified by my.cnf. Suggested unreadable by other / everyone.

Expand Cut Tags

No cut tags

Profile

ibneko: (Default)
ibneko

Syndicate

RSS Atom

Most Popular Tags

Style Credit

Page generated Jun. 28th, 2017 08:59 am
Powered by Dreamwidth Studios
November 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 2016