Storage strategies/NAS/NFS

Hi all,


we are faced with a little problem that, I'm sure, had some others before.

Short description: We run a webserver and currently it's a
single-machine-server. Soon we want to switch to a two-machine-systeme
with a load balancer. All static data will be stored on a NAS.
So far, no problems.

The trouble begins with mysql.
In the single server enviroment there is no problem, but what to do with
two servers.

First idea: a "global" data directory on the NAS. Performance-wise that
wouldn't be a problem. The bandwith needed for the db-apps is quite
small (DB-Connects occurs only within the Content Management System,
which writes complete files for the web services), But two
mysql-machines writing in one nfs-directory? Doesn't sound like a good
idea to me :-( (Or is it possible?)

Second idea: data directory remains on the NAS, but only one server
holds a mysql-server. The load balancer would switch db-related services
only to this machine. But that isn't a good idea either, because the two
servers should provide reliability. But if the server with mysql cracks,
the second would be without mysql-support.

First conclusion: On the second machine a mysql runs "on demand". If the
first one (on the other server) stops running, it would take over the
mysql-work. Possible? If yes, how?

Second Conclusion: mysql cluster. In this case, the data directory
wouldn't be on NAS, but on the two servers. Problem: We'd need a
management node. But on what machine? Second problem: Our databases
aren't that big, but it's still enough to "steal" valuable memory with
the "shared nothing" strategy and database only in memory. Since 5.1 it
can be disk-based, but we don't want to run a beta version on a
productive system.

So, any hints how we can solve this delimma?

Hi Marco,

What I would do is the following since you have to grow and you
mentioned you need a second web-server:

-2 webservers in the DMZ with the load balancer in front of them.
-one firewall between your DMZ and your "back-end"
-1 server sunning your DB (keep the files on your NAS)

Configure both webservers to connect remotely to the MYSQL server.

THis allows you to grow the "web-server" part of your app without
affecting your back-end. So you can add a 3rd, 4rth, 5th without a hitch
in the future.

Now as for the DB end its a standalone server just running your DB.
Seeing as its dedicated to DB stuff, it should serve you for a while.
(We have 5 apps from 4 different web-servers running their DB stuff on a
2CPU 4GB DB-server). However, if you ever believe you need more power,
then you can look into mysql clustering..etc...


Christos Kalantzis
Senior Database Administrator, System Administrator
Intertrade Systems Inc.

Marco Helgert wrote:
> Hi all,
>
>
> we are faced with a little problem that, I'm sure, had some others before.
>
> Short description: We run a webserver and currently it's a
> single-machine-server. Soon we want to switch to a two-machine-systeme
> with a load balancer. All static data will be stored on a NAS.
> So far, no problems.
>
> The trouble begins with mysql.
> In the single server enviroment there is no problem, but what to do with
> two servers.
>
> First idea: a "global" data directory on the NAS. Performance-wise that
> wouldn't be a problem. The bandwith needed for the db-apps is quite
> small (DB-Connects occurs only within the Content Management System,
> which writes complete files for the web services), But two
> mysql-machines writing in one nfs-directory? Doesn't sound like a good
> idea to me :-( (Or is it possible?)
>
> Second idea: data directory remains on the NAS, but only one server
> holds a mysql-server. The load balancer would switch db-related services
> only to this machine. But that isn't a good idea either, because the two
> servers should provide reliability. But if the server with mysql cracks,
> the second would be without mysql-support.
>
> First conclusion: On the second machine a mysql runs "on demand". If the
> first one (on the other server) stops running, it would take over the
> mysql-work. Possible? If yes, how?
>
> Second Conclusion: mysql cluster. In this case, the data directory
> wouldn't be on NAS, but on the two servers. Problem: We'd need a
> management node. But on what machine? Second problem: Our databases
> aren't that big, but it's still enough to "steal" valuable memory with
> the "shared nothing" strategy and database only in memory. Since 5.1 it
> can be disk-based, but we don't want to run a beta version on a
> productive system.
>
> So, any hints how we can solve this delimma?

Thank you Christos

since authoring the post I found out some other aspects.
We will go exactly the way you mentioned, so one server is for the
database alone.
But with one important difference:
Another Server (the most powerfull) runs mysqld, too, but as slave. If
the DB-server cracks, this machine runs as DB-Server. It's a bit tricky
to get it work with die changing ips, but it's running ;)

Thanks again,
Marco

Christos Kalantzis schrieb:
> Hi Marco,
>
> What I would do is the following since you have to grow and you
> mentioned you need a second web-server:
>
> -2 webservers in the DMZ with the load balancer in front of them.
> -one firewall between your DMZ and your "back-end"
> -1 server sunning your DB (keep the files on your NAS)
>
> Configure both webservers to connect remotely to the MYSQL server.
>
> THis allows you to grow the "web-server" part of your app without
> affecting your back-end. So you can add a 3rd, 4rth, 5th without a hitch
> in the future.
>
> Now as for the DB end its a standalone server just running your DB.
> Seeing as its dedicated to DB stuff, it should serve you for a while.
> (We have 5 apps from 4 different web-servers running their DB stuff on a
> 2CPU 4GB DB-server). However, if you ever believe you need more power,
> then you can look into mysql clustering..etc...
>
>
> Christos Kalantzis
> Senior Database Administrator, System Administrator
> Intertrade Systems Inc.
>
> Marco Helgert wrote:
>> Hi all,
>>
>>
>> we are faced with a little problem that, I'm sure, had some others before.
>>
>> Short description: We run a webserver and currently it's a
>> single-machine-server. Soon we want to switch to a two-machine-systeme
>> with a load balancer. All static data will be stored on a NAS.
>> So far, no problems.
>>
>> The trouble begins with mysql.
>> In the single server enviroment there is no problem, but what to do with
>> two servers.
>>
>> First idea: a "global" data directory on the NAS. Performance-wise that
>> wouldn't be a problem. The bandwith needed for the db-apps is quite
>> small (DB-Connects occurs only within the Content Management System,
>> which writes complete files for the web services), But two
>> mysql-machines writing in one nfs-directory? Doesn't sound like a good
>> idea to me :-( (Or is it possible?)
>>
>> Second idea: data directory remains on the NAS, but only one server
>> holds a mysql-server. The load balancer would switch db-related services
>> only to this machine. But that isn't a good idea either, because the two
>> servers should provide reliability. But if the server with mysql cracks,
>> the second would be without mysql-support.
>>
>> First conclusion: On the second machine a mysql runs "on demand". If the
>> first one (on the other server) stops running, it would take over the
>> mysql-work. Possible? If yes, how?
>>
>> Second Conclusion: mysql cluster. In this case, the data directory
>> wouldn't be on NAS, but on the two servers. Problem: We'd need a
>> management node. But on what machine? Second problem: Our databases
>> aren't that big, but it's still enough to "steal" valuable memory with
>> the "shared nothing" strategy and database only in memory. Since 5.1 it
>> can be disk-based, but we don't want to run a beta version on a
>> productive system.
>>
>> So, any hints how we can solve this delimma?

Hey Marco,

Yup, a "warm-spare" is a great idea..we are going to implement that too.
Plus if you want to balance some of the load, you can have "SELECT"
only queries, such as reports, run off of the slave, that way your LIVE
server doesn't get affected by long complex reports that some companies
(such as mine :-( ) runs.

Cheers

Christos



Marco Helgert wrote:
> Thank you Christos
>
> since authoring the post I found out some other aspects.
> We will go exactly the way you mentioned, so one server is for the
> database alone.
> But with one important difference:
> Another Server (the most powerfull) runs mysqld, too, but as slave. If
> the DB-server cracks, this machine runs as DB-Server. It's a bit tricky
> to get it work with die changing ips, but it's running ;)
>
> Thanks again,
> Marco
>
> Christos Kalantzis schrieb:
>> Hi Marco,
>>
>> What I would do is the following since you have to grow and you
>> mentioned you need a second web-server:
>>
>> -2 webservers in the DMZ with the load balancer in front of them.
>> -one firewall between your DMZ and your "back-end"
>> -1 server sunning your DB (keep the files on your NAS)
>>
>> Configure both webservers to connect remotely to the MYSQL server.
>>
>> THis allows you to grow the "web-server" part of your app without
>> affecting your back-end. So you can add a 3rd, 4rth, 5th without a hitch
>> in the future.
>>
>> Now as for the DB end its a standalone server just running your DB.
>> Seeing as its dedicated to DB stuff, it should serve you for a while.
>> (We have 5 apps from 4 different web-servers running their DB stuff on a
>> 2CPU 4GB DB-server). However, if you ever believe you need more power,
>> then you can look into mysql clustering..etc...
>>
>>
>> Christos Kalantzis
>> Senior Database Administrator, System Administrator
>> Intertrade Systems Inc.
>>
>> Marco Helgert wrote:
>>> Hi all,
>>>
>>>
>>> we are faced with a little problem that, I'm sure, had some others
>>> before.
>>>
>>> Short description: We run a webserver and currently it's a
>>> single-machine-server. Soon we want to switch to a two-machine-systeme
>>> with a load balancer. All static data will be stored on a NAS.
>>> So far, no problems.
>>>
>>> The trouble begins with mysql.
>>> In the single server enviroment there is no problem, but what to do with
>>> two servers.
>>>
>>> First idea: a "global" data directory on the NAS. Performance-wise that
>>> wouldn't be a problem. The bandwith needed for the db-apps is quite
>>> small (DB-Connects occurs only within the Content Management System,
>>> which writes complete files for the web services), But two
>>> mysql-machines writing in one nfs-directory? Doesn't sound like a good
>>> idea to me :-( (Or is it possible?)
>>>
>>> Second idea: data directory remains on the NAS, but only one server
>>> holds a mysql-server. The load balancer would switch db-related services
>>> only to this machine. But that isn't a good idea either, because the two
>>> servers should provide reliability. But if the server with mysql cracks,
>>> the second would be without mysql-support.
>>>
>>> First conclusion: On the second machine a mysql runs "on demand". If the
>>> first one (on the other server) stops running, it would take over the
>>> mysql-work. Possible? If yes, how?
>>>
>>> Second Conclusion: mysql cluster. In this case, the data directory
>>> wouldn't be on NAS, but on the two servers. Problem: We'd need a
>>> management node. But on what machine? Second problem: Our databases
>>> aren't that big, but it's still enough to "steal" valuable memory with
>>> the "shared nothing" strategy and database only in memory. Since 5.1 it
>>> can be disk-based, but we don't want to run a beta version on a
>>> productive system.
>>>
>>> So, any hints how we can solve this delimma?

Marco Helgert <marco@grandprixstats.com> wrote:
>
> Short description: We run a webserver and currently it's a
> single-machine-server. Soon we want to switch to a two-machine-systeme
> with a load balancer. All static data will be stored on a NAS.
> So far, no problems.
>
> The trouble begins with mysql.
> In the single server enviroment there is no problem, but what to do with
> two servers.

There are several possible setups. Which one is best, depends on
your needs. Do you want more performance, more reliability or both?
Do you experience problems now?

> First idea: a "global" data directory on the NAS. Performance-wise that
> wouldn't be a problem. The bandwith needed for the db-apps is quite
> small (DB-Connects occurs only within the Content Management System,
> which writes complete files for the web services),

Putting MySQLs datadir on NAS is not recommended. MySQL uses the
file system in a very NAS-infriendly way. You may experience heavy
performance degradation.

> But two
> mysql-machines writing in one nfs-directory? Doesn't sound like a good
> idea to me :-( (Or is it possible?)

You cannot have two mysqld processes working on the same datadir
(if that was your question).

> Second idea: data directory remains on the NAS, but only one server
> holds a mysql-server. The load balancer would switch db-related services
> only to this machine. But that isn't a good idea either, because the two
> servers should provide reliability. But if the server with mysql cracks,
> the second would be without mysql-support.

If you want maximum reliability, you should avoid single points of
failure. Both your load balancer and your NAS box are SPOFs.

If your goal is availability, you can configure the two machines as
a cluster. Just put *all* your data onto the NAS box and access it by
only one of the servers. In case of failure, switch to the second one.
If you're running Linux, you could use the Heartbeat [1] stuff. All
your tables *must* be InnoDB, otherwise the failover would take very
long (the second mysqld has to recover from a dirty tablespace).
If you want to avoid the NAS SPOF, you can use DRBD [2] to share the
data between the two servers. The load balancer ist not neccessary
anyway (heartbeat will put the service IP address on the active node)


[1] http://www.linux-ha.org/
[2] http://www.drbd.org/


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/