MySQL Forums
Forum List  »  MySQL & Storage

Re: MySQL + NAS(NFS) - Possible solution?
Posted by: Jay Pipes
Date: March 07, 2006 08:29PM

Darren Schreiber wrote:
> We have a very large InnoDB table (several million
> rows) that unfortunately can not be split. We
> assumed putting this on a SAN hosted environment
> would improve performance. When we tried it, we
> got very, very poor results. We have been looking
> at all the same things mentioned in this document
> alerady.
> We used both dd and sysbench tool to benchmark
> performance of this server and found it to be very
> fast (150MB/sec) EXCEPT when fsyncs() were
> involved. If an fsync() was done after every 10
> writes to disk, the performance dropped to
> 18MB/sec. I am not sure whether this is an
> indication of a kernel issue or a driver issue for
> the QLogic driver in use for connecting the SAN to
> the machine. Or whether this is a behavior of
> fsync itself that is expected due to the overhead
> in confirming a write to disk has succeeded and
> flushing associated buffers.
> I also am unclear as to how often MySQL calls
> fsync().
> Any light on this?

InnoDB issues fsync() calls at an interval for which you specify the configuration variable innodb_flush_log_at_trx_commit. At 0, a call to fsync() is made once per second (or around a second, depending on process scheduling). At 1, a call to fsync() is made after each transaction is committed. When the variable is set to 2, then the log file is written (using write(), but flushed every second (or around a second).

I'm not sure how often data files are flushed to disk.

Did you try Kim's suggestion of using O_DIRECT, and then having log files written locally, with data files written over SAN?

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster?

Options: ReplyQuote

Written By
Re: MySQL + NAS(NFS) - Possible solution?
March 07, 2006 08:29PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.