Re: Help with bad performance with synchronous commit

["Lazaro Garcia" <lazaro3487@xxxxxxxxx>]

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@xxxxxxxxxx]
Enviado el: viernes, 31 de marzo de 2017 12:37 p. m.
Para: Lazaro Garcia; pgsql-admin@xxxxxxxxxxxxxx
Asunto: Re: 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@xxxxxxxxx>:

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