Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ctxni-0001FB-G9 for pgsql-admin@arkaria.postgresql.org; Fri, 31 Mar 2017 14:45:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ctxng-000702-OI for pgsql-admin@arkaria.postgresql.org; Fri, 31 Mar 2017 14:45:16 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ctxls-000278-EG for pgsql-admin@postgresql.org; Fri, 31 Mar 2017 14:43:24 +0000 Received: from mail-io0-x22c.google.com ([2607:f8b0:4001:c06::22c]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1ctxll-0000wg-Ea for pgsql-admin@postgresql.org; Fri, 31 Mar 2017 14:43:23 +0000 Received: by mail-io0-x22c.google.com with SMTP id l7so40842396ioe.3 for ; Fri, 31 Mar 2017 07:43:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:subject:date:message-id:mime-version:thread-index :content-language; bh=ATUvWGuQhe5jyLigBHhz7se/uHpE+ipXRBoo7dWcbyA=; b=ExcCLe+2lDmnaZExBz2+HLK128Xc+2sW+ea40iavIvq6/D8TsjjE7N0fTGaVR72qk0 Zaln73aErlrQOOeaja0EZhap2yKSLJAo4bdza13Y2wy4MXP1qY0IK8Ezt/+cIQbWOK9p yhFWfsVOprI9BSZeN74Mya1hoxzOBCjblvj1LV+4dCE6WCqoSXl9rSLRmCkir/hM7slN axp26SAUPaPD/ViLcZJpaERFqlRkweWJiMvuhqf+U6QLAuuUaKNQ9wslern5m1zquj9k smhjP+xDs2lHNAkp1YLgat85Q4PoH+r0fk3G1XcB/1neXrVkJsBui2xEbLl+Y4eD4tnv 7AvA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:subject:date:message-id:mime-version :thread-index:content-language; bh=ATUvWGuQhe5jyLigBHhz7se/uHpE+ipXRBoo7dWcbyA=; b=Dc2OIqs9qKJUUe8IiF7HsrXJisgTHOMFukJHBHqVeA+TrfOTvOk8Dvb0Wa4GvQKrpL +0oSyKz+hUMVm9ujjJT7XpqpiVRlIFNmaypE3qmoVIYYQJ/qG+tloF9xNut1LK4fFK02 jymNqPWCTqKWSLN4Rd433X2Twx8Eo8+UzQJROvtPVvYvmpfNCwnf0nFZpVIgRxJdv+Vy ZxeNfMLrMyYGh6Ama8HoTWfYxadlyrLGceEKi2SblxED+9M+sA78oMK/Ww7yTop7oXkE Mxb+N9aJ9IAWi+OAdL6zo4wLyi7321UKU7hPljdHIrpmiX4E8sO31mQ76YveJYCMGVfe iyXw== X-Gm-Message-State: AFeK/H20ZR5zaY/GmIvgCJAobJVMcvvL6/2DTzf7cSl8wqufkDtwZLneq/3Fy77N2+8oxQ== X-Received: by 10.107.201.138 with SMTP id z132mr3369890iof.220.1490971396549; Fri, 31 Mar 2017 07:43:16 -0700 (PDT) Received: from irylachi ([200.55.161.75]) by smtp.gmail.com with ESMTPSA id q185sm1344727itc.22.2017.03.31.07.43.11 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Fri, 31 Mar 2017 07:43:15 -0700 (PDT) From: "Lazaro Garcia" To: Subject: Help with bad performance with synchronous commit Date: Fri, 31 Mar 2017 10:43:10 -0400 Message-ID: <001f01d2aa2d$1f9f27a0$5edd76e0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0020_01D2AA0B.988E23E0" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AdKqK3l5QmAXhz5fRIKRcKT1eC22+Q== Content-Language: es-mx X-Pg-Spam-Score: -2.4 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0020_01D2AA0B.988E23E0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello everyone I wite because I have a great problem and I have no idea how resolve this: This is the situation: I have installed a postgresql server 9.6.2 on Ubuntu server 16.04.2 LTS with this specifications: 12 cores y 30 GB de RAM. I have created a database with pgbench for testing and the performance are very bad with synchronous commit. These are the results: scaling factor: 1 query mode: simple number of clients: 50 number of threads: 12 duration: 30 s number of transactions actually processed: 2773 latency average = 544.336 ms latency stddev = 617.941 ms tps = 91.180927 (including connections establishing) tps = 91.194080 (excluding connections establishing) By analyzing the postgres log with pgbadger I could see that the updates take a long time for a table with 10 tuples only. Then I ran a explain analyze and the explain results contradict what the test throws: Update on pgbench_tellers (cost=4.14..8.16 rows=1 width=358) (actual time=0.021..0.021 rows=0 loops=1) -> Bitmap Heap Scan on pgbench_tellers (cost=4.14..8.16 rows=1 width=358) (actual time=0.010..0.010 rows=1 loops=1) Recheck Cond: (tid = 5) Heap Blocks: exact=1 -> Bitmap Index Scan on pgbench_tellers_pkey (cost=0.00..4.14 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1) Index Cond: (tid = 5) Planning time: 0.052 ms Execution time: 0.043 ms I have runned the same test in other machines with less RAM and CPU and the results are always better than this: Any help to identify the problem could be very appreciated. Regards ------=_NextPart_000_0020_01D2AA0B.988E23E0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello everyone

 

I wite because I have a great problem and I have no idea = how resolve this:

 

This is the situation:

 

I have installed a postgresql = server 9.6.2 on Ubuntu server 16.04.2 LTS with this = specifications:

 

12 cores y = 30 GB de RAM.

 

I have created a database with = pgbench for testing and the performance are very bad with =  synchronous commit. These are the results:

 

scaling factor: 1

query mode: = simple

number of clients: 50

number of threads: = 12

duration: 30 s

number of transactions actually = processed: 2773

latency average =3D 544.336 ms

latency stddev =3D 617.941 = ms

tps =3D = 91.180927 (including connections establishing)

tps =3D 91.194080 (excluding = connections establishing)

 

By analyzing the postgres log with = pgbadger I could see that the updates take a long time for a table with = 10 tuples only. Then I ran a explain analyze and the explain results = contradict what the test throws:

 

Update on pgbench_tellers  = (cost=3D4.14..8.16 rows=3D1 width=3D358) (actual time=3D0.021..0.021 = rows=3D0 loops=3D1)

   ->  Bitmap Heap Scan on = pgbench_tellers  (cost=3D4.14..8.16 rows=3D1 width=3D358) (actual = time=3D0.010..0.010 rows=3D1 loops=3D1)

   =       Recheck Cond: (tid =3D = 5)

         Heap = Blocks: exact=3D1

         = ->  Bitmap Index Scan on pgbench_tellers_pkey  = (cost=3D0.00..4.14 rows=3D1 width=3D0) (actual time=3D0.005..0.005 = rows=3D1 loops=3D1)

          =      Index Cond: (tid =3D 5)

Planning time: 0.052 = ms

Execution time: 0.043 = ms

 

I have runned the same test in = other machines with less RAM and CPU and the results are always better = than this:

 

Any help to identify the problem could be very = appreciated.

 

Regards

------=_NextPart_000_0020_01D2AA0B.988E23E0--