best practice: mysql_multi, VMs w/single instance per or doesn"t matter?

--20cf3001b503a4bbf8049d9b0b56
Content-Type: text/plain; charset=ISO-8859-1

I've always had a single physical server that is the qc mysql database for
all our applications but it's now up to 85 schemas so I want to break it up
along the same lines as production (where there's redundant pools of mysql
servers by application class).

my basic question is whether it's better to run multiple instances on the
same host or run single instances on multiple VMs on the same physical
server. I can see slight advantages/disadvantages to each but no obvious
upside nor downside to either.

remember, this is dev/qc, not prod, so I'm leaning toward VMs so I don't
have to manage port #s in configs or expect developers to remember that
(also, I don't have to modify scripts for multiple instances, paths, etc).
not big reasons for sure but all else equal I'll go the less work route and
the only upside to multi I see is not having to reload the box as VM host.

any compelling argument for either approach?

--20cf3001b503a4bbf8049d9b0b56--

Re: best practice: mysql_multi, VMs w/single instance per or doesn"tmatter?

--------------enigEF6F65331D3B57828E7FD2EB
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

i would use virtual machines because port/socket/configuration

after running our whole infrastructure on vmware i can not understand
how i could live without machine-snapshots and auto-failover :-)

on hardware with virtualization support performance is also
not a problem and ESXi is free without support on hardware
matching the HCL

Am 03.03.2011 22:52, schrieb Sid Lane:
> I've always had a single physical server that is the qc mysql database =
for
> all our applications but it's now up to 85 schemas so I want to break i=
t up
> along the same lines as production (where there's redundant pools of my=
sql
> servers by application class).
>=20
> my basic question is whether it's better to run multiple instances on t=
he
> same host or run single instances on multiple VMs on the same physical
> server. I can see slight advantages/disadvantages to each but no obvio=
us
> upside nor downside to either.
>=20
> remember, this is dev/qc, not prod, so I'm leaning toward VMs so I don'=
t
> have to manage port #s in configs or expect developers to remember that=

> (also, I don't have to modify scripts for multiple instances, paths, et=
c).
> not big reasons for sure but all else equal I'll go the less work route=
and
> the only upside to multi I see is not having to reload the box as VM ho=
st.
>=20
> any compelling argument for either approach?


--------------enigEF6F65331D3B57828E7FD2EB
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEUEARECAAYFAk1wD6EACgkQhmBjz394AnmICwCWK4MtC3wEd0MiI49MUSJf Yf7n
vQCfTGmDIXe9W93aI2ecMvQw9jur/Q0=
=mkZ8
-----END PGP SIGNATURE-----

--------------enigEF6F65331D3B57828E7FD2EB--

Re: best practice: mysql_multi, VMs w/single instance per or doesn"t matter?

--20cf3054ac49db90b2049d9b59ec
Content-Type: text/plain; charset=ISO-8859-1

Just know that there is not-a-problem in running multiple instances on the
same host,
then all you have to do is to evaluate the performance factor.
In your case I would not introduce the overhead of the VMs,
but take advantage of this to learn how to manage multiple instances on the
same host that is always useful.

You can have a look at Giuseppe Maxia's MySQL Sandbox

Or if you wish I can share my technique I use since 3.23.

Cheers

Claudio



2011/3/3 Reindl Harald

> i would use virtual machines because port/socket/configuration
>
> after running our whole infrastructure on vmware i can not understand
> how i could live without machine-snapshots and auto-failover :-)
>
> on hardware with virtualization support performance is also
> not a problem and ESXi is free without support on hardware
> matching the HCL
>
> Am 03.03.2011 22:52, schrieb Sid Lane:
> > I've always had a single physical server that is the qc mysql database
> for
> > all our applications but it's now up to 85 schemas so I want to break it
> up
> > along the same lines as production (where there's redundant pools of
> mysql
> > servers by application class).
> >
> > my basic question is whether it's better to run multiple instances on the
> > same host or run single instances on multiple VMs on the same physical
> > server. I can see slight advantages/disadvantages to each but no obvious
> > upside nor downside to either.
> >
> > remember, this is dev/qc, not prod, so I'm leaning toward VMs so I don't
> > have to manage port #s in configs or expect developers to remember that
> > (also, I don't have to modify scripts for multiple instances, paths,
> etc).
> > not big reasons for sure but all else equal I'll go the less work route
> and
> > the only upside to multi I see is not having to reload the box as VM
> host.
> >
> > any compelling argument for either approach?
>
>


--
Claudio

--20cf3054ac49db90b2049d9b59ec--

RE: best practice: mysql_multi, VMs w/single instance per or doesn"t matter?

There is almost no VM overhead these days. mySQL is disk I/O bound, not CPU
bound.

With VMWare you can setup your partitions to be raw disks (not virtual disk
files) so you get native I/O. If you were to get some SSD's, I bet you
would even see some significant performance increase too even over a true
native system. Also consider sharding your tables to put some on
raw/ssd/vmdk depending on how they're used.

VMWare has options that are nearly bare-metal. There are other free options
like KVM that are built right into the kernel. I personally use VirtualBox
here at work for development, but I use VMWare Workstation at home. At
previous jobs, we used VMWare Server (free) for all the UAT from the test
servers themselves to the test guest OS (XP, Win7, OSX, Linux, browser
variants, etc.)

