Skip to content

Instantly share code, notes, and snippets.

@baryluk
Last active March 23, 2019 20:03
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/b179e4dfc3ca54a6d4cc02ade0383c2f to your computer and use it in GitHub Desktop.
Save baryluk/b179e4dfc3ca54a6d4cc02ade0383c2f to your computer and use it in GitHub Desktop.
postgresql stats and settings after benchmark
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-23 19:34 | 28.9 min ago | 1 | | | 0 | 9,711,912 | 3,642,884 | 1,942,432 | *
public | pgbench_branches | 2019-03-23 19:34 | 28.4 min ago | 2 | 2019-03-23 20:02 | 1.1 min ago | 19 | 33 | 6,907 | 57 | *
public | pgbench_history | 2019-03-23 19:34 | 28.9 min ago | 1 | | | 0 | 49,470,060 | 0 | 9,894,062 |
public | pgbench_tellers | 2019-03-23 19:34 | 28.4 min ago | 2 | 2019-03-23 20:02 | 0.9 min ago | 13 | 16 | 235,556 | 53 | *
(4 rows)
mydb=> SELECT name, setting, unit, source from pg_settings WHERE name IN ( 'track_counts' , 'work_mem', 'effective_cache_size', 'shared_buffers', 'autovacuum', 'autovacuum_naptime', 'autovacuum_max_workers', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor', 'autovacuum_vacuum_cost_delay', 'autovacuum_vacuum_cost_limit', 'temp_buffers', 'max_stack_depth', 'fsync', 'huge_pages', 'maintenance_work_mem', 'max_files_per_process', 'vacuum_cost_delay', 'vacuum_cost_page_hit', 'vacuum_cost_page_miss', 'vacuum_cost_page_dirty', 'vacuum_cost_limit', 'effective_io_concurrency', 'max_worker_processes', 'max_parallel_workers', 'wal_sync_method', 'synchronous_commit', 'wal_buffers', 'max_wal_size', 'min_wal_size', 'track_activities', 'default_transaction_isolation', 'vacuum_cleanup_index_scale_factor', 'jit', 'jit_provider', 'random_page_cost', 'seq_page_cost');
name | setting | unit | source
-----------------------------------+----------------+------+----------------------
autovacuum | on | | default
autovacuum_max_workers | 3 | | default
autovacuum_naptime | 4 | s | configuration file
autovacuum_vacuum_cost_delay | 20 | ms | default
autovacuum_vacuum_cost_limit | 1000 | | configuration file
autovacuum_vacuum_scale_factor | 0.1 | | configuration file
autovacuum_vacuum_threshold | 50 | | default
default_transaction_isolation | read committed | | default
effective_cache_size | 524288 | 8kB | default
effective_io_concurrency | 128 | | configuration file
fsync | on | | default
huge_pages | try | | default
jit | off | | default
maintenance_work_mem | 1048576 | kB | configuration file
max_files_per_process | 1000 | | default
max_parallel_workers | 8 | | default
max_stack_depth | 2048 | kB | environment variable
max_wal_size | 1024 | MB | configuration file
max_worker_processes | 32 | | configuration file
min_wal_size | 80 | MB | configuration file
random_page_cost | 1.1 | | configuration file
seq_page_cost | 1 | | default
shared_buffers | 524288 | 8kB | configuration file
synchronous_commit | on | | default
temp_buffers | 1024 | 8kB | default
track_activities | on | | default
track_counts | on | | configuration file
vacuum_cleanup_index_scale_factor | 0.1 | | default
vacuum_cost_delay | 0 | ms | default
vacuum_cost_limit | 200 | | default
vacuum_cost_page_dirty | 20 | | default
vacuum_cost_page_hit | 1 | | default
vacuum_cost_page_miss | 10 | | default
wal_buffers | 2048 | 8kB | override
wal_sync_method | fdatasync | | default
work_mem | 4096 | kB | default
(36 rows)
SELECT * FROM pg_stat_bgwriter;
checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_backend_fsync | buffers_alloc | stats_reset
-------------------+-----------------+-----------------------+----------------------+--------------------+---------------+------------------+-----------------+-----------------------+---------------+-------------------------------
105 | 8715 | 14341312 | 0 | 677456624 | 5681132 | 48237 | 115518449 | 0 | 939774734 | 2019-03-23 03:39:04.643364+00
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment