Skip to content

Instantly share code, notes, and snippets.

@pklaus
Last active February 24, 2018 20:07
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save pklaus/1e90d57d6c03dbfdee1da1fd0cc4758e to your computer and use it in GitHub Desktop.
Save pklaus/1e90d57d6c03dbfdee1da1fd0cc4758e to your computer and use it in GitHub Desktop.
PostgreSQL Benchmarks with pgbench and pg_test_fsync on a SanDisk Ultra II 240GB SSD
$ uname -a
Linux owl 4.15.3-2-ARCH #1 SMP PREEMPT Thu Feb 15 00:13:49 UTC 2018 x86_64 GNU/Linux
$ cat /etc/issue
Arch Linux \r (\l)
$ pg_config --version
PostgreSQL 10.2
$ cd /var/lib/postgres/data/
$ pg_test_fsync
5 seconds per test
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 1517.195 ops/sec 659 usecs/op
fdatasync 1548.135 ops/sec 646 usecs/op
fsync 119.319 ops/sec 8381 usecs/op
fsync_writethrough n/a
open_sync 119.067 ops/sec 8399 usecs/op
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 770.065 ops/sec 1299 usecs/op
fdatasync 1452.741 ops/sec 688 usecs/op
fsync 118.800 ops/sec 8418 usecs/op
fsync_writethrough n/a
open_sync 56.664 ops/sec 17648 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 114.008 ops/sec 8771 usecs/op
2 * 8kB open_sync writes 59.714 ops/sec 16747 usecs/op
4 * 4kB open_sync writes 29.809 ops/sec 33546 usecs/op
8 * 2kB open_sync writes 14.912 ops/sec 67062 usecs/op
16 * 1kB open_sync writes 7.459 ops/sec 134066 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 119.022 ops/sec 8402 usecs/op
write, close, fsync 119.017 ops/sec 8402 usecs/op
Non-sync'ed 8kB writes:
write 526307.242 ops/sec 2 usecs/op
$ uname -a
Linux owl 4.15.3-2-ARCH #1 SMP PREEMPT Thu Feb 15 00:13:49 UTC 2018 x86_64 GNU/Linux
$ cat /etc/issue
Arch Linux \r (\l)
$ pg_config --version
PostgreSQL 10.2
$ pgbench -i -h localhost -p 5432 -U postgres -s 25
$ pgbench -h localhost -p 5432 -U postgres -t 1000
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 25
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
latency average = 0.998 ms
tps = 1001.767648 (including connections establishing)
tps = 1003.013554 (excluding connections establishing)
$ pgbench -h localhost -p 5432 -U postgres -t 10000 -c 90 -t 20
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 25
query mode: simple
number of clients: 90
number of threads: 1
number of transactions per client: 20
number of transactions actually processed: 1800/1800
latency average = 26.308 ms
tps = 3421.054694 (including connections establishing)
tps = 3430.507152 (excluding connections establishing)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment