When effective_io_concurrency is set to 0 all I/O is performed synchronously; Advanced Server issues a single I/O request and waits for that request to complete before proceeding. When it is set to 1, each block is requested asynchronously immediately following the previous operation, allowing the database to perform CPU work while the I/O proceeds simultaneously. In either case only a single drive in the I/O subsystem can be active leaving other drives participating in the same array idle.
To keep all the drives in the array active, Advanced Server issues multiple I/O requests concurrently. To do this Advanced Server must know how many drives participate in the I/O subsystem. Based on that value, Advanced Server estimates how many concurrent requests must be scheduled to keep all drives active. The higher the value of effective_io_concurrency, the more requests Advanced Server will try to keep pending at all times.
The optimal value is usually the number of data drives participating in the I/O system. For example for RAID-0 or RAID-1, it should be the total number of drives, whereas for RAID-5 it should be the number of data drives excluding the parity drives.
The expected speed increase for I/O influenced by this parameter is typically a factor equal to the number of drives; a 5-drive RAID array should see a five-fold increase in speed. However, not all of the I/O produced by a query is accelerated, so the effect on overall query execution time is less. Currently only Bitmap Heap Scans are accelerated.
Some caveats apply:
● In the case of OLTP systems with many concurrent active queries, using a single drive per query may be perfectly acceptable. Each query will make use of only a single drive, but in aggregate, concurrent OLTP queries will make effective use of all the drives. Increasing effective_io_concurrency effectively instructs Advanced Server to monopolize more I/O resources for each individual query; this is advantageous on systems handling few queries with available I/O resources but could have a detrimental effect on other concurrent queries.
● effective_io_concurrency is only used for Bitmap Heap Scans. For normal sequential scans the operating system should handle read-ahead internally (On Linux, see the blockdev command, in particular --setra and --setfra). For normal index scans, use the edb_prefetch_indexscans option.
● Advanced Server issues Asynchronous Pre-Fetch using the posix_fadvise() system routine (with the POSIX_FADV_WILLNEED option). This system call is not available or functional on every operating system. In particular, it has no effect on Solaris and does not exist on Windows.
● While the expected optimal value for effective_io_concurrency is the number of data drives, in practice, obtaining the maximum speedup sometimes requires some experimentation and experience shows that overestimating the number of drives can result in extra benefits.
● You should disable the Infinite Cache feature (set edb_enable_icache = "off") if effective_io_concurrency is set to a number other than 0. Asynchronous Pre-Fetch instructs the operating system to pre-fetch pages that are likely to be needed in the near future; Infinite Cache may cache those pages on a remote server farm instead of reading them from a local disk causing Infinite Cache and Asynchronous Pre-Fetch to interfere with each other, resulting in unnecessary disk I/O.