Re: Help with bad performance with synchronous commit

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:

----------------------------------------------------------------------------------------------------------------

From pg_test_fsync. What do you think about that results?

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                        93.886 ops/sec   10651 usecs/op

        fdatasync                            79.473 ops/sec   12583 usecs/op

        fsync                                22.773 ops/sec   43912 usecs/op

        fsync_writethrough                            n/a

        open_sync                            24.556 ops/sec   40723 usecs/op

Compare file sync methods using two 8kB writes:

(in wal_sync_method preference order, except fdatasync is Linux's default)

        open_datasync                        47.113 ops/sec   21226 usecs/op

        fdatasync                            80.205 ops/sec   12468 usecs/op

        fsync                                13.742 ops/sec   72767 usecs/op

        fsync_writethrough                            n/a

        open_sync                            12.198 ops/sec   81981 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            22.501 ops/sec   44442 usecs/op

         2 *  8kB open_sync writes           10.369 ops/sec   96444 usecs/op

         4 *  4kB open_sync writes            5.960 ops/sec  167783 usecs/op

         8 *  2kB open_sync writes            2.457 ops/sec  406981 usecs/op

        16 *  1kB open_sync writes            1.275 ops/sec  784472 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                  25.072 ops/sec   39886 usecs/op

        write, close, fsync                  23.322 ops/sec   42878 usecs/op

Non-sync'ed 8kB writes:

        write                            533368.453 ops/sec       2 usecs/op

-------------------------------------------------------------------------------------------------------------

hdparm -I /dev/sda

ATA device, with non-removable media

        Model Number:       TOSHIBA DT01ACA200

        Serial Number:      Z4NMKKGAS

        Firmware Revision:  MX4OABB0

        Transport:          Serial, ATA8-AST, SATA 1.0a, SATA II Extensions, SATA Rev 2.5, SATA Rev 2.6, SATA Rev 3.0; Revision: ATA8-AST T13 Project D1697 Revision 0b

Standards:

        Used: unknown (minor revision code 0x0029)

        Supported: 8 7 6 5

        Likely used: 8

Configuration:

        Logical         max     current

        cylinders       16383   16383

        heads           16      16

        sectors/track   63      63

        --

        CHS current addressable sectors:   16514064

        LBA    user addressable sectors:  268435455

        LBA48  user addressable sectors: 3907029168

        Logical  Sector size:                   512 bytes

        Physical Sector size:                  4096 bytes

        Logical Sector-0 offset:                  0 bytes

        device size with M = 1024*1024:     1907729 MBytes

        device size with M = 1000*1000:     2000398 MBytes (2000 GB)

        cache/buffer size  = unknown

        Form Factor: 3.5 inch

        Nominal Media Rotation Rate: 7200

Capabilities:

        LBA, IORDY(can be disabled)

        Queue depth: 32

        Standby timer values: spec'd by Standard, no device specific minimum

        R/W multiple sector transfer: Max = 16  Current = 0

        Advanced power management level: disabled

        DMA: mdma0 mdma1 mdma2 udma0 udma1 udma2 udma3 udma4 udma5 *udma6

             Cycle time: min=120ns recommended=120ns

        PIO: pio0 pio1 pio2 pio3 pio4

             Cycle time: no flow control=120ns  IORDY flow control=120ns

Commands/features:

        Enabled Supported:

           *    SMART feature set

                Security Mode feature set

           *    Power Management feature set

                Write cache

           *    Look-ahead

           *    Host Protected Area feature set

           *    WRITE_BUFFER command

           *    READ_BUFFER command

           *    NOP cmd

           *    DOWNLOAD_MICROCODE

                Advanced Power Management feature set

                Power-Up In Standby feature set

           *    SET_FEATURES required to spinup after power up

                SET_MAX security extension

           *    48-bit Address feature set

           *    Device Configuration Overlay feature set

           *    Mandatory FLUSH_CACHE

           *    FLUSH_CACHE_EXT

           *    SMART error logging

           *    SMART self-test

                Media Card Pass-Through

           *    General Purpose Logging feature set

           *    WRITE_{DMA|MULTIPLE}_FUA_EXT

           *    64-bit World wide name

           *    URG for READ_STREAM[_DMA]_EXT

           *    URG for WRITE_STREAM[_DMA]_EXT

           *    WRITE_UNCORRECTABLE_EXT command

           *    {READ,WRITE}_DMA_EXT_GPL commands

           *    Segmented DOWNLOAD_MICROCODE

                unknown 119[7]

           *    Gen1 signaling speed (1.5Gb/s)

           *    Gen2 signaling speed (3.0Gb/s)

           *    Gen3 signaling speed (6.0Gb/s)

           *    Native Command Queueing (NCQ)

           *    Host-initiated interface power management

           *    Phy event counters

           *    NCQ priority information

                Non-Zero buffer offsets in DMA Setup FIS

           *    DMA Setup Auto-Activate optimization

                Device-initiated interface power management

                In-order data delivery

           *    Software settings preservation

           *    SMART Command Transport (SCT) feature set

           *    SCT Write Same (AC2)

           *    SCT Error Recovery Control (AC3)

           *    SCT Features Control (AC4)

           *    SCT Data Tables (AC5)

Security:

        Master password revision code = 65534

                supported

        not     enabled

        not     locked

                frozen

       not     expired: security count

        not     supported: enhanced erase

        320min for SECURITY ERASE UNIT.

Logical Unit WWN Device Identifier: 5000039ffae513fd

        NAA             : 5

        IEEE OUI        : 000039

        Unique ID       : ffae513fd

Checksum: correct

---------------------------------------------------------------------------------------------

parted /dev/sda unit s print

Model: ATA TOSHIBA DT01ACA2 (scsi)

Disk /dev/sda: 3907029168s

Sector size (logical/physical): 512B/4096B Partition Table: gpt Disk Flags:

Number  Start      End          Size         File system     Name  Flags

1      2048s      4095s        2048s                              bios_grub

2      4096s      1003519s     999424s      ext2

3      1003520s   17004543s    16001024s    linux-swap(v1)

4      17004544s  3907028991s  3890024448s  ext4

I think that results could be better. What do you think?

Thank you very much for your time.

Regards.

De: David G. Johnston [mailto:david.g.johnston@gmail.com]
Enviado el: viernes, 31 de marzo de 2017 10:57 a. m.
Para: Lazaro Garcia
CC: pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] Help with bad performance with synchronous commit

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: