Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cu0jA-0001dt-8Y for pgsql-admin@arkaria.postgresql.org; Fri, 31 Mar 2017 17:52:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cu0j9-0002I7-Mw for pgsql-admin@arkaria.postgresql.org; Fri, 31 Mar 2017 17:52:47 +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 1cu0j8-0002Hh-Oh for pgsql-admin@postgresql.org; Fri, 31 Mar 2017 17:52:47 +0000 Received: from mail-io0-x22f.google.com ([2607:f8b0:4001:c06::22f]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cu0j5-0004uZ-FU for pgsql-admin@postgresql.org; Fri, 31 Mar 2017 17:52:45 +0000 Received: by mail-io0-x22f.google.com with SMTP id l7so44237033ioe.3 for ; Fri, 31 Mar 2017 10:52:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :thread-index:content-language; bh=p67JprsbCez1sUqZ8jKGcAVGBMCA4qIg/zgilbQ0NeU=; b=BSWzA6Mxe/FZ7QC/8dbNsYo7vpMtXkpbWLLhAww0N7ZgOaIuRrgz6m3zt+m3/I9pHT cfBxrljdqx+IT78LNA4jvUBOAg2NS81ExHxAe2VwBONalKDaedlk9C3j+Qq14QzBuHOW TqiWuV7vvCr8B/80aZjgSSmOfnxBNLD2QTtLw3YlemQtXD9NxEGFhuavCCgUYkh0JXB+ oCE31bEiJjYjolS8sV7ZQFqe41G6tq1PaQMN8NeCrpT02n2Fk9jp6c2tZwqjMlb8Vntn Jm3rzKEDys0hHsQuEpuqiHmjQZo8W5m1Wveha+rqSGXTCOzfECdtMsNGqhSBj6vruONs A/Lw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=p67JprsbCez1sUqZ8jKGcAVGBMCA4qIg/zgilbQ0NeU=; b=lHwN9oYVN6YayAGO7ILDnz017R2NDPMgmNO6DO2qXwhMyziacXJ2bTm3Fr9aNTPOlu dtbCTrhZHUHex72N0zq/VhZY3oM/bF2N3oZ7zx+nRkdMWfDrzUGWsy+xjDe9g4RTIRDB 5IU9k5nD15R2QjVJIqrkYNvsG+dYOMkMC21xjSRMG/nFjnzY1xU2eBJ55dG0r+bcKsNJ JYUp4o6NuUQp5zh/ikSXaiwinto5TZwXE7FIKqyV42fyhNxXN5SmFDOPgv5ZJQJOAIFN 300n1PD+KBTT7rIAtKk3wx7W72GsBdDZ9nN3H8c0hRtOC/s9aIsj/MpdJb2Nycvwajn2 4vxQ== X-Gm-Message-State: AFeK/H36VtP3AmbLcVYL9Bs6ZVryfb/cQfZBaftEELejF13ktyQQPwQATK4iNX1v7P0YGQ== X-Received: by 10.107.13.11 with SMTP id 11mr5537566ion.226.1490982762555; Fri, 31 Mar 2017 10:52:42 -0700 (PDT) Received: from irylachi ([200.55.161.75]) by smtp.gmail.com with ESMTPSA id f63sm1610518ita.26.2017.03.31.10.52.34 (version=TLS1_2 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Fri, 31 Mar 2017 10:52:42 -0700 (PDT) From: "Lazaro Garcia" To: "'Samed YILDIRIM'" , References: <001f01d2aa2d$1f9f27a0$5edd76e0$@gmail.com> <6346761490978204@web56g.yandex.ru> In-Reply-To: <6346761490978204@web56g.yandex.ru> Subject: Re: Help with bad performance with synchronous commit Date: Fri, 31 Mar 2017 13:52:31 -0400 Message-ID: <002e01d2aa47$94ff87a0$befe96e0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002F_01D2AA26.0DF2C9A0" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AQFuVlWdhUo+nA1nmFNyISMTKOiVPQJFKi/LomW2fwA= 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_002F_01D2AA26.0DF2C9A0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable After running the test with a scale factor of 100 this is the result: =20 transaction type: scaling factor: 100 query mode: simple number of clients: 80 number of threads: 12 duration: 60 s number of transactions actually processed: 13607 latency average =3D 353.630 ms latency stddev =3D 169.859 ms tps =3D 225.995896 (including connections establishing) tps =3D 226.009386 (excluding connections establishing) =20 During test execution iostat shows that: =20 avg-cpu: %user %nice %system %iowait %steal %idle 1.93 0.00 0.59 65.97 0.00 31.52 =20 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s = avgrq-sz avgqu-sz await r_await w_await svctm %util sda 0.00 0.00 305.00 10.00 2608.00 2112.00 = 29.97 21.93 73.66 72.83 99.20 3.17 100.00 sda1 0.00 0.00 0.00 0.00 0.00 0.00 = 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sda2 0.00 0.00 0.00 0.00 0.00 0.00 = 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sda3 0.00 0.00 0.00 0.00 0.00 0.00 = 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sda4 0.00 0.00 305.00 10.00 2608.00 2112.00 = 29.97 21.93 73.66 72.83 99.20 3.17 100.00 sdb 0.00 0.00 0.00 0.00 0.00 0.00 = 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb1 0.00 0.00 0.00 0.00 0.00 0.00 = 0.00 0.00 0.00 0.00 0.00 0.00 0.00 =20 Here the iowait is very higth. =20 When execute pg_test_fsync: =20 O_DIRECT supported on this platform for open_datasync and open_sync. =20 Compare file sync methods using one 8kB write: (in wal_sync_method preference order, except fdatasync is Linux's = default) open_datasync 101.279 ops/sec 9874 = usecs/op fdatasync 71.367 ops/sec 14012 = usecs/op fsync 20.590 ops/sec 48567 = usecs/op fsync_writethrough n/a open_sync 26.809 ops/sec 37301 = usecs/op =20 Compare file sync methods using two 8kB writes: (in wal_sync_method preference order, except fdatasync is Linux's = default) open_datasync 45.375 ops/sec 22038 = usecs/op fdatasync 73.560 ops/sec 13594 = usecs/op fsync 22.099 ops/sec 45251 = usecs/op fsync_writethrough n/a open_sync 10.872 ops/sec 91981 = usecs/op =20 Compare open_sync with different write sizes: (This is designed to compare the cost of writing 16kB in different write open_sync sizes.) 1 * 16kB open_sync write 18.690 ops/sec 53504 = usecs/op 2 * 8kB open_sync writes 9.397 ops/sec 106418 = usecs/op 4 * 4kB open_sync writes 5.399 ops/sec 185206 = usecs/op 8 * 2kB open_sync writes 2.426 ops/sec 412196 = usecs/op 16 * 1kB open_sync writes 1.173 ops/sec 852505 = usecs/op =20 Test if fsync on non-write file descriptor is honored: (If the times are similar, fsync() can sync data written on a different descriptor.) write, fsync, close 20.269 ops/sec 49336 = usecs/op write, close, fsync 24.950 ops/sec 40079 = usecs/op =20 Non-sync'ed 8kB writes: write 498332.902 ops/sec 2 = usecs/op =20 Here the ops/sec are very slow. =20 Any recommendation. =20 Regards. =20 =20 De: Samed YILDIRIM [mailto:samed@reddoc.net]=20 Enviado el: viernes, 31 de marzo de 2017 12:37 p. m. Para: Lazaro Garcia; pgsql-admin@postgresql.org Asunto: Re: [ADMIN] Help with bad performance with synchronous commit =20 Hi Lazaro, =20 Please initialize your test database with scale factor 50 by using = pgbench than perform test again and share results to us? =20 Best regards. 17:45, 31 Mart 2017, Lazaro Garcia >: Hello everyone =20 I wite because I have a great problem and I have no idea how resolve = this: =20 This is the situation: =20 I have installed a postgresql server 9.6.2 on Ubuntu server 16.04.2 LTS = with this specifications: =20 12 cores y 30 GB de RAM. =20 I have created a database with pgbench for testing and the performance = are very bad with synchronous commit. These are the results: =20 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) =20 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: =20 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 =20 I have runned the same test in other machines with less RAM and CPU and = the results are always better than this: =20 Any help to identify the problem could be very appreciated. =20 Regards ------=_NextPart_000_002F_01D2AA26.0DF2C9A0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

After running the test with a scale factor = of 100 this is the result:

 

transaction type: <builtin: TPC-B (sort = of)>

scaling factor: = 100

query mode: simple

number of clients: = 80

number of threads: = 12

duration: 60 s

number of transactions actually processed: = 13607

latency average =3D 353.630 = ms

latency stddev =3D 169.859 = ms

tps =3D 225.995896 (including connections = establishing)

tps =3D 226.009386 (excluding connections = establishing)

 

During test execution iostat shows = that:

 

avg-cpu:=C2=A0 %user=C2=A0=C2=A0 %nice = %system %iowait=C2=A0 %steal=C2=A0=C2=A0 %idle

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 1.93=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 = 0.59=C2=A0=C2=A0 65.97=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0 = 31.52

 

Device:=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 rrqm/s=C2=A0=C2=A0 wrqm/s=C2=A0=C2=A0=C2=A0=C2=A0 = r/s=C2=A0=C2=A0=C2=A0=C2=A0 w/s=C2=A0=C2=A0=C2=A0 = rkB/s=C2=A0=C2=A0=C2=A0 wkB/s avgrq-sz avgqu-sz=C2=A0=C2=A0 await = r_await w_await=C2=A0 svctm=C2=A0 %util

sda=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 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0 305.00=C2=A0=C2=A0 10.00=C2=A0 2608.00=C2=A0 = 2112.00=C2=A0=C2=A0=C2=A0 29.97=C2=A0=C2=A0=C2=A0 21.93=C2=A0=C2=A0 = 73.66=C2=A0=C2=A0 72.83=C2=A0=C2=A0 99.20=C2=A0=C2=A0 3.17 = 100.00

sda1=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 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0 0.00=C2=A0=C2=A0 0.00

sda2=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 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0 =C2=A0=C2=A00.00=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0 0.00=C2=A0=C2=A0 0.00

sda3=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 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0 0.00=C2=A0=C2=A0 0.00

sda4=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 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0 305.00=C2=A0=C2=A0 10.00=C2=A0 2608.00=C2=A0 = 2112.00=C2=A0=C2=A0=C2=A0 29.97=C2=A0=C2=A0=C2=A0 21.93=C2=A0=C2=A0 = 73.66=C2=A0=C2=A0 72.83=C2=A0=C2=A0 99.20=C2=A0=C2=A0 3.17 = 100.00

sdb=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 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0 0.00=C2=A0=C2=A0 0.00

sdb1=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 0.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0=C2=A0 0.00 = =C2=A0=C2=A0=C2=A0=C2=A00.00=C2=A0=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0=C2=A0 0.00=C2=A0=C2=A0 = 0.00=C2=A0=C2=A0 0.00

 

Here the iowait is very = higth.

 

When execute = pg_test_fsync:

 

O_DIRECT supported on this platform for = open_datasync and open_sync.

 

Compare file sync methods using one 8kB = write:

(in wal_sync_method preference order, = except fdatasync is Linux's default)

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = open_datasync=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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 101.279 ops/sec=C2=A0=C2=A0=C2=A0 9874 usecs/op

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = fdatasync=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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 71.367 ops/sec=C2=A0=C2=A0 14012 = usecs/op

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = fsync=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=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 20.590 ops/sec=C2=A0=C2=A0 = 48567 usecs/op

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = fsync_writethrough=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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 n/a

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = open_sync=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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 26.809 ops/sec=C2=A0=C2=A0 37301 = usecs/op

 

Compare file sync methods using two 8kB = writes:

(in wal_sync_method preference order, = except fdatasync is Linux's default)

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = open_datasync=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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 45.375 ops/sec=C2=A0=C2=A0 22038 usecs/op

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = fdatasync=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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 73.560 ops/sec=C2=A0=C2=A0 13594 = usecs/op

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = fsync=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=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 22.099 ops/sec=C2=A0=C2=A0 = 45251 usecs/op

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = fsync_writethrough=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=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0n/a

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = open_sync=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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 10.872 ops/sec=C2=A0=C2=A0 91981 = usecs/op

 

Compare open_sync with different write = sizes:

(This is designed to compare the cost of = writing 16kB in different write

open_sync sizes.)

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 1 * 16kB open_sync = write=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 18.690 ops/sec=C2=A0=C2=A0 53504 usecs/op

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 2 *=C2=A0 8kB open_sync = writes=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 9.397 ops/sec=C2=A0 106418 usecs/op

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 4 *=C2=A0 4kB open_sync = writes=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 5.399 ops/sec=C2=A0 185206 usecs/op

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 8 *=C2=A0 2kB open_sync = writes=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 2.426 ops/sec=C2=A0 412196 usecs/op

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 16 *=C2=A0 1kB open_sync = writes=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 1.173 ops/sec=C2=A0 852505 usecs/op

 

Test if fsync on non-write file descriptor = is honored:

(If the times are similar, fsync() can = sync data written on a different

descriptor.)

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = write, fsync, = close=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=C2=A0 20.269 ops/sec=C2=A0=C2=A0 49336 = usecs/op

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = write, close, = fsync=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=C2=A0 24.950 ops/sec=C2=A0=C2=A0 40079 = usecs/op

 

Non-sync'ed 8kB = writes:

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = write=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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 498332.902 ops/sec=C2=A0=C2=A0=C2=A0 = =C2=A0=C2=A0=C2=A02 usecs/op

 

Here the ops/sec=C2=A0=C2=A0 are very = slow.

 

Any = recommendation.

 

Regards.

 

 

De:<= /b> = Samed YILDIRIM [mailto:samed@reddoc.net]
Enviado el: viernes, = 31 de marzo de 2017 12:37 p. m.
Para: Lazaro Garcia; = pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] Help with bad = performance with synchronous commit

 

Hi = Lazaro,

 

Please initialize your test database with scale factor = 50 by using pgbench than perform test again and share results to = us?

 

Best regards.

17:45, 31 Mart 2017, Lazaro = Garcia <lazaro3487@gmail.com>:

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_002F_01D2AA26.0DF2C9A0--