When tuning performance for an IBM Informix instance, one important issue to look for is the presence of tables and indexes containing a large number of extents. Today’s high speed disks and SSD drives can mask some of the performance hit, making this an area that is often overlooked. However, it is still an area that needs to be addressed more often than it is.
Tables and indexes that are spread over a number of extents can cause significant performance impact, so such fragmentation is to be avoided. Fragmentation occurs when a table is initially created with too small an extent size, so that over time many extents must be added as the table and associated indexes grow. Table space is allocated in blocks of disk called extents. A table is created with an initial first extent size, and additional extents are added as needed with a size determined by the next extent size. The default extent size for Informix is 8 pages. If not configured properly at table creation, a large table may grow to be comprised of numerous small extents fragmented across the drive and interleaved with extents from other tables or indexes. The resulting disk layout can result in a performance hit on table scans as the system must read through a series of noncontiguous data fragments.
To avoid this problem, you should try to properly size the initial and next extent sizes at table creation. However, if sized incorrectly at first, table extent sizes can later be modified using the ALTER TABLE command. The size of the first, next or both extent sizes may be specified. The new first extent size will be stored in the system catalog, but will not take effect unless the table is rebuilt.
ALTER TABLE customer EXTENT SIZE 1000 NEXT SIZE 100;
Prior to Informix version 11.7, doing the same for indexes wasn’t as easy. There was no way to allocate an extent size for indexes; index extents were based on the table extent size and allocated by the server using the following formula. When rebuilding an index in an attempt to defragment it, the next table extent would need to be increased in order to change the size of the index extents. Finding the optimal size could be something of a guessing game.
Index extent size = (index_key_size /table_row_size) * table_extent_size index_key_size is the total widths of the indexed column or columns plus 4 for a key descriptor. table_row_size is the sum of all the columns in the row. table_extent_size is the value that you specify in the EXTENT SIZE keyword of the CREATE TABLE statement.
Starting with Informix 11.7, the long-requested ability to specify an extent size for an index is now provided. Unlike table extent allocation, the size can’t be altered after the index is created. The index will need to be recreated using the new extent sizes in order to defragment the index.
CREATE INDEX cust_idx1 ON customer . . . EXTENT SIZE 200 NEXT SIZE 20;
When reorganizing tables and indexes, you should take into account the current size and expected growth in order to properly allocate new extent sizes. Optimal extent sizing and defragmentation of interleaved tables and indexes can have a significant impact on the performance of your Informix server.
For more information on how the XTIVIA Informix team can help you with your Informix systems, please review the information at this URL: https://www.xtivia.com/services/data-management/informix/