Sybase I/O performance question

[Robert Binkhorst]

All,

Our DBA's have found that disk I/O on a sybase database using a raw device is about 80% slower than the same database writing to a filesystem. When Sybase uses the cache of the filesystem this can be explained, but even using the Sybase option of ignoring the cache on disk is faster than using the raw device. This is not expected behaviour, we were expecting the raw device to be faster than that.

Can someone tell me the best practise way of setting up the OS for Sybase, kernel parameters and vg/lv setting?

- We have asynchronous I/O enabled (kernel module is loaded):
crw-rw---- 1 sybase sybase 101 0x000004 Oct 25 20:50 async
- Setpriv has been set (also in /etc/privgroup):
setprivgrp sybase RTPRIO MLOCK CHOWN

LV configuration:
LV Name /dev/vg21/ddwh_data_6
VG Name /dev/vg21
LV Permission read/write
LV Status available/syncd
Mirror copies 1
Consistency Recovery MWC
Schedule parallel
LV Size (Mbytes) 10000
Current LE 2500
Allocated PE 5000
Stripes 0
Stripe Size (Kbytes) 0
Bad block on
Allocation strict
IO Timeout (Seconds) default


Any help is greatly appreciated.

Cheers,

Robert

[Lorenzo Facello]

Hi,
maybe this can help you
http://www.isug.com/Sybase_FAQ/ASE/Section10/3/Q10.3.1.html

pay attention also for the patching!
Regds
L.

[Robert Binkhorst]

Thanks Lorenzo,

However, asynchronous I/O has been setup correctly already.

BTW, We're running HP-UX 11i v1.0 and Sybase 12.503.

[Hoefnix]

Robert,

As you know, I don't know anything about Sybase. But could it be the same as using raw device when Oracle is used on it.

Just see next thread:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=472229

Check param:
max_async_ports

HTH (if not don't phone me ;) )
Peter

[T G Manikandan]

how many hard disks are used for the I/O distribution?

[Robert Binkhorst]

Hi,

Thanks Peter, already looked into that. The async is sufficient.

Does anyone change the lvol configuration when running sybase on a raw device?

The number of disks the I/O is spread over is important for performance, but not especially relevant here. I'm interested in why a rlvol is so much slower? Thanks though!

[Elmar P. Kolkman]

Robert,

are your filesystem and the rlvol on the same (set of) disks, or perhaps disks with different speeds?

To prevent issues, do tests on the same lvol. And make sure the data you use when testing is generated and not read from a filesystem, to prevent any caching.

Also make sure you don't have stale extents. They can slow down things. And since you mirror, make sure you don't mirror on a disk on the same controller. These things can be checked with 'lvdisplay -v'.

[Robert Binkhorst]

Hi Elmar,

Thanks for your reply. We've created the lvol on the same VG as the raw device, and are now re-testing. This shows no differences in speed.

There are no stale extends in this VG.

We use bs of 16k in our tests as Sybase uses this value internally when writing. The VG uses bs of 4Mb, there's an obvious increase in speed where writing to a raw device is faster after the 1Mb mark.

These are the results of our test:
Writing to a raw device:
timex dd if=test_in bs=16k of=/dev/vg21/rtest_dev_1
2441+1 records in
2441+1 records out

real 15.3
user 0.0
sys 0.2

Writing to a file:
timex dd if=test_in bs=16k of=test_file
2441+1 records in
2441+1 records out

real 0.3
user 0.0
sys 0.3

Any more thoughts?

[Robert Binkhorst]

Hi All,

These are the test results:
4096k 2,53 2,31
1024k 2,55 2,48
512k 2,08 3,55
256k 2,16 4,67
128k 2,63 0,32
64k 4,46 0,32
32k 8,12 0,32
16k 15,41 0,33
2k 117,67 0,71

Sorry for the bad layout, spaces seem to be removed when posting. See the attached file for a txt file.

Robert

[Robert Binkhorst]

Sorry guys, this explanation on the test results should make them clearer:
- The tests were done using a 40Mb file, and using dd to write it to either a raw device or a file (Both with their own column).
- The first column is blocksize as specified in dd command.
- Units reported are seconds required for the dd command to complete.
- Object of the test was to see whether "dsync=off" in sybase would be a lot faster that writing directly to a raw partition, and how much faster that would be. The value of dsync determines whether you use the filesystem cache (dsync=off) or use a different system call and write directly to disk.
- System uses asynchronous I/O, but not on filesystems (fs_async=0).

Conclusions: Writing to a file system is
+ faster when using =< 128k blocksize
+ slower when using blocksizes larger than 128k and smaller than 1024k
+ the same when you're using blocksizes larger that 1024k.

Other conclusions:
+ Writing to a raw device shows a nice curve, where it's getting faster when your blocksize increases.
+ writing to a filesystem is very fast when writing small files (Understandable, it has cache everywhere)

[Changchun Teng]

What's the value of the bufpages? Decrease the value of it, then redo the i/o performance test. There may be some difference with your perviouse test.