zfs discuss - Dec 2006 - System pause peculiarity with mysql on zfs

Hey all, I run a netra X1 as the mysql db server for my small  
personal web site. This X1 has two drives in it with SVM-mirrored UFS  
slices for / and /var, a swap slice, and slice 7 is zfs. There is one  
zfs mirror pool called "local" on which there are a few file systems,
one of which is for mysql. slice 7 used to be ufs, and I had no  
performance problems when that was the case. There is 1152MB of RAM  
on this box, half of which is in use. Solaris 10 FCS + all the latest  
patches as of today.

So anyway, after moving mysql to live on zfs (with compression turned  
on for the volume in question), I noticed that web pages on my site  
took a bit of time, sometimes up to 20 seconds to load. I''d jump on  
to my X1, and notice that according to top, kernel was hogging  
80-100% of the 500Mhz CPU, and mysqld was the top process in CPU use.  
The load average would shoot from a normal 0.something up to 6 or  
even 8. Command-line response was stop and go.

Then I''d notice my page would finally load, and that corresponded  
with load and kernel CPU usage decreasing back to normal levels.

I am able to reliably replicate this, and I ran lockstat while this  
was going on, the output of which is here:

     http://elektronkind.org/osol/lockstat-zfs-0.txt

Part of me is kind of sure that this is 6421427 as there appears to  
be long and copious trips through ata_wait() as that bug illustrates,  
but I just want to be sure of it (and when is that bug seeing a  
solaris 10 patch, btw?)

TIA,
/dale

Hi Dale,

Are you using MyISAM or InnoDB? Also, what''s your zpool configuration?

Best Regards,
Jason

On 12/7/06, Dale Ghent <daleg at elemental.org>
wrote:
>
> Hey all, I run a netra X1 as the mysql db server for my small
> personal web site. This X1 has two drives in it with SVM-mirrored UFS
> slices for / and /var, a swap slice, and slice 7 is zfs. There is one
> zfs mirror pool called "local" on which there are a few file
systems,
> one of which is for mysql. slice 7 used to be ufs, and I had no
> performance problems when that was the case. There is 1152MB of RAM
> on this box, half of which is in use. Solaris 10 FCS + all the latest
> patches as of today.
>
> So anyway, after moving mysql to live on zfs (with compression turned
> on for the volume in question), I noticed that web pages on my site
> took a bit of time, sometimes up to 20 seconds to load. I''d jump
on
> to my X1, and notice that according to top, kernel was hogging
> 80-100% of the 500Mhz CPU, and mysqld was the top process in CPU use.
> The load average would shoot from a normal 0.something up to 6 or
> even 8. Command-line response was stop and go.
>
> Then I''d notice my page would finally load, and that corresponded
> with load and kernel CPU usage decreasing back to normal levels.
>
> I am able to reliably replicate this, and I ran lockstat while this
> was going on, the output of which is here:
>
>      http://elektronkind.org/osol/lockstat-zfs-0.txt
>
> Part of me is kind of sure that this is 6421427 as there appears to
> be long and copious trips through ata_wait() as that bug illustrates,
> but I just want to be sure of it (and when is that bug seeing a
> solaris 10 patch, btw?)
>
> TIA,
> /dale
> _______________________________________________
> zfs-discuss mailing list
> zfs-discuss at opensolaris.org
> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
>

You said you are running Solaris 10 FCS but zfs was not released until
Solaris 10 6/06 which is Solaris 10U2.

On 12/7/06, Jason J. W. Williams <jasonjwwilliams at gmail.com>
wrote:
>
> Hi Dale,
>
> Are you using MyISAM or InnoDB? Also, what''s your zpool
configuration?
>
> Best Regards,
> Jason
>
> On 12/7/06, Dale Ghent <daleg at elemental.org> wrote:
> >
> > Hey all, I run a netra X1 as the mysql db server for my small
> > personal web site. This X1 has two drives in it with SVM-mirrored UFS
> > slices for / and /var, a swap slice, and slice 7 is zfs. There is one
> > zfs mirror pool called "local" on which there are a few file
systems,
> > one of which is for mysql. slice 7 used to be ufs, and I had no
> > performance problems when that was the case. There is 1152MB of RAM
> > on this box, half of which is in use. Solaris 10 FCS + all the latest
> > patches as of today.
> >
> > So anyway, after moving mysql to live on zfs (with compression turned
> > on for the volume in question), I noticed that web pages on my site
> > took a bit of time, sometimes up to 20 seconds to load. I''d
jump on
> > to my X1, and notice that according to top, kernel was hogging
> > 80-100% of the 500Mhz CPU, and mysqld was the top process in CPU use.
> > The load average would shoot from a normal 0.something up to 6 or
> > even 8. Command-line response was stop and go.
> >
> > Then I''d notice my page would finally load, and that
corresponded
> > with load and kernel CPU usage decreasing back to normal levels.
> >
> > I am able to reliably replicate this, and I ran lockstat while this
> > was going on, the output of which is here:
> >
> >      http://elektronkind.org/osol/lockstat-zfs-0.txt
> >
> > Part of me is kind of sure that this is 6421427 as there appears to
> > be long and copious trips through ata_wait() as that bug illustrates,
> > but I just want to be sure of it (and when is that bug seeing a
> > solaris 10 patch, btw?)
> >
> > TIA,
> > /dale
> > _______________________________________________
> > zfs-discuss mailing list
> > zfs-discuss at opensolaris.org
> > http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
> >
> _______________________________________________
> zfs-discuss mailing list
> zfs-discuss at opensolaris.org
> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://mail.opensolaris.org/pipermail/zfs-discuss/attachments/20061207/8c604f0a/attachment.html>

