RE: Ayuda - Rendimiento muy malo con Synchronous Commit

Alvaro desinstalé 9.6 e instalé la versión 9.5.6 que viene en los
repositorios de Ubuntu (no desde los repos de Postgres), luego ejecuté el
test nuevamente y estos fueron los resultados:

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)

Dos días después lo ejecuté nuevamente y el número de transacciones
disminuyó considerablemente:

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)

Manteniéndose más o menos contantes estos valores con un por ciento de
iowait sobre los 15 y 25 cuando ejecuto el test.

Me podrías dar algún consejo o pista del porqué de esta diferencia?

Que parámetros podría tener en cuenta para configurar de forma más eficiente
commit_delay y commit_siblings?

Debajo aparecen los detalles del disco:

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

Este es el resultado al ejecutar pg_test_fsync, que crees?

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

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

Estos son los datos del disco: 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

Me podrías dar algún consejo de tuning para los discos?

Saludos y muchas gracias por tu tiempo.

-----Mensaje original-----
De: Alvaro Herrera [mailto:alvherre(at)2ndquadrant(dot)com]
Enviado el: viernes, 31 de marzo de 2017 10:29 a. m.
Para: Lazaro Garcia
CC: 'Ayuda'
Asunto: Re: [pgsql-es-ayuda] Ayuda - Rendimiento muy malo con Synchronous
Commit

Lazaro Garcia escribió:

> scaling factor: 1

> number of clients: 50

> Analizando el log de postgres con pgbadger pude ver que los updates
> demoran enormemente para una tabla con 10 tuplas solamente. Luego
> ejecuté un explain analyze y los resultados del explain se contradicen a
lo que arroja el test:
>
>
>
> Update on pgbench_tellers (cost=4.14..8.16 rows=1 width=358) (actual
> time=0.021..0.021 rows=0 loops=1)

Este test no tiene sentido. Si la tabla es muy pequeña, los update van a
estar en conflicto permanente unos con otros, y por supuesto eso demorará.
Repite el test con un "scale" mayor (entiendo que la idea es que el scale
debería ser al menos tan grande como el núm de clientes)

Dicho eso, ni siquiera mencionaste la configuración de discos (así que
seguramente son lentos), y el sinc commit es sobre todo un test a qué tan
rápido puedes hacer flush a disco.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org)
Para cambiar tu suscripción:
http://www.postgresql.org/mailpref/pgsql-es-ayuda