I/O server configuration for prefetching and parallelism

To enable prefetching, the database manager starts separate threads of control, known as I/O servers, to read data pages.

As a result, query processing is divided into two parallel activities: data processing (CPU) and data page I/O. The I/O servers wait for prefetch requests from the CPU activity. These prefetch requests contain a description of the I/O that is needed to satisfy the query.

Configuring enough I/O servers (with the num_ioservers database configuration parameter) can greatly enhance the performance of queries that can benefit from prefetching. To maximize the opportunity for parallel I/O, set num_ioservers to at least the number of physical disks in the database.

It is better to overestimate than to underestimate the number of I/O servers. If you specify extra I/O servers, these servers are not used, and their memory pages are paged out with no impact on performance. Each I/O server process is numbered. The database manager always uses the lowest numbered process, and as a result, some of the higher numbered processes might never be used.

To estimate the number of I/O servers that you might need, consider the following:
  • The number of database agents that could be writing prefetch requests to the I/O server queue concurrently
  • The highest degree to which the I/O servers can work in parallel
Consider setting the value of num_ioservers to AUTOMATIC so that the database manager can choose intelligent values based on the system configuration.