While diagnosing performance issues with vendor software that runs off of SQL Anywhere (9.0.2), I stumbled upon some interesting data in regards to I/O Bandwidth. According to the 9.0.2 manual, the database property "CurrIO" shows "The current number of file I/Os that were issued by the server but have not yet completed.". However, it's unclear what this number should be, given a hardware configuration and/or database utilization.
After a bit of searching, I found that the SQL Anywhere 10.0.0 manual does go into this setting in a bit more detail in their chapter on performance:
To detect whether I/O
bandwidth is a limiting factor, check
the CurrIO database statistic. If this
statistic is not present on the graph,
click the Add Statistics button and
select CurrIO. Look for the largest
sustained number for this statistic.
For example, look for a high plateau
on the graph; the wider it is, the
more significant the impact. If the
graph has sustained values equal to,
or greater than 3 + the number of
physical disks used by database
server, it may indicate that the disk
system cannot keep up with the level
of database server activity.
Is this saying that, for example, if I have 5 disks in the server, this number should ideally be below 8? Is the meaning behind this value the same for version 9.0.2 as 10.0.0? The reason I find this hard to believe is the results of the following command are a bit off in my particular case:
SELECT db_property ( 'CurrIO' ), db_property ( 'MaxIO' )
The above command returns over 900 for the CurrIO and 1150 for the MaxIO. I have been monitoring this number for a few hours and the average is approximately 950 (Thanks to the Foxhound monitor from RisingRoad). These readings have been taken under normal database load.
Is my I/O bandwidth truly as inadequate as it looks, or am I misinterpreting these numbers?
Here is the current server configuration:
OS: Windows Server 2003 R2 32-bit
Database Version: SQL Anywhere (Adaptive Server Anywhere) 9.0.2.3381
CPU: 4x Intel Xeon Dual Core 3.00GHz
RAM: 26GB (22GB Allocated to SQL Anywhere cache)
HDD (C:/): OS + Temporary File Location
RAID 1
2x 36GB SCSI-320 (15k RPM)
HDD (D:/): DB File Location
RAID 5
4x 73GB SCSI-320 (15k RPM)
HDD (E:/): OS Pagefile + Log File Location (There is no Mirror Log)
RAID 5
4x 73GB SCSI-320 (15k RPM)
Notes: The RAID1 and first RAID5 (D:/) are on the same RAID controller. We were planning on upgrading both RAID5 with 146GB (15k RPM) drives in RAID10. Would that change help our apparent I/O Bandwidth issue?
Best Answer
When dealing with RAIDs the traditional Disk counters in perfmon may give back misleading results. They will show cache I/O rather than disk I/O. So make sure you also look at the
% Idle Timecounter. This will probably the most accurate result, but it will be inverted (lower percentage equals busier disks)