effective_io_concurrency on EBS/gp2
Vitaliy Garnashevich <vgarnashevich@gmail.com>
From: Vitaliy Garnashevich <vgarnashevich@gmail.com>
To: pgsql-performance@lists.postgresql.org
Date: 2018-01-31T12:03:17Z
Lists: pgsql-performance
Attachments
- test.txt (text/plain)
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