Postgres DBA

postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. track_io_timing = 'on' listen_addresses = '0.0.0.0' logging_collector = 'on' log_directory = '/log/pg_log' log_destination = 'stderr' log_rotation_size = '0' log_rotation_age = '1d' log_filename = '2sdba-s-tpg12.postgresql-%u.log' log_line_prefix = '%m| %d| %a| %u| %h| %p| %e| ' log_truncate_on_rotation = 'on' log_checkpoints = 'on' archive_mode = 'on' archive_command = 'true' archive_timeout = '30min' checkpoint_warning = '60' checkpoint_completion_target = '0.9' min_wal_size = '2GB' synchronous_commit = 'on' wal_compression = 'on' random_page_cost = '1.1' effective_io_concurrency = '300' wal_sender_timeout = '0' autovacuum_naptime = '1s' autovacuum_vacuum_scale_factor = '0.01' autovacuum_analyze_scale_factor = '0.005' autovacuum_vacuum_cost_delay = '2ms' autovacuum_max_workers = '4' autovacuum_work_mem = '256MB' vacuum_cost_limit = '4000' bgwriter_delay = '10ms' bgwriter_lru_multiplier = '4' bgwriter_lru_maxpages = '400' max_locks_per_transaction = '256' max_pred_locks_per_transaction = '256' temp_buffers = '14MB' idle_in_transaction_session_timeout = '1h' statement_timeout = '8h' pg_stat_statements.track_utility = 'off' max_parallel_maintenance_workers = '4' hash_mem_multiplier = '2' autovacuum_vacuum_insert_scale_factor = '0.01' shared_preload_libraries = 'pg_stat_statements , pg_wait_sampling' commit_delay = '1000' log_autovacuum_min_duration = '0' wipe_file_on_delete = 'on' wipe_heaptuple_on_delete = 'on' wipe_mem_on_free = 'on' wipe_memctx_on_free = 'on' wipe_xlog_on_free = 'on' log_connections = 'on' log_disconnections = 'on' pg_stat_statements.track = 'all' max_connections = '1000' max_parallel_workers_per_gather = '1' max_parallel_workers = '16' max_worker_processes = '16' effective_cache_size = '6GB' work_mem = '32MB' maintenance_work_mem = '512MB' max_wal_size = '32GB' checkpoint_timeout = '5min' shared_buffers = '4GB'

-- OLTP -- scenario1.sql -- 5.2 CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$ DECLARE  test_rec record ;  current_aid bigint ; BEGIN -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! -- ТОЛЬКО ДЛЯ scale = 685 -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! current_aid = floor(random() * (68500000 - 1 + 1)) + 1 ; select acc.abalance into test_rec from pgbench_accounts acc where acc.aid = current_aid ; return 0 ; END $$ LANGUAGE plpgsql;

-- scenario3.sql -- UPDATE -- 5.2 CREATE OR REPLACE FUNCTION scenario3() RETURNS integer AS $$ DECLARE  current_aid bigint ;  current_delta bigint ; BEGIN -- Генерация случайного сдвига   current_delta := (ROUND(RANDOM())::BIGINT) * 10 + 1; -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! -- ТОЛЬКО ДЛЯ scale = 685 -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!   -- Атомарный выбор и блокировка одной строки с пропуском заблокированных   -- FOR UPDATE SKIP LOCKED для выбора одной доступной строки current_aid = floor(random() * (68500000 - 1 + 1)) + 1 ;   SELECT aid INTO current_aid   FROM pgbench_accounts   WHERE aid = current_aid   FOR UPDATE SKIP LOCKED;   -- Если строка найдена — обновляем её   IF current_aid IS NOT NULL THEN    UPDATE pgbench_accounts    SET abalance = abalance + current_delta    WHERE aid = current_aid;   END IF;  return 0 ; END $$ LANGUAGE plpgsql;

-- scenario2.sql -- INSERT -- 5.2 CREATE OR REPLACE FUNCTION scenario2() RETURNS integer AS $$ BEGIN --------------------------------------------------- --СЦЕНАРИЙ 3 - INSERT ONLY -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! -- ТОЛЬКО ДЛЯ scale = 685 -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! INSERT INTO pgbench_history ( tid, bid, aid, delta, mtime ) VALUES ( floor(random() * (6850 - 1 + 1)) + 1 , floor(random() * (685 - 1 + 1)) + 1 , floor(random() * (68500000 - 1 + 1)) + 1 , random() * 1000.0 , CURRENT_TIMESTAMP ) ON CONFLICT DO NOTHING ; --ССЦЕНАРИЙ 3 - INSERT ONLY ---------------------------------------------------  return 0 ; END $$ LANGUAGE plpgsql;

--OLAP -- scenario1.sql -- 5.2 CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$ DECLARE  test_rec record ; BEGIN WITH branch_summary AS (   SELECT    b.bid,    COUNT(a.aid) as account_count,    SUM(a.abalance) as total_balance,    AVG(a.abalance) as avg_balance   FROM pgbench_branches b   LEFT JOIN pgbench_accounts a ON b.bid = a.bid   GROUP BY b.bid ), transaction_summary AS (   SELECT    h.bid,    COUNT(*) as transaction_count,    SUM(h.delta) as net_flow,    COUNT(DISTINCT h.aid) as active_accounts,    MIN(h.mtime) as first_transaction,    MAX(h.mtime) as last_transaction   FROM pgbench_history h   WHERE h.mtime > CURRENT_TIMESTAMP - INTERVAL '7 days'   GROUP BY h.bid ) SELECT   bs.bid,   bs.account_count,   bs.total_balance,   bs.avg_balance,   COALESCE(ts.transaction_count, 0) as transaction_count,   COALESCE(ts.net_flow, 0) as net_flow,   COALESCE(ts.active_accounts, 0) as active_accounts,   EXTRACT(DAY FROM ts.last_transaction - ts.first_transaction) as activity_days,   CASE    WHEN ts.transaction_count > 0    THEN bs.total_balance / ts.transaction_count    ELSE 0   END as balance_per_transaction INTO test_rec FROM branch_summary bs LEFT JOIN transaction_summary ts ON bs.bid = ts.bid WHERE bs.total_balance > 0 ORDER BY bs.total_balance DESC LIMIT 100; return 0 ; END $$ LANGUAGE plpgsql;