Replication on MySQL databases

--=-JjR8zlxOt/5YlXsjPGkC
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

Good day all

I am hoping that someone can perhaps help me with some resources
or info.

I need to go to a meeting in the next hour and was requested this
morning to research possible load balancing options for MySQL database.


What is currently running is a website (balanced over a couple of
web servers all connecting to the same database) using apacje and jdk.

2 MySQL databases running as Masler/Slave replication with all
reads and writes going to the master and the slave being used for data
exports and failover if required.


The websites are rather busy and during times of high load the
master server takes some strain.

The Databases are being to new upgraded hardware soon,
including a database upgrade.


The idea is that they also want to introduce load balancing for
the MySQL databases in order to manage the high load situations.

Any help would be appreciated as google has not yet turned up
any sufficient info for me in this short time I had been given.

Regards
Machiel

--=-JjR8zlxOt/5YlXsjPGkC--

Re: Replication on MySQL databases

--90e6ba6e8c5ad063e7049436adee
Content-Type: text/plain; charset=ISO-8859-1

If your sites are busy with *writes*, you're kind of stuck. Replication
means that every write that happens on one side, also MUST happen on the
other side, so you win nothing. Well, you win a little delay on half of your
writes, which is, to most people, really a downside, not an upside.

Your best bet in that scenario would be horizontal partitioning, that is,
put part of your tables on a second cluster. This entails quite some changes
to your application, though, and a hefty analysis of what tables you NEVER
use together in a single query. Can be quite the bugger to implement :-)
Keep in the back of your mind (but never tell management) that you *can*
actually use federated tables for accessing remote tables, but there's
plenty of drawbacks to that.


If you're talking mostly reads, you have more options. Even then, though, it
is best if your application is keenly aware of what's going on, as you have
no guarantee about the time it takes for an insert to replicate to all your
slaves - your application shouldn't panic if it can't immediately see the
data it just wrote.


Other people here will undoubtedly tell you about MMM - I keep hearing that
that's pretty good, but I (still) have no personal experience with it,
myself.



On Thu, Nov 4, 2010 at 10:04 AM, Machiel Richards wrote:

> Good day all
>
> I am hoping that someone can perhaps help me with some resources
> or info.
>
> I need to go to a meeting in the next hour and was requested this
> morning to research possible load balancing options for MySQL database.
>
>
> What is currently running is a website (balanced over a couple of
> web servers all connecting to the same database) using apacje and jdk.
>
> 2 MySQL databases running as Masler/Slave replication with all
> reads and writes going to the master and the slave being used for data
> exports and failover if required.
>
>
> The websites are rather busy and during times of high load the
> master server takes some strain.
>
> The Databases are being to new upgraded hardware soon,
> including a database upgrade.
>
>
> The idea is that they also want to introduce load balancing for
> the MySQL databases in order to manage the high load situations.
>
> Any help would be appreciated as google has not yet turned up
> any sufficient info for me in this short time I had been given.
>
> Regards
> Machiel
>



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--90e6ba6e8c5ad063e7049436adee--

Re: Replication on MySQL databases

--=-0nWneGg3jaF4ttW1t19/
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

Thank you for the quick response....

just to answer one of the things here, the load is mostly reads as
writes only happen in batches every so often.

When I am saying reads I am talking of up to 2000-5000 concurrently at
any given time during high load.




-----Original Message-----
From: Johan De Meersman
To: Machiel Richards
Cc: mysql mailing list
Subject: Re: Replication on MySQL databases
Date: Thu, 4 Nov 2010 10:21:11 +0100

If your sites are busy with *writes*, you're kind of stuck. Replication
means that every write that happens on one side, also MUST happen on the
other side, so you win nothing. Well, you win a little delay on half of
your writes, which is, to most people, really a downside, not an upside.

Your best bet in that scenario would be horizontal partitioning, that
is, put part of your tables on a second cluster. This entails quite some
changes to your application, though, and a hefty analysis of what tables
you NEVER use together in a single query. Can be quite the bugger to
implement :-) Keep in the back of your mind (but never tell management)
that you *can* actually use federated tables for accessing remote
tables, but there's plenty of drawbacks to that.


If you're talking mostly reads, you have more options. Even then,
though, it is best if your application is keenly aware of what's going
on, as you have no guarantee about the time it takes for an insert to
replicate to all your slaves - your application shouldn't panic if it
can't immediately see the data it just wrote.


Other people here will undoubtedly tell you about MMM - I keep hearing
that that's pretty good, but I (still) have no personal experience with
it, myself.



On Thu, Nov 4, 2010 at 10:04 AM, Machiel Richards
wrote:

Good day all

I am hoping that someone can perhaps help me with some
resources
or info.

I need to go to a meeting in the next hour and was requested
this
morning to research possible load balancing options for MySQL
database.


What is currently running is a website (balanced over a
couple of
web servers all connecting to the same database) using apacje
and jdk.

2 MySQL databases running as Masler/Slave replication with
all
reads and writes going to the master and the slave being used
for data
exports and failover if required.


The websites are rather busy and during times of high
load the
master server takes some strain.

The Databases are being to new upgraded hardware soon,
including a database upgrade.


The idea is that they also want to introduce load
balancing for
the MySQL databases in order to manage the high load situations.

Any help would be appreciated as google has not yet
turned up
any sufficient info for me in this short time I had been given.

Regards
Machiel



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel


--=-0nWneGg3jaF4ttW1t19/--

Re: Replication on MySQL databases

Classic scenario where MMM will be your best bet. Check out
http://mysql-mmm.org for more information. Setup two masters and 2 or
more slaves for full High Availability. It scales extremely well if
your application is read-heavy (which most applications are).

If you need help implementing this, I work for OpenQuery and we do
this kind of setup almost on a weekly basis. Check out the website in
the signature and let me/us know if you need our professional help.
Otherwise: feel free to ask questions here :)

kind regards,

On Thu, Nov 4, 2010 at 17:26, Machiel Richards wrote:
> Thank you for the quick response....
>
> just to answer one of the things here, the load is mostly reads as
> writes only happen in batches every so often.
>
> When I am saying reads I am talking of up to 2000-5000 concurrently at
> any given time during high load.

--
Walter Heck
Engineer @ Open Query (http://openquery.com)
Exceptional services for MariaDB and MySQL at a fixed budget

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org