Moving from one MySQL server to three MySQL servers?

--0-181545489-1280947220=:8568
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hello Gurus :-)=A0 I was running a simple load generator against our 16GB D=
ual =0AQuad core server and it pretty much came down to it's knees within t=
wo hours of =0Arunning tests.=A0 The customer DOES NOT WANT to change any c=
ode, they just want to =0Athrow hardware at it since it took them a year to=
create all of the code.=A0 It is =0Aa 140GB database with 21GB of indexs a=
ll using InnoDB - currently doing 70% =0Areads and 30% writes. My ques=
tion is what is the best way of distributing the load without changing =0Aa=
ny of the php / perl code that their web server uses?=A0 This is what I am =
=0Athinking but need someone to tell me it is a good idea or bad please?=0A=
=0A1. Setup a single master and 2 slaves.=A0 The question is how to tell th=
e web =0Aservers to get all the read data from the slaves and to only write=
to the =0Amaster? 2. Install a MySQL proxy box and let mysql proxy ha=
ndle the load, problem is now =0Ait is the SPOF! 3. Use DNS round robi=
n, BUT how to tell round robin to ONLY go to master for =0Awrites and ONLY =
use one of the 2 slaves for reads? Any links, ideas or suggestions is =
most appreciated. TIA... Nunzio =0A
--0-181545489-1280947220=:8568--

Re: Moving from one MySQL server to three MySQL servers?

On 8/4/2010 12:40 PM, Nunzio Daveri wrote:
> it pretty much came down to it's knees within two hours of
> running tests.

Can you clarify what happened in those 2 hours, exactly?

If you mean it took 2 hours of running a single test for performance to
collapse, I'm not sure this means anything. 2 hours of continuous
pounding may not be representative of how your application will actually
be used. If there will be lulls and your test doesn't include lulls to
give the system time to do periodic cleanups that let it withstand the
next round of pounding, all you're testing here is what will happen when
someone tries to DoS the system.

If instead you mean it took you 2 hours of trying before you found a
test that would kill the box, what was the test, and is it
representative of actual load conditions? Again, if not, all you've
done is found a DoS test case, not something that requires
rearchitecting everything.

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

Re: Moving from one MySQL server to three MySQL servers?

On Wed, August 4, 2010 11:40, Nunzio Daveri wrote:
> Hello Gurus :-)� I was running a simple load generator against our 16GB
> Dual
> Quad core server and it pretty much came down to it's knees within two
> hours of
> running tests.� The customer DOES NOT WANT to change any code, they just
> want to
> throw hardware at it since it took them a year to create all of the code.�
> It is
> a 140GB database with 21GB of indexs all using InnoDB - currently doing
> 70%
> reads and 30% writes.
>
> My question is what is the best way of distributing the load without
> changing
> any of the php / perl code that their web server uses?� This is what I am
> thinking but need someone to tell me it is a good idea or bad please?
>
> 1. Setup a single master and 2 slaves.� The question is how to tell the
> web
> servers to get all the read data from the slaves and to only write to the
> master?
>
> 2. Install a MySQL proxy box and let mysql proxy handle the load, problem
> is now
> it is the SPOF!
>
> 3. Use DNS round robin, BUT how to tell round robin to ONLY go to master
> for
> writes and ONLY use one of the 2 slaves for reads?
>
As was mentioned, what the test was would help. Are you using single file
or separate file per table. If this is a web application, separate the
database onto another server, move to separate files per table and put the
files on separate spindles. Of course, the real next step is to find
where the actual bottle neck is. Do you have slow query log enables etc.?
What are the results. How critical is the consistency between read and
writes. There will be a lag between the master and slave which may or may
not be critical.

------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154


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

Re: Moving from one MySQL server to three MySQL servers?

--001485f5b1fafa3cba048d05fce5
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

>
> 1. Setup a single master and 2 slaves. The question is how to tell the w=
eb
> servers to get all the read data from the slaves and to only write to the
> master?
>

Replication is not an answer to all performance problems. Although updates
on the slave are more optimized than if you ran the updates normally, if yo=
u
use MyISAM tables, table-locking will still occur, and databases under
high-load could still struggle.