This does look like the ATA driver bug rather than a ZFS issue per se.

(For the curious, the reason ZFS triggers this when UFS doesn''t is
because ZFS sends a synchronize cache command to the disk, which is not handled
in DMA mode by the controller; and for this particular controller, switching
between DMA and PIO mode has some quirks which were worked around by adding
delays. The fix involves a new quirk-work-around.)

Anton
 
 
This message posted from opensolaris.org

On Dec 7, 2006, at 1:46 PM, Jason J. W. Williams wrote:
> Hi Dale,
>
> Are you using MyISAM or InnoDB?
InnoDB.
> Also, what''s your zpool configuration?
A basic mirror:

[daleg at xenon]~>zpool status
   pool: local
state: ONLINE
scrub: none requested
config:

         NAME          STATE     READ WRITE CKSUM
         local         ONLINE       0     0     0
           mirror      ONLINE       0     0     0
             c0t0d0s7  ONLINE       0     0     0
             c0t2d0s7  ONLINE       0     0     0

errors: No known data errors

That''s gotta be what it is. All our MySQL IOP issues have gone away
one we moved to RAID-1 from RAID-Z.

-J

On 12/7/06, Anton B. Rang <Anton.Rang at sun.com>
wrote:
> This does look like the ATA driver bug rather than a ZFS issue per se.
>
> (For the curious, the reason ZFS triggers this when UFS doesn''t is
because ZFS sends a synchronize cache command to the disk, which is not handled
in DMA mode by the controller; and for this particular controller, switching
between DMA and PIO mode has some quirks which were worked around by adding
delays. The fix involves a new quirk-work-around.)
>
> Anton
>
>
> This message posted from opensolaris.org
> _______________________________________________
> zfs-discuss mailing list
> zfs-discuss at opensolaris.org
> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
>

On Dec 7, 2006, at 5:22 PM, Nicholas Senedzuk wrote:
> You said you are running Solaris 10 FCS but zfs was not released  
> until Solaris 10 6/06 which is Solaris 10U2.
Look at a Solaris 10 6/06 CD/DVD. Check out the Solaris_10/ 
UpgradePatches directory.

ah! well whaddya know...

Yes, apply those (you have to do them in the right order to do it in  
one run with ''patchadd -M'') and you can bring your older box
up to
date with the update release.

/dale

On Dec 7, 2006, at 6:14 PM, Anton B. Rang wrote:
> This does look like the ATA driver bug rather than a ZFS issue per se.
Yes indeed. Well, that answers that. FWIW, I''m hour 2 of a mysql  
configure script run. Yow!
> (For the curious, the reason ZFS triggers this when UFS doesn''t is
> because ZFS sends a synchronize cache command to the disk, which is  
> not handled in DMA mode by the controller; and for this particular  
> controller, switching between DMA and PIO mode has some quirks  
> which were worked around by adding delays. The fix involves a new  
> quirk-work-around.)
Ah, so I suppose this would affect the V100, too. The same ALi IDE  
controller in that box.

Thanks for the insight. Since the fix for this made it into snv_52, I  
suppose it''s too recent for a backport and patch release for s10 :(

/dale

Hi Dale,

For what its worth, the SX releases tend to be pretty stable. I''m not
sure if snv_52 has made a SX release yet. We ran for over 6 months on
SX 10/05 (snv_23) with no downtime.

Best Regards,
Jason

On 12/7/06, Dale Ghent <daleg at elemental.org>
wrote:
> On Dec 7, 2006, at 6:14 PM, Anton B. Rang wrote:
>
> > This does look like the ATA driver bug rather than a ZFS issue per se.
>
> Yes indeed. Well, that answers that. FWIW, I''m hour 2 of a mysql
> configure script run. Yow!
>
> > (For the curious, the reason ZFS triggers this when UFS
doesn''t is
> > because ZFS sends a synchronize cache command to the disk, which is
> > not handled in DMA mode by the controller; and for this particular
> > controller, switching between DMA and PIO mode has some quirks
> > which were worked around by adding delays. The fix involves a new
> > quirk-work-around.)
>
> Ah, so I suppose this would affect the V100, too. The same ALi IDE
> controller in that box.
>
> Thanks for the insight. Since the fix for this made it into snv_52, I
> suppose it''s too recent for a backport and patch release for s10
:(
>
> /dale
> _______________________________________________
> zfs-discuss mailing list
> zfs-discuss at opensolaris.org
> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
>