Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dFeDh-0007NO-QW for pgsql-performance@arkaria.postgresql.org; Tue, 30 May 2017 10:17:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dFeDh-0003rb-05 for pgsql-performance@arkaria.postgresql.org; Tue, 30 May 2017 10:17:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dFeDg-0003r6-96 for pgsql-performance@postgresql.org; Tue, 30 May 2017 10:17:44 +0000 Received: from mail-qt0-x235.google.com ([2607:f8b0:400d:c0d::235]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dFeDc-00053p-Fr for pgsql-performance@postgresql.org; Tue, 30 May 2017 10:17:43 +0000 Received: by mail-qt0-x235.google.com with SMTP id v27so66172145qtg.2 for ; Tue, 30 May 2017 03:17:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=MMKeIxoEU+DHGxyvlgiFbDcft4wDKC//bh8O9dNOkSQ=; b=XDfWJu2L2lBatgOFYreuU5s2wE9pkJDGHmN18KwHiLoE3DAh/wQvgjEl39P3oDjKX/ 3qISDAiuVa7CXU4hphGSmA6QeRZ2MKzaBJ/YjyvqsTbUuV7fD6vOHh0Q4i9yvvRGoM8Q hVuu7OAI9KWcAgzkqHopW4MeTeT3AruVHPADXfhY7tj7jYn38qdzrpxGpZae3Peqmz/K fMnU+YmNzpylN9y531St6hF4djyPchkkrUomglkpOohhQ4xOp5aajd6i1V0kL0Q1lq0c NlN3VDhT9t27FLUXmHGF5mg6bGmuEU0zRWqWx5S1fBJMa/5VBLfwC3hhRqh2xSaPwuvo 7RQg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=MMKeIxoEU+DHGxyvlgiFbDcft4wDKC//bh8O9dNOkSQ=; b=dGgAPP+GVBZNO8bBAWpY+jm3xhr+qrqEZgcSqPWxmWKI9cIAj2kmXmkzXbyWuaAq2t pxHziY7U/hVK//Ay7wk/+4Qge/siTdiuz9Rxe13XTGyLBAocYdP9gYwfWtGOLhF2//Qx dHO8EWOR391/C/t/JmMv3A/WycCiMIqM8RUIzPjnmAaLp1yqSGwtommy0ZV2x+uMk8is kzaTuIAe7LKVkgyDE6aOPViLeUTVffeDhCUohIiOXRpOcI2OQHW4+NFX0sL4MneYKjh7 4W9fb3IvqnPRCHNCr1yRschQdGzXzlVcmgJOT7oDmMbStUrrvETg4lkijZsYu8TbCQj1 yDBg== X-Gm-Message-State: AODbwcAE2bCduBIRZYCqlX8Cbnp5rzJBkuiLOHmxvlxNEnu105Lf8vsc gh2KDSibQQS4Rj9iaVNsOCMZR4ktzQ== X-Received: by 10.200.39.219 with SMTP id x27mr23262270qtx.80.1496139458277; Tue, 30 May 2017 03:17:38 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.164.228 with HTTP; Tue, 30 May 2017 03:17:37 -0700 (PDT) In-Reply-To: References: From: Rick Otten Date: Tue, 30 May 2017 06:17:37 -0400 Message-ID: Subject: Re: Client Server performance & UDS To: "Kevin.Hughes@uk.fujitsu.com" Cc: "pgsql-performa." Content-Type: multipart/alternative; boundary="001a113ac204f1f46b0550bb1c07" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a113ac204f1f46b0550bb1c07 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 thi= s > introduced latency =E2=80=93 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 > *Cc:* pgsql-performa. > *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 y= ou > 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 th= e > DB =E2=80=93 and it=E2=80=99s a small DB (a 50/100 Mbytes) so we would ex= pect it to be > contained in memory. Accesses need to be low latency =E2=80=93 unfortunat= ely there > are =E2=80=9Cserial=E2=80=9D accesses where the result of one access gove= rns 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 oppose= d > 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 =E2=80=9Cformatting=E2=80=9D 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) an= d > Fujitsu Laboratories of Europe Limited (registered in England No. 4153469= ) > both with registered offices at: Hayes Park Central, Hayes End Road, Haye= s, > Middlesex, UB4 8FE. > This email is only for the use of its intended recipient. Its contents ar= e > 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) an= d > Fujitsu Laboratories of Europe Limited (registered in England No. 4153469= ) > both with registered offices at: Hayes Park Central, Hayes End Road, Haye= s, > Middlesex, UB4 8FE. > This email is only for the use of its intended recipient. Its contents ar= e > 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. > --001a113ac204f1f46b0550bb1c07 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Establishing a connection with a PostgreSQL database is a = very expensive process on the database server. =C2=A0 On the other hand, es= tablishing a connection with pgbouncer is very fast. =C2=A0 =C2=A0Offloadin= g the connection management to pgbouncer can significantly reduce the conne= ction set up time.

I've found it to help even with a= pplications that have built-in connection pooling.

If your clients are keeping persistent connections open to the database, a= nd the latency you are experiencing is within the transaction itself, you m= ight look at disk I/O for your WAL (write ahead logs) and take a closer loo= k 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.<= u>

=C2=A0

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 =E2=80=93 getting a server from the pool es= tablishing the connection?

=C2=A0

Am I missing something?=

=C2=A0

=C2=A0

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=

=C2=A0

You should have a layer such as pgbouncer between yo= ur pg instance and your application.=C2=A0 It is designed to mitigate the a= ccess latency issues you describe.

=C2=A0

On May 26, 2017 10:03 AM, "Kevin.Hughes@uk.fujitsu.com" <Kevin.Hughes@uk.fujitsu.com> wrote:

Hi,

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 This is a general question around t= his performance area rather than a specific performance problem.....so I ap= ologise now for a lack of a specific detail.

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 We have an application that does ma= ny small actions on the DB =E2=80=93 and it=E2=80=99s a small DB (a 50/100 = Mbytes) so we would expect it to be contained in memory. Accesses need to be low latency =E2=80=93 unfortunately there are =E2=80=9Cserial=E2=80= =9D accesses where the result of one access governs the next. Luckily the= =C2=A0 work to be done by the DB is, we believe, =C2=A0very simple and henc= e fast. Everything is running on one (large) server so we use UDS to connect the client to the server.

=C2=A0

Out observation (suspicion) is that the latency of= =C2=A0 the access, as opposed to the cost of the query, is high. Having don= e some investigation=C2=A0 we believe the UDS latency may be contributing AND the cost imposed by postgres in =E2=80=9Cformatting=E2=80=9D the messa= ges between the client and server (transformation to network format?).

=C2=A0

We will try and get underneath this with real result= s/measurements but I would appreciate any comments pointers on what we are = doing and how/if we can optimise this style of applications

=C2=A0

=C2=A0

Cheers

=C2=A0

=C2=A0

=C2=A0

=C2=A0


Unless otherwise stated, this email has been sent from Fujitsu Services Lim= ited (registered in England No 96056); Fujitsu EMEA PLC (registered in Engl= and No 2216100) both with registered offices at: 22 Baker Street, London W1= U 3BW; PFU (EMEA) Limited, (registered in England No 1578652) and Fujitsu Laboratories of Europe Limited (registe= red in England No. 4153469) both with registered offices at: Hayes Park Cen= tral, 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 gua= rantee that this email has not been intercepted and amended or that it is v= irus-free.


Unless otherwise stated, this email has been sent from Fujitsu Services Lim= ited (registered in England No 96056); Fujitsu EMEA PLC (registered in Engl= and No 2216100) both with registered offices at: 22 Baker Street, London W1= U 3BW; PFU (EMEA) Limited, (registered in England No 1578652) and Fujitsu = Laboratories of Europe Limited (registered in England No. 4153469) both wit= h registered offices at: Hayes Park Central, Hayes End Road, Hayes, Middles= ex, 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 gua= rantee that this email has not been intercepted and amended or that it is v= irus-free.

--001a113ac204f1f46b0550bb1c07--