Virtual Machines are the ONLY way to go these days IMHO. It's silly to try
and setup mySQL on different ports and go through all that hassle and
configuration. With VM's you can just clone one to setup a new instance,
you have fail-over, backups, they're easy to move to new hardware, they
have console GUIs, intelligent shuffling of resources, maximizing hardware,
minimizing costs (electric, carbon, space, etc). There are so many benefits
and almost no detriments to a VM these days with computers as powerful as
they are. Even updating the VMs (patching) is fairly straight forward with
the major Linux distros (many even have web GUI front ends to push patches
to all VMs, not to mention automated unattended updates if you desire)

Just do it. DO IT! You won't ever look back, and like Reindl said, you'll
wonder how you got this far without VMs. :-)

-Daevid.

There are only 11 types of people in this world. Those that think binary
jokes are funny, those that don't, and those that don't know binary.


> -----Original Message-----
> From: Claudio Nanni [mailto:claudio.nanni@gmail.com]
> Sent: Thursday, March 03, 2011 2:14 PM
> To: Reindl Harald
> Cc: mysql@lists.mysql.com
> Subject: Re: best practice: mysql_multi, VMs w/single
> instance per or doesn't matter?
>
> Just know that there is not-a-problem in running multiple
> instances on the same host,
> then all you have to do is to evaluate the performance factor.
> In your case I would not introduce the overhead of the VMs,
> but take advantage of this to learn how to manage multiple
> instances on the
> same host that is always useful.
>
> You can have a look at Giuseppe Maxia's MySQL
> Sandbox
>
> Or if you wish I can share my technique I use since 3.23.
>
> Cheers
>
> Claudio
>
>
>
> 2011/3/3 Reindl Harald
>
> > i would use virtual machines because port/socket/configuration
> >
> > after running our whole infrastructure on vmware i can not
> understand
> > how i could live without machine-snapshots and auto-failover :-)
> >
> > on hardware with virtualization support performance is also
> > not a problem and ESXi is free without support on hardware
> > matching the HCL
> >
> > Am 03.03.2011 22:52, schrieb Sid Lane:
> > > I've always had a single physical server that is the qc
> mysql database
> > for
> > > all our applications but it's now up to 85 schemas so I
> want to break it
> > up
> > > along the same lines as production (where there's
> redundant pools of
> > mysql
> > > servers by application class).
> > >
> > > my basic question is whether it's better to run multiple
> instances on the
> > > same host or run single instances on multiple VMs on the
> same physical
> > > server. I can see slight advantages/disadvantages to
> each but no obvious
> > > upside nor downside to either.
> > >
> > > remember, this is dev/qc, not prod, so I'm leaning toward
> VMs so I don't
> > > have to manage port #s in configs or expect developers to
> remember that
> > > (also, I don't have to modify scripts for multiple
> instances, paths,
> > etc).
> > > not big reasons for sure but all else equal I'll go the
> less work route
> > and
> > > the only upside to multi I see is not having to reload
> the box as VM
> > host.
> > >
> > > any compelling argument for either approach?
> >
> >
>
>
> --
> Claudio
>


--
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: best practice: mysql_multi, VMs w/single instance per or doesn"t matter?

Other people have answered with pros and cons of virtualisation, but I would rather ask another question: why do you feel it necessary to split up the database?

If it's only used for QC, it's probably not in intensive use. Why would you go through the bother of splitting it up? You're staying on the same server, apparently, so you'll have to decide which instance gets what part of cpu, memory and other resources, you'll have to provide separate backup for all instances, et cetera; while leaving things as they are is zero effort.

What is the problem with the current setup that will be resolved by splitting?


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

--
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: best practice: mysql_multi, VMs w/single instance per or doesn"t matter?

The biggest issue for me is that you want your development environment to be
as identical as possible to the production environment (to avoid mistakes when
you move your application over). You don't want your production environment to
accidentally access your development data; but at the same time you want to
make sure that your development isn't accidentally playing with the "live"
data.

Since I'm running a *AMP application, I can just use "localhost" and not worry
about forgetting to change the database name references.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com

>-----Original Message-----
>From: Johan De Meersman [mailto:vegivamp@tuxera.be]
>Sent: Friday, March 04, 2011 6:21 AM
>To: Sid Lane
>Cc: MySql
>Subject: Re: best practice: mysql_multi, VMs w/single instance per or doesn't
>matter?
>
>
>Other people have answered with pros and cons of virtualisation, but I would
>rather ask another question: why do you feel it necessary to split up the
>database?
>
>If it's only used for QC, it's probably not in intensive use. Why would you
>go
>through the bother of splitting it up? You're staying on the same server,
>apparently, so you'll have to decide which instance gets what part of cpu,
>memory and other resources, you'll have to provide separate backup for all
>instances, et cetera; while leaving things as they are is zero effort.
>
>What is the problem with the current setup that will be resolved by
>splitting?
>
>
>--
>Bier met grenadyn
>Is als mosterd by den wyn
>Sy die't drinkt, is eene kwezel
>Hy die't drinkt, is ras een ezel
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





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