The MU forums have moved to WordPress.org

Multiple Databases and blogs dir? (35 posts)

  1. andrewbillits
    Member
    Posted 15 years ago #

    This really is a question for donncha but I feel that everyone will benefit from the answer.

    So donncha, a few people are starting to get near the 32K blog problem limit. Is there going to be anything "official" added into the nightlies to address these problems?

    The reason I ask is that I don't want to start working on a fix and find later down the road that it's going to cause compatibility issues.

    At the moment, i'm thinking a scaled down version of what wp.com runs on. The reason I say scaled down is that I seriously doubt anyone here is ever going to have 250K users and if they do, then they are going to need to have a few techs on payroll anyway. Maybe have the code in the nightlies support spreading users across 8(or some other small number) databases instead of the large number wp.com uses. Even if there were 128K users on a wpmu install, that would be 16K users per DB instead of 128.

  2. quenting
    Member
    Posted 15 years ago #

    +1 !!
    I'd definitely be interested in any features helping the multi-server, multi-DB environment. Heck, I'd be willing to pay for it.
    250k users really isn't that far.

  3. donncha
    Key Master
    Posted 15 years ago #

    It is a problem for larger blog sites, but it's not going to be in version 1. Let's aim to have it in there after that.
    I want to get that out this week but I'm struggling to fix the Javascript global categories dropdown in the Write page. wp.com uses practically the same code and it works, but the JS can't see the value of newcat.
    If there are any Javascript heads out there, feel free to bang on that! It'll get version 1 out the door that much faster!

  4. andrewbillits
    Member
    Posted 15 years ago #

    Sounds good. Unfortunatly JS is not my strong point.

  5. drmike
    Member
    Posted 15 years ago #

    Ditto. Back in my day, it was Turbo Pascal.

  6. andrea_r
    Moderator
    Posted 15 years ago #

    Donncha, did you get the js fixed?

  7. donncha
    Key Master
    Posted 15 years ago #

    Yup! Check the trac timeline for the check-in

  8. drmike
    Member
    Posted 15 years ago #

    Thinking about this again.

    Can anyone figure out what this line is doing?

  9. lunabyte
    Member
    Posted 15 years ago #

    At a quick glance, it's partial implementation of a multiple db setup.

    Similar in comparison to line 173, yet it is using certain user credentials to determine where to connect to. Mainly for the "global" db where all the global tables are.

    Note that currently, the first check (169) will not be true, and that the second or final parameters will be the ones used.

    The second and final would separate into a write and read DB, but as it stands right now they are one in the same (out of the box).

    So, like Donncha mentioned it is kinda there, but not completely as there is not currently a built-in method for determining a global db, or specifying alternate db's to connect to for reads and writes.

  10. drmike
    Member
    Posted 15 years ago #

    that's something that's also throwing me off. I don't understand why there would be a resiriction on write and read. As I understand it, it should be one global mysql user and they should have access to all of the databases.

    that and why the randoms in there? donncha says that they're md5'ing the blog_id. That should be determining which db to use, not a random.

  11. lunabyte
    Member
    Posted 15 years ago #

    My best guess at the moment would be that it's been altered to work with the current MU codebase, but that's a guess at the moment.

    As for read/write being separated, reads eat much less than a write. That being said, reads occur much more frequently than writes.

    So, I would guess again that by separating those actions it spreads the load quite a bit vice having it occur in the same db. Then, replicate the writes to the reads, and all is well.

  12. quenting
    Member
    Posted 15 years ago #

    this looks a bit like what we use on unblog.fr. It's to determine if the query must be run against local or global tables, because in a multi db setup you're gonna want them separated.
    However this check isn't good enough from what i can tell.
    Just checking if "wp_blogs" is in the query might trigger false positives if this string appears in the where clause, which happens sometimes. The check needs to be performed against the first half of the query (up to WHERE if there's one). This means a few more checks and substrs to make this code actually functionnal I would say.

  13. drmike
    Member
    Posted 15 years ago #

    And?

  14. andrewbillits
    Member
    Posted 15 years ago #

    quenting is right, you need to add a bit of code to actually make it work correctly.

  15. drmike
    Member
    Posted 15 years ago #

    And? ;)

  16. andrewbillits
    Member
    Posted 15 years ago #

    Would this be for personal use doc? I have a working multi-db wpdb.php but I don't have time to offer support to everyone. So, if it's for your site, then you might wanna send me an email.

    andrew A.T idtstudios.com

  17. lunabyte
    Member
    Posted 15 years ago #

    I feel so abused. lol

  18. andrewbillits
    Member
    Posted 15 years ago #

    How so?

  19. lunabyte
    Member
    Posted 15 years ago #

    I feel so abused. lol

  20. andrewbillits
    Member
    Posted 15 years ago #

    Yeah, that definetely answered the question. ;)

  21. lunabyte
    Member
    Posted 15 years ago #

    Damn it, looks like after all this time I got bit by the timeout/double post bug. :(

    So to answer it... Because I said so?

  22. drmike
    Member
    Posted 15 years ago #

    Well my own clients and my own install.

    I do note that you've mentioned that you run a specialized version of it that's not GPL'ed. I'll respect that.

    I'll cut you in for 50% of the take. ;)

    Actually what I'm getting stuck with is how to determine weither to use the global database with the list of blogs or to use the databases with the individual blogs.

    Rant: You know what gets me though? That "Does it scale?" in the FAQ. Sure it does if you whack at it....

  23. quenting
    Member
    Posted 15 years ago #

    well if that may help, here are the checks i make on sql queries to determine which db to use. It's a bit tricky but i'm sure you can figure out how to adapt it to your own wpdb.

    if(defined( "MY_USE_MULTIPLE_DBS" ) && CONSTANT( "MY_USE_MULTIPLE_DBS" ) == true) {
    // determine whether we must access local or global DB
    $myquerymatch = $query; // the part of the query we're gonna look into
    if(preg_match("/^\\s*(select|delete) /i",$query)) { // for select, delete we only look till where clause
    //echo "inselect<br>\n";
    $c=false;
    if(($c=strpos($query,'WHERE'))===false) { // stripos only in php 5
    $c=strpos($query,'where');
    }
    if($c!==false) {
    $myquerymatch = substr($query,0,$c);
    }
    } else if(preg_match("/^\\s*insert /i",$query)) { // for insert, we look till VALUES clause
    $c=false;
    if(($c=strpos($query,'VALUES'))===false) {
    $c=strpos($query,'values');
    }
    if($c!==false) {
    $myquerymatch = substr($query,0,$c);
    }
    } else if(preg_match("/^\\s*(update|replace) /i",$query)) { // for update, replace we look till SET clause
    $c=false;
    if(($c=strpos($query,'SET'))===false) {
    $c=strpos($query,'set');
    }
    if($c!==false) {
    $myquerymatch = substr($query,0,$c);
    }
    } // for alter table, create, truncate we get the whole query
    // now, is there any of our main tables inthere ?
    $myquerymatch .=' ';
    if(preg_match("/(".$this->my_global_tables.") /i",$myquerymatch)) {
    //echo " - global"."<br>\n";
    $dbh = $this->my_global_db_lnk;
    } else {
    //echo " - local"."<br>\n";
    $dbh = $this->my_local_db_lnk;
    }
    } else {
    etc.

  24. lunabyte
    Member
    Posted 15 years ago #

    "Technically" it can scale out of the box. Not efficiently, but it can in the literal sense of the definition.

    - You can put the DB on its own box.
    - You can set up a MYSQL cluster, and connect to it and let the cluster do the work.

    Technically, that is scaling, just not much logic to how it is scaled.

    We would like to see users broken up into specific DB's, to avoid the 32k file limit and other things as well. True, that makes sense.

    Even if you have 4 DB's on a box, or whatever. It's all fun and games.

    So I wouldn't disagree that it can be scaled out of the box, but on the flip side it also isn't exactly a logical path for scaling.

    All about marketing and buzz words, really.

  25. drmike
    Member
    Posted 15 years ago #

    Much like jumpline and they're being "flexable." ;)

  26. andrea_r
    Moderator
    Posted 15 years ago #

    Well, by that smae logic WPMU does anything you want it to do - just code it in. :D

    Woudl love for it to set up another db automagically after X number of blogs though.

  27. lunabyte
    Member
    Posted 15 years ago #

    Um, yeah. Something like that Doc.

    Andrea, that would depend on how mysql is set up, really. If the db user had the perms to do it, it could do that.

    But, then you're going based by # of users per db, and with users coming and going, a deal like that would wreck it.

  28. andrea_r
    Moderator
    Posted 15 years ago #

    Darn logic holes... :D

  29. drmike
    Member
    Posted 15 years ago #

    Would still have to do the gloabl db as well.

  30. Konstan
    Member
    Posted 15 years ago #

    A bit off topic here but I have a question for quenting:

    How did you accomplish to count the posts/commentaries to show on your main page(s)?

About this Topic

  • Started 15 years ago by andrewbillits
  • Latest reply from lunabyte