effective_io_concurrency on EBS/gp2 - Mailing list pgsql-performance

From Vitaliy Garnashevich
Subject effective_io_concurrency on EBS/gp2
Date
Msg-id 6c7a45df-f6ab-f2ce-6f84-9555864f6c86@gmail.com
Whole thread Raw
Responses Re: effective_io_concurrency on EBS/gp2
Re: effective_io_concurrency on EBS/gp2
List pgsql-performance
Hi,

I've tried to run a benchmark, similar to this one:


https://www.postgresql.org/message-id/flat/CAHyXU0yiVvfQAnR9cyH%3DHWh1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com#CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azsTdQ@mail.gmail.com

CREATE TABLESPACE test OWNER postgres LOCATION '/path/to/ebs';

pgbench -i -s 1000 --tablespace=test pgbench

echo "" >test.txt
for i in 0 1 2 4 8 16 32 64 128 256 ; do
   sync; echo 3 > /proc/sys/vm/drop_caches; service postgresql restart
   echo "effective_io_concurrency=$i" >>test.txt
   psql pgbench -c "set effective_io_concurrency=$i; set 
enable_indexscan=off; explain (analyze, buffers)  select * from 
pgbench_accounts where aid between 1000 and 10000000 and abalance != 0;" 
 >>test.txt
done

I get the following results:

effective_io_concurrency=0
  Execution time: 40262.781 ms
effective_io_concurrency=1
  Execution time: 98125.987 ms
effective_io_concurrency=2
  Execution time: 55343.776 ms
effective_io_concurrency=4
  Execution time: 52505.638 ms
effective_io_concurrency=8
  Execution time: 54954.024 ms
effective_io_concurrency=16
  Execution time: 54346.455 ms
effective_io_concurrency=32
  Execution time: 55196.626 ms
effective_io_concurrency=64
  Execution time: 55057.956 ms
effective_io_concurrency=128
  Execution time: 54963.510 ms
effective_io_concurrency=256
  Execution time: 54339.258 ms

The test was using 100 GB gp2 SSD EBS. More detailed query plans are 
attached.

PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit

The results look really confusing to me in two ways. The first one is 
that I've seen recommendations to set effective_io_concurrency=256 (or 
more) on EBS. The other one is that effective_io_concurrency=1 (the 
worst case) is actually the default for PostgreSQL on Linux.

Thoughts?

Regards,
Vitaliy


Attachment

pgsql-performance by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: Nested Loops
Next
From: Rick Otten
Date:
Subject: Re: effective_io_concurrency on EBS/gp2