Sybase IQ and Disk Striping

Striping data across multiple disks is an essential technique for good performance.

Disk striping can be performed at different places in a system, often as part of RAID hardware or software, for example:

At the device layer, such as on a disk array or controller.
In the operating system or dedicated device management software, such as Veritas.
In the application.

By default, IQ internally stripes pages across all files within a dbspace , so additional striping at the software or hardware level are not needed for performance. Of course, additional striping may be necessary as part of implementing storage redundancy for the database, for example if RAID-5 is used.

Best performance in Sybase IQ with storage redundancy is achieved with simple mirroring or “RAID-1”. As stated above, Sybase IQ will distribute the data across all of the 2-disk mirror sets within a dbspace.

Due to cost, most Sybase IQ databases will not use mirroring, and will be implemented with RAID-5 or a similar RAID level to achieve redundancy. With RAID-5, choosing an appropriate chunk size (how much data is written to one disk before moving on to the next disk) will have a significant performance impact on the system, since RAID-5 has a significant write overhead. If your application does frequent or time-sensitive loads, updates, or deletes, or if queries often do temp dbspace I/O, a smaller chunk size in the range of 25-50% of the size of a Sybase IQ database page will likely give best performance. If your application is mostly reads, with little write activity, a larger chunk size 75-100% of an IQ page size will likely provide best performance

Since Sybase IQ normally attempts to prefetch multiple reads or flush multiple writes in parallel, even with only a single active query, using a very small chunk size to spread each page read or write across many disks will have little benefit, and will usually hurt performance.

When using RAID, best performance is usually achieved using hardware (such as controller or array) based RAID. Software based RAID tools will work well, but may add a modest additional performance load on the server’s CPUs.