MySQL Forums
Forum List  »  MySQL & Storage

Re: MySQL + NAS(NFS or CIFS) + Huge Table Insert Problem (Long)
Posted by: Ingo Strüwing
Date: September 13, 2005 03:30AM

Though I feel like being neither a performance expert nor an InnoDB expert, I'll share my thoughts and raise some questions.

InnoDB is a transactional storage engine. At every commit (at least) it needs to flush its log synchronical to the disk (fsync(2)). This means that it has to wait for acknowledge from the disk that the log data are safe. Now the question is: How long are your transactions? Do you commit after each batch? After each couple of inserts? After every insert statement? Do you run with autocommit (which means an implicit commit after each insert statement)?

The above it the default behaviour. It might be slightly influenced by the option innodb_flush_log_at_trx_commit if durability requirements are not too high.

You might want to check your NAS performance with a small test program, which operates similar to your dd test, but fsync(2)s the disk after every block (or every second).

Since MyISAM does not use fsync(2), I wonder what it means that "a MyISAM table on localhost it runs fine, albeit slower". If using MyISAM instead of InnoDB is an option for you (beware the durability implications), I would like to ask you to expand more about what you tried with it. Since I feel more knowlegable with MyISAM, I might be able to provide more ideas for its optimization.

Options: ReplyQuote


Subject
Written By
Posted
Re: MySQL + NAS(NFS or CIFS) + Huge Table Insert Problem (Long)
September 13, 2005 03:30AM


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.