Question about MySQL

Hello MySQL developers,

Could any one tell me if MySQL's equivalent of PRO C concept in
ORACLE? and Is there any equivalent for SQL *NET in MySQL? Other than
C API/ODBC what are the other interfaces from client applications to
MySQL database ?

Another question is , whats the general experience of running MySQL
servers on NFS shares ? We are working on developing a custom
protocol for MySQL clustering that takes care of all file management
as part of the database clustering protocol, rather than a storage
engine feature. What are the likely setup of a database storage ? Is
it on RAW partition or on a File system ? Will ocfs2 be better used
for mySQL as well, in a clustered environment ?

I would appreciate if any one share with me their thoughts on this.
--
Best regards,
Peter Chacko

NetDiox computing systems,
Network storage & OS training and research.
Bangalore, India.
www.netdiox.com
080 2664 0708

--
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: Question about MySQL

Hi Peter, all,


let me just concentrate on the NFS aspect:


Peter Chacko wrote:
> [[...]]
>=20
> Another question is , whats the general experience of running MySQ=
L
> servers on NFS shares ?

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 compone=
nts
to the critical path. So your operations get slower, reliability
decreases, and (in case of any trouble) analysis becomes more difficu=
lt.

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 machin=
es
is based on economies of scale (huge disks), flexibility (partitionin=
g),
and centralized management (RAID replacement, backup).
There may be some merit to this in a specialized setup (NAS systems -
I'm not convinced of them, but don't claim expert knowledge about the=
m),
but IMO not using general-purpose machines and NFS.

Whatever the economical advantages during normal operation may be, yo=
u
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 require=
d
availability, but simply intolerable for others.


> We are working on developing a custom
> protocol for MySQL clustering that takes care of all file managemen=
t
> as part of the database clustering protocol, rather than a storage
> engine feature.

Sorry, I don't get the meaning of this in sufficient detail to commen=
t.


> What are the likely setup of a database storage ? I=
s
> it on RAW partition or on a File system ?

