Re: Help with bad performance with synchronous commit

Hi Lazaro,<div><br /></div><div>Please initialize your test database with scale factor 50 by using pgbench than perform test again and share results to us?</div><div><br /></div><div>Best regards.<br /><br />17:45, 31 Mart 2017, Lazaro Garcia &lt;lazaro3487(at)gmail(dot)com&gt;:<br /><blockquote><div lang="ES" link="#0563C1" vlink="#954F72"><div><p>Hello everyone</p><p> </p><p><span lang="EN-US">I wite because I have a great problem and I have no idea how resolve this:</span></p><p> </p><p><span lang="EN-US">This is the situation:</span></p><p><span lang="EN-US"> </span></p><p><span lang="EN-US">I have installed a postgresql server 9.6.2 on Ubuntu server 16.04.2 LTS with this specifications:</span></p><p><span lang="EN-US"> </span></p><p>12 cores y 30 GB de RAM.</p><p> </p><p><span lang="EN-US">I have created a database with pgbench for testing and the performance are very bad with  synchronous commit. These are the results:</span></p><p><span lang="EN-US"> </span></p><p>scaling factor: 1</p><p><span lang="EN-US">query mode: simple</span></p><p><span lang="EN-US">number of clients: 50</span></p><p><span lang="EN-US">number of threads: 12</span></p><p><span lang="EN-US">duration: 30 s</span></p><p><span lang="EN-US">number of transactions actually processed: 2773</span></p><p><span lang="EN-US">latency average = 544.336 ms</span></p><p><span lang="EN-US">latency stddev = 617.941 ms</span></p><p><span lang="EN-US">tps = 91.180927 (including connections establishing)</span></p><p><span lang="EN-US">tps = 91.194080 (excluding connections establishing)</span></p><p><span lang="EN-US"> </span></p><p><span lang="EN">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:</span></p><p><span lang="EN-US"> </span></p><p><span lang="EN-US">Update on pgbench_tellers  (cost=4.14..8.16 rows=1 width=358) (actual time=0.021..0.021 rows=0 loops=1)</span></p><p><span lang="EN-US">   -&gt;  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)</span></p><p><span lang="EN-US">         Recheck Cond: (tid = 5)</span></p><p><span lang="EN-US">         Heap Blocks: exact=1</span></p><p><span lang="EN-US">         -&gt;  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)</span></p><p><span lang="EN-US">               Index Cond: (tid = 5)</span></p><p><span lang="EN-US">Planning time: 0.052 ms</span></p><p>Execution time: 0.043 ms</p><p> </p><p><span lang="EN-US">I have runned the same test in other machines with less RAM and CPU and the results are always better than this:</span></p><p><span lang="EN-US"> </span></p><p><span lang="EN-US">Any help to identify the problem could be very appreciated.</span></p><p><span lang="EN-US"> </span></p><p><span lang="EN-US">Regards</span></p></div></div></blockquote></div>