load balanced mysql - GoFuckYourself.com

DatingGold · 11-08-2003, 12:36 AM

anyone ever load balanced mysql?

4Pics · 11-08-2003, 12:39 AM

You running into problems?

DatingGold · 11-08-2003, 12:40 AM

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

Stud Money · 11-08-2003, 12:41 AM

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.

Eve · 11-08-2003, 12:42 AM

Quote:

Originally posted by DatingGold
anyone ever load balanced mysql?

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

mrthumbs · 11-08-2003, 12:42 AM

Quote:

Originally posted by Stud Money
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.

exactly.. load balancing also means twice the shit.

JSA Matt · 11-08-2003, 12:43 AM

Quote:

Originally posted by Stud Money
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.

I would have to agree with this 100%.

Stud Money · 11-08-2003, 12:44 AM

Quote:

Originally posted by mrthumbs

exactly.. load balancing also means twice the shit.

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.

JSA Matt · 11-08-2003, 12:45 AM

Quote:

Originally posted by Eve

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

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

Eve · 11-08-2003, 12:50 AM

Quote:

Originally posted by JSA Matt

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...

JSA Matt · 11-08-2003, 12:52 AM

Quote:

Originally posted by Eve

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.

Mr.Fiction · 11-08-2003, 01:01 AM

Quote:

Originally posted by Stud Money
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.

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

JSA Matt · 11-08-2003, 01:01 AM

Quote:

Originally posted by Mr.Fiction

You will still need replication once you overload that one server.

You need a new programmer

Mr.Fiction · 11-08-2003, 01:02 AM

Quote:

Originally posted by JSA Matt

You need a new programmer

Depends on what you are doing.

JSA Matt · 11-08-2003, 01:03 AM

Quote:

Originally posted by Mr.Fiction

Depends on what you are doing.

Spam0r.

Mr.Fiction · 11-08-2003, 01:04 AM

Quote:

Originally posted by JSA Matt

Spam0r.

Not me.

Stud Money · 11-08-2003, 01:12 AM

Quote:

Originally posted by Mr.Fiction

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

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

codymc12 · 11-08-2003, 01:26 AM

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

Stud Money · 11-08-2003, 01:30 AM

Quote:

Originally posted by codymc12
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

goBigtime · 11-08-2003, 01:33 AM

Quote:

Originally posted by Eve

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

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

codymc12 · 11-08-2003, 01:38 AM

Quote:

Originally posted by Stud Money

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

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

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.

Jizar II · 11-08-2003, 01:45 AM

Quote:

Originally posted by Stud Money

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

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

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

Stud Money · 11-08-2003, 01:47 AM

Quote:

Originally posted by Jizar II

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.

codymc12 · 11-08-2003, 01:55 AM

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.

Stud Money · 11-08-2003, 01:56 AM

Quote:

Originally posted by codymc12
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

NetRodent · 11-08-2003, 02:15 AM

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.

Jizar II · 11-08-2003, 02:19 AM

Quote:

Originally posted by NetRodent

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

Phil21 · 11-08-2003, 02:19 AM

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

strobi · 11-08-2003, 06:36 AM

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

11-08-2003, 12:36 AM	#1
DatingGold $6 PER EMAIL JOiN Industry Role: Join Date: Feb 2003 Location: California Posts: 13,185	load balanced mysql anyone ever load balanced mysql? __________________ 9 Years of SOLID payouts and conversions! ADULT DATING - $100 PPS LIVE CAMS - $214 PPS WWW.DATINGGOLD.COM ICQ: 27442303

11-08-2003, 12:40 AM	#3
DatingGold $6 PER EMAIL JOiN Industry Role: Join Date: Feb 2003 Location: California Posts: 13,185	no just doing some planning for a huge new project.. __________________ 9 Years of SOLID payouts and conversions! ADULT DATING - $100 PPS LIVE CAMS - $214 PPS WWW.DATINGGOLD.COM ICQ: 27442303

11-08-2003, 02:15 AM	#26
NetRodent Confirmed User Join Date: Jan 2002 Location: In the walls of your house. Posts: 3,985	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. __________________ "Every normal man must be tempted, at times, to spit on his hands, hoist the black flag, and begin slitting throats." --H.L. Mencken

11-08-2003, 02:19 AM	#28
Phil21 Confirmed User Join Date: May 2001 Location: ICQ: 25285313 Posts: 993	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 __________________ Quality affordable hosting.

11-08-2003, 12:39 AM	#2
4Pics Confirmed User Industry Role: Join Date: Dec 2001 Posts: 7,952	You running into problems?

11-08-2003, 12:41 AM	#4
Stud Money So Fucking Banned Join Date: Sep 2003 Location: Somewhere between my monitor and my chair Posts: 3,214	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.

11-08-2003, 01:26 AM	#18
codymc12 Confirmed User Join Date: May 2003 Posts: 719	How do you interface with mysql on it's own server? Servername instead of 'localhost'?

11-08-2003, 01:55 AM	#24
codymc12 Confirmed User Join Date: May 2003 Posts: 719	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.

11-08-2003, 06:36 AM	#29
strobi Confirmed User Join Date: Nov 2002 Location: Belgium Posts: 7,383	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