2

I have a Sybase ASE 12.5.2 server running on Windows 2003 that is extremely slow for most select queries. The system is a 2 x Dual Core Xeon, 4GB memory and 200GB Raid5 (10k SAS Disks) for data on an LSI megaraid controller with a 128MB cache + BBU.

The only noticeable indication on the server is that the "Disk Read Queue Length" sits at 100 in perfmon. Usually this indicates an issue with the disks but nothing appears to be wrong with them.

Any ideas for what I can do to figure out what the problem is? I should also say that I'm far more familiar with Sybase on Solaris, not Sybase on Windows.

Here is a screenshot:

alt text http://img.skitch.com/20090722-t4sgfxd5fb2ck7bbsdjei38uu6.png

Here is a 40 Minute sp_sysmon output.

EDIT: Apparently the sp_sysmon output is too big for the serverfault question text area. Here is a link to the full output: [http://pastie.org/private/t5xqd0vamhz7ynnjuo3cxq][2]

EDIT: I changed the Screenshot to one that reflects what's going on much much better.

Mark Amerine Turner
  • 2,574
  • 1
  • 16
  • 17
  • What is the storage? Is it a RAID array or just a single disk or what? iSCSI SAN? 15K SAS? – Matt Rogish Jul 21 '09 at 17:38
  • also it seems to cut off the 2nd half of sp_sysmon output, can you edit and add it? I'm interested in the cache section – Matt Rogish Jul 21 '09 at 17:50
  • The system is a 2 x Dual Core Xeon, 4GB memory and 200GB Raid5 (10k SAS Disks) for data on an LSI megaraid controller with a 128MB cache + BBU. – Mark Amerine Turner Jul 21 '09 at 18:23
  • Added a link to the full output http://pastie.org/private/t5xqd0vamhz7ynnjuo3cxq – Mark Amerine Turner Jul 21 '09 at 18:23
  • Not adding this as an answer since i'm unsure how ASE behaves; but most databases with problems running on a RAID5 goes blazingly faster on RAID1 or RAID10. MS SQL is a good example, with logs on RAID1 and data on RAID5. – pauska Jul 21 '09 at 18:25
  • I have three other machine that are almost exactly the same, but at other locations. None of them have any performance issues on RAID5. I agree with you in theory but the data is so small I don't think the RAID setup is to blame here... but it could be, hence the reason I'm seeking help. – Mark Amerine Turner Jul 21 '09 at 18:35
  • Small data written very fast usually leads to high disk queue on RAID5, the size of the data doesnt really matter. Have you tried taking a look at the hdd leds and see if they are constantly busy? – pauska Jul 21 '09 at 18:42
  • Its Reads that are slow, not writes. – Mark Amerine Turner Jul 21 '09 at 18:50
  • The "SCALE" statistic on the left isn't actually the current value... When you click on the item the values in the last,avg, min is the stuff for that statistic. aka 2.x which is certainly well within usual levels for a RAID array – Matt Rogish Jul 21 '09 at 19:22
  • I added another screenshot that better shows what's going on. – Mark Amerine Turner Jul 22 '09 at 07:12

3 Answers3

3

Looking at the sysmon, you're attempting to do 964.2 IOPS,

Total Requested Disk I/Os 964.2 2373.5 2314183

of these 99.2% are accounted for by this device, all your other devices are doing very little. All you devices are on the same physical array (D:), but this isn't a problem if they're doing nothing.

Device:
D:\DTCLASS\ads\ads_data01
ads_data01 per sec per xact count % of total

Reads                                                                       
  APF                           459.9        1132.0     1103670      48.1 %
  Non-APF                       495.8        1220.3     1189821      51.8 %
Writes                            0.8           2.0        1978       0.1 %

Total I/Os 956.4 2354.3 2295469 99.2 %

950 IOPS should be within the capabilities of a 10 disc raid 5, particularly as these are all reads. If we allow a conservative estimate of 100IOPS per spindle we have an estimate of 1000 IOPS for this array. Edit: However it looks like I've misread your original post in assuming there's 10 discs! Please let us know how many discs are in this array.

Cache Search Summary Total Cache Hits 1283.3 3158.8 3079829 71.9 % Total Cache Misses 502.2 1236.3 1205348 28.1 %

You're getting quite a few cache misses. This would lead me to wonder if you've enough memory assigned to ASE. On windows with ASE 12.5 you're limited to approx 2.6 GB memory for ASE (Whether on 64 bit or 32 bit windows). Can you please let us see the output of sp_configure 'memory'. Am in particular interested in this line:

max memory 33792 2950000 1475000 1475000 memory pages(2k) dynamic

It's possible that you may have enough memory assigned to ASE, but you've not added it to the data cache. Could you also let us see the output of sp_cacheconfig.

You can see from the start of the sysmon output that your CPUs are spending most of their time dealing with IO:

Engine Busy Utilization CPU Busy I/O Busy Idle
Engine 0 2.4 % 79.9 % 17.7 %
Engine 1 2.0 % 75.2 % 22.8 %

You also only have 2 engines assigned to ASE, on a dual dual-core you can go up to 4 if this is a dedicated server. Although your licensing may not permit this, as ASE is licensed per core.

AdamH
  • 231
  • 1
  • 4
  • I am facing a similar issue with high io busy but could not locate the problem. Any chance you could take a look at the sysmon report? – user1471980 Aug 23 '16 at 13:34
2

it looks to me like your actual Avg disk queue length is 3.554. The scale is set to graph it at 100x the value.

typical indications of issues start when this number is greater than the number of spindles in the raid group.

i would start with procmon from sysinternals to start determining the disk usage. i would then look into db specfic tools to monitor queries (sorry - don't know much about sybase)

johnh
  • 585
  • 4
  • 9
0

You could try this optimizing tool to help track and troubleshoot the issue, it has a 14 day trial.

David Yu
  • 1,032
  • 7
  • 14