30 真正重要的配置

-- boot_val is the compiled-in default, whatever this server's config says
select lab.prove('shared_buffers defaults to 128 MB (16384 pages of 8 kB)',
  boot('shared_buffers') = '16384'
  and (select unit from pg_settings where name = 'shared_buffers') = '8kB');
select lab.prove('random_page_cost defaults to 4, seq_page_cost to 1',
  boot('random_page_cost') = '4' and boot('seq_page_cost') = '1');
select lab.prove('a checkpoint starts after about max_wal_size / (1 + checkpoint_completion_target) of WAL: ~539 MB',
  round(1024 / (1 + boot('checkpoint_completion_target')::numeric)) = 539);
select lab.prove('no slow-query, lock-wait, or temp-file logging by default',
  boot('log_min_duration_statement') = '-1'
  and boot('log_lock_waits') = 'off'
  and boot('log_temp_files') = '-1');

alter database :"DBNAME" set work_mem = '16MB';
select lab.prove('a new session picks up the database setting, with source = database',
  (select s from dblink('fresh',
     $$select setting || ' ' || unit || ' ' || source from pg_settings where name = 'work_mem'$$) t(s text))
  = '16384 kB database');
select lab.prove('this session, connected before the change, keeps its old value',
  current_setting('work_mem') = '4MB');
alter role lab_cfg_reporting set work_mem = '256MB';
select lab.prove('a role setting overrides the database setting (source = user)',
  (select s from dblink('reporting',
     $$select current_setting('work_mem') || ' ' || source from pg_settings where name = 'work_mem'$$) t(s text))
  = '256MB user');

select lab.prove('postmaster (restart): shared_buffers, max_connections, io_method',
  ctx('shared_buffers') = 'postmaster' and ctx('max_connections') = 'postmaster'
  and ctx('io_method') = 'postmaster');
select lab.prove('user: work_mem, random_page_cost, statement_timeout',
  ctx('work_mem') = 'user' and ctx('random_page_cost') = 'user' and ctx('statement_timeout') = 'user');

-- each row: one EXPLAIN ANALYZE of the sort at that work_mem
select lab.prove('at the default 4 MB the sort spills: external merge, about 50 MB on disk',
  (select method = 'external merge' and space_type = 'Disk'
          and space_kb between 45000 and 56000 and temp_written > 10000
     from sorts where work_mem = '4MB'));
select lab.prove('at 256 MB it sorts in memory, using about 109 MB',
  (select method = 'quicksort' and space_type = 'Memory' and space_kb between 100000 and 120000
     from sorts where work_mem = '256MB'));
select lab.prove('the threshold: 64 MB (less than the sort needs) still spills, 128 MB (more) does not',
  (select method from sorts where work_mem = '64MB') = 'external merge'
  and (select method from sorts where work_mem = '128MB') = 'quicksort');

\set q 'select * from events where project_id between 1 and 20 order by project_id limit 5000'
select lab.prove('at random_page_cost = 4: a bitmap heap scan, then a sort',
  (:'nodes_at_4')::text[] @> array['Sort', 'Bitmap Heap Scan']);
select lab.prove('at 1.1: an index scan in order, no sort',
  (:'nodes_at_11')::text[] @> array['Index Scan'] and not (:'nodes_at_11')::text[] @> array['Sort']);
select lab.prove('both touch about the same number of pages',
  :buffers_at_11::numeric / :buffers_at_4 between 0.8 and 1.25);

select lab.prove('checkpoint_timeout 5 min, checkpoint_completion_target 0.9, min_wal_size 80 MB',
  boot('checkpoint_timeout') = '300' and boot('checkpoint_completion_target') = '0.9'
  and boot('min_wal_size') = '80');
select lab.prove('wal_compression off (superuser), wal_buffers -1 (automatic, restart)',
  boot('wal_compression') = 'off' and ctx('wal_compression') = 'superuser'
  and boot('wal_buffers') = '-1' and ctx('wal_buffers') = 'postmaster');
