Moving MySQL to its own host

Simon · External Since: Nov 23, 2005 Posts: 9

Hi There,

We have a XenEnterprise Server running all debian sarge guests. One of
the guests is our client web server (Apache/MySQL/PHP). I have read
that there can be a performance boost in moving the mysql onto its own
host. Would this be worth looking at?

Thanks

Simon

--
To UNSUBSCRIBE, email to debian-isp-REQUEST DeleteThis @lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster DeleteThis @lists.debian.org

Jonathan Yu · External Since: Jun 26, 2007 Posts: 2

Hi there,

I think you should first look at adding memcached to the application
to accelerate it. But definitely, Apache+PHP and MySQL both require a
lot of CPU, so if memcached doesn't help or if you can't justify
rewriting the application you're using (assuming it doesn't already
provide memcached support), then that might be a next step worth
looking into.

If you decide to go the memcached route, first try putting all of the
applications on the same server. If there are still performance
issues, move MySQL to a different server--you can (and should) keep
Apache and memcached on the same server, since Apache is memory-light
and CPU-intensive whereas memcached is memory-intensive but CPU-light.

There is a lot of overhead for databases because they need to maintain
ACID compliance; thus they tend to be really disk-intensive. Apache
and MySQL will both compete for the disk as well as the CPU, which is
why there are scalability issues for large web sites.

To my knowledge, big web sites (such as Wikipedia) usually have a farm
of database servers running MySQL with replication enabled. Then they
have front-end web servers (possibly load balanced using the
appropriate mod_proxy modules for Apache) which also run memcached.
Memcached is a distributed caching system, so you can take advantage
of the memory of all servers to cache database data.

It all depends on your application and how many hits you are
receiving/predicting that you will receive. Not all applications
support memcached. Depending on the size of the application, I would
be willing to help you rewrite the application to include memcached
support, but it's been a long time since I've used PHP + MySQL, so you
may be better off asking somebody else.

Hope this helps.

Regards,

Jonathan Yu

On 6/25/07, Simon <greminn RemoveThis @gmail.com> wrote:
> Hi There,
>
> We have a XenEnterprise Server running all debian sarge guests. One of
> the guests is our client web server (Apache/MySQL/PHP). I have read
> that there can be a performance boost in moving the mysql onto its own
> host. Would this be worth looking at?
>
> Thanks
>
> Simon
>
>
> --
> To UNSUBSCRIBE, email to debian-isp-REQUEST RemoveThis @lists.debian.org
> with a subject of "unsubscribe". Trouble? Contact listmaster RemoveThis @lists.debian.org
>
>

--
To UNSUBSCRIBE, email to debian-isp-REQUEST RemoveThis @lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster RemoveThis @lists.debian.org

Duncan Robertson · External Since: Oct 17, 2005 Posts: 14

On Tue, 26 Jun 2007 11:50:36 +1200
Simon <greminn.DeleteThis@gmail.com> wrote:

> Hi There,
>
> We have a XenEnterprise Server running all debian sarge guests. One of
> the guests is our client web server (Apache/MySQL/PHP). I have read
> that there can be a performance boost in moving the mysql onto its own
> host. Would this be worth looking at?
>

First determine what the bottleneck (if any) is.. if all the Xen hosts are running off the same disk and
the database is diskbound it may be a lot of work for no improvement, when re-arranging the way the applications use the disks may help.

> Thanks
>
> Simon
>

--
To UNSUBSCRIBE, email to debian-isp-REQUEST.DeleteThis@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster.DeleteThis@lists.debian.org

Craig Sanders · External Since: Nov 19, 2004 Posts: 107

On Tue, Jun 26, 2007 at 10:17:13PM -0400, Roberto C. Sánchez wrote:
> - calling MySQL ACID compliant is a bit of a stretch

a huge stretch Smile

> - if Apache is hammering your disk, then you very likely don't have
> enough RAM (unless your website has gargantuan amounts of content
> not in a database with a fairly uniform access pattern)

