PostgreSQL Read IOPS limit per connection

*PG Version:*

PostgreSQL 9.6.10 on x86_64-pc-linux-gnu (Debian 9.6.10-1.pgdg80+1),
compiled by gcc (Debian 4.9.2-10+deb8u1) 4.9.2, 64-bit

*Installed via apt-get:*

apt-get install -y postgresql-9.6=9.6.10-1.pgdg80+1
postgresql-client-9.6=9.6.10-1.pgdg80+1
postgresql-contrib-9.6=9.6.10-1.pgdg80+1

*On a Debian 9.4 machine, 4.9 Kernel:*

uname -a: Linux srv-7 4.9.0-6-amd64 #1 SMP Debian 4.9.82-1+deb9u3
(2018-03-02) x86_64 GNU/Linux

Running inside a Docker 17.05 container.

*Hardware:*

Server: Dell R430 96 GB RAM, 2 Xeon processors with 10 cores, 20 threads
each, total 40 threads.

Connected to SAN: Dell Compellent SC2020, with 7 x Samsung PM1633 SSDs
https://www.samsung.com/us/labs/pdfs/collateral/pm1633-prodoverview-2015.pdf,
RAID10+RAID5 configuration, 8GB Cache, read-write battery backed cache
enabled, connected via dedicated iSCSI switches and dedicated Ethernet
ports, in link aggregation mode (2x1Gbps max bandwidth).

Data files and log files on above SAN storage on same volume, dedicated
volume for temporary files.

*Performance issue:*

I’m trying to figure out if PostgreSQL (PG) has some inherent limit on IOPS
per connection.

Running pgbench with multiple clients (-c 30) we are able to see 20K+ IOPS
, which is what we expect. But, if we use just one client, we get 1200
IOPS, avg disk queue size around 1:

pgbench -U postgres -S -T 60 -c 1

iotop:

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz
avgqu-sz await r_await w_await svctm %util

dm-10 0.00 0.00 1242.00 1.00 10796.00 20.00
17.40 0.96 0.78 0.78 0.00 0.68 84.00

We tried to increase effective_io_size from 1 to 30, to no effect on
multiple tests.

Running the fio disk benchmarking tool, we found the same number of IOPS
(1200) on a random read test if we set the io depth to 1.

If we increase the io depth to 30, we find about the same number of IOPS
(20K) we see on pgbench with multiple clients:

--fio config file

[job]

bs=8k

rw=randread

random_generator=lfsr

direct=1

ioengine=libaio

iodepth=30

time_based

runtime=60s

size=128M

filename=/var/lib/postgresql/data_9.6/file.fio

iotsat:

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz
avgqu-sz await r_await w_await svctm %util

dm-10 0.00 0.00 19616.00 0.00 156928.00 0.00
16.00 29.53 1.51 1.51 0.00 0.05 100.00

Which leads us to believe PG is limited to an IO depth of 1 per connection
(PG submits just 1 I/O request per connection, not multiple ones), even
though effective_io_concurrency could lead to greater I/O queue and
probably greater IOPS as well.

Is this some inherent limitation of PG or am I misunderstanding something?

One of the issues I’m trying to solve is related to extracting data from a
large table, which users a full table scan. We see the same 1200 IOPS limit
of pgbench when we SELECT on this table using just one connection. If there
is a limitation per connection, I might set up the application to have
several connections, and then issue SELECTs for different sections of the
table, and later join the data, but it looks cumbersome, especially if the
DB can do extract data using more IOPS.

Best regards,
Haroldo Kerry
CTO/COO