Multiserver Databases

I've got a multi-server setup with load-balancing on the frontend (LVS-DR) and a shared storage server on the backend (NFS).

I've set it up so that the storage server is the DB server for the cluster (this is so that the databases can be stored along with the website files and emails on a RAID array that'll get routinely backed up over the network).

I'm transferring some websites from our old server (which was just a single PC server, set up with Webmin) and these websites use Wordpress.

So I used the APS installer to add Wordpress to the website and then, from within Wordpress, used the "Duplicator" plugin to shift the website files and database from the old machine to the new servers.

This didn't entirely work as the "wp-config.php" had "localhost:3306" as the db host, so it created the DB on the node that just happened to be chosen by the load balancer to handle the request. So, we had the problem that this one node would show the website correctly, but if another node was chosen by the load balancer, it wouldn't find the database tables and showed the Wordpress install page. Not good.

Basically, when I use the APS installer to install Wordpress, it seems to always use "localhost:3306". This isn't right for my setup.

Also, if I manually change that to the IP address of the storage server "192.168.0.200:3306" then I get "error connecting to database". If I go to the database settings, then I can enable "remote access" and add the IP addresses of the web servers in the cluster, then it works.

So I can manually make this work but this procedure is a little awkward and I have to consider that we're going to have resellers - web designers - also using this system and I can't really have them jumping through these hoops every time.

Is there a way to make the APS installer for Wordpress use the DB server's address automatically and then also automatically make the database "remote access" by the web servers in the cluster?

 

I thought it might require a hack.

But that's probably the best long-term solution, as then I can customise it to work exactly as I need it.

Where's the source for the APS installer stuff kept?

 

Thanks. I'll go take a look.

 

BobGeorge, your setup (front end load balancer and backend db cluster) is very interesting to me. would you mind sharing a how to , more details, or anything to elaborate on the setup and how it works with ISPC? I don't recall seeing a howto anywhere here. If it's too much work, how about just a diagram?

 

