Disk and VxFS performance tuning for Sybase tempdb devices

[Mladen Despic]

Sybase Server (ASE 12.0), running under HP-UX 11.00, is experiencing performance degradation due to a high level of disk i/o contention on the two "tempdb" devices: tempdb.dat and tempdb.log.
While all other Sybase devices are configured as "raw devices" (i.e., raw lvols with no file system), the two "tempdb" devices are 800-Mb files on separate VxFS filesystems. This was done, as recommended by Sybase, for performance reasons. There is no need for data integrity on tempdb devices, so the goal is to make use of the buffer cache. Also, the "dsync" option is turned off for these devices (so, in effect, we are dealing with async i/o requests)

Originally, we had both file systems on the same disk device (actually, same LDEV on XP256). The average disk i/o queue used to be over 300.

We were able to improve performance by re-coding some sql stored procedures, and by using 64k-striping over 6 disks (3 for tempdb.dat + 3 for tempdb.log). The disk i/o queues are now about 35 for each of the three disks supporting tempdb.log, and about 15 for the other 3 disks.

To accomodate for future growth (increased user activity is expected in the near future), we're also TRYING TO TUNE VxFS FOR BETTER PERFORMANCE.

Since different VxFS mount options deal with tradeoff between performance and integrity, and since we don't require data integrity in this case, I tried using the most relaxed conditions via the following:

mount -F vxfs -o nolog,mincache=tmpcache, convosync=delay

To my surprise, these options actually degraded performance a bit. Disk i/o queues increased as well.

I also tried:

mount -F vxfs -o nolog,mincache=direct,convosync=direct ...

but this was even worse. Yes, the disk i/o queues disappeared, but the CPU utilization went up and the code took longer to execute.

So, here are my questions:

1. Why would the options like, mincache=mincache, or convosync=delay, perform worse than the default mount options?

2. How does the size of the buffer cache play role in this situation? The current size is 122 Mb. Should I go for 800Mb, or maybe 1Gb? (I've seen several posts recommending the maximum size for the buffer cache at 300-400 Mb - for HP-UX 11.00)

3. Do you have any other performance tuning suggestions?

I appreciate and welcome your input.

Mladen

[Mladen Despic]

Correction:

mincache=mincache

was a typo. It was meant to be:

mincache=tmpcache

[Daryl UnRuh]

Any thoughts at all?
Thanks,

Mladen

[keith persons]

Mladen,

The mincache and convosync mount options probably won't help you at all, as you observed, on most mounted file systems they'll actually degrade throughput. I would also add the option nodatainlog, since you're not concerned about integrity disable all logging.

Buffer cache will probably help in your situation and I would suggest taking it up to the 300-400mb range to start, much more than 600mb will potentially be a waste of memory. Increasing the cache should improve things since the logs you mentioned are mostly write intensive. Plus, you'll stand a better chance of increasing the throughput between the system's cache and the XP cache - it's the XP itself that 'completes' the write in this scenario.

Other suggestions are more dependent on actual implementations, but you might want to start by hardcoding the kernel tunables and defeat all formulas. Also, make sure your timeslice is not 1. More data will be needed to provide any additional insights.

Keith