Replication is not a guarantee that the slave will be in sync with the
master at any one point in time. Even assuming the connection is always up,
a busy slave may not yet have caught up with the master, so you can't simpl=
y
interchange SELECT queries across master and slave servers.

On Thu, Aug 5, 2010 at 2:11 AM, Wm Mussatto wrote:

> On Wed, August 4, 2010 11:40, Nunzio Daveri wrote:
> > Hello Gurus :-)ï¿=BD I was running a simple load generator against =
our 16GB
> > Dual
> > Quad core server and it pretty much came down to it's knees within two
> > hours of
> > running tests.ï¿=BD The customer DOES NOT WANT to change any code, =
they just
> > want to
> > throw hardware at it since it took them a year to create all of the
> code.ï¿=BD
> > It is
> > a 140GB database with 21GB of indexs all using InnoDB - currently doing
> > 70%
> > reads and 30% writes.
> >
> > My question is what is the best way of distributing the load without
> > changing
> > any of the php / perl code that their web server uses?ï¿=BD This is=
what I am
> > thinking but need someone to tell me it is a good idea or bad please?
> >
> > 1. Setup a single master and 2 slaves.ï¿=BD The question is how to =
tell the
> > web
> > servers to get all the read data from the slaves and to only write to t=
he
> > master?
> >
> > 2. Install a MySQL proxy box and let mysql proxy handle the load, probl=
em
> > is now
> > it is the SPOF!
> >
> > 3. Use DNS round robin, BUT how to tell round robin to ONLY go to maste=
r
> > for
> > writes and ONLY use one of the 2 slaves for reads?
> >
> As was mentioned, what the test was would help. Are you using single fil=
e
> or separate file per table. If this is a web application, separate the
> database onto another server, move to separate files per table and put th=
e
> files on separate spindles. Of course, the real next step is to find
> where the actual bottle neck is. Do you have slow query log enables etc.=
?
> What are the results. How critical is the consistency between read and
> writes. There will be a lag between the master and slave which may or ma=
y
> not be critical.
>
> ------
> William R. Mussatto
> Systems Engineer
> http://www.csz.com
> 909-920-9154
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Daim.prabhat@gmail.com
>
>


--=20
Best Regards,

Prabhat Kumar
MySQL DBA

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--001485f5b1fafa3cba048d05fce5--

Re: Moving from one MySQL server to three MySQL servers?

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

My experience with replication:

Most of the times, is good enough, fast enough... I have just reworked some
part of an application to split the reporting module from all other modules.


We are still using php 4.3 with pear::db module (what? legacy software is
hard to kill! we are trying!, Honest!)..

So, we need to pull 2 tricks, first of it was, that we setup replicatio to
two slaves... one for reporting, one for selects and had our master
instance.

we had an autoinclude file, which invoqued the db connection. The first
trick we pulled of was to detect if we where accesing the reports, by path
name using the a regexp over $_SERVER['SCRIPT_FILENAME'], if the filename
matched the modules regexp we instanciated the db connection to one of
slaves, the reports slave.

The other trick we made, was to modifie pear::db so we matched, again each
->query or ->simple_query against a regeexp containing the ^select pattern
and used the conection to the select database....

this allowed us to manage 3 databases, 1 for writing, 1 for selects and 1
for reporting with almos 0 changes to the actual code.

Hope this ideas work for you.

Maybe you aren't use php, but I bet you are using some kind of data base
abstraction layer...

--90e6ba308c5ad2a96b048d0ca9a9--

Re: Moving from one MySQL server to three MySQL servers?

Hi Nunzio, all!


I cannot give specific hints, not being a MySQL tuning expert, but I
repeat my general question:

Nunzio Daveri schrieb:
> Hello Gurus :-) I was running a simple load generator against our 16GB=
Dual=20
> Quad core server and it pretty much came down to it's knees within two =
hours of=20
> running tests. The customer DOES NOT WANT to change any code, they jus=
t want to=20
> throw hardware at it since it took them a year to create all of the cod=
e.

*Which* *component* exactly "came down"?

