We’re moving from one MySQL server to two (one master and one slave). Does EE have any facility for dealing with the fact that ‘write’ queries can only be run on the master server?

Has anyone else dealt with this issue in the past? If so, what was your solution?

ExpressionEngine has problems with master-master replication, especially when the auto-increments are set to an increment other than 1. What happens at that point is that the increments become mis-matched in the ExpressionEngine database.  I don’t know if this problem exists with master slave replication; I know only of the former issues as they’ve caused one of our users grief in the past.

As far as the writing - ExpressionEngine can only write to the server that you have it pointed to; there is no built-in handling for this type of load balancing.

To be honest, this could lead to quite a few unexpected situations.  You might want to do some rigorous testing before going live on this setup.

So, just so I can understand (because I don’t really “get” all this server stuff) - what would a huge site do if they needed multiple database servers due to really large traffic? It sounds like you are saying that people can only run one DB server with EE, no matter what their traffic needs, or am I misunderstanding (which may well be the case!)?

Your best bet might be to read the second edition of the book
“High Performance MySQL”, which is due out in June. ISBN is 0596101716.

In most situations, most database accesses are reads. Only in the rare
case that you need to write to the database do you need to update the master.
Updates to the master are replicated (i.e. trickle down) to read-only slaves.
Only when you must be absolutely sure that you have the latest version of
something do you need to check with (and read from) the master. 
Most of the time you can just read the unchanging (or rarely-changing) stuff
from read-only slaves.
The application would be written accordingly.

drusoicy, it can be done, EngineHosting does it, I believe:

[W]e can custom build hosting solutions including multiple front-end web servers, high performance database and central storage arrays. This solution is very popular with busy news and information sites, corporate web sites in need of high availability, or any project which requires a combination of high performance and high reliability.

You just need to know what you’re doing, and a certain advanced level of server knowledge is certainly required.

Hey, I’m one of Dru’s EE techs.  I’m thinking of two possible solutions:

1) Modify EE’s db.mysql.php class so that it maintains two connections, one for reading only (which will choose one of the servers) and one for writing (if the other server was a slave server).  Then it will only send SELECT queries to the slave server (if it has chosen the slave server) and all other queries to the master server.

The changes in that instance would appear to be fairly minimal, so long as I make sure that the “default” connection is the master server and I process the queries correctly.

The other option is:

2) Use mysql_proxy to handle all the SQL requests and have it sort the requests automatically.  This sounds like an ideal solution minus the fact that mysql_proxy has a bug where malformed queries will crash the proxy server entirely.  So this solution would require someone (i.e. me) fixing the bug in mysql_proxy before putting it into production.

Can anyone see any other pros/cons to either of these solutions?

I would recommend using a host that has load-balanced MySQL database clusters that do not require modifying ExpressionEngine, since we cannot provide support for the latter, and a host worth their salt can do the former and Nevin is planning on stepping in here and correcting me a bit here. :-D

Just to reiterate what Derek said, and to address this:

So, just so I can understand (because I don’t really “get” all this server stuff) - what would a huge site do if they needed multiple database servers due to really large traffic? It sounds like you are saying that people can only run one DB server with EE, no matter what their traffic needs, or am I misunderstanding (which may well be the case!)?

All I wanted to do was relate to you some of the problems that I have seen using replication schemes.  However, yes, ExpressionEngine supports only one database, and expanding into multiple databases for one site would be a customization that is beyond the support we can offer.

I think that what many people might do is use one databse for ExpressionEngine, and then offload some other items. For instance off-loading advertisements(assuming they’re using a database-driven software other than EE) into another database and pulling them into the ExpressionEngine site.  Of course, I don’t know if you’ve already done that on your family of sites, but if you haven’t - that might be one way to do things.

Okay so - out of curiousity, how would one scale ExpressionEngine, if you guys only support one database? Like, if we used an extreme example, and said that ArsTechnica or Engadget were run on EE. These are sites that have traffic that would simply overwhelm and crash a single database server. Would they just not get support?

Or is it the way we are thinking about implementing it that is wrong, and we should be considering something else? Derek mentioned MySQL database clusters, but my understanding is that a cluster is needs 3 machines minimum, but 4 or more preferred.. Something I can’t afford just yet - we are going from one to two - so a cluster is out.

I guess another way to ask it is, when a site reaches capacity on one database server, where do they go from there? What are the options that allow the company to continue getting official EE support? I mean, short of leaving their current host and joining EngineHosting?

Where the heck is Nevin when you need him??