(I will answer your question later, Turbanator, but I'm currently at work and must fix this database installer problem first.)

Till, I've followed the code in "aps_guicontroller.inc.php" - against what's in my database - and I think I see the problem.

When I create a site, the web server is the storage server. The other web server nodes in the cluster are mirrors of the storage server and then use NFS to access the files on the storage server.

The code, though, gets the web server for that site from the database - which is the storage server - and then checks to see if that server is also a DB server - which the storage server also is - and then presumes "localhost".

But this presumption is subtly wrong, because though the DB server is the storage server, it should be referred to by its IP address and not by "localhost". Because, of course, on other nodes, "localhost" does not equate to the storage server, it equates to whatever host is running the code and that's different for each node.

So instead of (at line 249):

$settings['main_database_host'] = 'localhost';

It should rather find the DB server's IP address and use that. Irrespective of whether the web server is or isn't a DB server. As the IP address is correct, whatever node happens to be trying to connect to the DB server - whereas "localhost" is only correct when it's the DB server itself doing that.

So the change I'm going to make, I think, is just to remove the "web server == db server" check altogether and always use the "else" code every time, which does obtain the IP address. This "else" code remains logically correct, even when the web server and DB server are the same node (albeit that, for situations where it genuinely is "localhost" only, it's unnecessarily turning on remote access when it doesn't need to).

I'll let you know if this change actually works, once I've tried it out.

 

It's not as simple as just removing the check, apparently.

It just quit the installation without an error message, which from the comments in the code suggests that it's failing the next "if" test afterwards.

I mean, for my own purposes, I can just force the settings I need by hard-coding the IP address instead of "localhost". But it'd be nice to fix the issue more generally, so it could also benefit others who might be having a problem with this too.

The code assumes that if the web server is also a DB server then it's "localhost", but this is not necessarily true in a multi-server setup, as there could be other web servers mirroring that one and, for them, it's not "localhost" but the IP address of the DB server. A tiny subtlety of semantics.

 

Will do.

I presume you mean the one at "git.ispconfig.org", yes? As that's the only one I find with a search.

 

Sorry, Turbantor, I've taken forever to get back to you on this.

Really, in terms of ISPConfig, it's just a multiserver setup. The nodes themselves are actually oblivious to the fact that there's a frontend and a backend.

This is easier to understand with the load balancing. As far as the web server is concerned, it just receives a packet on port 80 asking for the website files. The fact that this packet first arrived at the load balancer, which then decided to choose that server from the pool of available web servers and then passed that packet onto that web server is something that the web server doesn't need to know about. It's all perfectly transparent to it.

And the backend is similarly transparent to it too, in that the shared directories are mounted - via NFS - on top of the local equivalents, so that when programs access the file system to read and write files, they aren't actually aware that it's going over an Ethernet link to another machine. What they see is a directory with files in it.

For the backend, the storage server has shared "/var/www" and "/var/vmail" directories, which are mounted, via NFS, onto the equivalent local directories on the nodes. So when Apache looks into "/var/www" for a website file, it's actually going over the network to get it. But, as far as Apache is concerned, it's just looking into the "/var/www" directory and seeing files to serve.

(To improve NFS performance, I've also got "cachefilesd" running on the nodes. When files are pulled over the network, they're cached locally on disk. Then, if the file is requested again and it's still in the cache, it'll come from local storage - cachefilesd just sends a checksum for the file and, if it matches, then it'll server the local file and, if it doesn't, then the file has been changed by another node since it was cached and it'll do the full network roundtrip to get it. Other than the operating systems and applications, nothing else is stored on the local disk, so the rest of it can be used for local caching.)

The other mitigation for network delay is simply raw hardware. The storage server has dual 10 gigabit NICs. It's the main data channel here, so it gets the big bandwidth cable.

(To be continued.)

 

On the load balancing side, I did initially try HAProxy. The thing is that it's only "layer 7" load balancing for HTTP. For any other protocol - which includes HTTPS, by the way, as the encryption prevents HAProxy from reading and rewriting the headers (unless you do all the heavy lifting work of encrypting and decrypting on the load balancer itself - so that it's decrypted before HAProxy sees it - but that's kind of counter to the whole idea of load balancing to share out the processing burden amongst all the nodes) - then it's basically just "layer 4" load balancing. That is, just distributing TCP packets.

So I just decided to go whole hog on the "layer 4" load balancing and went with LVS.

The big advantage of this is that it's a kernel module. If you're running a recent Linux kernel, then it's already there. It's basically an extension to the "netfilter" network stack that Linux already has for filtering and routing packets about the place, but adds some load balancing algorithms to decide on-the-lly where to route a packet.

There are three flavours of LVS.

There's LVS-NAT, which uses NAT - just like your home router does - to rewrite the IP address of the packet and then, passing the return packets back through the load balancer, it can "undo" the NAT rewrites. The problem with LVS-NAT is that it requires that the return packets go back through the load balancer, as the packets need to be de-NAT-ted on their way out.

So, to mitigate that, there's LVS-TUN. This uses an "ipip" tunnel - much like a VPN does - to wrap the incoming packet inside another packet for delivery to a processing server. The server itself can then just reply directly to the client machine, without packets going back through the load balancer.

But then there's LVS-DR. Direct routing. The trick with this one is to drop down a network layer and just rewrite the MAC address on the incoming packets. When a switch or router needs to deliver to an IP address, it sends out an ARP request - "who's 192.168.0.12?" - and the server with that IP address responds with its MAC address. Which is the actual hardware address needed to deliver a packet to a device.

So the trick with LVS-DR is that the incoming packet remains otherwise untouched, save for having the MAC address rewritten to the MAC address of the server chosen to process this packet, and then it's passed on to that server. Otherwise, it's perfectly transparent (and, at the application level, it is transparent because, once delivered, a packet has its MAC address and that whole layer stripped off of it).

The server receiving the packet just sees the incoming packet, just as if it had come straight to it. And it can reply directly to the client.

The trick with LVS-DR, though, is that you need to perform a little trickery to make it work, as there is "the ARP problem". That is, for the servers receiving the packets to think that the packets are addressed to them, they need to respond on the cluster's shared IP address (the virtual IP or VIP, as it's called).

But they must not respond to ARP requests for this IP address, because every single node in the cluster has an interface to this IP address. So if you asked "who's 192.168.0.99?" - where that's the VIP address - then all of them would respond simultaneously and you've got a race condition as to gets there first.

So you need to set up an interface with the VIP address on each node, but then silence any ARP responses for that IP address on everything but the load balancer (which is legitimately where packets ought to be sent for that IP address).

There are many ways to do this, but I went with creating a dummy interface with the VIP address and then, as a "pre-up" in the network configuration, run an "arptables" command to tell it to drop ARP requests to the VIP.

This can be tricky. Until I got it right, I'd get "connection refused" responses. It can be a pain to diagnose the problems and I was forced to use wireshark and follow the packets, until I realised what was going on.

It can be a pain to set up LVS-DR. But I feel it's worth it, because this is kernel load balancing. It's all happening in the kernel, so there are no copies to userspace and back, nor transition from kernel to userspace and back. It's really fast. And the "direct routing" flavour does nothing more than rewrite a MAC address. So, really, you can't get faster. The bare minimum is being done by the kernel's netfilter routines directly.

And, once mastered, it's perfectly transparent, as I was saying.

You can set up LVS directly through the "ipvsadm" command, which you can use to change the kernel's tables for this.

But I wanted high availability, so what I've got is "heartbeat" running between the two "frontend" nodes. Heartbeat sends a, well, "heartbeat" signal from the active load balancer to the backup passive load balancer. Both of them are listening on the VIP for packets, but the passive one just sits there waiting. When the passive node can't hear the active node's "heartbeat" then the active node must have died, so it takes over its duties.

With heartbeat, you can configure "resources" for it to maintain - to keep up and running - and the two resources I've got is the cluster VIP address and a daemon called "ldirectord".

What "ldirectord" does is configure the LVS tables for you. It also performs "health checks". So, for example, with HTTP, it'll simply request a file from the web server and wait for a reply. If it gets a reply, then the web server is up and running. But, if not, then it's gone down and ldirectord will change the "weight" parameter of that node in the LVS table to zero. This means, as it's got a weight of zero, that the load balancing algorithm will no longer select it.

This gracefully takes the node out of the pool, but keeps it in the tables, so that ldirectord will continue to perform "health checks" on that server. If it comes back up, the health check will pass, and the daemon will restore its weight (in my case, all weights are 1, as I'm not favouring any server over any other, as they are literally all the same so there's no reason to do otherwise, but you can set the weights to different things to bias the ratios of the load balancers choices, if you like).

 

FTP was a pain.

It was almost working. I could connect - with TLS - and login and get a directory listing. I could download files just fine.

But when I uploaded, it would reach 100% and then just get stuck there. Eventually, it would time out and try again. Then give up after so many failed attempts.

The thing was, if I looked at the uploaded file, then it was being uploaded correctly. Well, sometimes it was the correct file, but a few kilobytes short of being complete. But I think that's just caching. If the connection had closed, then the file would have been closed and the data in the disk cache flushed to disk.

What was happening - after I traced this at the packet level - was that FTP uses a closed connection to denote "end of file" in transfers. It doesn't send the file size or anything ,it just closes the data connection when it's done. And this "close connection" packet - the FIN packet - was getting killed off at my router.

It was working but my particular hardware didn't like it. So I cheated.

FTP is not load balanced. I've port forwarded FTP traffic around the load balancer to the FTP server. This is not scalable and I will have to come back to it later, if we scale up more, but it works and I wasted nearly a month trying to resolve it, so enough is enough.

The thing is, FTP is a low traffic service anyway. It's just web designers uploading their files now and again. So, for the time being, I can get away with there only being one FTP server.

Also, my thinking here is that the problem was the hardware. The router didn't like it. So, if we do ever scale up big enough to need more FTP servers and need it to be load balanced, we'll be doing well enough financially that I can request a better router.

I hate FTP. We shall speak of it no more. ;P

 

Pardon the length of that response, folks. But summarising a few months' work adequately enough for it to potentially be useful to others just isn't something that can be done in under 140 characters, if you know what I mean.

 

Thank you so much!!

 

Going forward, I'm looking into using MySQL Router (MySQL developers) or MaxScale (MariaDB developers) for better database clustering.

And I was thinking that this could implicitly fix this multiserver database problem in a more graceful way, as these solutions also act as proxies by their nature. So they could be installed to listen at "localhost:3306" - and therefore applications could just connect to "localhost", transparently oblivious of where the DB server actually is - and then the queries can be re-routed onto the actual DB server (or DB servers, if I start scaling it up into a larger MySQL cluster in the future).

I need to look further into it - not sure yet whether MySQL Router or MaxScale would be more appropriate, but as I'm using MariaDB then perhaps MaxScale, by the same developers, would make sense - but I thought I'd mention it here, as this is probably the more graceful way to handle this issue, because it's transparent to applications (they just connect to "localhost", as if the database were local) and it prepares the way for scaling up the cluster to have multiple DB servers and load-balancing and all the rest of it later on.

 

@BobGeorge you could simply use haproxy with tcp forwarding.