SAN Performance with HP-UX 11.31 and Sybase

[John Everitt]

System: RX8640 running 11.31 with Sybase 15.0.1 attached to EVA8000 through dual HBAs (AB378-60101 4GB Single Port).

Problem: I'm having some performance issues with Sybase which is indicating that it is spending significant amounts of time waiting for disk io. I'm using raw partitions for database data and log areas and vxfs mounted filesystems for the tempdb data and log area. The async driver is also installed. I have had Sybase check out their server and they suggest io is slow. When I do a sar -H, I get ...

cptux12/sybase/data/tdbdata>sar -H 5 5

HP-UX cptux12 B.11.31 U ia64 03/03/08

11:46:40 ctlr util t-put IO/s r/s w/s read write avque avwait avse
rv
%age MB/s num num num MB/s MB/s num msec ms
ec
11:46:45 fcd0 93 0.59 249 228 21 0.45 0.14 1 0
9
fcd1 94 0.60 249 228 21 0.45 0.15 1 0
10
11:46:50 mpt0 100 0.04 7 2 4 0.00 0.03 1 0
2
mpt2 100 0.04 6 2 4 0.00 0.03 1 0
2
fcd0 98 0.55 231 214 17 0.42 0.13 1 0
27
fcd1 97 0.55 231 214 17 0.42 0.13 1 0
24
11:46:55 fcd0 87 0.59 256 239 17 0.47 0.12 1 0
13
fcd1 87 0.60 256 238 18 0.46 0.14 1 0
11
11:47:00 fcd0 68 0.40 198 191 7 0.39 0.01 1 0
6
fcd1 65 0.41 198 191 7 0.39 0.01 1 0
5
11:47:05 fcd0 81 0.86 354 319 35 0.63 0.23 1 0
6
fcd1 79 0.87 354 319 36 0.63 0.24 1 0
6

Average fcd0 85 0.60 258 238 20 0.47 0.13 1 0
12
Average fcd1 84 0.61 258 238 20 0.47 0.13 1 0
11
Average mpt0 100 0.01 1 0 1 0.00 0.01 1 0
2
Average mpt2 100 0.01 1 0 1 0.00 0.01 1 0
2

I get the impression that the HBAs are suggesting they are very busy but are not actually getting much throughput and the SAN switch is idle.

Does anyone have any ideas ?

Many thanks.

[Steven E. Protter]

Shalom,

SAN performance is highly dependent on disk configuration.

If there is a significant amount of writing that needs to be done to disks and its not sequential that data and index and temporary write areas should be on raid 1 or raid 10 disk.

This significantly improves write performance.

If its a read only data mine than raid 5 is sufficient, but you probably would not have this thread posted if that was the case.

SEP

[John Everitt]

SEP,

Our data areas are on RAID-5 while the logs are generally on RAID-1 (I say generally as it's not always easy to restore Sybase databases so that the log continues to be on the log devices and the data on the data devices).

We also tend to do 90% read / 10% write as a rule of thumb and this is with Sybase cache performing well (~99% cache hit rate).

Is there any reason why the HBAs would work so hard to achieve what appears to be so little ? This may well be perfectly normal but it just looks weird to me ... I am a DBA, so my definition of weird tends to differ from most other people's.

John

[Ritter Philippe]

Hello,

I have the same problem here, with Sybase 15.0.2, and also on an Itanium Server with 11.31.

Have you found a solution ? For me, the problem seems to come from how the filesystem are mounted with VxFS. I opened a case, but no solution yet.

My logs are in raw device, and the data in file system. But I see with glance, that the dataserver process is waiting on the Cache, and that the write are not asynchrone. But with Sybase 12.5.3 and HP-UX 11.11, I have really good performance.
Thanks

[John Everitt]

Ritter,

After lots of tinkering and discussion with both Sybase and HP, I don't have a complete solution but a list of things to check and recommendations for running Sybase on HP-UX. These are not in any particular order, I would just go through them one by one, see if they're relevant to your situation and test to see if it improves your performance.

1) On HP-UX, the recommendation from Sybase is to only use raw partitions for your database log and data AND tempdb log and data. You rightly point out issues with mount options on Vxfs and asynchronous support. You can tinker with Vxfs to see if you can get better performance (expecially using directio on 11.31) but Sybase seem to suggest that on HP-UX you are better off just going with raw for just about everything (except system databases of course).

2) If you have the possibility investigate using a larger page size. 4K or even 8K could well give you better IO performance. The downside of a larger page size is the Sybase will require more memory especially for procedure cache and database caches.

3) Check your SAN firmware levels. We have since found that our EVA was on firmware that was 18 months old and not only had performance issues but also had a problem where in some circumstances data corruption could take place. Our switches were also out of date.

4) Make sure your HBA's are in the PCI-X slots. We found one of our HBA's in a 133MHz slot and not a 266MHz. Use fcmsutil with the vpd option to find out what bus speed you HBA is running at.

5) Add a named cache for your database logs with at least a 4k pool. This should increase the i/o chunk size when reading / writing to your transaction logs.

6) Try using a large i/o pool on your tempdb cache. If you don't have a tempdb cache, add one, and if possible make sure that it is sized so that it is rarely 100% utilised. Sybase 15.0 I believe is a big user of tempdb and giving it a nice big cache should help.

7) Make sure your procedure cache is big enough. Again Sybase 15 is a big user of procedure cache so make sure this is sized appropriately.

8) Turn off auditing and monitoring to see if this makes any difference. I'm a big fan of MDA tables but some parts of it can really slow your server down. Turn it all off, see if performance improves then selectively turn bits of it back on and monitor the degradation. I know the pipe stuff can really put the brakes on Sybase ("error log pipe", "sql text pipe", etc).

9) Oh, back to the EVA, make sure your DG's are not getting more io's than recommended for your spindle count. We had a DG (for our logs) that only had 16 spindles. HP took a look and we were doing 4x as many ios to that DG than was recommended for a DG of 16 spindles. We are in the process of balancing the ios across DGs now.

10) Another SAN thing is to use active / active multi-pathing if possible and to ensure that your multi-pathing software balances across all paths.

11) Up your scsi queue depth on all your san devices to 32 or 64 (depending on the speed of your san disk system). We have an EVA 8000 and 64 seems to work pretty well. Definately better than the default of 8 anyway. Use the scsimgr HPUX command to adjust the queue depths. Use gpm to monitor queue lengths ... you really should see very little queueing at the OS level.

This should keep you busy for a while. I am continuing my investigation as I haven't completely solved our problem yet and will pass on any further advice.

I still don't know what causes our HBA's to be so busy though, so if there are any HP-UX gurus out there that explain to me why my HBAs are so busy doing so little, can they please let me know. Thanks !!!

Regards,

John

[Ritter Philippe]

Hi John,

Thanks very much for your recommandation. I will look closely at all !

Kind regards
Philippe RITTER