Latest full replay benchmarks

Configuration common to all steem-6 and steem-9 runs

• shared_mem stored on 4xnvme raid (NOT USING RAM!)
• /var/lib/postgres on uncompressed dataset
• haf_db_store (haf tablespace) on compressed dataset
• block_log also on compressed dataset (for no particular reason)

steem-6 (Ryzen 5950X 128GB RAM 4x2TB Samsung 980 Pro zfs drive)

1.27.5 RC with full 128GB and 4 maintenance workers fully completed in 23h30m.

• Replayed to live sync in 17h50m.
• Recreated index 3h43m + keys 1h57m = 5h40m.
• Note: creating indexes while nothing else going on (i.e. using benchmark script) only took 124m (2h4m) with 4 maintenance workers. Took 195m with 2 workers. We should look at locking the tables then creating the indexes to see if we get any speedups or if this data was flawed/fluke. Another point of interest is that hive.applied_hardforks constraints are taking a long time, which seems strange for such a super small table. Maybe the constraints require indexes (from constraints) on either hive.operations or hive.account_operations, so the constraints building goes slow until those constraints get built. If so, maybe we can prioritize the associated indexes. Possibly we end up getting some conflicting accesses to the tables that slows down overall creation of the constraints.
• Zipped log file: s6.7z

Testing with only 64GB memory fully completed in 43h25m.

Notes: less memory didn't affect index/key recreation, but slowed down replay by ~2x.

• Replayed to live sync in 38h5m.
• Recreated indexes+constraints in 5h16m.
• Zipped log file: s6-64gb.7z
• To reduce memory for this test:
  • sudo vi /etc/default/grub
  • add mem=64G to line that starts with GRUB_CMDLINE_LINUX
  • run sudo update-grub

Testing with only 32GB memory (no replay, just snapshot test, took 4.8h)

• Replay was going to take many days, so stopped.
• Instead, synced with 9 days old snapshot
• Restored HAF constraints (indexes kept): 5429s = 1.5h
• Entered LIVE sync from start state: 17309s = 4.8h

Testing 128GB startup dropping indexes and constraints (threshold=1)

1. PROFILE: Entered P2P sync from start state: 6 s
2. PROFILE: Entering LIVE sync, creating indexes/constraints as needed: 609 s (10m)
3. PROFILE: Restored HAF table indexes: 10084s (168m)
4. PROFILE: Restored HAF constraints: 4991s (83m)
5. PROFILE: Entered LIVE sync from start state: 15684 s (261m)

Testing 128GB startup recreating VALID constraints

• Tested when only a few blocks behind and lowering live_sync_threshhold to 10000
• Dropping/restoring VALID constraints puts a lower threshold of 100m on startup

1. PROFILE: Entered P2P sync from start state: 6 s
2. PROFILE: Entering LIVE sync, creating indexes/constraints as needed: 21 s
3. PROFILE: Restored HAF table indexes: 0s
4. PROFILE: Restored HAF constraints: 6009s
5. PROFILE: Entered LIVE sync from start state: 6038 s (100m)

Testing 128GB startup recreating NOT VALID constraints

• Tested by using day old snapshot (30K blocks behind) and lowering live_sync_threshhold to 10000
• Reduces time to startup by 100m, so most restarts are almost immmediate

1. PROFILE: Entered P2P sync from start state: 2 s
2. PROFILE: Entering LIVE sync, creating indexes/constraints as needed: 233 s (4m to catchup)
3. PROFILE: Restored HAF table indexes: 0s
4. PROFILE: Restored HAF constraints: 0s
5. PROFILE: Entered LIVE sync from start state: 239 s

steem-8 (Ryzen 5950X 128GB RAM 4x2TB Samsung 980 Pro zfs drive) fully completed in 31h.

• This test was done with autotuned postgres.conf (see below).
• Replayed to live sync in 22h24m.
• Recreated index 4h21m + constraints 4h15m = 8h36m.
• steem-8-reindex-to-live.log.gz
• postgres directories have zfs recordsize=8k

# Settings generated by PGTune (https://pgtune.leopard.in.ua/)
# DB Version: 14
# OS Type: linux
# DB Type: dw
# Total Memory (RAM): 64 GB
# CPUs num: 32
# Data Storage: ssd

max_connections = 40
shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 13107kB
huge_pages = try
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 32
max_parallel_workers_per_gather = 16
max_parallel_workers = 32
max_parallel_maintenance_workers = 4

# These settings are recommended for PostgreSQL on ZFS:
full_page_writes = off
wal_init_zero = off
wal_recycle = off

steem-9 (Ryzen 5950X 128GB RAM 4x2TB Corsair Force MP600 zfs drive)

Log:s9.7z

Gandalf added some hived/haf/hivemind benchmarks here: hive#556

Latest full sync benchmarks