Skip to content

Instantly share code, notes, and snippets.

@baryluk

baryluk/gist:fed99894c0e29894a0884013162f09fa

Last active March 25, 2019 08:33
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save baryluk/fed99894c0e29894a0884013162f09fa to your computer and use it in GitHub Desktop.
Save baryluk/fed99894c0e29894a0884013162f09fa to your computer and use it in GitHub Desktop.
Download ZIP
pgbench scale 1000, clients 64, prepared statements, 8000 seconds
Raw
gistfile1.txt
https://imgur.com/3mbDns1.png
PostgreSQL 11.2
Each pgbench run:
/usr/lib/postgresql/11/bin/pgbench --initialize --scale=10 --fillfactor=90 mydb # basically drop old tables
/etc/init.d/postgresql stop
/etc/init.d/postgresql start
/usr/lib/postgresql/11/bin/pgbench --initialize --scale=1000 --fillfactor=90 mydb
psql mydb --command "VACUUM FULL pgbench_accounts"
/usr/lib/postgresql/11/bin/pgbench --jobs=64 --client=64 --protocol=prepared --time=8000 --progress=1 mydb
mydb=# SELECT category, name, setting, unit, source FROM pg_settings WHERE source != 'default' AND category !~ 'Locale|SSL|File Locations' ORDER BY category, name;
category | name | setting | unit | source
------------------------------------------------------+---------------------------------+-----------------------------------------+------+--------------------
Autovacuum | autovacuum_max_workers | 6 | | configuration file
Autovacuum | autovacuum_naptime | 3 | s | configuration file
Autovacuum | autovacuum_vacuum_cost_delay | 10 | ms | configuration file
Autovacuum | autovacuum_vacuum_cost_limit | 5000 | | configuration file
Autovacuum | autovacuum_vacuum_scale_factor | 0.1 | | configuration file
Client Connection Defaults / Statement Behavior | transaction_deferrable | off | | override
Client Connection Defaults / Statement Behavior | transaction_isolation | read committed | | override
Client Connection Defaults / Statement Behavior | transaction_read_only | off | | override
Connections and Authentication / Authentication | password_encryption | scram-sha-256 | | configuration file
Connections and Authentication / Connection Settings | max_connections | 100 | | configuration file
Connections and Authentication / Connection Settings | port | 5432 | | configuration file
Connections and Authentication / Connection Settings | unix_socket_directories | /var/run/postgresql | | configuration file
Lock Management | max_pred_locks_per_page | 4 | | configuration file
Preset Options | data_checksums | off | | override
Preset Options | wal_segment_size | 16777216 | B | override
Process Title | cluster_name | 11/main | | configuration file
Query Tuning / Other Planner Options | jit | on | | configuration file
Query Tuning / Planner Cost Constants | random_page_cost | 1.1 | | configuration file
Reporting and Logging / What to Log | application_name | psql | | client
Reporting and Logging / What to Log | log_line_prefix | %m [%p] %q%u@%d | | configuration file
Reporting and Logging / What to Log | log_timezone | UTC | | configuration file
Resource Usage / Asynchronous Behavior | effective_io_concurrency | 128 | | configuration file
Resource Usage / Asynchronous Behavior | max_parallel_workers_per_gather | 0 | | configuration file
Resource Usage / Asynchronous Behavior | max_worker_processes | 32 | | configuration file
Resource Usage / Cost-Based Vacuum Delay | vacuum_cost_delay | 0 | ms | configuration file
Resource Usage / Cost-Based Vacuum Delay | vacuum_cost_limit | 10000 | | configuration file
Resource Usage / Cost-Based Vacuum Delay | vacuum_cost_page_dirty | 10 | | configuration file
Resource Usage / Cost-Based Vacuum Delay | vacuum_cost_page_hit | 1 | | configuration file
Resource Usage / Cost-Based Vacuum Delay | vacuum_cost_page_miss | 5 | | configuration file
Resource Usage / Memory | autovacuum_work_mem | 1048576 | kB | configuration file
Resource Usage / Memory | dynamic_shared_memory_type | posix | | configuration file
Resource Usage / Memory | maintenance_work_mem | 1048576 | kB | configuration file
Resource Usage / Memory | max_stack_depth | 4096 | kB | configuration file
Resource Usage / Memory | shared_buffers | 524288 | 8kB | configuration file
Resource Usage / Memory | temp_buffers | 4096 | 8kB | configuration file
Resource Usage / Memory | work_mem | 65536 | kB | configuration file
Statistics / Query and Index Statistics Collector | stats_temp_directory | /var/run/postgresql/11-main.pg_stat_tmp | | configuration file
Write-Ahead Log / Checkpoints | checkpoint_completion_target | 0.9 | | configuration file
Write-Ahead Log / Checkpoints | checkpoint_timeout | 60 | s | configuration file
Write-Ahead Log / Checkpoints | checkpoint_warning | 5 | s | configuration file
Write-Ahead Log / Checkpoints | max_wal_size | 10240 | MB | configuration file
Write-Ahead Log / Checkpoints | min_wal_size | 80 | MB | configuration file
Write-Ahead Log / Settings | commit_delay | 10 | | configuration file
Write-Ahead Log / Settings | commit_siblings | 10 | | configuration file
Write-Ahead Log / Settings | wal_buffers | 8192 | 8kB | configuration file
Write-Ahead Log / Settings | wal_writer_delay | 40 | ms | configuration file
(46 rows)
Machine:
# inxi
CPU: 16-Core (2-Die) AMD Ryzen Threadripper 2950X (-MT MCP MCM-) speed/min/max: 2855/2200/3500 MHz
Kernel: 4.19.0-2-amd64 x86_64 Up: 33d 15h 31m Mem: 6645.8/128873.5 MiB (5.2%) Storage: 30.57 GiB (212.0% used) Procs: 496
Shell: bash 5.0.2 inxi: 3.0.32
File system: tmpfs + overlayfs (all in RAM).
Autovacuum stats after each run:
mydb=> SELECT psut.schemaname, psut.relname,
to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum,
to_char(EXTRACT(EPOCH FROM (NOW() - psut.last_vacuum))/60, '999,999,990.0') || ' min ago' as last_vacuum_ago,
vacuum_count,
to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum,
to_char(EXTRACT(EPOCH FROM (NOW() - psut.last_autovacuum))/60, '999,999,990.0') || ' min ago' as last_autovacuum_ago,
autovacuum_count,
to_char(pg_class.reltuples, '9G999G999G999') AS n_tup,
to_char(psut.n_dead_tup, '9G999G999G999') AS dead_tup,
to_char(CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
+ (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
* pg_class.reltuples), '9G999G999G999') AS av_threshold,
CASE WHEN psut.n_dead_tup >= CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
+ (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
* pg_class.reltuples)
THEN '*' ELSE ''
END AS expect_av
FROM pg_stat_user_tables psut
JOIN pg_class on psut.relid = pg_class.oid
ORDER BY psut.schemaname, psut.relname;
schemaname | relname | last_vacuum | last_vacuum_ago | vacuum_count | last_autovacuum | last_autovacuum_ago | autovacuum_count | n_tup | dead_tup | av_threshold | expect_av
------------+------------------+------------------+------------------------+--------------+------------------+------------------------+------------------+----------------+----------------+----------------+-----------
public | pgbench_accounts | 2019-03-25 05:24 | 183.7 min ago | 1 | 2019-03-25 08:08 | 20.1 min ago | 3 | 100,000,000 | 1,817,940 | 10,000,050 |
public | pgbench_branches | 2019-03-25 05:51 | 156.8 min ago | 2 | 2019-03-25 08:04 | 23.5 min ago | 4574 | 1,000 | 0 | 150 |
public | pgbench_history | 2019-03-25 05:24 | 183.7 min ago | 1 | 2019-03-25 08:05 | 22.5 min ago | 3 | 500,199,936 | 0 | 50,020,044 |
public | pgbench_tellers | 2019-03-25 05:51 | 156.8 min ago | 2 | 2019-03-25 08:04 | 23.5 min ago | 4669 | 10,000 | 0 | 1,050 |
(4 rows)
Yes, it does run autovacuum every few seconds (this is tunned).
Otherwise I am getting serious dips in performance every minute, as pgbench is heavy on UPDATE.
Disk space after each run is finished:
# du -hs /var/lib/postgresql/11/main/
50G /var/lib/postgresql/11/main/
#
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment