Optimizing Disk I/O - Controlling Physical Data Placement.

Sybase/SAP ASE is robust and popular for its stability, versatility and performance for mixed data loads (Both OLTP and DSS). As database scalability is a biggest concern over years, continuous optimization of critical resources like CPU, MEMORY, effective QUERY writing and Storage for IO is necessary to satisfy the Clients requirements and keep the response-time and throughput inline with expectations.

I would like to write this article on IO/Storage as Disk waits are a huge performance bottleneck among critical resources. This problem becomes more apparent when the amount of data starts to grow so large that 99%+ logical IO is being impossible and some physical IO is always needed. Increase the number of available disk spindles (and thereby reduce the seek overhead) by either create devices in different disks or striping the disks besides portioning the table.

Let us first see the OS Level optimization options later see Sybase options.

If you do not need to know when files were last accessed (which is not really useful on a database server), you can mount your file systems with the -o noatime option. That skips updates to the last access time in inodes on the file system, which avoids some disk seeks.

On many operating systems, you can set a file system to be updated asynchronously by mounting it with the -o async option. If your computer is reasonably stable, this should give you better performance without sacrificing too much reliability. (This flag is on by default on Linux.)

Striping

Striping means that you have many disks and put the first block on the first disk, the second block on the second disk, and the N-th block on the (N MOD number_of_disks) disk, and so on. This means if your normal data size is less than the stripe size (or perfectly aligned), you get much better performance. Striping is very dependent on the operating system and the stripe size, so benchmark your application with different stripe sizes. The speed difference for striping is very dependent on the parameters. Depending on how you set the striping parameters and number of disks, you may get differences measured in orders of magnitude. You have to choose to optimize for random or sequential access.

For reliability, you may want to use RAID 0+1 (striping plus mirroring), but in this case, you need 2 × N drives to hold N drives of data. This is probably the best. However, you may also have to invest in some volume-management software to handle it efficiently.

A good option is to vary the RAID level according to how critical a type of data is. For example, store semi-important data that can be regenerated on a RAID 0 disk, but store really important data such as host information and logs on a RAID 0+1 or RAID N disk. RAID N can be a problem if you have many writes, due to the time required to update the parity bits.

Specific to Sybase (ASE way of accessing Storage):

To make the most of physical database tuning, understand these distinctions between logical and physical devices:

• The physical disk or physical device is the hardware that stores the data.

• A database device or logical device is all or part of a physical disk that has been initialized (with the disk init command) for use by Database Server. A database device can be an operating system file, an entire disk, or a disk partition.

• A segment is a named collection of database devices used by a database. The database devices that make up a segment can be located on separate physical devices.

• A partition is a subset of a table. Partitions are database objects that can be managed independently. You can split partitioned tables, so multiple tasks can access it simultaneously. You can place a partition on a specific segment. If each partition is on a different segment and each segment has its own database device, queries accessing these tables benefit from improved parallelism.

Improving performance by controlling object placement

Adaptive Server allows you to control the placement of databases, tables, and indexes across physical storage devices, which can improve performance by equalizing the reads and writes to disk across many devices and controllers. For example, you can:

• Place database data segments on a specific device or devices, storing the database log on a separate physical device so that reads and writes to the log do not interfere with data access.
• Spread large, heavily used tables across several devices.
• Place specific tables or nonclustered indexes on specific devices. For example, you might place a table on a segment that spans several devices and its nonclustered indexes on a separate segment.
• Place the text and image page chain for a table on a separate device from the table. The table stores a pointer to the actual data value in the separate database structure, so each access to a text or image column requires at least two I/Os.
• Distribute tables evenly across partitions on separate physical disks to provide optimum parallel query performance and improve insert and update performance.

For multiuser and multi-CPU systems that perform a lot of disk I/O, be especially aware of physical and logical device issues and the distribution of I/O across devices:

·         Plan a balanced separation of objects across logical and physical devices.
·         Use enough physical devices, including disk controllers, to ensure physical bandwidth.
·         Use an increased number of logical devices to ensure minimal contention for internal I/O queues.
·         Determine and use a number of partitions that allows parallel scans and meets query performance goals.

If you experience problems due to disk contention and other problems related to object placement, check for and correct these issues:
·         Random-access (I/O for data and indexes) and serial-access (log I/O) processes use the same disks.
·         Database processes and operating system processes use the same disks.
·         Serial disk mirroring.
·         Database logging or auditing takes place on the same disk as data storage.

ASE provides options to Improve I/O performance by:

• Using segments
• Partitioning tables for performance
• RAID devices and partitioned tables

To improve I/O performance in Adaptive Server, you can also try:

• ·         Spreading data across disks to avoid I/O contention
• ·         Isolating server-wide I/O from database I/O
• ·         Separating data storage and log storage for frequently updated databases
• ·         Keeping random disk I/O away from sequential disk I/O
• ·         Mirroring devices on separate physical disks
• ·         Using partitions to distribute table data across devices

A segment is a label that points to one or more logical devices. Use segments to improve throughput by:

• Splitting large tables across disks, including tables that are partitioned for parallel query performance
• Separating tables and their nonclustered indexes across disks
• Separating table partitions and index across the disks
• Placing the text and image page chain on a disk other than the one on which the table resides, where the pointers to the text values are stored.

In addition, you can use segments to control space usage:

• Tables or partitions cannot grow larger than their segment allocation. You can use segments to limit the table or partition size.
• Tables or partitions on other segments cannot use the space allocated to objects on another segment.
• The threshold manager monitors space usage.

Partitioning a table can improve performance for several types of processes.

• Partitioning allows parallel query processing to access each partition of the table. Each worker process in a partitioned-based scan reads a separate partition.
• Partitioning allows you to load a table in parallel with bulk copy.
• For more information on parallel bcp, see the Utility Programs manual.
• Partitioning allows tou to distribute a table’s I/O over multiple database devices.
• Semantic partitioning (range-, hash- and list-partitioned tables) improves response time because the query processor eliminates some partitions.
• Partitioning provides multiple insertion points for a heap table.

The tables you choose to partition and the type of partition depend on the performance issues you encounter and the performance goals for the queries on the tables.

RAID

A striped redundant array of independent disks (RAID) device can contain multiple physical disks, but Adaptive Server treats such a device as a single logical device. You can use multiple partitions on the single logical device and achieve good parallel query performance.

To determine the optimum number of partitions for your application mix, start with one partition for each device in the stripe set. Use your operating system utilities (vmstat, sar, and iostat on UNIX; Performance Monitor on Windows) to check utilization and latency.

To check maximum device throughput, use select count(*), using the index table_name clause to force a table scan if a nonclustered index exists. This command requires minimal CPU effort and creates very little contention for other resources.

The Adaptive Server optimizer attempts to find the most efficient access path to your data for each table in a query by estimating the cost of the physical I/O needed to access the data, and the number of times each page must be read while in the data cache.

Performance is critically depends on access methods in ASE.

In most database applications, there are many tables in the database, and each table has one or more indexes. Depending on whether you have created indexes, and what kind of indexes you have created, the optimizer’s access method options include:·  

• Table scan – reading all the table’s data pages, sometimes hundreds or thousands of pages.
• Index access – using the index to find only the data pages needed, sometimes as few as three or four page reads in all.
• Index covering – using only an index to return data, without reading the actual data rows, requiring only a fraction of the page reads required for a table scan.

Make sure your IO is controlled with the above options so as to use optimized way of the storage and better throughput and response-time. 

- Thanks for many blogs/sites, especially SAP/Sybase sites,  helps me write this besides my experience :)
.