That may depend on the storage engine used.
MyISAM uses a file-per-table approach, so must be run on a file syste=
m;
InnoDB may be able to use a raw partition (I'm not sure there);
for others, I can't tell.


> Will ocfs2 be better use=
d
> for mySQL as well, in a clustered environment ?

I strongly doubt it.

AIUI, the big advantage of cluster file systems is that they allow al=
l
machines to access and modify shared data.
With a DBMS, you don't want to share data across machines, you want t=
he
database to be accessed only be the one machine (possibly multi-CPU, =
but
shared memory) running the database processes, because on that machin=
e
you have the various database caches.
Then, that machine makes the data available to all clients, so you ge=
t a
logical sharing on a higher protocol level (SQL).

To have multiple machines accessing the same database storage, you wo=
uld
first need some protocol to ensure cache coherency, and that is not
contained in MySQL (in the general server).
To use MySQL on multiple machines for the same data, you set up repli=
cation.

The alternative approach would be to use MySQL Cluster, which is
designed to hold the data in main memory (for extremely low latency) =
and
to use the disk only for backup purposes.


>=20
> I would appreciate if any one share with me their thoughts on this.

My comments above are based on my experience during DBMS development
(including distributed DBMS), but not on any financial calculations o=
r
DBA work. Weigh them with other answers.


Regards,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB1610=
28


--
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: Question about MySQL

Hi Jorg,

I really appreciate your help sharing your experience/thoughts.

Yes, i fully concur with you, NFS is not designed for Databases. But
you know there are Distributed SAN file systems (that use Direct IO to
the SAN) are serving databases like DB2 in many installations for
shared storage. NFS cannot match its performance...due to its added
latency. But you know ORACLE has added a direct NFS support to its
storage, that has no file system caching, and is far better than
native NFS client driver.

My goal here is to make NFS works much better, as a thin layer,
optimized for Database IO by developing a new NFS stack in a clustered
environment, as part of the clustering intelligence itself.

If any one is aware of such efforts please share it with me ( That
way we can avoid duplicate efforts and we can just complement what
they are not doing )

Thanks

On Fri, Aug 7, 2009 at 1:49 PM, Joerg Bruehe wrote:
> Hi Peter, all,
>
>
> let me just concentrate on the NFS aspect:
>
>
> Peter Chacko wrote:
>> [[...]]
>>
>> Another question is , whats the general experience of =A0running MySQL
>> servers on NFS shares ?
>
> 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 (NAS 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.
>
>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 We are working on develo=
ping a custom
>> protocol for MySQL clustering that takes care of all file management
>> as part of the database clustering protocol, rather than a storage
>> engine feature.
>
> Sorry, I don't get the meaning of this in sufficient detail to comment.
>
>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 What are the likely setup of a database =
storage ? Is
>> it on RAW partition or on a File system ?
>
> That may depend on the storage engine used.
> MyISAM uses a file-per-table approach, so must be run on a file system;
> InnoDB may be able to use a raw partition (I'm not sure there);
> for others, I can't tell.
>
>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 Will ocfs2 =A0be better used
>> for mySQL as well, in a clustered environment ?
>
> I strongly doubt it.
>
> AIUI, the big advantage of cluster file systems is that they allow all
> machines to access and modify shared data.
> With a DBMS, you don't want to share data across machines, you want the
> database to be accessed only be the one machine (possibly multi-CPU, but
> shared memory) running the database processes, because on that machine
> you have the various database caches.
> Then, that machine makes the data available to all clients, so you get a
> logical sharing on a higher protocol level (SQL).
>
> To have multiple machines accessing the same database storage, you would
> first need some protocol to ensure cache coherency, and that is not
> contained in MySQL (in the general server).
> To use MySQL on multiple machines for the same data, you set up replicati=
on.
>
> The alternative approach would be to use MySQL Cluster, which is
> designed to hold the data in main memory (for extremely low latency) and
> to use the disk only for backup purposes.
>
>
>>
>> I would appreciate if any one share with me their thoughts on this.
>
> My comments above are based on my experience during DBMS development
> (including distributed DBMS), but not on any financial calculations or
> DBA work. Weigh them with other answers.
>
>
> Regards,
> Jörg
>
> --
> Joerg Bruehe, =A0MySQL Build Team, =A0Joerg.Bruehe@Sun.COM
> Sun Microsystems GmbH, =A0 Komturstraße 18a, =A0 D-12099 Berlin
> Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
> Vorsitzender des Aufsichtsrates: Martin Haering =A0 =A0 Muenchen: HRB1610=
28
>
>



--=20
Best regards,
Peter Chacko

NetDiox computing systems,
Network storage & OS training and research.
Bangalore, India.
www.netdiox.com
080 2664 0708

--
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: Question about MySQL

I always accepted that NFS was unacceptably slow for database access, until=
I actually tested it. Turns out that with lots of RAM and properly tuned =
caches, you're optimizing for minimal IO anyway. A good file server will ha=
ve massive amounts of IO OPS. On top of that if you're using GigE over few=
hops, then it's really not slower than local disks.

Remember: benchmark and test your assumptions!

Regards,
Gavin Towey

-----Original Message-----
From: Joerg.Bruehe@Sun.COM [mailto:Joerg.Bruehe@Sun.COM]
Sent: Friday, August 07, 2009 1:19 AM
To: Peter Chacko
Cc: mysql
Subject: Re: Question about MySQL

Hi Peter, all,


let me just concentrate on the NFS aspect:


Peter Chacko wrote:
> [[...]]
>
> Another question is , whats the general experience of running MySQL
> servers on NFS shares ?

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 (NAS 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.


> We are working on developing a custom
> protocol for MySQL clustering that takes care of all file management
> as part of the database clustering protocol, rather than a storage
> engine feature.

Sorry, I don't get the meaning of this in sufficient detail to comment.


> What are the likely setup of a database storage ? Is
> it on RAW partition or on a File system ?

That may depend on the storage engine used.
MyISAM uses a file-per-table approach, so must be run on a file system;
InnoDB may be able to use a raw partition (I'm not sure there);
for others, I can't tell.


> Will ocfs2 be better used
> for mySQL as well, in a clustered environment ?

I strongly doubt it.

AIUI, the big advantage of cluster file systems is that they allow all
machines to access and modify shared data.
With a DBMS, you don't want to share data across machines, you want the
database to be accessed only be the one machine (possibly multi-CPU, but
shared memory) running the database processes, because on that machine
you have the various database caches.
Then, that machine makes the data available to all clients, so you get a
logical sharing on a higher protocol level (SQL).

To have multiple machines accessing the same database storage, you would
first need some protocol to ensure cache coherency, and that is not
contained in MySQL (in the general server).
To use MySQL on multiple machines for the same data, you set up replication=
..

The alternative approach would be to use MySQL Cluster, which is
designed to hold the data in main memory (for extremely low latency) and
to use the disk only for backup purposes.


>
> I would appreciate if any one share with me their thoughts on this.

My comments above are based on my experience during DBMS development
(including distributed DBMS), but not on any financial calculations or
DBA work. Weigh them with other answers.


Regards,
Jörg

--
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
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: Question about MySQL

Hi Gavin,

I am interested in the things you made for the optimization. Can you share
with us such things?

Thanks a lot,

--=20
Banyan He
Network & System Security Infrastructure
Mail: banyan@rootong.com
Blog: http://www.rootong.com/blog
LinkedIn: http://www.linkedin.com/in/banyanhe
Website: http://www.rootong.com


> From: Gavin Towey
> Date: Fri, 7 Aug 2009 11:07:19 -0700
> To: "Joerg.Bruehe@Sun.COM" , Peter Chacko
>
> Cc: mysql
> Subject: RE: Question about MySQL
>=20
> I always accepted that NFS was unacceptably slow for database access, unt=
il I
> actually tested it. Turns out that with lots of RAM and properly tuned
> caches, you're optimizing for minimal IO anyway. A good file server will =
have
> massive amounts of IO OPS. On top of that if you're using GigE over few =
hops,
> then it's really not slower than local disks.
>=20
> Remember: benchmark and test your assumptions!
>=20
> Regards,
> Gavin Towey
>=20
> -----Original Message-----
> From: Joerg.Bruehe@Sun.COM [mailto:Joerg.Bruehe@Sun.COM]
> Sent: Friday, August 07, 2009 1:19 AM
> To: Peter Chacko
> Cc: mysql
> Subject: Re: Question about MySQL
>=20
> Hi Peter, all,
>=20
>=20
> let me just concentrate on the NFS aspect:
>=20
>=20
> Peter Chacko wrote:
>> [[...]]
>>=20
>> Another question is , whats the general experience of running MySQL
>> servers on NFS shares ?
>=20
> 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.
>=20
> 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.
>=20
> 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 (NAS systems -
> I'm not convinced of them, but don't claim expert knowledge about them),
> but IMO not using general-purpose machines and NFS.
>=20
> 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.
>=20
>=20
>> We are working on developing a custom
>> protocol for MySQL clustering that takes care of all file management
>> as part of the database clustering protocol, rather than a storage
>> engine feature.
>=20
> Sorry, I don't get the meaning of this in sufficient detail to comment.
>=20
>=20
>> What are the likely setup of a database storage ? Is
>> it on RAW partition or on a File system ?
>=20
> That may depend on the storage engine used.
> MyISAM uses a file-per-table approach, so must be run on a file system;
> InnoDB may be able to use a raw partition (I'm not sure there);
> for others, I can't tell.
>=20
>=20
>> Will ocfs2 be better used
>> for mySQL as well, in a clustered environment ?
>=20
> I strongly doubt it.
>=20
> AIUI, the big advantage of cluster file systems is that they allow all
> machines to access and modify shared data.
> With a DBMS, you don't want to share data across machines, you want the
> database to be accessed only be the one machine (possibly multi-CPU, but
> shared memory) running the database processes, because on that machine
> you have the various database caches.
> Then, that machine makes the data available to all clients, so you get a
> logical sharing on a higher protocol level (SQL).
>=20
> To have multiple machines accessing the same database storage, you would
> first need some protocol to ensure cache coherency, and that is not
> contained in MySQL (in the general server).
> To use MySQL on multiple machines for the same data, you set up replicati=
on.
>=20
> The alternative approach would be to use MySQL Cluster, which is
> designed to hold the data in main memory (for extremely low latency) and
> to use the disk only for backup purposes.
>=20
>=20
>>=20
>> I would appreciate if any one share with me their thoughts on this.
>=20
> My comments above are based on my experience during DBMS development
> (including distributed DBMS), but not on any financial calculations or
> DBA work. Weigh them with other answers.
>=20
>=20
> Regards,
> Jörg
>=20
> --
> Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
> Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
> Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
> Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
>=20
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com
>=20
>=20
> The information contained in this transmission may contain privileged and
> confidential information. It is intended only for the use of the person(s=
)
> named above. If you are not the intended recipient, you are hereby notifi=
ed
> that any review, dissemination, distribution or duplication of this
> communication is strictly prohibited. If you are not the intended recipie=
nt,
> please contact the sender by reply email and destroy all copies of the
> original message.
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dbanyan@rootong.com
>=20



--
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: Question about MySQL

And NFS is becoming better and better with the adoption of 10GbE, and
NFSoRDMA ...i am sure at that point no body will complain about NFS
performance for databases. And for a parallel database access, pNFS is
also shaping up well. As NFS creators are now owned by ORACLE who
themselves have developed technology like direct NFS, NFS and Database
storage will be great buddies in future.

thanks

On Fri, Aug 7, 2009 at 11:37 PM, Gavin Towey wrote:
> I always accepted that NFS was unacceptably slow for database access, unt=
il I actually tested it. =A0Turns out that with lots of RAM and properly tu=
ned caches, you're optimizing for minimal IO anyway. A good file server wil=
l have massive amounts of IO OPS. =A0On top of that if you're using GigE ov=
er few hops, then it's really not slower than local disks.
>
> Remember: benchmark and test your assumptions!
>
> Regards,
> Gavin Towey
>
> -----Original Message-----
> From: Joerg.Bruehe@Sun.COM [mailto:Joerg.Bruehe@Sun.COM]
> Sent: Friday, August 07, 2009 1:19 AM
> To: Peter Chacko
> Cc: mysql
> Subject: Re: Question about MySQL
>
> Hi Peter, all,
>
>
> let me just concentrate on the NFS aspect:
>
>
> Peter Chacko wrote:
>> [[...]]
>>
>> Another question is , whats the general experience of =A0running MySQL
>> servers on NFS shares ?
>
> 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 (NAS 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.
>
>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 We are working on develo=
ping a custom
>> protocol for MySQL clustering that takes care of all file management
>> as part of the database clustering protocol, rather than a storage
>> engine feature.
>
> Sorry, I don't get the meaning of this in sufficient detail to comment.
>
>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 What are the likely setup of a database =
storage ? Is
>> it on RAW partition or on a File system ?
>
> That may depend on the storage engine used.
> MyISAM uses a file-per-table approach, so must be run on a file system;
> InnoDB may be able to use a raw partition (I'm not sure there);
> for others, I can't tell.
>
>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 Will ocfs2 =A0be better used
>> for mySQL as well, in a clustered environment ?
>
> I strongly doubt it.
>
> AIUI, the big advantage of cluster file systems is that they allow all
> machines to access and modify shared data.
> With a DBMS, you don't want to share data across machines, you want the
> database to be accessed only be the one machine (possibly multi-CPU, but
> shared memory) running the database processes, because on that machine
> you have the various database caches.
> Then, that machine makes the data available to all clients, so you get a
> logical sharing on a higher protocol level (SQL).
>
> To have multiple machines accessing the same database storage, you would
> first need some protocol to ensure cache coherency, and that is not
> contained in MySQL (in the general server).
> To use MySQL on multiple machines for the same data, you set up replicati=
on.
>
> The alternative approach would be to use MySQL Cluster, which is
> designed to hold the data in main memory (for extremely low latency) and
> to use the disk only for backup purposes.
>
>
>>
>> I would appreciate if any one share with me their thoughts on this.
>
> My comments above are based on my experience during DBMS development
> (including distributed DBMS), but not on any financial calculations or
> DBA work. Weigh them with other answers.
>
>
> Regards,
> Jörg
>
> --
> Joerg Bruehe, =A0MySQL Build Team, =A0Joerg.Bruehe@Sun.COM
> Sun Microsystems GmbH, =A0 Komturstraße 18a, =A0 D-12099 Berlin
> Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
> Vorsitzender des Aufsichtsrates: Martin Haering =A0 =A0 Muenchen: HRB1610=
28
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.co=
m
>
>
> The information contained in this transmission may contain privileged and=
confidential information. It is intended only for the use of the person(s)=
named above. If you are not the intended recipient, you are hereby notifie=
d that any review, dissemination, distribution or duplication of this commu=
nication is strictly prohibited. If you are not the intended recipient, ple=
ase contact the sender by reply email and destroy all copies of the origina=
l message.
>



--=20
Best regards,
Peter Chacko

NetDiox computing systems,
Network storage & OS training and research.
Bangalore, India.
www.netdiox.com
080 2664 0708

--
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: Question about MySQL

Hi all!


First of all, please excuse the typo I made in my posting.
I had written
>> There may be some merit to this in a specialized setup (NAS system=
s -
>> I'm not convinced of them, but don't claim expert knowledge about =
them),
and of course meant SAN, not NAS systems.


As regards NFS:

Peter Chacko wrote:
> And NFS is becoming better and better with the adoption of 10GbE, a=
nd
> NFSoRDMA ...i am sure at that point no body will complain about NFS
> performance for databases. And for a parallel database access, pNFS=
is
> also shaping up well. As NFS creators are now owned by ORACLE who
> themselves have developed technology like direct NFS, NFS and Datab=
ase
> storage will be great buddies in future.
>=20
> thanks
>=20
> On Fri, Aug 7, 2009 at 11:37 PM, Gavin Towey wrote:
>> I always accepted that NFS was unacceptably slow for database acce=
ss,
>> until I actually tested it. Turns out that with lots of RAM and p=
roperly
>> tuned caches, you're optimizing for minimal IO anyway. A good file=
server
>> will have massive amounts of IO OPS. On top of that if you're usi=
ng GigE
>> over few hops, then it's really not slower than local disks.


I immediately agree that NFS becomes faster due to technical progress=
,
like any other part of computing.

But however clever you optimize your NFS setup (including the network=
,
the servers, ...), you always have the additional latency of the netw=
ork
and the NFS server (compared to the local disk).
Remember: Database performance is not only about throughput, it is al=
so
about latency.
So NFS can only be slower than a local disk, never the same turnaroun=
d
time, let alone faster (assuming equal disks and machines, of course)=
..

Whether that is *too* slow is another question - depending on your
software, your workload, and your hardware it may be fast enough.


However, 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 tha=
t
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 y=
ou
had applied similar enhancements to your database server and its loca=
l
disks.

>>
>> Remember: benchmark and test your assumptions!

Agreed.


Regards,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
(+49 30) 417 01 487
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB1610=
28


--
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: Question about MySQL

Hi Banyan,

I'm really just talking about basic optimization techniques:

1. Install lots of RAM
2. Convert all table to innodb
3. Allocate about 80% of memory to innodb_buffer_pool_size

If you haven't seen this script yet, I suggest you start here:
https://launchpad.net/mysql-tuning-primer

Regards,
Gavin Towey

-----Original Message-----
From: Banyan He [mailto:banyan@rootong.com]
Sent: Friday, August 07, 2009 11:12 AM
To: Gavin Towey; Joerg.Bruehe@Sun.COM; Peter Chacko
Cc: mysql
Subject: Re: Question about MySQL

Hi Gavin,

I am interested in the things you made for the optimization. Can you share
with us such things?

Thanks a lot,

--
Banyan He
Network & System Security Infrastructure
Mail: banyan@rootong.com
Blog: http://www.rootong.com/blog
LinkedIn: http://www.linkedin.com/in/banyanhe
Website: http://www.rootong.com


> From: Gavin Towey
> Date: Fri, 7 Aug 2009 11:07:19 -0700
> To: "Joerg.Bruehe@Sun.COM" , Peter Chacko
>
> Cc: mysql
> Subject: RE: Question about MySQL
>
> I always accepted that NFS was unacceptably slow for database access, unt=
il I
> actually tested it. Turns out that with lots of RAM and properly tuned
> caches, you're optimizing for minimal IO anyway. A good file server will =
have
> massive amounts of IO OPS. On top of that if you're using GigE over few =
hops,
> then it's really not slower than local disks.
>
> Remember: benchmark and test your assumptions!
>
> Regards,
> Gavin Towey
>
> -----Original Message-----
> From: Joerg.Bruehe@Sun.COM [mailto:Joerg.Bruehe@Sun.COM]
> Sent: Friday, August 07, 2009 1:19 AM
> To: Peter Chacko
> Cc: mysql
> Subject: Re: Question about MySQL
>
> Hi Peter, all,
>
>
> let me just concentrate on the NFS aspect:
>
>
> Peter Chacko wrote:
>> [[...]]
>>
>> Another question is , whats the general experience of running MySQL
>> servers on NFS shares ?
>
> 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 (NAS 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.
>
>
>> We are working on developing a custom
>> protocol for MySQL clustering that takes care of all file management
>> as part of the database clustering protocol, rather than a storage
>> engine feature.
>
> Sorry, I don't get the meaning of this in sufficient detail to comment.
>
>
>> What are the likely setup of a database storage ? Is
>> it on RAW partition or on a File system ?
>
> That may depend on the storage engine used.
> MyISAM uses a file-per-table approach, so must be run on a file system;
> InnoDB may be able to use a raw partition (I'm not sure there);
> for others, I can't tell.
>
>
>> Will ocfs2 be better used
>> for mySQL as well, in a clustered environment ?
>
> I strongly doubt it.
>
> AIUI, the big advantage of cluster file systems is that they allow all
> machines to access and modify shared data.
> With a DBMS, you don't want to share data across machines, you want the
> database to be accessed only be the one machine (possibly multi-CPU, but
> shared memory) running the database processes, because on that machine
> you have the various database caches.
> Then, that machine makes the data available to all clients, so you get a
> logical sharing on a higher protocol level (SQL).
>
> To have multiple machines accessing the same database storage, you would
> first need some protocol to ensure cache coherency, and that is not
> contained in MySQL (in the general server).
> To use MySQL on multiple machines for the same data, you set up replicati=
on.
>
> The alternative approach would be to use MySQL Cluster, which is
> designed to hold the data in main memory (for extremely low latency) and
> to use the disk only for backup purposes.
>
>
>>
>> I would appreciate if any one share with me their thoughts on this.
>
> My comments above are based on my experience during DBMS development
> (including distributed DBMS), but not on any financial calculations or
> DBA work. Weigh them with other answers.
>
>
> Regards,
> Jörg
>
> --
> Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
> Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
> Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
> Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com
>
>
> The information contained in this transmission may contain privileged and
> confidential information. It is intended only for the use of the person(s=
)
> named above. If you are not the intended recipient, you are hereby notifi=
ed
> that any review, dissemination, distribution or duplication of this
> communication is strictly prohibited. If you are not the intended recipie=
nt,
> please contact the sender by reply email and destroy all copies of the
> original message.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dbanyan@rootong.co=
m
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

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