pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with bad performance with synchronous commit
5+ messages / 3 participants
[nested] [flat]
* Help with bad performance with synchronous commit
@ 2017-03-31 14:43 Lazaro Garcia <lazaro3487@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Lazaro Garcia @ 2017-03-31 14:43 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help with bad performance with synchronous commit
@ 2017-03-31 14:57 David G. Johnston <david.g.johnston@gmail.com>
parent: Lazaro Garcia <lazaro3487@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2017-03-31 14:57 UTC (permalink / raw)
To: Lazaro Garcia <lazaro3487@gmail.com>; +Cc: pgsql-admin
On Friday, March 31, 2017, Lazaro Garcia <lazaro3487@gmail.com> wrote:
> I have runned the same test in other machines with less RAM and CPU and
> the results are always better than this:
>
Which leaves questions about the storage device unanswered - and you are
using a disk-writing query.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help with bad performance with synchronous commit
@ 2017-03-31 16:36 Samed YILDIRIM <samed@reddoc.net>
parent: Lazaro Garcia <lazaro3487@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Samed YILDIRIM @ 2017-03-31 16:36 UTC (permalink / raw)
To: Lazaro Garcia <lazaro3487@gmail.com>; pgsql-admin
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 <lazaro3487@gmail.com>:<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"> -> 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"> -> 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>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help with bad performance with synchronous commit
@ 2017-03-31 17:52 Lazaro Garcia <lazaro3487@gmail.com>
parent: Samed YILDIRIM <samed@reddoc.net>
0 siblings, 0 replies; 5+ messages in thread
From: Lazaro Garcia @ 2017-03-31 17:52 UTC (permalink / raw)
To: 'Samed YILDIRIM' <samed@reddoc.net>; pgsql-admin
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 = 353.630 ms
latency stddev = 169.859 ms
tps = 225.995896 (including connections establishing)
tps = 226.009386 (excluding connections establishing)
During test execution iostat shows that:
avg-cpu: %user %nice %system %iowait %steal %idle
1.93 0.00 0.59 65.97 0.00 31.52
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
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)
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
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
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
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
Non-sync'ed 8kB writes:
write 498332.902 ops/sec 2 usecs/op
Here the ops/sec are very slow.
Any recommendation.
Regards.
De: 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 <mailto: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 = 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help with bad performance with synchronous commit
@ 2017-04-03 16:54 Lazaro Garcia <lazaro3487@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Lazaro Garcia @ 2017-04-03 16:54 UTC (permalink / raw)
To: 'David G. Johnston' <david.g.johnston@gmail.com>; +Cc: pgsql-admin
Thanks for the reply:
pgbench -U postgres -p 6432 -j 10 -c 500 -T 60 -h localhost -P 2 pgbench (write test)
I have uninstalled 9.6 and installed postgresql 9.5.6 from Ubuntu repos. These was de new results:
transaction type: TPC-B (sort of)
scaling factor: 200
query mode: simple
number of clients: 500
number of threads: 10
duration: 60 s
number of transactions actually processed: 236733 latency average: 126.187 ms latency stddev: 152.347 ms tps = 3919.425981 (including connections establishing) tps = 3919.945554 (excluding connections establishing)
2 days later the test throws less results but constant.
transaction type: TPC-B (sort of)
scaling factor: 200
query mode: simple
number of clients: 500
number of threads: 10
duration: 60 s
number of transactions actually processed: 76788 latency average: 394.308 ms tps = 1268.044488 (including connections establishing) tps = 1268.124718 (excluding connections establishing)
The iowait was between 15 and 25 percet when the test was runing:
Is there any way to identify the cause of this differences?
How can I configure commit_delay and commit_siblings properly?
These are disk details:
----------------------------------------------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-04-03 16:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-03-31 14:43 Help with bad performance with synchronous commit Lazaro Garcia <lazaro3487@gmail.com>
2017-03-31 14:57 ` David G. Johnston <david.g.johnston@gmail.com>
2017-04-03 16:54 ` Lazaro Garcia <lazaro3487@gmail.com>
2017-03-31 16:36 ` Samed YILDIRIM <samed@reddoc.net>
2017-03-31 17:52 ` Lazaro Garcia <lazaro3487@gmail.com>
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