From: Lazaro Garcia <lazaro3487@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Help with bad performance with synchronous commit
Date: Fri, 31 Mar 2017 10:43:10 -0400
Message-ID: <001f01d2aa2d$1f9f27a0$5edd76e0$@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: lazaro3487@gmail.com
Subject: Re: Help with bad performance with synchronous commit
In-Reply-To: <001f01d2aa2d$1f9f27a0$5edd76e0$@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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