Anybody seen any performance changes with going to oracle 8.1.7 (from 8.0.5) with the databases on netapp storage? (host is sun enterprise 5000, solaris 2.6).
Many batch jobs ran dramatically slower on a DB that was upgraded from 8.0 to 8.1. Fingers are getting pointed to the netapp, partly 'cause nobody wants to contemplate down-revving the database. The fact that the jobs ran fine on the netapp before the upgrade would seem to exonerate it but it's a convenient target...
A few efforts at tuning the DB seemed to reclaim much of the loss; I'm no oracle expert but it sounds to me like the DB is far from optimal, for example it's set to a 2k block size which seems questionable to me.
Anyway - anyone encountered anything similar?
Chris Moll Lawrence Berkeley Laboratory
Chris,
I have experience with SUN E3500s going over 100MBit to a NetApp F760. We run pretty much only the latest release of 8.1.7 in various compatibility modes. Listener problems....intermedia problems....etc in earlier versions.
Yes, we use 8K blocks. Has anyone run snoop to see if there is excessive calling to DNS for a lookup of the filer. That is something I saw recently.
Is the Network dedicated to the NetApp? Are you on Gigabit Ethernet?
Also, I think that 8.1.7 is better on Solaris 2.8.
Anyway, as far as the NetApp goes, I have seen close to local (SUN) RAID 0 performance over 100MBit and twice that fast over Gig.
I have done a bit of tuning on an E5000 also. I would be interested in continuing this thread with you.
I think some IT folks from my group were up in Livermore a couple of weeks ago.
Joe
At 04:46 PM 5/10/02 -0700, Chris Moll wrote:
Anybody seen any performance changes with going to oracle 8.1.7 (from 8.0.5) with the databases on netapp storage? (host is sun enterprise 5000, solaris 2.6).
Many batch jobs ran dramatically slower on a DB that was upgraded from 8.0 to 8.1. Fingers are getting pointed to the netapp, partly 'cause nobody wants to contemplate down-revving the database. The fact that the jobs ran fine on the netapp before the upgrade would seem to exonerate it but it's a convenient target...
A few efforts at tuning the DB seemed to reclaim much of the loss; I'm no oracle expert but it sounds to me like the DB is far from optimal, for example it's set to a 2k block size which seems questionable to me.
Anyway - anyone encountered anything similar?
Chris Moll Lawrence Berkeley Laboratory
Joseph Bishop wrote:
.........
Yes, we use 8K blocks. Has anyone run snoop to see if there is excessive calling to DNS for a lookup of the filer. That is something I saw recently.
We've seen aberant DNS behaviour certainly - the worst being misconfigured DHCP hanging mountd on the netapp. Looks ok now though.
Is the Network dedicated to the NetApp? Are you on Gigabit Ethernet?
yes (100Mbit), no (about to switch to gigE)
Also, I think that 8.1.7 is better on Solaris 2.8.
interesting, the newest machines are 2.8 but at the moment the main workhorses are still 2.6.
Latest developments: after various efforts at tuning the DBs, it now appears that for most jobs the performance difference has been more than made up for. In fact one big job runs somewhat faster on the netapp than on local disk... There was one remaining pathalogical job, but just today one of the programmers discovered an SQL change dramatically sped the job up.
So in summary: it appears to me that oracle 8.1.7 has changes to it that may cause worse performance for poorly configured DBs compared to oracle 8.0.5, especially on the netapp. A bit of standard Oracle tuning wipes out the difference and brings the netapp back much closer to local disk performance (with 100MBit). But in certain cases SQL changes were the cure (always a waste tweaking performance parameters when the application is doing something grossly inefficient...)
Chris Moll Lawrence Berkeley Laboratory
At 10:27 AM 5/16/02 -0700, Chris Moll wrote:
Joseph Bishop wrote:
.........
Yes, we use 8K blocks. Has anyone run snoop to see if there is excessive calling to DNS for a lookup of the filer. That is something I saw recently.
We've seen aberant DNS behaviour certainly - the worst being misconfigured DHCP hanging mountd on the netapp. Looks ok now though.
I would not have DHCP on an NFS machine. Only fixed IP.
Is the Network dedicated to the NetApp? Are you on Gigabit Ethernet?
yes (100Mbit), no (about to switch to gigE)
That will bump your database performance by 100-200%
Also, I think that 8.1.7 is better on Solaris 2.8.
interesting, the newest machines are 2.8 but at the moment the main workhorses are still 2.6.
Latest developments: after various efforts at tuning the DBs, it now appears that for most jobs the performance difference has been more than made up for. In fact one big job runs somewhat faster on the netapp than on local disk... There was one remaining pathalogical job, but just today one of the programmers discovered an SQL change dramatically sped the job up.
So in summary: it appears to me that oracle 8.1.7 has changes to it that may cause worse performance for poorly configured DBs compared to oracle 8.0.5, especially on the netapp. A bit of standard Oracle tuning wipes out the difference and brings the netapp back much closer to local disk performance (with 100MBit). But in certain cases SQL changes were the cure (always a waste tweaking performance parameters when the application is doing something grossly inefficient...)
Yep, SQL can be a hog. I had a query once that ran for over a minute. Problem was our application needed it to complete in less than 2 seconds. After reworking the query into a stored procedure with 2 queries, we got to .3 seconds.
Chris Moll Lawrence Berkeley Laboratory
Nice chatting with you. Any help I can give ....you can have.
Joe
-
Chris Moll -
Joseph Bishop