select lab.prove('18 defaults: effective_io_concurrency and maintenance_io_concurrency 16',
  boot('effective_io_concurrency') = '16' and boot('maintenance_io_concurrency') = '16');
select lab.prove('io_workers defaults to 3 and changes with a reload',
  boot('io_workers') = '3' and ctx('io_workers') = 'sighup');

select lab.prove('autovacuum_vacuum_cost_limit -1, meaning vacuum_cost_limit (200)',
  boot('autovacuum_vacuum_cost_limit') = '-1' and boot('vacuum_cost_limit') = '200');
select lab.prove('scale factors 0.2, 0.2 (insert), 0.1 (analyze)',
  boot('autovacuum_vacuum_scale_factor') = '0.2'
  and boot('autovacuum_vacuum_insert_scale_factor') = '0.2'
  and boot('autovacuum_analyze_scale_factor') = '0.1');
select lab.prove('autovacuum_max_workers 3, a reload in 18, up to autovacuum_worker_slots (16, restart)',
  boot('autovacuum_max_workers') = '3' and ctx('autovacuum_max_workers') = 'sighup'
  and boot('autovacuum_worker_slots') = '16' and ctx('autovacuum_worker_slots') = 'postmaster');

select lab.prove('events (275 MB) is past 8 x 3^3 = 216 MB but short of 648 MB: four workers',
  pg_relation_size('events') between 216 * 1024 * 1024 and 648 * 1024 * 1024);
set max_parallel_workers_per_gather = 8;
select lab.prove('with the cap raised, the planner plans 4 workers for events',
  (select (p -> 'Plan' -> 'Plans' -> 0 ->> 'Workers Planned')::int
     from lab.plan('select kind, count(*) from events group by kind') p) = 4);

load 'auto_explain';
select lab.prove('the slow count reads about 35,000 pages',
  lab.buffers($$select count(*) from events where kind = 'alert' and payload->>'status' = 'failed'$$)
  between 33000 and 37000);
-- est: the scan's estimate across all processes; actual: rows x loops
select lab.prove('the planner estimates under 5,000 matching rows; there are over 60,000',
  (select est < 5000 and actual > 60000 from est));

select lab.prove('without PG_OOM_ADJUST_FILE, a backend inherits the postmaster''s oom_score_adj',
  position('PG_OOM_ADJUST_FILE'::bytea in pg_read_binary_file('/proc/' || :pm_pid || '/environ')) = 0
  and host_file('/proc/' || :pm_pid || '/oom_score_adj')
      = host_file('/proc/' || pg_backend_pid() || '/oom_score_adj'));

select lab.prove('huge_pages defaults to try, and changing it needs a restart',
  boot('huge_pages') = 'try' and ctx('huge_pages') = 'postmaster');
select lab.prove('on Linux, checkpoint_flush_after is 256 kB and bgwriter_flush_after 512 kB; backends: 0',
  boot('checkpoint_flush_after') = '32' and boot('bgwriter_flush_after') = '64'
  and boot('backend_flush_after') = '0'
  and (select unit from pg_settings where name = 'checkpoint_flush_after') = '8kB');

select lab.prove('shared_buffers lives in anonymous mmap memory; dynamic shared memory is POSIX shm',
  current_setting('shared_memory_type') = 'mmap'
  and current_setting('dynamic_shared_memory_type') = 'posix');
select lab.prove('the lab kit''s compose file gives /dev/shm 512 MB, not Docker''s 64 MB',
  host_file('/proc/self/mounts') ~ '/dev/shm \S+ \S*size=524288k');

-- the lab also checks that every name in the config exists in pg_settings,
-- and SETs every value a session can set, inside a transaction
select lab.prove('the session-settable starter values are all accepted (wal_compression = lz4 included)',
  current_setting('wal_compression') = 'lz4' and current_setting('random_page_cost') = '1.1');