pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedsignificant jump in sql statement timing for on server vs a remote connection
5+ messages / 4 participants
[nested] [flat]
* significant jump in sql statement timing for on server vs a remote connection
@ 2022-04-19 21:00 Sbob <sbob@quadratum-braccas.com>
0 siblings, 2 replies; 5+ messages in thread
From: Sbob @ 2022-04-19 21:00 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi all;
We are debugging a sql performance issue. We have a sql file with 50,000
simple select statements in it. If I run the file locally it completes
in less than 15sec. If I force the local connection to be a tcp/ip
connection via psql -h and I get approximately the same results, 15 - 16sec.
However if we move the file to another server in the same network and
run with a psql -h then it runs for more than 10min. Are there any
postgres specific issues / settings / connection overhead we should
look at? Or is this simply a network issue and fully outside the scope
of the postgres database?
FYI:
postgresql 13
1.5TB of RAM
512GB of buffer_pool
10GB of work_mem
Thanks in advance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: significant jump in sql statement timing for on server vs a remote connection
@ 2022-04-19 21:04 Justin Pryzby <pryzby@telsasoft.com>
parent: Sbob <sbob@quadratum-braccas.com>
1 sibling, 0 replies; 5+ messages in thread
From: Justin Pryzby @ 2022-04-19 21:04 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Apr 19, 2022 at 03:00:09PM -0600, Sbob wrote:
> We are debugging a sql performance issue. We have a sql file with 50,000
> simple select statements in it. If I run the file locally it completes in
> less than 15sec. If I force the local connection to be a tcp/ip connection
> via psql -h and I get approximately the same results, 15 - 16sec.
>
>
> However if we move the file to another server in the same network and run
> with a psql -h then it runs for more than 10min. Are there any postgres
> specific issues / settings / connection overhead we should look at? Or is
> this simply a network issue and fully outside the scope of the postgres
> database?
What OS ? What kind of authentication are you using ?
Is there a connection pooler involved ? Did you try like that ?
Did you test how long it takes to run 10k empty statements locally vs remotely ?
time yes 'SELECT;' |head -9999 |psql ... >/dev/null
--
Justin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: significant jump in sql statement timing for on server vs a remote connection
@ 2022-04-20 04:17 Jeff Janes <jeff.janes@gmail.com>
parent: Sbob <sbob@quadratum-braccas.com>
1 sibling, 1 reply; 5+ messages in thread
From: Jeff Janes @ 2022-04-20 04:17 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Tue, Apr 19, 2022 at 5:00 PM Sbob <sbob@quadratum-braccas.com> wrote:
>
> However if we move the file to another server in the same network and
> run with a psql -h then it runs for more than 10min.
What is the ping time? Packet loss? You can't take for granted that the
network is good and fast just because they are on the same LAN.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: significant jump in sql statement timing for on server vs a remote connection
@ 2022-04-20 15:16 Sbob <sbob@quadratum-braccas.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Sbob @ 2022-04-20 15:16 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On 4/19/22 22:17, Jeff Janes wrote:
> On Tue, Apr 19, 2022 at 5:00 PM Sbob <sbob@quadratum-braccas.com> wrote:
>
>
> However if we move the file to another server in the same network and
> run with a psql -h then it runs for more than 10min.
>
>
> What is the ping time? Packet loss? You can't take for granted that
> the network is good and fast just because they are on the same LAN.
>
> Cheers,
>
> Jeff
Here is the ping stats:
--- db-primary ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3000ms
rtt min/avg/max/mdev = 0.304/0.348/0.400/0.039 ms
This seems pretty good yes?Anything else I could look at?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: significant jump in sql statement timing for on server vs a remote connection
@ 2022-04-20 15:51 Ranier Vilela <ranier.vf@gmail.com>
parent: Sbob <sbob@quadratum-braccas.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ranier Vilela @ 2022-04-20 15:51 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Em qua., 20 de abr. de 2022 às 12:16, Sbob <sbob@quadratum-braccas.com>
escreveu:
>
> On 4/19/22 22:17, Jeff Janes wrote:
>
> On Tue, Apr 19, 2022 at 5:00 PM Sbob <sbob@quadratum-braccas.com> wrote:
>
>>
>> However if we move the file to another server in the same network and
>> run with a psql -h then it runs for more than 10min.
>
>
> What is the ping time? Packet loss? You can't take for granted that the
> network is good and fast just because they are on the same LAN.
>
> Cheers,
>
> Jeff
>
>
> Here is the ping stats:
>
> --- db-primary ping statistics ---
> 4 packets transmitted, 4 received, 0% packet loss, time 3000ms
>
3000 ms?
Are sure that haven't packet loss?
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-04-20 15:51 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-19 21:00 significant jump in sql statement timing for on server vs a remote connection Sbob <sbob@quadratum-braccas.com>
2022-04-19 21:04 ` Justin Pryzby <pryzby@telsasoft.com>
2022-04-20 04:17 ` Jeff Janes <jeff.janes@gmail.com>
2022-04-20 15:16 ` Sbob <sbob@quadratum-braccas.com>
2022-04-20 15:51 ` Ranier Vilela <ranier.vf@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox