The MU forums have moved to WordPress.org

I've groked WP_USE_MULTIPLE_DB (37 posts)

  1. PerS
    Member
    Posted 15 years ago #

    What I can't understand is all this secrecy around large wpmu installs. Is this in the spirit of WordPress and GPL ? :)

    btw, when I've found a way to handle more than 32k direcotries, I'll post a how-to (if anyone has a solution, please tell me), describing how to do multiple db + large file structures in wpmu

  2. drmike
    Member
    Posted 15 years ago #

    What I can't understand is all this secrecy around large wpmu installs.

    More likely it's either no one has sat down to figure out a method of doing so or some of us are getting rather tired of folks coming in here demanding to be waiting on hand and foot, accusing folks here of not bowing down and scraping when they have a question or an issue.

    Thank you for at least saying 'please'. You're already doing better than most folks here.

  3. andrea_r
    Moderator
    Posted 15 years ago #

    And mike also forgot those of us most likely to figure it out haven't said so because they haven't got the time to do it yet, or only just got started, so there's nothing to report.

    Excellent points as always DrMike.

  4. nexia
    Member
    Posted 15 years ago #

    /me spice this thread with a good smile to Mike!

    ... btw, without having the solution for multiple DB, i have the solution in hand for multiple directories-split for linux and raid servers... (ya know, limit of 62000 files in dir...)

    edit: damn, i forgot to write it somewhere...

  5. PerS
    Member
    Posted 15 years ago #

    I'm sorry if I offended you, that was not my intention (that's why I put a smile in there).

    This post is a kind of a reaction to donncha's post where he states I can't work on a competing site i.e. it's not WordPress.com, when asked for how to create large wpmu sites. He is the chief architect for wpmu and not letting us wpmu fans getting the knowledge wpmu advertize (Unlimited users and blogs etc) makes me a bit frustrated/ stressed :)

    btw, I mailed donncha the url to this tread since I mention him, and so he can follow the discussion and maybe comment.

  6. PerS
    Member
    Posted 15 years ago #

    nexia, you can have mine. Just change the UPLOADS definition in wp-settings.php to:

    define( "UPLOADS", "wp-content/blogs.dir" . strrev(chunk_split (strrev(md5($wpdb->blogid)), 2,'/')) . "/files/" );

  7. selad
    Member
    Posted 15 years ago #

    PerS,
    Can you please explain what the above code does?

    Thanks :)

  8. PerS
    Member
    Posted 15 years ago #

    selad, it creates a path looking like this:

    wp-content/blogs.dir/c4/ca/42/38/a0/b9/23/82/0d/cc/50/9a/6f/75/84/9b/files/

    since the md5() uses the blog id as 'seed' if will be different for each user.

  9. selad
    Member
    Posted 15 years ago #

    PerS thanks. Can you say what this is for?

  10. donncha
    Key Master
    Posted 15 years ago #

    Selad - The ext2/3 filesystems on Linux only allow 32,000 files or directories in a directory.
    When your wpmu site gets bigger than that number of blogs you have some serious problems because you can't create any more upload directories for those blogs!

    Do the same with wp-db and you can send requests to different databases. Running blog_id through md5() is the big secret :)

  11. nexia
    Member
    Posted 15 years ago #

    we can have a minimum change of path instead of the md5 thing... the basic limit of 32k files per directory also means a limit in the number of files per HD, what this md5 thing does not cover...

    splitting the userid is a good thing, and it can be done with less function calls than the md5... nobody have the same userid, so having someone with userid = 46654 will be stored in the /4/6/6/5/4/files/ instead of that lingering md5 path...

    also, having a function to generate and manage the userid path would permit to have a different HD for any userid you wish, like everybody from 999 to 1999 would be stored in that specific HD, etc... can also be specific to permissions, as you can give a single HD free for a single userid... actually nothing is done on that part... (ok, i have myself!)

  12. drmike
    Member
    Posted 15 years ago #

    Just for the record, at least PerS said please and offered to work through the issue his or herself. That's better than a lot of folks do around here.

    Even if Linux allows you only 32k files, would that mean you would top out at less than 10k blogs? I know when a new MySQL table is created, it actually creates 3 files in the subdirectory holding the database. That's where I would see the issue which concerns me.

  13. helmi
    Member
    Posted 15 years ago #

    Thats also the database issue i talked about some months ago, mike ;) The tableset on a per-user-base is a good thing but it really rocks if you can load balance between several databases that get created dynamically with a maximum of let's say 500 or 1000 blogs per database. That way outsourcing databases to different servers would be relatively easy.

  14. pumpkinslayer
    Member
    Posted 15 years ago #

    It seems almost any way of splitting it up would do the trick.

    I would add to nexia's and try standardize the naming scheme.

    Example:
    Set a limit like a depth of 8 (99,999,999 blogs) so blog 1 would be 0/0/0/0/0/0/0/1 and blog 456789 would be 0/0/4/5/6/7/8/9 I think it looks a little prettier and gives an intuitive way to find a particular blog's files.

    My original thought was to split into thousands, using something like floor($blogid, 1000)/$blogid, but the many directory way gives much more flexibility in my honest opinion.

  15. drmike
    Member
    Posted 15 years ago #

    You have to remember those of us here who have limits on how many databases (not tables) that they can have though. While my hosting and synhosting which I use for my own sites have no limit on the number of databases that one can have, there are hosts out there who charge for each and every database used.

    Of course they're not very good hosts but folks still use them.

    I'm running towards having a calulation where blogs through 1000, use 1 database. 1001 to 2000, use another. etc. I guess something out of (heck, I can't think of the file name) the file that actually makes the connection whould be where to do this.

  16. PerS
    Member
    Posted 15 years ago #

    Ref multiple databases, I might do it like this:

    <?php
    define("NUMDB",5);
    $dbname = sprintf("wpmudb-%s",( $wpdb->blogid % NUMDB) + 1); // will return wpmudb-1 to wpmudb-5
    define('DB_NAME', $dbname);
    define('DB_HOST', $dbname . '.domain.com');
    ?>

    It's easier to maintain than using the logic behind WP_USE_MULTIPLE_DB.

  17. PerS
    Member
    Posted 15 years ago #

    pumpkinslayer, I agree. Something like this maybe:

    $offsetLeft = intval($intPadLength - strlen(trim($wpdb->blogid)));
    $strPadded = str_repeat('0', $offsetLeft) . $wpdb->blogid;
    define( "UPLOADS2", "wp-content/blogs.dir" . strrev(chunk_split (strrev($strPadded), 2,'/')) . "/" );

    It will give a path like this:
    wp-content/blogs.dir/00/00/00/01/

  18. helmi
    Member
    Posted 15 years ago #

    drmike: but then you have to remember that there are still hosts without any databases *wooh* :)

    I think people trying to run a blog hosting service should be aware that there are resources to spend in souch a system and the technical base of this system couldn't be the spirit to enable everyone running his/her own bloghosting service. If it would be like this we would have more bloghosters than blogs in let's say three years? :)

    I think a good, dynamic multiple database logic is what wpmu needs to be performant and flexible enough to run economically for kind of bigger hosters.

    Maybe we can find a solution to make it switchable and leave the slowgo version as standard? Think this should be possible.

  19. quenting
    Member
    Posted 15 years ago #

    I posted here : http://mu.wordpress.org/forums/topic.php?id=2163 a request for feedback on a possible scaling architecture for MU. I didn't get any answer which doesn't help pushing me taking the time to write down tips on whatever I found out here. Overcoming the directory limit is a first step, but it won't get you past one or two servers. If you're thinking big, something more complex has to be put into place.
    PerS, about your multiDB code, how do you handle the main generic tables they need to be sync'ed between your multiple db instances, don't they ?

  20. PerS
    Member
    Posted 15 years ago #

    quenting, I'm not sure yet. I might use the logic in wp-db->db_connect(), but modifying it for my use.

    wp-db->db_connect() uses mt_rand to select which master db to write to, I'd like to have more controll using the blog id, but I haven't found a good method yet. The one I wrote above using modulus to distribute between servers will fail if I increase NUMDB (eg: from 4 to 5, blog id 8 will no longer point to wpmudb4, instead it will point to wpmudb9). If you have an idea on how I can distribute the blog ids, please tell me.

  21. quenting
    Member
    Posted 15 years ago #

    actually i don't. not sure what you can do except do a lot of databases right now so that you don't have to change later (i think that's the option taken by wp.com and their 4096 DBs).
    I'm on another path myself, routing based on blog name.

  22. marke1
    Member
    Posted 15 years ago #

    The WP_USE_MULTIPLE_DB switch controls whether MU uses numerous database SERVERS -- not databases. Of course each server does have its own databases, but the point here is that the switch doesn't divide users across many database. That is, if I understand correctly. I could be wrong....

    What it appears to be used for is load balancing across multiple database servers that use replication. So in practice for example, one db server is used for reading data, and other db servers are used for writing data (blog posts, comments, etc).

    Then when new info is written to a server that info is replicated to all the other servers. This is a function of MySQL Server itself.

    So unless your db server is under heavy load or will be at some point then there's no need to worry about that switch.

    What you DO need to worry about is too many files in a dir, etc. So the fix posted in this thread will help prevent that. Incidentally it's the same way Mediawiki software (used by Wikipedia) handles that exact same limitation - by splitting files into a large number of directories.

    Anyway, someone who isn't familiar with MySQL Server replication wouldn't figure out how to use the WB_USE_MULTIPLE_DB switch... As it turns out I am familiar with that, so I took a look to see how MU uses the switch, and there's the answer.

    As for worrying about your db getting too big, don't worry about it too much. MySQL is fast even with huge tables as long as your server is fast, has plenty of RAM, and MySQL is optimized properly. Worry instead about backing up your db regularly somehow in case of disk failure or corruption.

    That's my advice.

    If anyone out there wants to implement that switch then do a search on $db_list along with the switch name and you'll come across some wp-config.php settings that need to be put in place...

  23. lunabyte
    Member
    Posted 15 years ago #

    This thread is really outdated.

    It's already been done, and available through a nice plugin.

  24. mysorehead
    Member
    Posted 15 years ago #

    The HyperDB mailing list is really really quiet but there was a session on it at wordcamp so hopefully more development on it will be forth coming.

    Not that I'm interested in multiple databases, just commenting.

  25. drmiketemp
    Member
    Posted 15 years ago #

    Anyway, someone who isn't familiar with MySQL Server replication wouldn't figure out how to use the WB_USE_MULTIPLE_DB switc

    I'll own up to that. :)

  26. marke1
    Member
    Posted 15 years ago #

    > This thread is really outdated.

    > It's already been done, and available through
    > a nice plugin.

    I think to be more clear you could have stated that "it's already been done and for sale as a plugin"

  27. drmiketemp
    Member
    Posted 15 years ago #

    *shrug* Considering what else is included in that package, it may be worth it to you.

  28. lunabyte
    Member
    Posted 15 years ago #

    I think to be more clear you could have stated that "it's already been done and for sale as a plugin"

    So has a Ferrari. So what's the point?

    I don't find any reason to have stated it different. So, if you don't mind, how about not trying to correct something I say to fit your liking?

    Oh, I'm sorry. Did I offend you by linking to something that isn't handed out on a spoon?

    :O

    You mean everything under the sun in the world isn't always free?

    Oh gosh, the horror of it all.

  29. mysorehead
    Member
    Posted 15 years ago #

    hyperDB is free, has the backing of the wordpress guys and is designed for this purpose. The functions are there in the class they just need to be called somewhere.

    I haven't tried as I only have small mus but it seems to that it just rotates through the partitions in order so adding a partition to an already existing stall would require some effort. Having said that you could try modifying the get_ds_part_from_table function to meet your individual circumstances.

    If you need this feature then get your hands dirty and give it a go.

    Richard

  30. lunabyte
    Member
    Posted 15 years ago #

    HyperDB doesn't work for splitting up an MU DB onto multiple servers, it simply let you use more than 1 server which in turn each hold the entire DB. It is "designed" towards WP.

    It would have to be modified to support MU, no different than the current class.

    It's nice, for what it does, but it isn't what was initially thought in terms of it being an MU solution.

About this Topic