In order for others to give helpful hints, you need to tell whether it's
CPU or disk, the cache sizes you have, and (preferably) the hit rates.
Also, do "vmstat" while the server is loaded - in the worst case, your
caches are larger than your RAM will hold, and you get paging added to
cache reload.

> =
It is=20
> a 140GB database with 21GB of indexs all using InnoDB - currently doing=
70%=20
> reads and 30% writes.

That is at least some info.
Personally, I think 30 % writes is a relatively high rate, and I'm not
sure replication will help.

>=20
> My question is what is the best way of distributing the load without ch=
anging=20
> any of the php / perl code that their web server uses? This is what I =
am=20
> thinking but need someone to tell me it is a good idea or bad please?

Before you decide to distribute the load, you need to check whether the
limiting component in the current machine can have its load reduced or
its capacity increased. Expanding a component is sure to be cheaper than
adding another similar box.
Also, are you sure there is no other activity on that machine which
could be taken off?

>=20
> [[...]]

Jörg

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


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

RE: Moving from one MySQL server to three MySQL servers?

Have you double checked the hardware? Are you using 5400rpm drives, or =
15k
rpm drives? I/O bottlenecks are common, if you can't read the data =
fast
enough, then it will definitely be slower, and appear to have more =
issues
that it really does. If the client can't/won't change/alter the code, =
then
maybe looking at changing the hardware would be better. Having a =
smaller
drive size raid array with faster harddrives may solve the I/O =
bottleneck if
that is the case.

And maybe it is just poorly written queries with crappy indexing? Maybe =
look
at the slow query log, and ensure that the RIGHT indexes are there
(140gb/21gb index doesn't mean that the indexes are the correct ones)

Going to a replication setup may not be the solution to your problems, =
and
could just be a bandaid (and prolly cause you many sleepless nights
maintaining data integrity). Find out the cause of the problem, before
adding to it.

Steven Staples


> -----Original Message-----
> From: Nunzio Daveri [mailto:nunziodaveri@yahoo.com]
> Sent: August 4, 2010 2:40 PM
> To: mysql@lists.mysql.com
> Subject: Moving from one MySQL server to three MySQL servers?
>=20
> Hello Gurus :-)=A0 I was running a simple load generator against our =
16GB
> Dual
> Quad core server and it pretty much came down to it's knees within two
> hours of
> running tests.=A0 The customer DOES NOT WANT to change any code, they =
just
> want to
> throw hardware at it since it took them a year to create all of the =
code.
> It is
> a 140GB database with 21GB of indexs all using InnoDB - currently =
doing
70%
> reads and 30% writes.
>=20
> My question is what is the best way of distributing the load without
> changing
> any of the php / perl code that their web server uses?=A0 This is what =
I am
> thinking but need someone to tell me it is a good idea or bad please?
>=20
> 1. Setup a single master and 2 slaves.=A0 The question is how to tell =
the
web
> servers to get all the read data from the slaves and to only write to =
the
> master?
>=20
> 2. Install a MySQL proxy box and let mysql proxy handle the load, =
problem
> is now
> it is the SPOF!
>=20
> 3. Use DNS round robin, BUT how to tell round robin to ONLY go to =
master
> for
> writes and ONLY use one of the 2 slaves for reads?
>=20
> Any links, ideas or suggestions is most appreciated.
>=20
> TIA...
>=20
> Nunzio
>=20
>=20
>=20
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.851 / Virus Database: 271.1.1/3023 - Release Date: =
08/04/10
> 00:45:00


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

Re: Moving from one MySQL server to three MySQL servers?

--0-945519127-1281022528=:48502
Content-Type: text/plain; charset=us-ascii

Hi all, thanks for the feedback. Good information for me to work with :-)

The server in this case is a stand alone with nothing more then CentOS and MySQL
5.1.44 on it. The drives are sas 10K rpm drives. The problem I see is that
when you stress test the server (typically by running loads of reports -
selects, joins) the machine hits 98% cpu and leaves only 800mb of free RAM out
of the 16 GB of which I told it to allocate 12GB for Innodb in my.cnf. Once the
server sucks up all the memory when we are stress testing it, it holds the 12 gb
as hostage and refuses to release it back into the pool, regardless of weather
there is load or not and on box. So when I do top-c before I run the reports,
it says mysql is using 2GB, then I run the stress test (several reports) and it
hits 12GB then I stop the stress and even 30 mins later the server says there is
only 800mb of ram free??? If I start to stress it again then it starts to go
into swap. Really weird, thus wanting to split the load onto 3 machines.

