public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedClient Server performance & UDS
7+ messages / 4 participants
[nested] [flat]
* Client Server performance & UDS
@ 2017-05-26 14:02 Kevin.Hughes@uk.fujitsu.com <Kevin.Hughes@uk.fujitsu.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kevin.Hughes@uk.fujitsu.com @ 2017-05-26 14:02 UTC (permalink / raw)
To: pgsql-performance
Hi,
This is a general question around this performance area rather than a specific performance problem.....so I apologise now for a lack of a specific detail.
We have an application that does many small actions on the DB - and it's a small DB (a 50/100 Mbytes) so we would expect it to be contained in memory. Accesses need to be low latency - unfortunately there are "serial" accesses where the result of one access governs the next. Luckily the work to be done by the DB is, we believe, very simple and hence fast. Everything is running on one (large) server so we use UDS to connect the client to the server.
Out observation (suspicion) is that the latency of the access, as opposed to the cost of the query, is high. Having done some investigation we believe the UDS latency may be contributing AND the cost imposed by postgres in "formatting" the messages between the client and server (transformation to network format?).
We will try and get underneath this with real results/measurements but I would appreciate any comments pointers on what we are doing and how/if we can optimise this style of applications
Cheers
Unless otherwise stated, this email has been sent from Fujitsu Services Limited (registered in England No 96056); Fujitsu EMEA PLC (registered in England No 2216100) both with registered offices at: 22 Baker Street, London W1U 3BW; PFU (EMEA) Limited, (registered in England No 1578652) and Fujitsu Laboratories of Europe Limited (registered in England No. 4153469) both with registered offices at: Hayes Park Central, Hayes End Road, Hayes, Middlesex, UB4 8FE.
This email is only for the use of its intended recipient. Its contents are subject to a duty of confidence and may be privileged. Fujitsu does not guarantee that this email has not been intercepted and amended or that it is virus-free.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Client Server performance & UDS
@ 2017-05-27 12:26 Rick Otten <rottenwindfish@gmail.com>
parent: Kevin.Hughes@uk.fujitsu.com <Kevin.Hughes@uk.fujitsu.com>
0 siblings, 1 reply; 7+ messages in thread
From: Rick Otten @ 2017-05-27 12:26 UTC (permalink / raw)
To: Kevin.Hughes@uk.fujitsu.com; +Cc: pgsql-performance
You should have a layer such as pgbouncer between your pg instance and your
application. It is designed to mitigate the access latency issues you
describe.
On May 26, 2017 10:03 AM, "Kevin.Hughes@uk.fujitsu.com" <
Kevin.Hughes@uk.fujitsu.com> wrote:
> Hi,
>
>
>
> This is a general question around this performance area
> rather than a specific performance problem.....so I apologise now for a
> lack of a specific detail.
>
>
>
> We have an application that does many small actions on the
> DB – and it’s a small DB (a 50/100 Mbytes) so we would expect it to be
> contained in memory. Accesses need to be low latency – unfortunately there
> are “serial” accesses where the result of one access governs the next.
> Luckily the work to be done by the DB is, we believe, very simple and
> hence fast. Everything is running on one (large) server so we use UDS to
> connect the client to the server.
>
>
>
> Out observation (suspicion) is that the latency of the access, as opposed
> to the cost of the query, is high. Having done some investigation we
> believe the UDS latency may be contributing AND the cost imposed by
> postgres in “formatting” the messages between the client and server
> (transformation to network format?).
>
>
>
> We will try and get underneath this with real results/measurements but I
> would appreciate any comments pointers on what we are doing and how/if we
> can optimise this style of applications
>
>
>
>
>
> Cheers
>
>
>
>
>
>
>
>
>
> Unless otherwise stated, this email has been sent from Fujitsu Services
> Limited (registered in England No 96056); Fujitsu EMEA PLC (registered in
> England No 2216100) both with registered offices at: 22 Baker Street,
> London W1U 3BW; PFU (EMEA) Limited, (registered in England No 1578652) and
> Fujitsu Laboratories of Europe Limited (registered in England No. 4153469)
> both with registered offices at: Hayes Park Central, Hayes End Road, Hayes,
> Middlesex, UB4 8FE.
> This email is only for the use of its intended recipient. Its contents are
> subject to a duty of confidence and may be privileged. Fujitsu does not
> guarantee that this email has not been intercepted and amended or that it
> is virus-free.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Client Server performance & UDS
@ 2017-05-30 07:34 Kevin.Hughes@uk.fujitsu.com <Kevin.Hughes@uk.fujitsu.com>
parent: Rick Otten <rottenwindfish@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kevin.Hughes@uk.fujitsu.com @ 2017-05-30 07:34 UTC (permalink / raw)
To: Rick Otten <rottenwindfish@gmail.com>; +Cc: pgsql-performance
Hi Rick thanks for the reply.
Our aim is to minimise latency hence we have a dedicated 1:1 relationship between the client and the server. If I use connection pooling surely this introduced latency – getting a server from the pool establishing the connection?
Am I missing something?
From: Rick Otten [mailto:rottenwindfish@gmail.com]
Sent: 27 May 2017 13:27
To: Hughes, Kevin <Kevin.Hughes@uk.fujitsu.com>
Cc: pgsql-performa. <pgsql-performance@postgresql.org>
Subject: Re: [PERFORM] Client Server performance & UDS
You should have a layer such as pgbouncer between your pg instance and your application. It is designed to mitigate the access latency issues you describe.
On May 26, 2017 10:03 AM, "Kevin.Hughes@uk.fujitsu.com<mailto:Kevin.Hughes@uk.fujitsu.com>" <Kevin.Hughes@uk.fujitsu.com<mailto:Kevin.Hughes@uk.fujitsu.com>> wrote:
Hi,
This is a general question around this performance area rather than a specific performance problem.....so I apologise now for a lack of a specific detail.
We have an application that does many small actions on the DB – and it’s a small DB (a 50/100 Mbytes) so we would expect it to be contained in memory. Accesses need to be low latency – unfortunately there are “serial” accesses where the result of one access governs the next. Luckily the work to be done by the DB is, we believe, very simple and hence fast. Everything is running on one (large) server so we use UDS to connect the client to the server.
Out observation (suspicion) is that the latency of the access, as opposed to the cost of the query, is high. Having done some investigation we believe the UDS latency may be contributing AND the cost imposed by postgres in “formatting” the messages between the client and server (transformation to network format?).
We will try and get underneath this with real results/measurements but I would appreciate any comments pointers on what we are doing and how/if we can optimise this style of applications
Cheers
Unless otherwise stated, this email has been sent from Fujitsu Services Limited (registered in England No 96056); Fujitsu EMEA PLC (registered in England No 2216100) both with registered offices at: 22 Baker Street, London W1U 3BW; PFU (EMEA) Limited, (registered in England No 1578652) and Fujitsu Laboratories of Europe Limited (registered in England No. 4153469) both with registered offices at: Hayes Park Central, Hayes End Road, Hayes, Middlesex, UB4 8FE.
This email is only for the use of its intended recipient. Its contents are subject to a duty of confidence and may be privileged. Fujitsu does not guarantee that this email has not been intercepted and amended or that it is virus-free.
Unless otherwise stated, this email has been sent from Fujitsu Services Limited (registered in England No 96056); Fujitsu EMEA PLC (registered in England No 2216100) both with registered offices at: 22 Baker Street, London W1U 3BW; PFU (EMEA) Limited, (registered in England No 1578652) and Fujitsu Laboratories of Europe Limited (registered in England No. 4153469) both with registered offices at: Hayes Park Central, Hayes End Road, Hayes, Middlesex, UB4 8FE.
This email is only for the use of its intended recipient. Its contents are subject to a duty of confidence and may be privileged. Fujitsu does not guarantee that this email has not been intercepted and amended or that it is virus-free.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Client Server performance & UDS
@ 2017-05-30 10:17 Rick Otten <rottenwindfish@gmail.com>
parent: Kevin.Hughes@uk.fujitsu.com <Kevin.Hughes@uk.fujitsu.com>
0 siblings, 2 replies; 7+ messages in thread
From: Rick Otten @ 2017-05-30 10:17 UTC (permalink / raw)
To: Kevin.Hughes@uk.fujitsu.com <Kevin.Hughes@uk.fujitsu.com>; +Cc: pgsql-performance
Establishing a connection with a PostgreSQL database is a very expensive
process on the database server. On the other hand, establishing a
connection with pgbouncer is very fast. Offloading the connection
management to pgbouncer can significantly reduce the connection set up time.
I've found it to help even with applications that have built-in connection
pooling.
If your clients are keeping persistent connections open to the database,
and the latency you are experiencing is within the transaction itself, you
might look at disk I/O for your WAL (write ahead logs) and take a closer
look at WAL and checkpoint tuning.
On Tue, May 30, 2017 at 3:34 AM, Kevin.Hughes@uk.fujitsu.com <
Kevin.Hughes@uk.fujitsu.com> wrote:
> Hi Rick thanks for the reply.
>
>
>
> Our aim is to minimise latency hence we have a dedicated 1:1 relationship
> between the client and the server. If I use connection pooling surely this
> introduced latency – getting a server from the pool establishing the
> connection?
>
>
>
> Am I missing something?
>
>
>
>
>
> *From:* Rick Otten [mailto:rottenwindfish@gmail.com]
> *Sent:* 27 May 2017 13:27
> *To:* Hughes, Kevin <Kevin.Hughes@uk.fujitsu.com>
> *Cc:* pgsql-performa. <pgsql-performance@postgresql.org>
> *Subject:* Re: [PERFORM] Client Server performance & UDS
>
>
>
> You should have a layer such as pgbouncer between your pg instance and
> your application. It is designed to mitigate the access latency issues you
> describe.
>
>
>
> On May 26, 2017 10:03 AM, "Kevin.Hughes@uk.fujitsu.com" <
> Kevin.Hughes@uk.fujitsu.com> wrote:
>
> Hi,
>
>
>
> This is a general question around this performance area
> rather than a specific performance problem.....so I apologise now for a
> lack of a specific detail.
>
>
>
> We have an application that does many small actions on the
> DB – and it’s a small DB (a 50/100 Mbytes) so we would expect it to be
> contained in memory. Accesses need to be low latency – unfortunately there
> are “serial” accesses where the result of one access governs the next.
> Luckily the work to be done by the DB is, we believe, very simple and
> hence fast. Everything is running on one (large) server so we use UDS to
> connect the client to the server.
>
>
>
> Out observation (suspicion) is that the latency of the access, as opposed
> to the cost of the query, is high. Having done some investigation we
> believe the UDS latency may be contributing AND the cost imposed by
> postgres in “formatting” the messages between the client and server
> (transformation to network format?).
>
>
>
> We will try and get underneath this with real results/measurements but I
> would appreciate any comments pointers on what we are doing and how/if we
> can optimise this style of applications
>
>
>
>
>
> Cheers
>
>
>
>
>
>
>
>
>
>
> Unless otherwise stated, this email has been sent from Fujitsu Services
> Limited (registered in England No 96056); Fujitsu EMEA PLC (registered in
> England No 2216100) both with registered offices at: 22 Baker Street,
> London W1U 3BW; PFU (EMEA) Limited, (registered in England No 1578652) and
> Fujitsu Laboratories of Europe Limited (registered in England No. 4153469)
> both with registered offices at: Hayes Park Central, Hayes End Road, Hayes,
> Middlesex, UB4 8FE.
> This email is only for the use of its intended recipient. Its contents are
> subject to a duty of confidence and may be privileged. Fujitsu does not
> guarantee that this email has not been intercepted and amended or that it
> is virus-free.
>
>
> Unless otherwise stated, this email has been sent from Fujitsu Services
> Limited (registered in England No 96056); Fujitsu EMEA PLC (registered in
> England No 2216100) both with registered offices at: 22 Baker Street,
> London W1U 3BW; PFU (EMEA) Limited, (registered in England No 1578652) and
> Fujitsu Laboratories of Europe Limited (registered in England No. 4153469)
> both with registered offices at: Hayes Park Central, Hayes End Road, Hayes,
> Middlesex, UB4 8FE.
> This email is only for the use of its intended recipient. Its contents are
> subject to a duty of confidence and may be privileged. Fujitsu does not
> guarantee that this email has not been intercepted and amended or that it
> is virus-free.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Client Server performance & UDS
@ 2017-05-30 20:20 Karl Czajkowski <karlcz@isi.edu>
parent: Rick Otten <rottenwindfish@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Karl Czajkowski @ 2017-05-30 20:20 UTC (permalink / raw)
To: Rick Otten <rottenwindfish@gmail.com>; +Cc: Kevin.Hughes@uk.fujitsu.com <Kevin.Hughes@uk.fujitsu.com>; pgsql-performance
On May 30, Rick Otten modulated:
> If your clients are keeping persistent connections open to the
> database, and the latency you are experiencing is within the
> transaction itself, you might look at disk I/O for your WAL (write
> ahead logs) and take a closer look at WAL and checkpoint tuning.
>
Also, if you are doing similar operations over and over with literal
data in them, you may have more query planner overhead per transaction
than if you prepared statements when opening the persistent connection
and then simply executed the statements over and over with different
parameters for each request.
Karl
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Client Server performance & UDS
@ 2017-06-09 13:20 Kevin.Hughes@uk.fujitsu.com <Kevin.Hughes@uk.fujitsu.com>
parent: Rick Otten <rottenwindfish@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Kevin.Hughes@uk.fujitsu.com @ 2017-06-09 13:20 UTC (permalink / raw)
To: Rick Otten <rottenwindfish@gmail.com>; +Cc: pgsql-performance
Well after some messing around I have eventually dug out some meaningful(?) information.
We engineered a simple test with a stored procedure that returned immediately – so the SQL is very simple (SELECT * from storedProc). This means no DB activity.
On a test machine that took ~44usecs....measured from the client. This sounds good BUT when I test the UDS on the same machine the round trip latency is ~3usecs. The implication is that the cost is in the message handling – at both ends.
Digging into this further I ran ltrace against the postgres server......it was seriously busy but then again my client was spinning running the same request continually. The results from ltrace shows that the server is doing what appears to be data copies – it’s doing lots of strlen/strncpy/memcpy functions. Since the stored procedure is just returning it shouts that the server costs are dominated by message handling (copying in/out of buffers).
Is there any way that this cost can be reduced? As I understand it Postgres the client/server protocol is platform independent EVEN when the client and server are on the same machine. Is there an optimisation that can avoid this overhead?
Another more radical solution to my admittedly very specific problem is to run the server and client in the same process.....don’t think that can be done with postgres
As always any help appreciated.
Cheers
From: Rick Otten [mailto:rottenwindfish@gmail.com]
Sent: 30 May 2017 11:18
To: Hughes, Kevin <Kevin.Hughes@uk.fujitsu.com>
Cc: pgsql-performa. <pgsql-performance@postgresql.org>
Subject: Re: [PERFORM] Client Server performance & UDS
Establishing a connection with a PostgreSQL database is a very expensive process on the database server. On the other hand, establishing a connection with pgbouncer is very fast. Offloading the connection management to pgbouncer can significantly reduce the connection set up time.
I've found it to help even with applications that have built-in connection pooling.
If your clients are keeping persistent connections open to the database, and the latency you are experiencing is within the transaction itself, you might look at disk I/O for your WAL (write ahead logs) and take a closer look at WAL and checkpoint tuning.
On Tue, May 30, 2017 at 3:34 AM, Kevin.Hughes@uk.fujitsu.com<mailto:Kevin.Hughes@uk.fujitsu.com> <Kevin.Hughes@uk.fujitsu.com<mailto:Kevin.Hughes@uk.fujitsu.com>> wrote:
Hi Rick thanks for the reply.
Our aim is to minimise latency hence we have a dedicated 1:1 relationship between the client and the server. If I use connection pooling surely this introduced latency – getting a server from the pool establishing the connection?
Am I missing something?
From: Rick Otten [mailto:rottenwindfish@gmail.com<mailto:rottenwindfish@gmail.com>]
Sent: 27 May 2017 13:27
To: Hughes, Kevin <Kevin.Hughes@uk.fujitsu.com<mailto:Kevin.Hughes@uk.fujitsu.com>>
Cc: pgsql-performa. <pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>>
Subject: Re: [PERFORM] Client Server performance & UDS
You should have a layer such as pgbouncer between your pg instance and your application. It is designed to mitigate the access latency issues you describe.
On May 26, 2017 10:03 AM, "Kevin.Hughes@uk.fujitsu.com<mailto:Kevin.Hughes@uk.fujitsu.com>" <Kevin.Hughes@uk.fujitsu.com<mailto:Kevin.Hughes@uk.fujitsu.com>> wrote:
Hi,
This is a general question around this performance area rather than a specific performance problem.....so I apologise now for a lack of a specific detail.
We have an application that does many small actions on the DB – and it’s a small DB (a 50/100 Mbytes) so we would expect it to be contained in memory. Accesses need to be low latency – unfortunately there are “serial” accesses where the result of one access governs the next. Luckily the work to be done by the DB is, we believe, very simple and hence fast. Everything is running on one (large) server so we use UDS to connect the client to the server.
Out observation (suspicion) is that the latency of the access, as opposed to the cost of the query, is high. Having done some investigation we believe the UDS latency may be contributing AND the cost imposed by postgres in “formatting” the messages between the client and server (transformation to network format?).
We will try and get underneath this with real results/measurements but I would appreciate any comments pointers on what we are doing and how/if we can optimise this style of applications
Cheers
Unless otherwise stated, this email has been sent from Fujitsu Services Limited (registered in England No 96056); Fujitsu EMEA PLC (registered in England No 2216100) both with registered offices at: 22 Baker Street, London W1U 3BW; PFU (EMEA) Limited, (registered in England No 1578652) and Fujitsu Laboratories of Europe Limited (registered in England No. 4153469) both with registered offices at: Hayes Park Central, Hayes End Road, Hayes, Middlesex, UB4 8FE.
This email is only for the use of its intended recipient. Its contents are subject to a duty of confidence and may be privileged. Fujitsu does not guarantee that this email has not been intercepted and amended or that it is virus-free.
Unless otherwise stated, this email has been sent from Fujitsu Services Limited (registered in England No 96056); Fujitsu EMEA PLC (registered in England No 2216100) both with registered offices at: 22 Baker Street, London W1U 3BW; PFU (EMEA) Limited, (registered in England No 1578652) and Fujitsu Laboratories of Europe Limited (registered in England No. 4153469) both with registered offices at: Hayes Park Central, Hayes End Road, Hayes, Middlesex, UB4 8FE.
This email is only for the use of its intended recipient. Its contents are subject to a duty of confidence and may be privileged. Fujitsu does not guarantee that this email has not been intercepted and amended or that it is virus-free.
Unless otherwise stated, this email has been sent from Fujitsu Services Limited (registered in England No 96056); Fujitsu EMEA PLC (registered in England No 2216100) both with registered offices at: 22 Baker Street, London W1U 3BW; PFU (EMEA) Limited, (registered in England No 1578652) and Fujitsu Laboratories of Europe Limited (registered in England No. 4153469) both with registered offices at: Hayes Park Central, Hayes End Road, Hayes, Middlesex, UB4 8FE.
This email is only for the use of its intended recipient. Its contents are subject to a duty of confidence and may be privileged. Fujitsu does not guarantee that this email has not been intercepted and amended or that it is virus-free.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Client Server performance & UDS
@ 2018-06-07 09:40 e-blokos <infos@e-blokos.com>
parent: Kevin.Hughes@uk.fujitsu.com <Kevin.Hughes@uk.fujitsu.com>
0 siblings, 0 replies; 7+ messages in thread
From: e-blokos @ 2018-06-07 09:40 UTC (permalink / raw)
To: pgsql-performance
It is certainly the TCP loopback overhead you are experiencing.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-06-07 09:40 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-05-26 14:02 Client Server performance & UDS Kevin.Hughes@uk.fujitsu.com <Kevin.Hughes@uk.fujitsu.com>
2017-05-27 12:26 ` Rick Otten <rottenwindfish@gmail.com>
2017-05-30 07:34 ` Kevin.Hughes@uk.fujitsu.com <Kevin.Hughes@uk.fujitsu.com>
2017-05-30 10:17 ` Rick Otten <rottenwindfish@gmail.com>
2017-05-30 20:20 ` Karl Czajkowski <karlcz@isi.edu>
2017-06-09 13:20 ` Kevin.Hughes@uk.fujitsu.com <Kevin.Hughes@uk.fujitsu.com>
2018-06-07 09:40 ` e-blokos <infos@e-blokos.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox