List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:June 10 2010 8:27am
Subject:Re: 2 servers 1 common data base
View as plain text  
Hi!


camelia botez wrote:
> We have 2 mysql servers - one active , second standby.
> The data base is on nsf storage file system mounted on the active server.
> We want to turn on active the second server and to be able to use both
> servers  with the same nfs mounted data base.

NFS may be good for many purposes, but using it for database storage is
not among them.

Rather than write from scratch, I'll quote my own postings to this list
of 2009-Aug-7 and 2009-Aug-10:

|> I would *never* use NFS storage for any DBMS (except for some testing):
|> NFS access is slower than local disk access, and it adds more components
|> to the critical path. So your operations get slower, reliability
|> decreases, and (in case of any trouble) analysis becomes more difficult.
|>
|> I cannot imagine any setup where you have a machine strong enough to run
|> your DBMS on it, but not capable of driving sufficient local disks.
|>
|> The typical argument for having centralized disks serving many machines
|> is based on economies of scale (huge disks), flexibility (partitioning),
|> and centralized management (RAID replacement, backup).
|> There may be some merit to this in a specialized setup (SAN systems -
|> I'm not convinced of them, but don't claim expert knowledge about them),
|> but IMO not using general-purpose machines and NFS.
|>
|> Whatever the economical advantages during normal operation may be, you
|> should not forget the huge costs you would incur if any in-between
|> component breaks and your database stops operating.
|> This may be tolerable for some applications, depending on the required
|> availability, but simply intolerable for others.


|> ... my main objection against using NFS for database storage is not
|> performance, it is complexity:
|> If your database server does not use local disks but NFS, then the
|> network between the database server and the NFS server as well as that
|> server suddenly become essential components for your database setup.
|> As any component may fail, you increase the risk to your DB.
|>
|> You may reduce the individual risk by selecting better hardware, dual
|> controllers, dual cabling, mirrored machines, ... as much as you like,
|> the result will still be higher complexity and higher risks than if you
|> had applied similar enhancements to your database server and its local
|> disks.


Regarding to your technical question:

> Just now when I try to start mysqld on the second server I get an error
> that says data base cannot be opened is locked by another mysql instance.
> What can be done to run on both servers mysqld simultaneously  and use
> the same data base?

One of the technical limitations of many NFS implementations is locking:
The concept of NFS is to be a stateless system (on the NFS server), and
that is not compatible with supporting file locks.

So it may be that NFS denies a lock request from the remote machine, or
you may have a NFS implementation that supports locking, and the request
is denied because the local instance already holds a lock.

Running two MySQL servers simultaneously on the same data files is even
worse than trying NFS: An instance of the MySQL server assumes it is the
only entity that accesses the data files, and manipulating overlapping
data from two instances is a sure way to damage the data structures.
So rather than trying to overcome that hurdle, you should be glad it is
protecting you.


Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  Joerg.Bruehe@stripped
Sun Microsystems GmbH,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028

Thread
2 servers 1 common data basecamelia botez10 Jun
  • Re: 2 servers 1 common data baseBenedikt Schackenberg10 Jun
    • Re: 2 servers 1 common data baseJohan De Meersman10 Jun
      • Re: 2 servers 1 common data baseWalter Heck - OlinData.com10 Jun
  • Re: 2 servers 1 common data baseJoerg Bruehe10 Jun
    • Re: 2 servers 1 common data baseJohan De Meersman10 Jun