Performance question

[Stanislav Markovic]

We have a data warehouse-type app running on an HPN4000, ~1TB of data stored on an XP512, CU in RAID5, Open9. LUNs were assigned properly, making sure that the load is split btw all ACP pairs. However, initially when LVs were created, they were not striped across all available LUNs, but were created and extended with essentially the default options. Since we thought that striping is certainly the proper way to go, we decided to move all data to another area of XP and recreate the LVs. The options we used this time with lvcreate were -r n -s g -D y. PE size remained the default, 4MB. Then the DBAs started moving the data. This process lasted several days, and they observed the following over the time:

1. Report generation speed greatly improved.
2. Data loading speed greatly decreased.

The latter is causing grave concern since it currently takes us more than 1 h to load 1 h worth of real-time data (previously ~20min). We are only able to catch up during the night, when fewer records are created.

Since Oracle config otherwise didn't change at all, I am assuming that this is an appropriate forum for this problem.

Any suggestions will be greatly appreciated!

-- Stanislav

[GOLDSTEIN Marc]

We had a perf problem on a HP3000 last year with RAID5.
The solutions we used were:
- use maximum cache memory in the disk box,
- use raid 1 most of the time,
- use 8 LDEV instead of just 3.
This greatly improve perf in both areas, reading and writing.
Hope this will help.
Good luck!!!

[Stanislav Markovic]

Marc,

- What do you mean by "using maximum cache"? Our XP has the maximum ammount installed (32GB). Although theoretically we could move the internalborder to allow more room for write than for read cache, we were told by HP, essentially, "don't try this at home".
- Unfortunately size of DB prevents us to use RAID 1. It was a business decision.
- I don't see how you got the impression that we use 3 LDEVs. The DB size is, as I said, ~1TB, and we are in fact using 7*31 = 217 LDEVs.

[Mark van Silfhout]

It would be very interesting to install Performance Advisor (you can download a try and buy version) to see whether there's one (or maybe a couple) hotspots which you can eliminate, or whether the performance in general is has gone down (I think you've created some hotspots after the striping...)
Did you create the LUSES in dispersed mode (or were the arraygroups formatted in dispersed mode)?

hope this helps,

Mark

[Stanislav Markovic]

Mark,

Performance assessment is part of the regular process in our company. I am therefore posting the question on this forum as a complement to that.

Oh, and we don't use LUSEs at all, and I don't know how the array groups were formatted when the LDEVs were created. That process is outsourced to HP completely.

[Gadura Praveen]

I am runing datawarehousing application on the N-Class and XP512. We get excellent performance and the only difference is that I have 8 GB cache and all the disks are configured in RAID 1.
My 2 cents :-
1) Make sure that you have not created a logical volume , which is stripped accross LUNs in the same RAID group.I use the -i and -I options, with lvcreate, which gives better control on the stripe size.( man lvcreate).
2) Are you using raw logical volumes or filesystems for the database ? If you are are using VXFS fileystem , try mounting the filesystems ( only the database filesystems ) with "mincache=direct" and "convosync=direct" option. These options help bypassing the filesystem buffer cache and the IO's are done directly using Oracle's buffer cache.
3) If you use Glance/Perfview or sar , during data load do you see any resource bottleneck?
Low memory , high priority queue on processor or disk subsystem queue can slow down the server.

Hope this helps!

Praveen

[Stanislav Markovic]

Gadura,
As I prepared to implement your recommendations, DBAs discovered that the loss of performance was actually due to DB corruption, and the implementation was cancelled (this is a production system). As soon as it was corrected, we got excellent performance.
I did, however, implement them on one of our test systems and will monitor the performance very closely. In any case, thanks a lot!