effective_io_concurrency很重要的一个参数

effective_io_concurrency (integer)

Sets the number of concurrent disk I/O operations that PostgreSQL expects can be executed simultaneously. Raising this value will increase the number of I/O operations that any individual PostgreSQL session attempts to initiate in parallel. The allowed range is 1 to 1000, or zero to disable issuance of asynchronous I/O requests. Currently, this setting only affects bitmap heap scans.

For magnetic drives, a good starting point for this setting is the number of separate drives comprising a RAID 0 stripe or RAID 1 mirror being used for the database. (For RAID 5 the parity drive should not be counted.) However, if the database is often busy with multiple queries issued in concurrent sessions, lower values may be sufficient to keep the disk array busy. A value higher than needed to keep the disks busy will only result in extra CPU overhead. SSDs and other memory-based storage can often process many concurrent requests, so the best value might be in the hundreds.

Asynchronous I/O depends on an effective posix_fadvise function, which some operating systems lack. If the function is not present then setting this parameter to anything but zero will result in an error. On some operating systems (e.g., Solaris), the function is present but does not actually do anything.

The default is 1 on supported systems, otherwise 0. This value can be overridden for tables in a particular tablespace by setting the tablespace parameter of the same name (see ALTER TABLESPACE).

　　

 

可以根据表空间去设置，不同表空间有关联不同的磁盘整列，选取的策略不一样。

 

另外，一般来说该参数只会对大量数据页访问时效果显著：

1）设置了该值，磁盘的预读会失效，顺序扫描和索引扫描都可以利用磁盘预读，但bitmap索引扫描不行，是因为effective_io_concurrency参数调用advise64()控制的预读功能造成，effective_io_concurrency的默认值为1，它只对bitmap index scan有效。将effective_io_concurrency禁用，设置为0，磁盘预读也生效了。

2）可以设置大一点，微观上通过strace -p跟踪postgres后端进程看看索引扫描和bitmap索引扫描各自调用的API有什么区别。fadvise64()和read()仍然是交替的，但fadvise64()会提前好几个周期就将相应的预读请求发给IO设备。

ead(33, "\0\0\0\0\3400S\234\0\0\0\0\304\1\350\1\0 \4 \0\0\0\0\270\237\214\0p\237\214\0"..., 8192) = 8192
fadvise64(33, 273088512, 8192, POSIX_FADV_WILLNEED) = 0
lseek(33, 273088512, SEEK_SET)          = 273088512
read(33, "\0\0\0\0\250\263S\234\0\0\0\0\304\1\350\1\0 \4 \0\0\0\0\270\237\214\0p\237\214\0"..., 8192) = 8192
fadvise64(33, 273096704, 8192, POSIX_FADV_WILLNEED) = 0
read(33, "\0\0\0\0008\337S\234\0\0\0\0\304\1\350\1\0 \4 \0\0\0\0\270\237\214\0p\237\214\0"..., 8192) = 8192
fadvise64(33, 273154048, 8192, POSIX_FADV_WILLNEED) = 0
lseek(33, 273154048, SEEK_SET)          = 273154048
read(33, "\0\0\0\0p\20U\234\0\0\0\0\304\1\350\1\0 \4 \0\0\0\0\270\237\214\0p\237\214\0"..., 8192) = 8192
fadvise64(33, 273170432, 8192, POSIX_FADV_WILLNEED) = 0
lseek(33, 273170432, SEEK_SET)          = 273170432
read(33, "\0\0\0\0\250gU\234\0\0\0\0\304\1\350\1\0 \4 \0\0\0\0\270\237\214\0p\237\214\0"..., 8192) = 8192
fadvise64(33, 273211392, 8192, POSIX_FADV_WILLNEED) = 0
lseek(33, 273211392, SEEK_SET)          = 273211392
read(33, "\0\0\0\0\250AV\234\0\0\0\0\304\1\350\1\0 \4 \0\0\0\0\270\237\214\0p\237\214\0"..., 8192) = 8192
fadvise64(33, 273219584, 8192, POSIX_FADV_WILLNEED

　　

3）如果是SSD，从测试结果看，建议关闭异步IO。（虽然手册中提到如果是内存盘或SSD盘，可以设置更大的值，但是实测效果不理想。）