Nevin may still be on the road, and his expertise here will trump anything I can tell you, but I will relate what I can from our conversations.  MySQL clustering just isn’t seen except in rare private environments, where the application is written specifically for the specific MySQL server environment.  And you’d still have issues where the slave is slightly out of sync, displaying different content, and so on.  Master Slave in stable environments is used more for faster fail-over in the case of a server failure, not for load balancing.  mysql_proxy is not recommended for anyone who wants stability.  There are third party out-of-the-box solutions for these types of environments, but for thousands of dollars per core.

It would be at least as valuable a use of your time to identify why you are contemplating load balanced db servers to start with.  There’s another high profile site that I believe gets more traffic than your network of sites, though that’s a bit of a guess as I have not seen very recent traffic activity from either of you, and they are no where near straining their single dual/dual core, 4GB of memory database server, running ExpressionEngine, phpAdsNew, and vBulletin.  Perhaps there are hardware issues you could look at, including the network infrastructure,  or even something on the software side, hiring someone to optimize your templates.

To answer the question directly, though, as to how one would scale ExpressionEngine - it scales fine, better than most PHP/MySQL applications in fact.  But it is that, a PHP/MySQL application, so there are some inherent limitations, and EE is not designed with the bleeding-edge of either technology in mind, but with the stable and proven environments.  I’d much rather help you figure out where you’re losing performance now than in trying to encourage you to hack ExpressionEngine to work in an unusual environment that doesn’t have an expert system administrator at the helm, or in spending thousands of dollars annually for a third party product that would possibly provide the stability you need without having to hire a full time database administrator.

Sorry folks, I have been tied up with some travel and some large projects at the moment.

To start off, no, our database servers at EngineHosting.com are not load-balanced, I have yet to see a stable, cost effective way to load-balance MySQL servers, and even our large budget enterprise clients have not been able to justify either the costs involved or in some cases stability (like mysql-proxy), or custom coding needed to do load balanced solutions.  Even if EE supported direct slave/master setup itself, few other typically used web applications support that configuration out of the box, so the overall “plus” is limited to simply the applications that support it.  The rest of the applications that don’t directly support it would still be doing read and writes to your master (or a 2nd master) database server.

A few things to keep in mind about Master/Slave replication.  Your Slave server is going to need to have the same write performance at the disk i/o level as your Master server or it will start to lag behind in updating from the Master, this can happen too if the slave server is under heavy loads too, this may or may not be acceptable in your specific environment.  If you are having performance issues at the write/update query level (hopefully you have identified fully your need to load-balance MySQL), then you are not gaining anything in long run adding the slave as your Master is still going to lag behind the write/update queries its trying to currently handle.

I am not saying you have not already, but I would make sure your MySQL server config is optimized for the type of traffic you handle, ie: are you really seeing an impact from heavier read traffic, or a lot of lagging write traffic, locked tables and high i/o waits as your disk-subsystem is trying to catch up to the write requests sent to it?  Have you identified any specific queries on your site causing the issues?  These are important steps of course.

With all of that said we have done evaluation of using Continuent’s uni/cluster for MySQL Enterprise clustering product, and they are a MySQL Enterprise Certified Technology product/partner as well.  But the cost for licensing annually, last we checked, will likely be many times what you are paying for your dedicated MySQL servers combined, but it does fill specific clustering needs very well if you have the budget for it.

There really are too many variables to simply say the use of x and y will allow you to do what you need.  Each sites read/write query needs are different, the hardware to throw at the project is different, etc.  But overall our larger traffic clients are running upwards of 4 to 8 dual/quad core web server front-ends to handle web traffic and being served with dual/dual and dual/quad core database servers usually with 4 to 8 disk RAID 10 w/ 15k rpm drives for fast read and write i/o. In some cases with slave servers for fast database server recover, or simply frequent incremental sql backups throughout the day.

As a side note MySQL Cluster does not cluster MyISAM or InnoDB tables, there is a separate engine that allows for the clustering, and from what I have seen there are possible issues with certain features/functions web apps expect in the two common engines that either may not exist or are not fully supported in the same way, so going to MySQL Cluster may not be an answer either.  This of course is one of the selling points to Continuent’s product is that it transparently load-balances MySQL and InnoDB tables without your applications knowing the difference.

Not sure if that helped much, again if you have not done so already I would strongly suggest identifying exactly where your bottleneck is in the use of your single MySQL server.  Look hard at the types of queries its handling, your MySQL configurations, and the server hardware itself too.

Thanks, Nevin, take a bow!

Well still not sure that actually helped much, but it’s been while since I wrote a book in here