Can I Update to More DBs later

I have question. If I install 256 DBs, the project page says that I can host up to 100,000 blogs.

1) Is that a fixed figure, meaning I cannot host, say 110,000 blogs?

2) Is it possible to increase from 256 to 4096 DBs at later stage when the need arises and how difficult would it be to do that?

3) In the supporter plugin, it is also mentioned that if the DBs are not created correctly, I am to run the update sql file. I cannot find the file in either of the plugins (multi DB and supporter).

  • Shawn

    1) No. You can host as many as you like, but you will not see the same performance improvements after *approximately* these limits.

    2) While it is *possible*, it's going to be a hassle. Splitting one database to many is much easier than juggling many to many.

    3) Look under the "docs" folder for "fresh_sql.txt". With supporter + multi-db, it's important that you add the following lines to your db-config file:

    add_global_table('wp_supporters');
    add_global_table('wp_supporter_signup_stats');
    add_global_table('wp_supporter_daily_stats');
    add_global_table('wp_supporter_ads');
  • becky

    Shawn,

    Thanks for your explanation. I found the fresh sql file under docs now

    The thing is I have installed the supporter plugin before installing multi-db. I checked the global table and it seem to have all the supporter tables that was already in my original db prior to installing the multi db. For safe measure, I have also added the few lines in the db config file.

    Ok, as long as it is possible to host more than 100k without having to necessarily split then i am ok with 256. Dreading to add user to 4096 DBs manually.

  • Shawn

    The migration from 256 to 4096 *can* be automated. But one of the expectations of using so many databases in the first place is to split the load onto multiple servers. Juggling all the databases between several servers is far more difficult than splitting them all up the first time. It would be so much easier to do this if the site is completely offline, but it could be done gradually, blog by blog, by modifying the db-config file and effectively double-parsing for both 256 and 4096 hashes and then manually porting the new hash and settings if the original blog id is within a certain list. Yes, this would increase the time to convert, but it could be done with almost no downtime, and could still be almost entirely automated.

    The "add_global_table" lines are critical, as they prevent the plugin from trying to recreate the settings for each blog, instead of using the same tables that are shared across all blogs and users.

    Ranting now...I wonder why there isn't a metadata assignment to indicate global tables for each plugin, or, even better, simply an array_merge() to add the active plugins tables (if desired) to the global tables array ($wpdb->global_tables or $wpdb->ms_global_tables)? This would be the easiest thing to do, and would future-proof the code as long as this list was preserved (and I can't imagine why it would be abandoned now that MS is integrated). The values could be directly modified thru the multi-db extension class, too. Hm. Yes, these variables are tagged as private, but they really shouldn't be, IMO (or there ought to be public accessors for them).

  • twicealive

    Hi Becky,

    Another method of migrating up to 4096 db's is to first reconstitute the 256 db's back into one place.

    You could actually merge each individual backup.sql file into a single file, then upload that file into a whole new database. That would take right back to where you are now and you could begin the process to migrate from 1 db to 4096 db's.

    By this method you wouldn't need to damage your existing configuration, you could just leave it alone while recreating a whole new system with the same data. After everything checks out, then you could wipe out the original system when you wanted to.

    I'm sure that can be a lot of work, but even so, it's worth looking in to when the time comes.

    MJ

  • Shawn

    depends on how they were backed up.

    if you used phpMyAdmin then you can opt to not include the db name in the backup, which results in a universal SQL statement. import it directly into your single database and you're done.

    if you used the mysql into file method, you may need to do a little SQL juggling on your way back in. I regularly have to juggle databases between servers and I use a script that has a couple of commands like this:

    cat *.sql | sed -e 's/CREATE\sDATABASE/\#CREATE\sDATABASE/' | mysql -hlocalhost -umyusername -pmypassword mydatabasename

    You could use this method to effectively remove the database creation and assignment scripts so the SQL is imported only into the 'mydatabasename' database. Shell access is required.

  • twicealive

    Hi Becky,

    If you want to manually rollback to one db, I think you should be able to upload each of the 256 db's individually into a new single db, like this:

    1.) Shutdown the multisite.
    2.) You should backup your physical files too. At the very least, backup "wp-content".
    3.) Export each of your 256 databases in SQL format.
    4.) Once you have the SQL file for each database, create a new blank database and import the SQL files one at a time.

    The result is a Single Multisite Database.

    5.) Finally set the options in wp-config.php to point to the new single database and delete the db.php file from your wp-content directory.

    You should now be running on a single database.

    There is a method to automatically migrate your 256 db's into 4096 db's, but personally I like the idea of leaving my existing site alone as a failsafe precaution. I would certainly shutdown the network while doing the upgrade. Or, if it is imperative to keep your network online, I guess it wouldn't hurt for people to access the original site merely for browsing, but then you would need a way to absolutely lock-down the original site so no one could login or create new websites.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.