Hi All,
Is there a way to tune the zfs prefetch on a per pool basis? I have a
customer that is seeing slow performance on a pool the contains multiple
tablespaces from an Oracle database, looking at the LUNs associated to
that pool they are constantly at 80% - 100% busy. Looking at the output
from arcstat for the miss % on data, prefetch and metadata we are
getting around 5 - 10 % on data, 50 - 70 % on prefetch and 0% on
metadata. I am thinking that the majority of the prefetch misses are
due to the tablespace data files.
The configuration of the system is as follows
Sun Fire X4600 M2 8 x 2.3 GHz Quad Core Processor, 256GB Memory
Solaris 10 Update 7
ZFS Arc cache max set to 85GB
4 Zpools configured from a 6540 Storage array
* apps - single LUN (raid 5) recordsize set to 128k, from the array,
pool contains binaries and application files
* backup - 8 LUNs (varying sizes all from a 6180 array with SATA
disks) used for storing oracle dumps
* data - 5 LUNs (Raid 10 6 physical drives) recordsize set to 8k,
used for Oracle data files
* logs - single LUN (raid 10 from 6 physical drives) recordsize set
to 128k, used for Oracle redo log files, temp db, undo db and
control files.
18 Solaris 10 zones, of which 12 of these are oracle zones sharing the
data and logs pools.
I think that the prefetch will be useful on the apps and backup pools,
however I think that on the data and logs pools this could be causing
issues with the amount of IO that is being caused by the prefetch and
the amount that it is missing in the arcstats could be the reason why
the devices are at 100% busy. Is there a way to turn the prefetch off
for just a single pool? Also is this something that can be done online
or will it require a reboot to put into effect.
Thanks in advance for your assistance in this matter.
Regards
Tony
--
Oracle <http://www.oracle.com>
Tony Marshall | Technical Architect
Phone: +44 118 924 9516 <tel:+44%20118%20924%209516> | | | Mobile: +44
7765 898570 <tel:+44%207765%20898570>
Oracle Remote Operations Management
United Kingdom
ORACLE Corporation UK Ltd is a company incorporated in England & Wales |
Company Reg. No. 1782505 | Reg. office: Oracle Parkway, Thames Valley
Park, Reading RG6 1RA
Green Oracle <http://www.oracle.com/commitment> Oracle is committed to
developing practices and products that help protect the environment
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://mail.opensolaris.org/pipermail/zfs-discuss/attachments/20101209/fa16f1b1/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: oracle_sig_logo.gif
Type: image/gif
Size: 658 bytes
Desc: not available
URL:
<http://mail.opensolaris.org/pipermail/zfs-discuss/attachments/20101209/fa16f1b1/attachment-0002.gif>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: green-for-email-sig_0.gif
Type: image/gif
Size: 356 bytes
Desc: not available
URL:
<http://mail.opensolaris.org/pipermail/zfs-discuss/attachments/20101209/fa16f1b1/attachment-0003.gif>
Hello Tony, If the hardware hasn''t changed I''d look at the workload on the database server. If the customer is taking regular statspack snapshots they might be able to see whats causing the extra activity. They can use AWR or the diagnostic pack, if they are licensed, to see the offending SQL or PL/SQL or any hot objects. However if you want to tune at the ZFS level then the following has some advice for ZFS and databases http://www.solarisinternals.com/wiki/index.php/ZFS_for_Databases. On 9 December 2010 15:48, Tony Marshall <tony.marshall at oracle.com> wrote:> Hi All, > > Is there a way to tune the zfs prefetch on a per pool basis? I have a > customer that is seeing slow performance on a pool the contains multiple > tablespaces from an Oracle database, looking at the LUNs associated to that > pool they are constantly at 80% - 100% busy. Looking at the output from > arcstat for the miss % on data, prefetch and metadata we are getting around > 5 - 10 % on data, 50 - 70 % on prefetch and 0% on metadata. I am thinking > that the majority of the prefetch misses are due to the tablespace data > files. > > The configuration of the system is as follows > > Sun Fire X4600 M2 8 x 2.3 GHz Quad Core Processor, 256GB Memory > Solaris 10 Update 7 > ZFS Arc cache max set to 85GB > 4 Zpools configured from a 6540 Storage array > > - apps - single LUN (raid 5) recordsize set to 128k, from the array, > pool contains binaries and application files > - backup - 8 LUNs (varying sizes all from a 6180 array with SATA disks) > used for storing oracle dumps > - data - 5 LUNs (Raid 10 6 physical drives) recordsize set to 8k, used > for Oracle data files > - logs - single LUN (raid 10 from 6 physical drives) recordsize set to > 128k, used for Oracle redo log files, temp db, undo db and control files. > > 18 Solaris 10 zones, of which 12 of these are oracle zones sharing the data > and logs pools. > > I think that the prefetch will be useful on the apps and backup pools, > however I think that on the data and logs pools this could be causing issues > with the amount of IO that is being caused by the prefetch and the amount > that it is missing in the arcstats could be the reason why the devices are > at 100% busy. Is there a way to turn the prefetch off for just a single > pool? Also is this something that can be done online or will it require a > reboot to put into effect. > > Thanks in advance for your assistance in this matter. > > Regards > Tony > -- > [image: Oracle] <http://www.oracle.com> > Tony Marshall | Technical Architect > Phone: +44 118 924 9516 <tel:+44%20118%20924%209516> | | | Mobile: +44 > 7765 898570 <tel:+44%207765%20898570> > Oracle Remote Operations Management > United Kingdom > > ORACLE Corporation UK Ltd is a company incorporated in England & Wales | > Company Reg. No. 1782505 | Reg. office: Oracle Parkway, Thames Valley Park, > Reading RG6 1RA > [image: Green Oracle] <http://www.oracle.com/commitment> Oracle is > committed to developing practices and products that help protect the > environment > > _______________________________________________ > zfs-discuss mailing list > zfs-discuss at opensolaris.org > http://mail.opensolaris.org/mailman/listinfo/zfs-discuss > >-- Thanks A Jabbar Azam -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://mail.opensolaris.org/pipermail/zfs-discuss/attachments/20101209/3dba1dd9/attachment.html>
I''ve also found this http://developers.sun.com/solaris/docs/wp-oraclezfsconfig-0510_ds_ac2.pdf On 9 December 2010 20:22, Jabbar <ajazam at gmail.com> wrote:> Hello Tony, > > If the hardware hasn''t changed I''d look at the workload on the database > server. If the customer is taking regular statspack snapshots they might be > able to see whats causing the extra activity. They can use AWR or the > diagnostic pack, if they are licensed, to see the offending SQL or PL/SQL or > any hot objects. > > However if you want to tune at the ZFS level then the following has some > advice for ZFS and databases > http://www.solarisinternals.com/wiki/index.php/ZFS_for_Databases. > > On 9 December 2010 15:48, Tony Marshall <tony.marshall at oracle.com> wrote: > >> Hi All, >> >> Is there a way to tune the zfs prefetch on a per pool basis? I have a >> customer that is seeing slow performance on a pool the contains multiple >> tablespaces from an Oracle database, looking at the LUNs associated to that >> pool they are constantly at 80% - 100% busy. Looking at the output from >> arcstat for the miss % on data, prefetch and metadata we are getting around >> 5 - 10 % on data, 50 - 70 % on prefetch and 0% on metadata. I am thinking >> that the majority of the prefetch misses are due to the tablespace data >> files. >> >> The configuration of the system is as follows >> >> Sun Fire X4600 M2 8 x 2.3 GHz Quad Core Processor, 256GB Memory >> Solaris 10 Update 7 >> ZFS Arc cache max set to 85GB >> 4 Zpools configured from a 6540 Storage array >> >> - apps - single LUN (raid 5) recordsize set to 128k, from the array, >> pool contains binaries and application files >> - backup - 8 LUNs (varying sizes all from a 6180 array with SATA >> disks) used for storing oracle dumps >> - data - 5 LUNs (Raid 10 6 physical drives) recordsize set to 8k, >> used for Oracle data files >> - logs - single LUN (raid 10 from 6 physical drives) recordsize set to >> 128k, used for Oracle redo log files, temp db, undo db and control files. >> >> 18 Solaris 10 zones, of which 12 of these are oracle zones sharing the >> data and logs pools. >> >> I think that the prefetch will be useful on the apps and backup pools, >> however I think that on the data and logs pools this could be causing issues >> with the amount of IO that is being caused by the prefetch and the amount >> that it is missing in the arcstats could be the reason why the devices are >> at 100% busy. Is there a way to turn the prefetch off for just a single >> pool? Also is this something that can be done online or will it require a >> reboot to put into effect. >> >> Thanks in advance for your assistance in this matter. >> >> Regards >> Tony >> -- >> [image: Oracle] <http://www.oracle.com> >> Tony Marshall | Technical Architect >> Phone: +44 118 924 9516 <tel:+44%20118%20924%209516> | | | Mobile: +44 >> 7765 898570 <tel:+44%207765%20898570> >> Oracle Remote Operations Management >> United Kingdom >> >> ORACLE Corporation UK Ltd is a company incorporated in England & Wales | >> Company Reg. No. 1782505 | Reg. office: Oracle Parkway, Thames Valley Park, >> Reading RG6 1RA >> [image: Green Oracle] <http://www.oracle.com/commitment> Oracle is >> committed to developing practices and products that help protect the >> environment >> >> _______________________________________________ >> zfs-discuss mailing list >> zfs-discuss at opensolaris.org >> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss >> >> > > > -- > Thanks > > A Jabbar Azam > >-- Thanks A Jabbar Azam -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://mail.opensolaris.org/pipermail/zfs-discuss/attachments/20101209/a4fd69aa/attachment.html> -------------- next part -------------- A non-text attachment was scrubbed... Name: not available Type: image/gif Size: 356 bytes Desc: not available URL: <http://mail.opensolaris.org/pipermail/zfs-discuss/attachments/20101209/a4fd69aa/attachment.gif> -------------- next part -------------- A non-text attachment was scrubbed... Name: not available Type: image/gif Size: 658 bytes Desc: not available URL: <http://mail.opensolaris.org/pipermail/zfs-discuss/attachments/20101209/a4fd69aa/attachment-0001.gif>
Hi
I''d certainly look at the sql being run, examine the explain plan and
in
particular SQL_TRACE, TIMED_STATISTICS, and TKPROF, these will really
highlight issues.
see following for autotrace which can generate explain plan etc.
http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/autotrac.htm
then the following can really help
SQL>alter session set sql_trace=true;
run sql
SQL>alter session set sql_trace=false ( this si very important as it
closes the trace session )
SQL>show parameters show parameters user_dump_dest
.... location of output from sql trace
go to user dump dest
you wills ee somethign like
${ORACLE_SID}_ora_6919.trc
tkprof ${ORACLE_SID}_ora_6919.trc 6919.trc explain=scott/tiger sys=no
ie explain=schema owner and passwrd, if unsure just run
tkprof ${ORACLE_SID}_ora_6919.trc 6919.trc
this can provide some very informative info, ie unseen ora errors from
user functions and so on.
read the following to get an idea of how to get at the problematic SQL
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_1016.htm#i26072
I had an interesting issue the other day, where a tablespace was nearing
100% full on a test DB that isn''t properly monitored, and queries
stated
to run really really slow.
Enda
On 09/12/2010 20:22, Jabbar wrote:> Hello Tony,
>
> If the hardware hasn''t changed I''d look at the workload
on the database
> server. If the customer is taking regular statspack snapshots they might
> be able to see whats causing the extra activity. They can use AWR or the
> diagnostic pack, if they are licensed, to see the offending SQL or
> PL/SQL or any hot objects.
>
> However if you want to tune at the ZFS level then the following has some
> advice for ZFS and databases
> http://www.solarisinternals.com/wiki/index.php/ZFS_for_Databases.
>
> On 9 December 2010 15:48, Tony Marshall <tony.marshall at oracle.com
> <mailto:tony.marshall at oracle.com>> wrote:
>
> Hi All,
>
> Is there a way to tune the zfs prefetch on a per pool basis? I have
> a customer that is seeing slow performance on a pool the contains
> multiple tablespaces from an Oracle database, looking at the LUNs
> associated to that pool they are constantly at 80% - 100% busy.
> Looking at the output from arcstat for the miss % on data, prefetch
> and metadata we are getting around 5 - 10 % on data, 50 - 70 % on
> prefetch and 0% on metadata. I am thinking that the majority of the
> prefetch misses are due to the tablespace data files.
>
> The configuration of the system is as follows
>
> Sun Fire X4600 M2 8 x 2.3 GHz Quad Core Processor, 256GB Memory
> Solaris 10 Update 7
> ZFS Arc cache max set to 85GB
> 4 Zpools configured from a 6540 Storage array
>
> * apps - single LUN (raid 5) recordsize set to 128k, from the
> array, pool contains binaries and application files
> * backup - 8 LUNs (varying sizes all from a 6180 array with SATA
> disks) used for storing oracle dumps
> * data - 5 LUNs (Raid 10 6 physical drives) recordsize set to
> 8k, used for Oracle data files
> * logs - single LUN (raid 10 from 6 physical drives) recordsize
> set to 128k, used for Oracle redo log files, temp db, undo db
> and control files.
>
> 18 Solaris 10 zones, of which 12 of these are oracle zones sharing
> the data and logs pools.
>
> I think that the prefetch will be useful on the apps and backup
> pools, however I think that on the data and logs pools this could be
> causing issues with the amount of IO that is being caused by the
> prefetch and the amount that it is missing in the arcstats could be
> the reason why the devices are at 100% busy. Is there a way to turn
> the prefetch off for just a single pool? Also is this something that
> can be done online or will it require a reboot to put into effect.
>
> Thanks in advance for your assistance in this matter.
>
> Regards
> Tony
> --
> Oracle <http://www.oracle.com>
> Tony Marshall | Technical Architect
> Phone: +44 118 924 9516 <tel:+44%20118%20924%209516> | | |
Mobile:
> +44 7765 898570 <tel:+44%207765%20898570>
> Oracle Remote Operations Management
> United Kingdom
>
> ORACLE Corporation UK Ltd is a company incorporated in England &
> Wales | Company Reg. No. 1782505 | Reg. office: Oracle Parkway,
> Thames Valley Park, Reading RG6 1RA
> Green Oracle <http://www.oracle.com/commitment> Oracle is
committed
> to developing practices and products that help protect the environment
>
> _______________________________________________
> zfs-discuss mailing list
> zfs-discuss at opensolaris.org <mailto:zfs-discuss at
opensolaris.org>
> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
>
>
>
>
> --
> Thanks
>
> A Jabbar Azam
>
>
>
> _______________________________________________
> zfs-discuss mailing list
> zfs-discuss at opensolaris.org
> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss