Re: PostgreSQL Read IOPS limit per connection

>
>
> *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:
>

The default transaction done by pgbench simply has no opportunity for
dispatching multiple io requests per connection. It just a series of
single-row lookups and single-row updates or inserts. You will have to use
a different benchmark if you want to exercise this area. Probably
something analytics heavy.

Also, you would want to use the newest version of PostgreSQL, as 9.6
doesn't have parallel query, which is much more generally applicable than
effective_io_concurrency is.

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.
>
The kernel should detect a sequential read in progress and invoke
readahead. That should be able to keep the CPU quite busy with data for
any decent IO system. Are you sure IO is even the bottleneck for your
query?

Perhaps your kernel readahead settings need to be tuned. Also, you may
benefit from parallel query features implemented in newer versions of
PostgreSQL. In any event, the default transactions of pgbench are not
going to be useful for benchmarking what you care about.

Cheers,

Jeff