Re: Setting effective_io_concurrency in VM?

On Mon, Nov 27, 2017 at 11:23 AM, Don Seiler <don(at)seiler(dot)us> wrote:
> Good afternoon.
>
> We run Postgres (currently 9.2, upgrading to 9.6 shortly) in VMWare ESX
> machines. We currently have effective_io_concurrency set to the default of
> 1. I'm told that the data volume is a RAID 6 with 14 data drives and 2
> parity drives. I know that RAID10 is recommended, just working with what
> I've inherited for now (storage is high-end HP 3Par and HP recommended RAID
> 6 for best performance).
>
> Anyway, I'm wondering if, in a virtualized environment with a VM datastore,
> it makes sense to set effective_io_concurrency closer to the number of data
> drives?
>
> I'd also be interested in hearing how others have configured their
> PostgreSQL instances for VMs (if there's anything special to think about).

Generally VMs are never going to be as fast as running on bare metal
etc. You can adjust it and test it with something simple like pgbench
with various settings for -c (concurrency) and see where it peaks etc
with the setting. This will at least get you into the ball park.

A while back we needed fast machines with LOTS of storage (7TB data
drives with 5TB of data on them) and the only way to stuff that many
800GB SSDs into a single machine was to use RAID-5 with a spare (I
lobbied for RAID6 but was overidden eh...) We were able to achieve
over 15k TPS in pgbench with a 400GB data store on those boxes. The
secret was to turn off the cache in the RAID controller and cranl up
effective io concurrency to something around 10 (not sure, it's been a
while).

tl;dr: Only way to know is to benchmark it. I'd guess that somewhere
between 10 and 20 is going to get the best throughput but that's just
a guess. Benchmark it and let us know!