topic Re: Disk I/O performance for Sybase database loads in Operating System - HP-UX

Disk I/O performance for Sybase database loads

Rajasekhar Raman — Wed, 10 Dec 2003 13:50:55 GMT

I have two almost identically configured machines, both running HP-UX 11.00 patched to the same level (N4000). Both are running two instances of Sybase and have 3 CPUs dedicated to each instance (total of 6 CPUs, and 8 GB RAM total). The database is 27 Gb and has 6 devices ( raw logical devices). All the devices are striped across 4 18Gb disks, with a stripe size of 64k.

The perplexing thing is this:
One one machine it takes about 16 minutes to load this database from the dumps and on the other it takes anywhere from 45 to 75 minutes to load the same.

I have run sar -d to check what's going on. The primary difference I see is that the "r+w/s" and the "blks/s" is much higher on the faster machine ( as you would expect). I also noticed that the "avque" is around 0.50 on the slower machine (throuhout the load) and varies on the faster machine between 1 and 50. However when I do a simple "dd" to check on the speed of some data transfer from one disk to another, both machines clock about the same. I checked the kernel parameters and they are all the same. Is there anything I am missing here. It almost seems like there is a lid on the I/O size on one machine..

Any suggestions are welcome.

Re: Disk I/O performance for Sybase database loads

Steven E. Protter — Wed, 10 Dec 2003 13:56:16 GMT

Check the mount options in /etc/fstab these can have an impact on performance.

kmtune kernel output should be run and compared, because the kernel is a major factor here.

I'm attaching a script that collects performance data in the background and can help you identify the bottleneck.'

This one has recently been upgraded and bug fixed. Its production.

This document may help:
http://www2.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-1335382922+1071082507851+28353475&searchCrit=allwords

SEP

Re: Disk I/O performance for Sybase database loads

Rajasekhar Raman — Wed, 10 Dec 2003 14:28:17 GMT

The Sybase devices are raw devices, so they are not mounted. However the filesystems on which the database dumps are stored are indeed mounted. What should I be checking for on these filesystems ?

Re: Disk I/O performance for Sybase database loads

Rajasekhar Raman — Wed, 10 Dec 2003 14:32:34 GMT

Cannot find the document in the link specified

Re: Disk I/O performance for Sybase database loads

Jeff Schussele — Wed, 10 Dec 2003 14:44:37 GMT

Hi Shekar,

Are *both* these SCSI or Fibre Channel disks - i.e. the same? Fibre Channel are faster than copper SCSI.
Also, I'd check to see which slots the HBAs are in.

If one is in a Turbo slot & the other's in a TwinTurbo slot, it could make a big difference in disk I/O throughput.

Slots 1 & 2 are Turbo on an N4000 & all others (3 thru 12) are Twin Turbo.

HTH,
Jeff

Re: Disk I/O performance for Sybase database loads

doug mielke — Wed, 10 Dec 2003 14:48:53 GMT

Just some questions I'd ask.

are dump devices identical? Local or networked?

If networked, compare netstat, lanadmin and traceroute between systems. Look for shared media, high % of deferred transmissions.
if local, you could test with a dd to /dev/null to compare speed of the tape only.

I'm sure you would have noticed, but look at the access times of each device. Drive going bad may be doing re-reads.

Raw devices still use disk cache, yes? (I've never used raw) If so, something on the slow system can be occupying disk cache, while the fast sysem has full use of it.

Re: Disk I/O performance for Sybase database loads

Rajasekhar Raman — Wed, 10 Dec 2003 14:50:07 GMT

They are both SCSI disks. On the slower machine the dumps are on 73GB drives. All disks are on an SC10 enclosure. I have made sure that the Volume groups are spread across controllers, and no controller on the system has more than 5 disks on it.

The slower system does have an FC disk array (a DS2405 on which we store database dumps, but the performance for the loads is the same with these too!)

What do you mean by slots 1 and 2. Do you mean the PCI controller card slots on the back ??

-Shekar

Re: Disk I/O performance for Sybase database loads

Rajasekhar Raman — Wed, 10 Dec 2003 14:52:56 GMT

Doug,

The dump devices are local on both systems. The only other difference in the systems is that one has about 3 fully populated SC10s and the 1 DS2405 (with 6 disks), and the faster system has 2 SC10s.

As mentioned earlier, the SC10 is in split bus mode on both systems.

-Shekar

Re: Disk I/O performance for Sybase database loads

Jeff Schussele — Wed, 10 Dec 2003 14:55:52 GMT

Yep - the slots in the back. They are numbered off to the side & do show whether the slot is Turbo ot TwinTurbo as well on N4000s.

Jeff

Re: Disk I/O performance for Sybase database loads

Rajasekhar Raman — Wed, 10 Dec 2003 15:55:16 GMT

Jeff,

On the slower system one of the SC10 controllers is on the "Turbo" slot, the rest in the "Twin Turbo" slot. however, like I said, the Volume groups are spread over multiple controllers. Each volume group consist of 4 disks which are all on separate controllers and the logical volumes are striped ( 64kb) over the 4 disks.

On the faster system, there are no cards in the "Turbo" slots.

That being said, what would account for the "dd" command taking the same time on the same volume groups on both the systems.

-Shekar