The MU forums have moved to WordPress.org

Multi-database / server functionality (27 posts)

  1. cafespain
    Member
    Posted 13 years ago #

    Hi everyone,
    Thanks for all the advice and recommendations on this post:
    http://mu.wordpress.org/forums/topic.php?id=7998&page&replies=11

    I downloaded and looked at HyperDb (and tracked down all the documentation I could), but I think it is aimed a step beyond where I am (and probably most others are) at the moment with separate Read and Write databases and replication in mind.

    So... I took the Hyperdb class and have re-written the main server/database choosing function and the settings functions to enable the functionality I initially require. The remaining functions in the Hyperdb class are the same as the original that Automattic made available.

    The new class has the following functionality:

    * Multiple database servers - Servers can be grouped as sets of replicated servers, if the class can't connect to one of the servers in the group it will try each of the others in turn until it can connect to one.

    * Multiple databases - can be spread between multiple servers / groups of servers. Again, if class can't connect to a particular database on one server/group of servers then it will attempt to connect on the next server/group of servers that it knows has that database.

    * 16, 256 or 4096 databases - the default is splitting between 16 databases.

    * Special case databases - I have my system set up so that I have a "Global" database in which I keep the main WPMU tables (site, user, blogs, meta, etc) and any tables that I think will be used across all blogs (in my case the IP2Nation geo-databases and some other large tables that I don't want to have copies of everywhere).

    * VIP databases - these are really just like special case databases above, but instead of specifying which individual tables are kept in a certain database, you can assign a blog to a particular database so any new tables created (by plugins for example) are automatically created in the right place.

    The Special case and VIP databases are in addition to the number of standard databases. So you can quite easily have 16 standard databases for your run-of-the-mill blogs, a global database for all your settings and configuration and 1 or more VIP databases for your special customers. Special case and VIP databases can be assigned to separate servers, so if you want a different database server just for your paying customers then you can.

    * Default database - if the class can't decide where to put data then it will fall-back to a default database. I have mine set to the global database, then I can keep track of any problems and move things around as and when required.

    - Basic setup example -

    For an example of a basic setup using the class, I will describe the setup I currently have.

    * 1 database server called 'local' - it is actually my main (and so far only) webserver/db box.
    * 16 standard databases assigned to the 'local' server, (with same name prefix and numbered 1 to 16 - a blog is assigned to a database using an MD5 hash of the blogid).
    * 1 Global database assigned to the 'local' server.
    * Default database setup to be the Global database.
    * 1 VIP database assigned to the 'local' server which holds 2 blogs.
    * All standard WordPress MU tables assigned to the Global database.

    - Intermediate setup example -

    A slightly more advanced setup might include 3 database servers with the database spread amongst them.
    1 server could hold the Global database and the first 4 of our 16 default databases.
    The second server can hold the remaining 12 default databases and the final server will hold the VIP database.
    This can easily be achieved by simply assigning each of the databases to a different server in the configuration file.

    - Advanced setup example -

    One or more groups of servers. Each group will consist of a Master database and a number of replicated slaves. All writes must go to the master, but reads can go to any of the servers.
    This is, hopefully, the next bit of functionality I would like to re-introduce. I stripped out the read and write database code from the original Hyperdb class to enable the splitting of databases between servers. Once I get my head around the original read/write code, I will work on bringing it back in.

    The new class is all of 1 day old (well about 8 hours really), I've got it up and running on my testing server (which will become my live server later this week...eek) and it seems to be functioning well at the moment (well, you wouldn't notice it is there, which is the plan I suppose), though there isn't a great deal of traffic (other than me).

    If anyone would like a copy to test then let me know and I will send you through a copy and a sample settings file (for the Basic setup above). I won't make a download link available yet, until I (and possibly a few others) have hammered it to get rid of any left-over bugs and niggles.

    Any recommendations for functionality would also be welcomed.

    If you've made it this far then thanks for reading.
    Barry

  2. honewatson
    Member
    Posted 13 years ago #

    Thank you for posting Barry.

    Please forward me a copy to test here: hone (att->) apicart (dot->) org

  3. Trent
    Member
    Posted 13 years ago #

    I would love to look at it as well Barry. trent [d0t] adams [at] gmail [d0t] com

    Trent

  4. blogono
    Member
    Posted 13 years ago #

    thanks a lot for posting, would like to test it ... please mail me the files

    office (atttt) blogono.com

  5. cafespain
    Member
    Posted 13 years ago #

    Hi all,
    Email is on it's way

  6. zappoman
    Member
    Posted 13 years ago #

    Barry,

    This sounds impressive... I'd love to play around with it.

    Are you still interested in beta testers? Drop me an email at bradh squiglything konamoxt dot com

  7. selad
    Member
    Posted 13 years ago #

    Would be happy to play with it. Please emaile at:
    elad dot salomons at gmail

  8. cafespain
    Member
    Posted 13 years ago #

    Hi Selad and Zappoman :)
    Email on it's way through to you.
    Let me know if you have any problems / would like more details on setting it up.
    Rgds

  9. cafespain
    Member
    Posted 13 years ago #

    Hi all
    WPMU 1.5 RC1 (and probably WP2.5 as well) has added a few extra functions to the wp-db class. These where, obviously, not present in the Hyperdb class and so where also not present in my modified version.

    I've now added the extra functions to my class and it is working again under 1.5 RC1. I will email all those who have already expressed an interest with a new version. If any one else wants a copy let me know. I have a few more bits of functionality I want to include and will then prepare a release version.

    For those using Hyperdb who want to upgrade the extra functions are (simply cut and paste from the default wp-db class - you will need to make sure any class variables are also transfered across):
    set_prefix
    escape_by_ref
    prepare
    suppress_errors
    check_database_version
    supports_collation
    get_caller

  10. Trent
    Member
    Posted 13 years ago #

    Great stuff!

  11. webmaestro
    Member
    Posted 13 years ago #

    This looks interesting, please forward to me at cleeds (insert the cool circled a thingie) freedom (followed by the the tiny circle thingamajig) and that ever ubiquitous 'com' string. BTW, I'd appreciate both the wpmu-1.3.3 and the wpmu-1.5 versions... ;-)

    Thanks!

  12. techcham
    Member
    Posted 13 years ago #

    Could you send me a copy as well, please? I'm working on a pretty big project and am setting up a mysql cluster for it and would love to test this out.

    revans [at] robertrevans [dot] com

    Thanks!

  13. cafespain
    Member
    Posted 13 years ago #

    Thanks for your interest. I will put together the update zip file in the morning and send it through. I've also added/re-introdcued a bit of extra functionality so I'll post about that as well.

    webmaestro: The 1.5 version is completely backwards compatible with the earlier version. I only added the extra functions and variables, so none of the existing functions that 1.3.3 required and used have been changed.

  14. adoorshallopen
    Member
    Posted 13 years ago #

    I am interested in this also. Could you please send it to me:

    newsletter@adoorshallopen.com

  15. cafespain
    Member
    Posted 13 years ago #

    Ok everyone.
    I have uploaded the initial version (which is compatible with MU 1.5RC1) to a Google Code repository.

    It can be accessed from this URL:
    http://code.google.com/p/clearskysnet-multidb/

    I will be adding (re-introducing) some extra functionality over the next few days, so keep an eye on it.

    I'll also add the setup instructions when the Google code wiki is back online.

    Note: This version is probably only for advanced(ish) users only. Whilst I am using it on a semi-live site it may still contain bugs.

    Addendum: I've created a Google group specifically for the MultiDb code (link is on the link above) for general chit chat and updates. If you are using, or plan to use, the MultiDb code then why not pop over and say hi.

  16. Trent
    Member
    Posted 13 years ago #

    I see there were some additional changes to wp-db.php that you might want to look at for WP 1.5 now :) rev 1280 or so...

    Trent

  17. enseignement
    Member
    Posted 13 years ago #

    1. How is it different from the wpmupremium plugin for multiple databases ?
    2. Can we install this on a production mu installation ? I've already hundreds of blogs, how will this manage those that are alreay in a single database

    Thanks for your help

  18. cafespain
    Member
    Posted 13 years ago #

    Trent, Thanks, the changes are now in the trunk - I will make a download available v.soon.

    enseignement:
    1. I don't know I've never seen the wpmupremium plugin for multiple databases

    2. I use it on a live server, but I started using it from the beginning. I currently don't have an easy method of moving blogs from a single database to multiple but I am working on some steps. The only way so far is to set your existing blogs as vip blogs and set them up as being in a particular database, then any new blogs will move to the new multiple databases but the existing ones will remain where they are until manually (scriptually) moved.

  19. ligio
    Member
    Posted 13 years ago #

    Hi!
    I want to use the HyperDB class for my 20 blogs, but I don't know how to configure this class!

    How can I tell to this class where is the Master and where is the Slave of the mysql replication ?

    There is a tutorial or some API about "how to configure hyperDB" ?

    thanks a lot!

  20. cafespain
    Member
    Posted 13 years ago #

    Hi again
    Just a short notice to let you know I've updated the download package for MultiDb. It's now on version 0.5. It has all of the changes required for MU 1.5.1 and now has the ability to use regular expressions to identify VIP blogs (this is handy if you have a lot of existing users and want to keep your installation running whilst you move database tables around).

    I have this version live on my own site and the StayPress test server and it seems to run ok.

    The next code change will be to (re)introduce Master/slave database functionality and read/write only databases (I have this running on a test machine and it's looking good).

    I'm still working on the quick setup tutorial but will post it on my blog when complete.

    Download link:
    http://code.google.com/p/clearskysnet-multidb/

  21. Trent
    Member
    Posted 13 years ago #

    That is good to know cafespain as I was looking forward to testing out the replication :)

    Trent

  22. binh
    Member
    Posted 13 years ago #

    I don't think this is what I need at the moment, but it's good to keep track of.

  23. enseignement
    Member
    Posted 13 years ago #

    Once you've come with the possibility for an existing platform to use your code, I'll test your code.

    Keep up the good work

  24. Trent
    Member
    Posted 13 years ago #

    Hey Barry I was wondering if you have updated with the latest SVN of hyperDB? They made some pretty large changes and it HyperDB works fine with WPMU 2.6. Just wondering :)

    Trent

  25. cafespain
    Member
    Posted 13 years ago #

    Just finished some changes to bring in MU 2.6 compatibility (it's only in the SVN at the minute, though I'm running it as of today on my installs):
    http://code.google.com/p/clearskysnet-multidb/source/browse/#svn/trunk

    I will be going over the HyperDb changes this week and hopefully add in all the juicy stuff :)

  26. steve.mcnally
    Member
    Posted 12 years ago #

    Just finished some changes to bring in MU 2.6 compatibility ... http://code.google.com/p/clearskysnet-multidb/source/browse/#svn/trunk

    Thanks for this, Barry. I've been working on a naked hyperdb implementation; your additions seem incredibly useful.

    Have you been updating at all for 2.7x? Now that WPMU 2.7.1 is released, it would be interesting to see what, if any, changes would be required to run your code.

    I see no recent updates in your googlecode svn repo.

    enseignement:
    1. I don't know I've never seen the wpmupremium plugin for multiple databases

    The docs I've sen on the wpmupremium plugin for multiple databases looks eerily similar to yours.

    The 16, 256 or 4096 databases splitting; perhaps that from hyperdb, but there docs state the same splitting ...

    Anyway, hope to see updates; I'll be working with your .5 trunk in the interim.

    Best regards -

  27. andrea_r
    Moderator
    Posted 12 years ago #

    "The 16, 256 or 4096 databases splitting; perhaps that from hyperdb, but there docs state the same splitting ..."

    Because it's based on math, not people copying one another's code. :)

About this Topic

  • Started 13 years ago by cafespain
  • Latest reply from andrea_r