News for hackers. Stuff that matters.
Re:BRIN Indexes in Postgres 9.5 by doh on Friday September 16, 2016 @05:39PM (#12517551)
My understanding is that the effective_io_concurrency represents the number of data drives that partcipate in the i/o system excluding the parity drives.

From my experience, for each SSD drive you can set it between 5-8 (if there are in raid0).

BTW in 9.6 the effective_io_concurrency [0] can be different for each table space (especially useful if you have multiple drives of a varying quality [ssd, spinning] in one server).

[0] https://www.postgresql.org/docs/9.6/static/runtime-config-re...

Re:BRIN Indexes in Postgres 9.5 by pgaddict on Friday September 16, 2016 @08:19PM (#12518381)
It's somewhat related to the number of data drives, but as you point out it's a bit more complicated in practice - it's not just the number of drives. For example due to parallelism built into SSDs (each SSD is actually a stack of smaller drives). For rotational devices it's complicated by TCQ/NCQ, optimizations that require a queue of commands for each device to actually work.

On modern SSD, values 5-8 per device are definitely on the low side. See for example this thread from 2014, on (then released S3500 SSD from Intel):

https://www.postgresql.org/message-id/CAHyXU0yiVvfQAnR9cyH%3...

That shows the performance increases up to effective_io_concurrency=256 (of course, in reality there may be multiple queries running on the system, sharing the I/O paths).

Re:BRIN Indexes in Postgres 9.5 by doh on Friday September 16, 2016 @11:03PM (#12518897)
Very interesting.

I did some testing a while ago and didn't see much gain past 30 on 4 SSD disks. Should do another round of testing. Thank you for sharing.

Re:BRIN Indexes in Postgres 9.5 by pgaddict on Saturday September 17, 2016 @02:53PM (#12521835)
It's quite difficult (or impossible) to get a single "right" value, as it depends both on the workload and hardware (say, whether you have SSDs attached directly or through a RAID controller).