Also it doesn't help when your innodb index is larger than physical memory ;-)
The server only reports 50 to 100 slow queries per day out of the hundreds and
thousands of queries it is running.

But after all the chatter, I think I will use one of our test/dev servers,
install fresh OS, install 5.1.49 then import the db without indexing, run a good
100mb of sql statements against it from our prod servers logs, then look for
what fields need to be indexed under slow query logs and then go from there. Is
this a good idea vs. going straight to splitting the load into 3 servers?

I KNOW the tables and format and the way they have setup the database including
Indexing is bad, but mgmt says throw hardware as it's cheaper then re-writting
code and re-architecting the db ;-)

Again...

Thanks for all of your feedback Gurus :-)

Nunzio





________________________________
From: Steven Staples
To: Nunzio Daveri ; mysql@lists.mysql.com
Sent: Thu, August 5, 2010 7:23:19 AM
Subject: RE: Moving from one MySQL server to three MySQL servers?

Have you double checked the hardware? Are you using 5400rpm drives, or 15k
rpm drives? I/O bottlenecks are common, if you can't read the data fast
enough, then it will definitely be slower, and appear to have more issues
that it really does. If the client can't/won't change/alter the code, then
maybe looking at changing the hardware would be better. Having a smaller
drive size raid array with faster harddrives may solve the I/O bottleneck if
that is the case.

And maybe it is just poorly written queries with crappy indexing? Maybe look
at the slow query log, and ensure that the RIGHT indexes are there
(140gb/21gb index doesn't mean that the indexes are the correct ones)

Going to a replication setup may not be the solution to your problems, and
could just be a bandaid (and prolly cause you many sleepless nights
maintaining data integrity). Find out the cause of the problem, before
adding to it.

Steven Staples


> -----Original Message-----
> From: Nunzio Daveri [mailto:nunziodaveri@yahoo.com]
> Sent: August 4, 2010 2:40 PM
> To: mysql@lists.mysql.com
> Subject: Moving from one MySQL server to three MySQL servers?
>
> Hello Gurus :-) I was running a simple load generator against our 16GB
> Dual
> Quad core server and it pretty much came down to it's knees within two
> hours of
> running tests. The customer DOES NOT WANT to change any code, they just
> want to
> throw hardware at it since it took them a year to create all of the code.
> It is
> a 140GB database with 21GB of indexs all using InnoDB - currently doing
70%
> reads and 30% writes.
>
> My question is what is the best way of distributing the load without
> changing
> any of the php / perl code that their web server uses? This is what I am
> thinking but need someone to tell me it is a good idea or bad please?
>
> 1. Setup a single master and 2 slaves. The question is how to tell the
web
> servers to get all the read data from the slaves and to only write to the
> master?
>
> 2. Install a MySQL proxy box and let mysql proxy handle the load, problem
> is now
> it is the SPOF!
>
> 3. Use DNS round robin, BUT how to tell round robin to ONLY go to master
> for
> writes and ONLY use one of the 2 slaves for reads?
>
> Any links, ideas or suggestions is most appreciated.
>
> TIA...
>
> Nunzio
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.851 / Virus Database: 271.1.1/3023 - Release Date: 08/04/10
> 00:45:00



--0-945519127-1281022528=:48502--

Re: Moving from one MySQL server to three MySQL servers?

--00151748db02414f8a048d294ee7
Content-Type: text/plain; charset=ISO-8859-1

Any kind of optimization you need works.
I would use a explain sql statements istead to import without indexes, that
will shed more light...

Even if u optimize the report, if you have concurrent access demanding a
bunch load of data you will other operations get stuck... for that is a good
reason to have a master/write server and slave/read servers... if you make a
row level block or table level block at a slave/read server, master/write
still can work.

And mysql is kinda greedy with the ram once it has it. Tries to keep index
information, select cache, etc, as long as possible, which is a good
thing...

