Updated: Multiple Databases

Version 0.6.0 of the Multiple DB package is now available. This new version includes the following changes:

1) wp-db.php synced to WordPress MU 1.3 wp-db.php
2) New script to help move blogs to the new databases
3) Updated documentation and example files

This release was basically a compatibility release. There will be another release soon that will include a few new features.

Please let us know if there are any problems and we'll get them fixed ASAP.

Thanks,
Andrew

  • Andrew
      Does the actual number need to be passed over or can you just tell mysql to use auto_increment?

    Just tell MySQL to use auto_increment and you're good to go.

      My main install is about that old. I'll be rolling multi-dbs in on US Thanksgiving.

      Unless you need it sooner, in which case I can do it on the local install.

    Nah, i've got plenty of test sites i'm just busy at the moment. I'll do a bit of testing later this week.

    Btw, is there a feature that anyone would really like to see added? Luke's idea about multiple VIP databases is already on my list.

    Thanks,
    Andrew

  • Luke

    If you want complete honesty, I'd like to see a shortened configuration. At least as an option.

    I mean even with 256 db's, that's still more configuration than necessary, isn't it?

    Is someone really going to have 256 users and passwords?

    Maybe they do, maybe not.

    Here's my idea, but remember as an option.

    Have 16 username/password combos; 0-9, and a-f.
    Then, when the a5 db is called it grabs the "a" username/password.

    Thinking about it, even if you break it up onto multiple db servers, with 16 different db servers running in 4096 mode, that's still 256 per server, which is 0-9 and a-f.

    The only possible issue to this, would be the db_name. But, let's say all my db's are website_udb-0 through website_udb-f. (udb being short for user database.) The db_name in the settings could be "website_udb-", and the script then tacks on the hash.

    Unless you want to get creative with db names or whatever. But, since it would only be an option it wouldn't kill that either.

    I guess the other possibility would be to break up the configuration and have it load a configuration when it connects. Have all your VIP/Global stuff in the main config file, but then user db's broken up into multiple files, included on the fly.

    Keeping them in the directory right above the site root would keep them from being publicly accessible, plus the script can still grab it. Something like /path/db-config/a/a0.php would work. Since you're not going user to user on tables, it would be OK.

    Just a couple of thoughts I've been kicking around to squeeze every drop of performance possible out of it and MU.

  • drmike

    Never saw Pulp Fiction. Not much of a movie person anymore. Too big of a block of time required really plus no real spendable cash.

    I don't even remember the last movie I watched. I listened to the Simpson's movie a few weeks ago here in the library while coding. That and the last Harry Potter.

    +1 for the smaller file but it's not that bad of an issue, at least not at 256. Even with your older, original code.

    Couldn't you do the username/password combos with the current code? You would just have to manually hardcode the file.

  • Luke

    Actually, I already have something similar set up, with 16 user/pass/host options for a 256 install. I was just throwing it out if someone else found the concept useful.

    Of course 256 doesn't include your global, home, vip, and custom db's either. Still, why do the work in ~1100-1200 lines when you can do it 100 or less? For me, it's worked out well.

    If at some point I need to move out to 4096 down the road, I'll definitely segment the user database stuff into multiple files. Probably split the hash up, and have 16 directories with 256 files each or something. I dunno, I'm far from that yet.

    I guess the whole way I see it is this:

    If it's one file that's 2k lines, no biggie. But it's when you go, "Oh, it's only 2k lines" multiple times over multiple times (plugins, mu-plugins, includes, etc) where it adds up. Especially with mu-plugins, which is why any time I do an mu-plugin it's set to only include the file(s) it needs, when its needed. If the condition for inclusion isn't met, that's a 4 or 5 line check vice loading the whole thing. You might be surprised, but it adds up quickly.

    I'm revamping a client site at the moment, and their site was horrid. When I started, the main page was running 979 db queries on average with load times in the 10-15 second range. Tons of stuff in mu-plugins, and other stuff as well. Granted, the person they had originally working with them wasn't quite tuned in to MU and its global scale.

  • Luke

    Testing Report: move_blogs.php

    Strange: On a 256 scale, it tried to connect to db_name_6, vice 06. For the time being, I just added a hard coded $newdbsize after the if/else sequence which worked.

    Noticed: It doesn't move global tables, which may/may not be an issue for some if they aren't going to use a new db name for their global stuff.

    Link error: To start the copy process click here: links to moveblogs.php, instead of move_blogs.php

    Link error: Once completed, refresh this page by clicking here: also links to moveblogs.php as above.

  • Luke

    Note:

    To the "strange" report above:

    if ($db_scaling = '16'){
    
    $newdbsize = '1';
    
    } else if ($db_scaling = '256'){
    
    $newdbsize = '2';
    
    } else if ($db_scaling = '4096'){
    
    $newdbsize = '3';
    
    }

    Change to:

    if ($db_scaling == '16'){
    
    $newdbsize = '1';
    
    } else if ($db_scaling == '256'){
    
    $newdbsize = '2';
    
    } else if ($db_scaling == '4096'){
    
    $newdbsize = '3';
    
    }

    It was using a single =, vice a double so it was trying to hit it like it was 16 vice 256.

    Additional:

    mysql_free_result($sql_table);
    mysql_free_result($insert_info);

    At the bottom, should be after the queries run vice at the end of the file to be of any use.

    --- --- ---

    As a positive note: it did move (as a copy) all the tables to the right spots. Including the global tables (which I hacked in) as well as my own specification for blog_id 1 to its own db (hacked in).

    To me, however, if the tables are only being copied it would make sense to support moving the global tables to their own global db. Which if that becomes the case, it would be nice for the linked tools (http://calc.idtstudios.com/db_sql.php) to also support spitting out global table info as well.

  • Andrew
      Strange: On a 256 scale, it tried to connect to db_name_6, vice 06. For the time being, I just added a hard coded $newdbsize after the if/else sequence which worked.

      Noticed: It doesn't move global tables, which may/may not be an issue for some if they aren't going to use a new db name for their global stuff.

      Link error: To start the copy process click here: links to moveblogs.php, instead of move_blogs.php

      Link error: Once completed, refresh this page by clicking here: also links to moveblogs.php as above.

    I'll fix all of these tonight and put out a new release.

    Thanks,
    Andrew

  • Luke

    I feel ya. I do it on occasion too, and then kick myself in the ass later on. Such is code though.

    I will note though, that once it runs, it runs like a champ.

    Put it this way, locally (of course) it took almost 10 minutes for PhpMyAdmin to "rename" the table. Had to increase the execution time up to like 15 minutes just for this particular project so I could work with its database. (Command line, I know, but I'm lazy like that. lol)

    This script took less than 2 minutes.

    ~1800 users / 18k tables

    And yeah, I'm not complaining. It did save a days worth of work. :wink:

  • Luke

    Looking at the script now, it's checking the table name for the blog_id with is_numeric.

    If that condition is not met, then it must be a global table right? :wink:

    If someone goes beyond that, and has a few different custom db's with custom tables and such, moving those could be done by hand. If you want, I can email the file I've modified and you can pick and choose from that.

    // Next topic:

    Subdirectory install, pulls up the global tables and the home blog tables fine, but is crapping out on individual users and returning a 404. Replacing with the original config and wpdb resolves the issue.

    Not completely sure about what's going on with it, but thought I would make note of it.

    Username/pass is good (it's local so they're all the same), as well as the table name and such.
    I'll have to look deeper into it before I say it's a bug, but thought I'd mention it in case someone else has had the same issue.

  • minusme

    Looking at the script now, it's checking the table name for the blog_id with is_numeric.

    If that condition is not met, then it must be a global table right? :wink:

    Exactly. It would talk all of 5 minutes to reverse the script (including code beauty and witty comments) to only copy non-blog tables. As long as you don't have any non-blog tables that start with wp_ and then a #, it would be easy. I guess I figured why move them if they are working where they are.

  • drmike

    Something else that comes to mind since we're doing moving of databases. Seems like there was a rash of charsets getting mucked up in the forums recently. (I just kept out of those.) I know there's ways to convert different charsets into utf8 which is what mu is supposed to run under and it appears that many folks weren't doing it as such. Care to add in a conversion script?

    edit: Reference: http://textsnippets.com/posts/show/84

  • Luke

    Pretty much minusme. I emailed Andrew a copy of what I'd changed (plus the other fixes above).

    Thanks for saving me a couple days of mundane and boring work though. :smiley:

    The script was pretty quick on my "loaded test server", and once I fixed up the bugs and actually ran it, it was flawless.

    All tables copied to the right spot, as it should be.

    @Doc, that would be handy. 99% of the time is seems that "most" db's start out (in terms of MU) as latin_sweedish_1, with fields as latin_sweedish_ci (or whatever it is).

    When I finally upgrade to 1.3, I'll probably export the data, change the fields to the correct format, and import.

    Then again, I don't have any internationalization going on, so it makes it a lot easier to play with.

  • minusme

    The script was pretty quick on my "loaded test server", and once I fixed up the bugs and actually ran it, it was flawless.

    Not to be too obnoxious, but the errors came from changes that were made to the script I originally sent. Not that I never make errors, but I didn't make 'those' errors, LOL :wink:

    If you've already made the changes, perhaps Andrew will incorporate them into my script. Andrew let me know if you need any more tweaks, and I'll be happy to update.

    Care to add in a conversion script?

    I really haven't needed this myself, but I'll take a look. If it's relatively easy, I'll look at adding it in.

    Cheers....

  • Luke

    Yeah, I know. I sent a fixed version over, so it should be updated in the near future. Mostly were just minor typo's, but I also had the check for non-blog tables in there. (Really simple, actually, if it doesn't meet the first criteria, it must be global, so I just added an else to the sequence. With a global table variable at the top in the settings as well.)

    I would say that having a script detect and then change to utf8 would be handy for some folks. Heck, it might be beneficial even if not using other languages.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.