>Q1: Did you set the filesystem''s recordsize to match
MySQL/InnoDB''s page
size?
yes, my recordsize is 16K,the same as innodb page size.
>Q2: Did you disable the ZIL? If so then do re-enable it.
ZIL is enable.
And this day ,i do more test,compare with ufs ,result like this:
*6 ssd as zpool:*
~600 qps when sync_binlog=1 & innodb_flush_log_at_trx_commit=1
~600 qps when sync_binlog=10 & innodb_flush_log_at_trx_commit=1
~600 qps when sync_binlog=0 & innodb_flush_log_at_trx_commit=1
~900 qps when sync_binlog=1 & innodb_flush_log_at_trx_commit=0
~5500 qps when sync_binlog=10 & innodb_flush_log_at_trx_commit=0
~15000 qps when sync_binlog=0 & innodb_flush_log_at_trx_commit=0
~800 qps when sync_binlog=1 & innodb_flush_log_at_trx_commit=2
~4500 qps when sync_binlog=10 & innodb_flush_log_at_trx_commit=2
~13000 qps when sync_binlog=0 & innodb_flush_log_at_trx_commit=2
* 1 ssd as zpool:*
~350 qps when sync_binlog=1 & innodb_flush_log_at_trx_commit=1
~400 qps when sync_binlog=10 & innodb_flush_log_at_trx_commit=1
~400 qps when sync_binlog=0 & innodb_flush_log_at_trx_commit=1
~900 qps when sync_binlog=1 & innodb_flush_log_at_trx_commit=0
~5300 qps when sync_binlog=10 & innodb_flush_log_at_trx_commit=0
~15000 qps when sync_binlog=0 & innodb_flush_log_at_trx_commit=0
~750 qps when sync_binlog=1 & innodb_flush_log_at_trx_commit=2
~4500 qps when sync_binlog=10 & innodb_flush_log_at_trx_commit=2
~13000 qps when sync_binlog=0 & innodb_flush_log_at_trx_commit=2
* 1 ssd as ufs:*
~1500 qps when sync_binlog=1 & innodb_flush_log_at_trx_commit=1
~2100 qps when sync_binlog=10 & innodb_flush_log_at_trx_commit=1
~2100 qps when sync_binlog=0 & innodb_flush_log_at_trx_commit=1
~4000 qps when sync_binlog=1 & innodb_flush_log_at_trx_commit=0
~13500 qps when sync_binlog=10 & innodb_flush_log_at_trx_commit=0
~19500 qps when sync_binlog=0 & innodb_flush_log_at_trx_commit=0
~4000 qps when sync_binlog=1 & innodb_flush_log_at_trx_commit=2
~13000 qps when sync_binlog=10 & innodb_flush_log_at_trx_commit=2
~18000 qps when sync_binlog=0 & innodb_flush_log_at_trx_commit=2
2009/4/16 <zfs-discuss-request at opensolaris.org>
> > Hi,all
> >
> > I did some test about MySQL''s Insert performance
> > on ZFS, and met a big performance problem,
> > *i''m not sure what''s the point*.
>
> [snip performance and config info]
>
> >Is there any one can help me,
> >why fsync on zfs is so bad?
> >or other problem?
>
> My guess:
> The InnoDB engine uses copy-on-write internally.
> zfs adds another layer of copy-on-write. Both try to
> optimize localization (keep related data close on the disk).
>
> Amongst other things this fight between the two causes
> fragmentation.
>
> Performance will get better if someone designs a MySQL
> storage engine which is aware of zfs and uses zfs
> copy-on-write primitives.
>
> I doubt it will be called InnoDB, because InnoDB is
> Oracle-owned, and probably not maintained by SUN.
> --
> ( Kees Nuyt
> )
> c[_]
>
>
> Date: Wed, 15 Apr 2009 12:45:13 -0500
> From: Nicolas Williams <Nicolas.Williams at sun.com>
> To: zfs-discuss at opensolaris.org
> Subject: Re: [zfs-discuss] MySQL On ZFS Performance(fsync) Problem?
> Message-ID: <20090415174512.GU1500 at Sun.COM>
> Content-Type: text/plain; charset=us-ascii
>
> On Wed, Apr 15, 2009 at 07:39:13PM +0200, Kees Nuyt wrote:
> > On Wed, 15 Apr 2009 14:28:45 +0800, ??????
> > <sky000 at gmail.com> wrote:
> > > I did some test about MySQL''s Insert performance
> > > on ZFS, and met a big performance problem,
> > > *i''m not sure what''s the point*.
>
> Q1: Did you set the filesystem''s recordsize to match
MySQL/InnoDB''s
> page
> size?
>
> If not, then try doing so (and re-create/copy the DB files to ensure
> they get the new recordsize).
>
> Q2: Did you disable the ZIL? If so then do re-enable it.
>
> > [snip performance and config info]
> >
> > >Is there any one can help me,
> > >why fsync on zfs is so bad?
> > >or other problem?
> >
> > My guess:
> > The InnoDB engine uses copy-on-write internally.
> > zfs adds another layer of copy-on-write. Both try to
> > optimize localization (keep related data close on the disk).
> >
> > Amongst other things this fight between the two causes
> > fragmentation.
>
> I doubt that''s the problem. On ZFS fsync() would mean syncing
more
> than
> just the writes to the given file, rather: all the pending writes. To
> make that go faster ZFS has the ZIL as a way to avoid having to commit
> an entire ZFS transaction. But even so writes to the ZIL are
> synchronous. If fsync()s are too slow even with the ZIL enabled then
> you should put the ZIL on a write-biased flash device if at all
> possible.
>
> > Performance will get better if someone designs a MySQL
> > storage engine which is aware of zfs and uses zfs
> > copy-on-write primitives.
>
> That may be, but I don''t believe that two layers of COW will
cause
> problems in this case. See my questions above.
>
> Nico
> --
>
>
> ------------------------------
>
--
Sky.Jian iMySQLer
?????http://www.jianzhaoyang.com
BBS???http://iMySQLer.com
????http://groups.google.com/group/mysqler
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://mail.opensolaris.org/pipermail/zfs-discuss/attachments/20090416/22cbeeeb/attachment.html>