Help with bad performance with synchronous commit

From: "Lazaro Garcia" <lazaro3487(at)gmail(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Help with bad performance with synchronous commit
Date: 2017-03-31 14:43:10
Message-ID: 001f01d2aa2d$1f9f27a0$5edd76e0$@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2017-03-31 14:57:01 Re: Help with bad performance with synchronous commit
Previous Message Douglas Brown 2017-03-29 13:24:57 Re: Integrating Postgresql with Active Directory