saurabhnanda

Time per request:       0.255 [ms] (mean, across all concurrent requests)
Time per request:       0.250 [ms] (mean, across all concurrent requests)
Time per request:       0.279 [ms] (mean, across all concurrent requests)
Time per request:       0.263 [ms] (mean, across all concurrent requests)
Time per request:       0.251 [ms] (mean, across all concurrent requests)
Time per request:       0.354 [ms] (mean, across all concurrent requests)
Time per request:       0.251 [ms] (mean, across all concurrent requests)
Time per request:       0.256 [ms] (mean, across all concurrent requests)
Time per request:       0.250 [ms] (mean, across all concurrent requests)
Time per request:       0.359 [ms] (mean, across all concurrent requests)

saurabhnanda

<Plugin postgresql>
	<Writer sqlstore>
		# See /usr/share/doc/collectd-core/examples/postgresql/collectd_insert.sql for details
		Statement "SELECT collectd_insert($1, $2, $3, $4, $5, $6, $7, $8, $9);"
		StoreRates true
	</Writer>
	<Database collectd>
		Host "REDACTED"
		Port "5433"
		User "REDACTED"

saurabhnanda

[LOCALHOST:~ saurabhnanda] $ brew install netpbm
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 1 tap (homebrew/core).
==> New Formulae
triangle
==> Updated Formulae
dep                                kafka                              mandoc                             simple-scan

Warning: You are using macOS 10.11.

saurabhnanda

Tuning ZFS + Postgres to outperform EXT4 + Postgres

Please refer to ZFS 2-3x slower than EXT4 to see how ZFS defaults + Postgres defaults severely underperform EXT4 defaults + Postgres defaults (and also to know more about the system on which these benchmarks were performed). This page documents how to tune ZFS + Postgres to give better performance for the tpcb-like benchmark.

BIG FAT WARNING

Please do not copy these settings blindly because I am myself not clear on why/how these settings had the impact they did. For example, I cannot explain why full_page_writes=off independently did not give that much boost, nor did an optimized PG configuration. However, putting both of them together gave a 2-4x boost compared to baseline numbers.

Benchmark results

saurabhnanda

ZFS 2-3x slower than EXT4

• With the default settings that ship with Postgres, ZFS underperforms EXT4 by a factor of 2-3x.
  • Most notable setting is shared_buffers=128MB
• When benchmarks are running on ZFS, severe write amplification is reported by iotop and txg_sync is performing a lot of IO. Example:
• The DB used for benchmarking is ~30GB. The system has a total of 64GB RAM with the ARC being given a maximum of ~32GB. Irrespective of the small value of shared_buffers, almost the entire DB should be cached in the ARC and the ZFS benchmark should not suffer so greatly.

Table of contents

• Benchmark results for Postgres on ZFS vs EXT4

saurabhnanda

pxnvme                      
    description: Desktop Computer
    vendor: FUJITSU
    width: 64 bits
    capabilities: smbios-3.0 dmi-3.0 smp vsyscall32
    configuration: administrator_password=disabled boot=normal chassis=desktop family=CELSIUS-FTS power-on_password=disabled uuid=3D27F167-2009-2245-B638-CAAA9BF3C468
  *-core
       description: Motherboard
       product: D3417-B2
       vendor: FUJITSU

saurabhnanda

e2e-38-135.e2enetworks.net.in
    description: Computer
    width: 64 bits
    capabilities: smp vsyscall32
  *-core
       description: Motherboard
       physical id: 0
     *-memory
          description: System memory
          physical id: 0

saurabhnanda

ubuntu-1804-bionic-64-minimal
    description: Desktop Computer
    vendor: FUJITSU
    width: 64 bits
    capabilities: smbios-3.0 dmi-3.0 smp vsyscall32
    configuration: administrator_password=disabled boot=normal chassis=desktop family=ESPRIMO-FTS power-on_password=disabled uuid=B318EE42-A7E2-A140-B4D5-C613A473D3B3
  *-core
       description: Motherboard
       product: D3401-H2
       vendor: FUJITSU

saurabhnanda

#!/bin/bash

PGPASSWORD="benchmarking" pgbench \
  --initialize \
  --init-steps=dtgpf \
  --scale=2000 \
  --host=$IPADDRESS \
  --username=benchmarking

for script in tpcb-like; do

saurabhnanda

smartctl 6.6 2016-05-31 r4324 [x86_64-linux-4.15.0-38-generic] (local build)
Copyright (C) 2002-16, Bruce Allen, Christian Franke, www.smartmontools.org

=== START OF INFORMATION SECTION ===
Device Model:     Micron_1100_MTFDDAK512TBN
Serial Number:    18301E037494
LU WWN Device Id: 5 00a075 11e037494
Firmware Version: M0MU031
User Capacity:    512,110,190,592 bytes [512 GB]
Sector Size:      512 bytes logical/physical