i'm curious....in a virtual server system like xen or vmware, how is
disk-caching handled?

e.g. say you have a bunch of virtual machines, each allocated 256MB RAM, but
the physical hardware has, say, 2GB RAM. is disk cached/buffered individually
by each virtual machine, or by the hypervisor kernel, or by both?

craig

--
craig sanders <cas DeleteThis @taz.net.au>

Unseen University had never admitted women, muttering something about
problems with the plumbing, but the real reason was an unspoken dread that
if women were allowed to mess around with magic they would probably be
embarrassingly good at it ...
-- Terry Pratchett, "The Light Fantastic"

--
To UNSUBSCRIBE, email to debian-isp-REQUEST DeleteThis @lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster DeleteThis @lists.debian.org

Jonathan Yu · External Since: Jun 26, 2007 Posts: 2

SGkgdGhlcmUsCgpPbiA2LzI2LzA3LCBDcmFpZyBTYW5kZXJzIDxjYXNAdGF6Lm5ldC5hdT4gd3Jv
dGU6Cj4gT24gVHVlLCBKdW4gMjYsIDIwMDcgYXQgMTA6MTc6MTNQTSAtMDQwMCwgUm9iZXJ0byBD
LiBTw6FuY2hleiB3cm90ZToKPiA+ICAtIGNhbGxpbmcgTXlTUUwgQUNJRCBjb21wbGlhbnQgaXMg
YSBiaXQgb2YgYSBzdHJldGNoCj4KPiBhIGh1Z2Ugc3RyZXRjaCA6LSkKSSdtIGEgUG9zdGdyZVNR
TCB1c2VyIHRoZXNlIGRheXMsIGJ1dCBJIHRob3VnaHQgTXlTUUwgKyBJbm5vREIgaXMgQUNJRApj
b21wbGlhbnQ/Cj4KPiA+ICAtIGlmIEFwYWNoZSBpcyBoYW1tZXJpbmcgeW91ciBkaXNrLCB0aGVu
IHlvdSB2ZXJ5IGxpa2VseSBkb24ndCBoYXZlCj4gPiAgICBlbm91Z2ggUkFNICh1bmxlc3MgeW91
ciB3ZWJzaXRlIGhhcyBnYXJnYW50dWFuIGFtb3VudHMgb2YgY29udGVudAo+ID4gICAgbm90IGlu
IGEgZGF0YWJhc2Ugd2l0aCBhIGZhaXJseSB1bmlmb3JtIGFjY2VzcyBwYXR0ZXJuKQpJZiB5b3Un
cmUgdXNpbmcgcHJlZm9yayBhbmQgdXNpbmcgYSB0b24gb2YgUkFNLCB0aGVuIHRoZXJlIHdpbGwg
YmUgYQpsb3QgbGVzcyBjYWNoaW5nLiBDaGFuZ2luZyB0byBhIGRpZmZlcmVudCBNUE0gKGluIHRo
ZSBjYXNlIG9mIEFwYWNoZTIpCm1pZ2h0IGFsbGV2aWF0ZSB0aGUgcHJvYmxlbT8KPgo+IGknbSBj
dXJpb3VzLi4uLmluIGEgdmlydHVhbCBzZXJ2ZXIgc3lzdGVtIGxpa2UgeGVuIG9yIHZtd2FyZSwg
aG93IGlzCj4gZGlzay1jYWNoaW5nIGhhbmRsZWQ/Cj4KPiBlLmcuIHNheSB5b3UgaGF2ZSBhIGJ1
bmNoIG9mIHZpcnR1YWwgbWFjaGluZXMsIGVhY2ggYWxsb2NhdGVkIDI1Nk1CIFJBTSwgYnV0Cj4g
dGhlIHBoeXNpY2FsIGhhcmR3YXJlIGhhcywgc2F5LCAyR0IgUkFNLiAgaXMgZGlzayBjYWNoZWQv
YnVmZmVyZWQgaW5kaXZpZHVhbGx5Cj4gYnkgZWFjaCB2aXJ0dWFsIG1hY2hpbmUsIG9yIGJ5IHRo
ZSBoeXBlcnZpc29yIGtlcm5lbCwgb3IgYnkgYm90aD8KSSB3b3VsZCB0aGluaywgbG9naWNhbGx5
LCBib3RoIG1ha2VzIHRoZSBtb3N0IHNlbnNlLiBUaGUgdmlydHVhbAptYWNoaW5lcyBkb24ndCBr
bm93IHRoYXQgdGhleSdyZSBydW5uaW5nIG9uIHZpcnR1YWxpemVkIGhhcmR3YXJlLiBUaGUKaHlw
ZXJ2aXNvciBrZXJuZWwgd291bGQgb25seSBjYWNoZSB1bnRpbCB0aGUgZnVsbCBzeXN0ZW0gaXMg
cnVubmluZwpvdXQgb2YgbWVtb3J5IChhcyBpdCdzIGJlaW5nIGFsbG9jYXRlZCB0byB0aGUgaW5k
aXZpZHVhbCB2aXJ0dWFsCm1hY2hpbmVzKSwgc28uLi4gSSB0aGluayBpbiB0aGUgZW5kIGl0IGRl
cGVuZHMgb24gd2hhdCBraW5kIG9mCnZpcnR1YWxpemF0aW9uIHNvZnR3YXJlIHlvdSdyZSB1c2lu
Zy4KPgo+Cj4gY3JhaWcKPgo+IC0tCj4gY3JhaWcgc2FuZGVycyA8Y2FzQHRhei5uZXQuYXU+Cj4K
PiBVbnNlZW4gVW5pdmVyc2l0eSBoYWQgbmV2ZXIgYWRtaXR0ZWQgd29tZW4sIG11dHRlcmluZyBz
b21ldGhpbmcgYWJvdXQKPiBwcm9ibGVtcyB3aXRoIHRoZSBwbHVtYmluZywgYnV0IHRoZSByZWFs
IHJlYXNvbiB3YXMgYW4gdW5zcG9rZW4gZHJlYWQgdGhhdAo+IGlmIHdvbWVuIHdlcmUgYWxsb3dl
ZCB0byBtZXNzIGFyb3VuZCB3aXRoIG1hZ2ljIHRoZXkgd291bGQgcHJvYmFibHkgYmUKPiBlbWJh
cnJhc3NpbmdseSBnb29kIGF0IGl0IC4uLgo+ICAgICAgICAgICAgICAgICAtLSBUZXJyeSBQcmF0
Y2hldHQsICJUaGUgTGlnaHQgRmFudGFzdGljIgo+Cj4KPiAtLQo+IFRvIFVOU1VCU0NSSUJFLCBl
bWFpbCB0byBkZWJpYW4taXNwLVJFUVVFU1RAbGlzdHMuZGViaWFuLm9yZwo+IHdpdGggYSBzdWJq
ZWN0IG9mICJ1bnN1YnNjcmliZSIuIFRyb3VibGU/IENvbnRhY3QgbGlzdG1hc3RlckBsaXN0cy5k
ZWJpYW4ub3JnCj4KPgo=

Thomas Goirand · External Since: Apr 09, 2005 Posts: 161

Craig Sanders wrote:
> i'm curious....in a virtual server system like xen or vmware, how is
> disk-caching handled?
>
> e.g. say you have a bunch of virtual machines, each allocated 256MB RAM, but
> the physical hardware has, say, 2GB RAM. is disk cached/buffered individually
> by each virtual machine, or by the hypervisor kernel, or by both?
>
>
> craig

To what it seems to me: each VM will have it's own mechanics to do the
caching first. Then, as the hypervisor does the I/O backend (dom0 xvd
daemon) will as well do caching of the sectors of your hard drives. It
should be exactly the same case for VMWare...

Thomas

--
To UNSUBSCRIBE, email to debian-isp-REQUEST.TakeThisOut@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster.TakeThisOut@lists.debian.org