load balanced mysql

You running into problems?

no just doing some planning for a huge new project..

Its MUCH easier to whack MySQL onto its own dedicated server, trust me on this we had a nightmare trying to balance the damn thing

40+ Databses running constantly.

Yeah, it's a bitch, especially when it comes to the replication portion. Your best bet is looking into PostgreSQL. You will see better performance as a whole from pg anyway

exactly.. load balancing also means twice the shit.

I would have to agree with this 100%.

Yup!

We originally tried balancing 15 databases across 7 domains on a couple of dedi boxes, ended up saying fuck it and we now have 3 dedi boxes 2 running domains / sites and one solely for MySQL no hardship with anything all of our MySQL runs smother than it has ever done even running a single DB.

Postgre is shit. Anyone who knows what mysql can handle will tell you the same. Postgre does have some nice features, but overall it is not worth using over mysql, besides.. mysql will have those features in a matter of months anyways

This has definitely been a longtime debate. Read the docs. What you are doing definitely can have an impact concerning what database to choose. The biggest problem people have with Pg is that no one knows how to set it up properly.
If you want to build bad-ass-like mysql servers though, get some Sun Blade 1000's, fuckin smokes on those...

I know it is.. i've spent hours and hours reading about which is better and IMHO mysql out-performs postgre by a long run.

You will still need replication or some other solution once you overload that one server, right?

You need a new programmer

Depends on what you are doing.

Spam0r.

Not me.

Guess it all depends on the type of server you set it up on

We made sure that the server we chose was WAY MORE than what we would need for the immediate future.

If at such a time we decide that we need to do more work on the MySQL we will need to make a business decision based on what we have to go on at the time but as i say, certainly for the next 24 months we wont have to worry at all

How do you interface with mysql on it's own server? Servername instead of 'localhost'?

MySQL="mysqldb.hostname.com"
User="username"
Pass="password"

Your host will give you the location of the dedicated MySQL box

Yeah I've heard that PostgreSQL is good when you've outgrown MySQL.... most people don't ever have to worry about that though.

Thanks. Just finished databasing our paysite - a huge project. No current worries, but we're growing, and it's good to know that you can put it on it's own dedicated.

Will this be the only location of any mysql then? or do you still have some of the db's locally? We have looked into the dedicated option, but it's quite confusing

All of ours, to my knowledge are stored on this one server.

You should probably ask your host about it all before you start making any changes i know it took Gary + Lee around 24 hours to move all of the DB's across to the new server from the older ones.

I'd think moving it would be fairly simple. What would be time consuming for me is rooting out every php or cgi program that interfaces with mysql to change the configs.

Hence the 24 hour time frame

Replication in mysql is marvelous and rather easy to set up. We actually use a mix of mysql and postgres. Mysql for data collection and simple queries (such as members area authentication), postgres does the heavy stats compiliations.

When working with multiple databases across multiple servers, its often a good idea to abastract the actual database connections out of your scripts. Its much easier to change 1 line in a library than 1 line in each of 500 scripts.

Exactly

MySQL replication really isn't inherently hard to setup.. However it's not really what most people think it us. Mysql 4 makes this way easier, and removes a lot of the bugs we've run across doing it. At least in theory, we havn't upgraded yet.. Huge project coming up to do that though.

The problem is you have to design your applications to support multiple databases. Essentially you have one big "master" server, which handles all writes (inserts, deletes, and updates), and one or more slaves that handle all reads (select's, etc.). Almost all apps are much more heavy read than write, so it works out well. You can go a step further by having a slave server dedicated for long queries, etc. But instead of say, in PHP having one database handle you need to have two, and make sure you use the correct one for updates, and the other one for select's.

In that setup, you can scale mysql way past what you can do on any sized single machine. We have a dual xeon w/ 10 seagate X15.3's (15,000 RPM drives) in a RAID 10 configuration, as our master. This handles the updates easily, and then we have a few single P4 systems that handle select's. You can load balance the slave machines behind a load balancer of some type, so you can then have a redundant cluster. A bit more complex, and you can have auto failover for the master server, with a slave server taking over the masters duties in the event of a failure.

This setup handles around 30-40 writes/sec, and around 1500-2500 queries/sec during peak times. We don't utilize much code-based caching since our app doesn't really lend itself well to that, and it was simply cheaper to spend the money on a couple slave servers than design the code (and modify the base we already had) to support that.

And we have a few tables that are getting close to 20GB in size.

Overall I'm fairly happy with it, other than some mysql annoyances/bugs they have in replication (such as DO NOT create a temporary table in a replicated database.. Seemingly half the time this totally fucks shit up, and you have to go manually resync the slaves), just stupid shit like that. And like I said, they focused a lot on replication in mysql 4, so we're moving towards that platform shortly.

-Phil

prepare to pay BIG dollars for an admin who can run the thing smoothly, it's top notch admin work to efficiently run a load balanced thingy. Just buy an awesome xeon box an run it dedicated