and if the client is willing to trow more hardware to the issue, trow more
RAM to those servers...

--00151748db02414f8a048d294ee7--

Re: Moving from one MySQL server to three MySQL servers?

On 8/5/2010 9:35 AM, Nunzio Daveri wrote:
>
> So when I do top-c before I run the reports,
> it says mysql is using 2GB, then I run the stress test (several reports) and it
> hits 12GB then I stop the stress and even 30 mins later the server says there is
> only 800mb of ram free???

That's normal Linux behavior:

http://www.linuxatemyram.com/

I've known this to be true for many years, but just for grins, I tested
it again on a box here. It's a development box, so it hadn't been used
at all over the weekend, and hadn't been used yet today, yet it shows
only 5% of its 6 GB total as free.

> Also it doesn't help when your innodb index is larger than physical memory ;-)

Yes, you should indeed fix that.

> But after all the chatter, I think I will use one of our test/dev servers,
> install fresh OS, install 5.1.49 then import the db without indexing, run a good
> 100mb of sql statements against it from our prod servers logs, then look for
> what fields need to be indexed under slow query logs and then go from there. Is
> this a good idea vs. going straight to splitting the load into 3 servers?

Yes.

Keep in mind that replication is a sidecar bolted onto DB systems like
MySQL. It's not a core behavior of the relational model, so it has a
lot of penalties. The current hoopla about "NoSQL" systems is one
answer to this, and for a lot of applications, it is a much better way
to get a distributed DB.

> mgmt says throw hardware as it's cheaper then re-writting
> code and re-architecting the db ;-)

They may well be right.

Just one observation: your "16 GB RAM" number means you're not using
DDR3 yet, either because the machine doesn't support it, or you're not
putting memory sticks in it in threes like you should.

Either way, it means RAM accesses could be 50% faster simply by moving
to DDR3, changing nothing else about the system configuration. Couple
that with the fact that the next common step up in RAM size for DDR3
systems from where you are now is 24 GB, just over your current index
size. Those two simple changes may be enough to fix your problem.

If you find a way to optimize the indexes to get it all under 16 GB,
well, so much the better. Upgrade to 24 GB (or 36...?) anyway and be
happy knowing you've bought yourself more time before you need to do the
next upgrade. Meantime, let Linux continue to eat your RAM. :)

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

Re: Moving from one MySQL server to three MySQL servers?

On Thu, Aug 5, 2010 at 8:35 AM, Nunzio Daveri wrot=
e:
>
> The server in this case is a stand alone with nothing more then CentOS an=
d MySQL
> 5.1.44 on it. =A0The drives are sas 10K rpm drives. =A0The problem I see =
is that
> when you stress test the server (typically by running loads of reports -
> selects, joins) the machine hits 98% cpu and leaves only 800mb of free RA=
M out
> of the 16 GB of which I told it to allocate 12GB for Innodb in my.cnf. =
=A0Once the
> server sucks up all the memory when we are stress testing it, it holds th=
e 12 gb
> as hostage and refuses to release it back into the pool, regardless of we=
ather

We had a similar system where the innodb data dictionary (poor-man's
description is an in-memory map of all the tables it has opened up to
that point) consumed all free memory and the kernel started killing
processes to get some back. At the time, stock mysql did not have the
ability to limit the size of the data dictionary memory usage (and I
don't see it as an available option for 5.1.x currently), so I tested
XtraDB and it prevented the memory usage from growing without bound.
XtraDB also gave us lots of knobs to twiddle and extra insights into
what innodb was doing. I've not experimented with current mysql 5.1.x
to see how much of that extra control has made it into the mainline.

> only 800mb of ram free??? =A0If I start to stress it again then it starts=
to go
> into swap. =A0Really weird, thus wanting to split the load onto 3 machine=
s.

When you restarted the stress test, did it access new tables or the
same ones that the previous stress test did? What happens if you tell
innodb to use less memory and do your tests? Have you found any way
to make mysql not run out of memory?

--=20
Regards...=A0 =A0 =A0 Todd
I seek the truth...it is only persistence in self-delusion and
ignorance that does harm.=A0 -- Marcus Aurealius

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