Sites across Multiple servers

[eluser]opel[/eluser]
I am looking for advice on a Codeigniter development we are about to start. The site is a social site and requires maximum uptime so the proposed solution is to host the site (CI) on a separate server from the MYSQL database which would in turn be mirrored and backed up else where.

I was wondering if there will be any issues with hosting the database on a separate server from the CI app and will it affect anything with the CI cache library?

Any other advice would be appreciated.

Thanks

[eluser]Otaku Coder[/eluser]
My guess is that you'd just put in an IP address for the server URL in the config.php, so you'd access the DB via TCP rather than sockets.

[eluser]opel[/eluser]
Cool, I use a MT Grid Server for home sites and their DB is hosted on another server I'm sure and haven't had any problems.

[eluser]philpalmieri[/eluser]
Hi Opel,

What you are doing is perfect, and should not interfere with performance much at all.

What you will want to do is on your mysql server, setup an account just for the web server, and only accessible from that web server. Then in you DB config setup the host as the domain or IP of your DB server.

As the site grows, you can setup replication, and easily setup anoher db connection in CI - do all you reads from con1, and all your writes to con2 etc.

another bit of wisdom: replication/remote db can effect speed by milliseconds. but clients will always choose a stable, 1 second slower system than a rocket fast site that loses connection or times out when its limit is reached.

Phil

[eluser]opel[/eluser]
Thanks for your help Phil.

Am I write in thinking that I would then have $this->db1->get($sql); for a read and $this->db2->etc for a write?

Sounds straight forward but then how to you have a sync between 2 database if you read directly after write e.g display what you have just submitted?

[eluser]philpalmieri[/eluser]
Yeah,

In your database.php config file you setup 2 connections:

Code:

//Local
$db['default']['hostname'] = "localhost";
$db['default']['username'] = "localuser";
$db['default']['password'] = "localpass";
$db['default']['database'] = "your_local_db";
$db['default']['dbdriver'] = "mysql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";

//Remote
$db['remote']['hostname'] = "remote_host";
$db['remote']['username'] = "remote_user";
$db['remote']['password'] = "remote_pass";
$db['remote']['database'] = "your_remote_db";
$db['remote']['dbdriver'] = "mysql";
$db['remote']['dbprefix'] = "";
$db['remote']['pconnect'] = TRUE;
$db['remote']['db_debug'] = TRUE;
$db['remote']['cache_on'] = FALSE;
$db['remote']['cachedir'] = "";
$db['remote']['char_set'] = "utf8";
$db['remote']['dbcollat'] = "utf8_general_ci";

Then in your models you can assign a local instance of the remote db by doing

Code:

$this->_remoteDB = $this->load->database('remote', TRUE);

//then use as
$remote_stuff = $this->_remoteDB->get('remote_stuff')->result();

[eluser]jedd[/eluser]
[quote author="Opel" date="1237391561"]I am looking for advice on a Codeigniter development we are about to start. The site is a social site and requires maximum uptime so the proposed solution is to host the site (CI) on a separate server from the MYSQL database which would in turn be mirrored and backed up else where.[/quote]

When you say mirror - do you mean hot or cold? Phil's bang on the money about MySQL replication, but I can't work out if that's what you mean you're doing from the get-go. (Mirror means lots of different things to lots of people.)

If you are going to do it later, then you need to design it now - again as Phil says, separating your reads from your writes is something that'll split some load around. If you ever go to two slaves, then it becomes .. more confusing.

If you ever want to go to more than one web server, then it becomes particularly confusing - off the top of my head, I think it'd be painful to try to do a complex RESTful site using CI.

Going back to your original question, though .. you say you want to separate the DB from the web front end in order to maximise uptime - but having two possible points of failure instead of one doesn't assist with that goal - it increases your risk of downtime.

I was wondering if there will be any issues with hosting the database on a separate server from the CI app and will it affect anything with the CI cache library?

Well, apart from the added code complexity, the problem of coding something to scale even further later, the immediate performance hit, the greater exposure to hardware faults ..

How big will the database be, and how many concurrent users are you designing for? What's the network capacity between your web server and your database server, and what's the size of the pipe to the Internet? How normalised is your schema? Are you using stored procedures?

As to the CI cache library - my understanding of a 1 x web, n x db server configuration is that there's no concern - the web server is where the cache is stored.

[eluser]brianw1975[/eluser]
I wouldn't worry about db replication to begin with; especially if you are using the caching... I'd worry about setting up a load balancer first.

You'd basically have 1 DB server and 2 Apache servers with a load balancer that will direct traffic accordingly.

The load balancer can also keep tabs on the DB server and direct traffic to a "Site down for maintenance" page in case the DB has crashed or whatever. I've not set up a load balancer myself, my last employer had one in place when i started there; but the idea is pretty straight forward (and should be able to be set up on the primary web server.

[eluser]Otaku Coder[/eluser]
The other thing I'd suggest is to setup an ssh tunnel for the DB traffic as it's encrypted on the fly. I do that for geographically seperate DB clusters and web clusters, but if they're in the same server room then just setup a VLAN and use the spare ethernet ports on the servers. Each webserver would have a public IP and a private IP which would be dedicated DB traffic.

Phil is on the nail with replication, Master/Slave is easier to setup, just make sure you have binary logging turned on in the master config, and this would require a restart of the server so do this while it's not live. The other benefit of using replication is that you can have another slave DB setup purely for taking backups. That way you can stop your backup slave, flush the cache to ensure consistency then take a backup and start it again. In MySQL 5 you can also do Master-Master replication but this is a bit buggy in my opinion.

[eluser]RS71[/eluser]
Please excuse my lack of knowledge on the subject but phil, when you're talking about having two connections, would it be to the same DB server?

I'm coding a web app/site at the moment and I expect to have traffic at about 29k to 180k daily accesses within couple months. What sort of hardware and setup should I be running? I imagine its hard to answer the following question without knowing queries, code, etc but what sort of hardware would I get by with initially? Also, how often (or how do I determine how often) should I backup my database?

Thanks in advance,
RS71