PostgreSQL Read IOPS limit per connection

2018-12-27 Thread Haroldo Kerry 
*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/srkB/swkB/s avgrq-sz
avgqu-sz   await r_await w_await  svctm  %util

dm-10 0.00 0.00 1242.001.00 10796.0020.00
17.40 0.960.780.780.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/srkB/swkB/s avgrq-sz
avgqu-sz   await r_await w_await  svctm  %util

dm-10 0.00 0.00 19616.000.00 156928.00 0.00
16.0029.531.511.510.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

Re: PostgreSQL Read IOPS limit per connection

2018-12-27 Thread Justin Pryzby 
On Thu, Dec 27, 2018 at 02:44:55PM -0200, Haroldo Kerry wrote:
> PostgreSQL 9.6.10 on x86_64-pc-linux-gnu (Debian 9.6.10-1.pgdg80+1),

> 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).

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

postgres uses one server backend per client.

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

https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-ASYNC-BEHAVIOR
=> "Currently, this setting only affects bitmap heap scans."

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

It is a hsitoric limitation, but nowadays there's parallel query, which uses
2ndary "backend worker" processes.

It's supported in v9.6 but much more versatile in v10 and v11.

Justin

Query Performance Issue

2018-12-27 Thread neslişah demirci 
Hi everyone ,

Have this explain analyze output :

*https://explain.depesz.com/s/Pra8a *

Appreciated for any help .

*PG version*
---
 PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-28), 64-bit

*OS version :*
CentOS Linux release 7.5.1804 (Core)

shared_buffers : 4GB
work_mem : 8MB

Re: PostgreSQL Read IOPS limit per connection

2018-12-27 Thread Haroldo Kerry 
Justin,
Thanks for the quick response, I'll check it out.

Happy holidays,
Haroldo Kerry

On Thu, Dec 27, 2018 at 2:55 PM Justin Pryzby  wrote:

> On Thu, Dec 27, 2018 at 02:44:55PM -0200, Haroldo Kerry wrote:
> > PostgreSQL 9.6.10 on x86_64-pc-linux-gnu (Debian 9.6.10-1.pgdg80+1),
>
> > 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).
>
> > I’m trying to figure out if PostgreSQL (PG) has some inherent limit on
> IOPS
> > per connection.
>
> postgres uses one server backend per client.
>
> > We tried to increase effective_io_size from 1 to 30, to no effect on
> > multiple tests.
>
>
> https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-ASYNC-BEHAVIOR
> => "Currently, this setting only affects bitmap heap scans."
>
> > Is this some inherent limitation of PG or am I misunderstanding
> something?
>
> It is a hsitoric limitation, but nowadays there's parallel query, which
> uses
> 2ndary "backend worker" processes.
>
> It's supported in v9.6 but much more versatile in v10 and v11.
>
> Justin
>


-- 

Haroldo Kerry

CTO/COO

Rua do Rócio, 220, 7° andar, conjunto 72

São Paulo – SP / CEP 04552-000

[email protected]

www.callix.com.br

Re: PostgreSQL Read IOPS limit per connection

2018-12-27 Thread Jeff Janes 
>
>
> *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

Re: PostgreSQL Read IOPS limit per connection

2018-12-27 Thread Justin Pryzby 
On Thu, Dec 27, 2018 at 08:20:23PM -0500, Jeff Janes wrote:
> 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.

It *does* have parallel query (early, somewhat limited support),
but not enabled by default.
https://www.postgresql.org/docs/9.6/parallel-query.html

There was some confusion due to being disabled in 9.6, only:
https://www.postgresql.org/message-id/20180620151349.GB7500%40momjian.us

Cheers,
Justin