Re: Help with bad performance with synchronous commit

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(at)reddoc(dot)net]
Enviado el: viernes, 31 de marzo de 2017 12:37 p. m.
Para: Lazaro Garcia; pgsql-admin(at)postgresql(dot)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(at)gmail(dot)com <mailto:lazaro3487(at)gmail(dot)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