On Sun, Jun 18, 2017 at 7:09 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
Hi guys.I just wanna understand the effective_io_concurrency value better.My current Master database server has 16 vCPUS and I useeffective_io_concurrency = 0.It seems as though the number of virtual CPUs little to no bearing on whether, or to what value, you should set this parameter. Obviously with only one CPU parallelism wouldn't be possible (I'm assuming a single query does not make multiple parallel requests for data)
Ah, but it does. That is exactly what this parameter is for.
Unfortunately, it is only implemented in very narrow circumstances. You have to be doing bitmap index scans of many widely scattered rows to make it useful. I don't think that this is all that common of a situation. The problem is that at every point in the scan, it has to be possible to know what data block it is going to want N iterations in the future, so you can inform the kernel to pre-fetch it. That is only easy to know for bitmap scans.
If you have a RAID, set it to the number of spindles in your RAID and forget it. It is usually one of the less interesting knobs to play with. (Unless your usage pattern of the database is unusual and exact fits the above pattern.)
Cheers,
Jeff
