# Параметры конфигурации PostgreSQL ## Параметр work_mem | autovacuum_work_mem | maintenance_work_mem 1. Посмотрим, какие параметры выставлены сейчас в системе ```bash postgres$ psql -d demo demo=> select * from pg_settings where name like '%work_mem%'; -[ RECORD 1 ]---+---------------------------------------------------------------------------------------------------------------------- name | autovacuum_work_mem setting | -1 unit | kB category | Resource Usage / Memory short_desc | Sets the maximum memory to be used by each autovacuum worker process. extra_desc | context | sighup vartype | integer source | default min_val | -1 max_val | 2147483647 enumvals | boot_val | -1 reset_val | -1 sourcefile | sourceline | pending_restart | f -[ RECORD 2 ]---+---------------------------------------------------------------------------------------------------------------------- name | maintenance_work_mem setting | 65536 unit | kB category | Resource Usage / Memory short_desc | Sets the maximum memory to be used for maintenance operations. extra_desc | This includes operations such as VACUUM and CREATE INDEX. context | user vartype | integer source | default min_val | 1024 max_val | 2147483647 enumvals | boot_val | 65536 reset_val | 65536 sourcefile | sourceline | pending_restart | f -[ RECORD 3 ]---+---------------------------------------------------------------------------------------------------------------------- name | work_mem setting | 4096 unit | kB category | Resource Usage / Memory short_desc | Sets the maximum memory to be used for query workspaces. extra_desc | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files. context | user vartype | integer source | default min_val | 64 max_val | 2147483647 enumvals | boot_val | 4096 reset_val | 4096 sourcefile | sourceline | pending_restart | f ``` 2. Сильно уменьшим параметр `work_mem` ```bash demo=> alter system set work_mem='64'; ALTER SYSTEM demo=> select * from pg_reload_conf(); -[ RECORD 1 ]--+-- pg_reload_conf | t ``` 3. Проверим, какой стал сейчас параметр ```bash demo=> select * from pg_settings where name like 'work_mem'; -[ RECORD 1 ]---+---------------------------------------------------------------------------------------------------------------------- name | work_mem setting | 64 unit | kB category | Resource Usage / Memory short_desc | Sets the maximum memory to be used for query workspaces. extra_desc | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files. context | user vartype | integer source | configuration file min_val | 64 max_val | 2147483647 enumvals | boot_val | 4096 reset_val | 64 sourcefile | /var/lib/pgsql/12/data/postgresql.auto.conf sourceline | 5 pending_restart | f ``` 4. Выполним запрос и разберем план его выполнения ```bash demo=> explain (analyze,buffers)select count(*) from (select distinct flight_id, flight_no from bookings.flights) f where flight_id in (select flight_id from bookings.boarding_passes b where seat_no = 'A2'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=41654.16..41654.17 rows=1 width=8) (actual time=671.702..671.758 rows=1 loops=1) Buffers: shared hit=11304 read=4907 dirtied=15 written=8, temp read=360 written=606 -> Hash Join (cost=37262.02..39134.38 rows=1007911 width=0) (actual time=671.697..671.753 rows=0 loops=1) Hash Cond: (f.flight_id = b.flight_id) Buffers: shared hit=11304 read=4907 dirtied=15 written=8, temp read=360 written=606 -> Subquery Scan on f (cost=10896.02..12048.08 rows=65832 width=4) (actual time=98.297..98.299 rows=1 loops=1) Buffers: shared hit=1591, temp read=360 written=606 -> Unique (cost=10896.02..11389.76 rows=65832 width=11) (actual time=98.295..98.296 rows=1 loops=1) Buffers: shared hit=1591, temp read=360 written=606 -> Sort (cost=10896.02..11060.60 rows=65832 width=11) (actual time=98.293..98.294 rows=1 loops=1) Sort Key: flights.flight_id, flights.flight_no Sort Method: external merge Disk: 1384kB Buffers: shared hit=1591, temp read=360 written=606 -> Seq Scan on flights (cost=0.00..2246.32 rows=65832 width=11) (actual time=0.010..46.799 rows=65835 loops=1) Buffers: shared hit=1588 -> Hash (cost=26241.65..26241.65 rows=7548 width=4) (actual time=573.384..573.438 rows=0 loops=1) Buckets: 2048 Batches: 8 Memory Usage: 16kB Buffers: shared hit=9713 read=4907 dirtied=15 written=8 -> Gather (cost=1000.00..26241.65 rows=7548 width=4) (actual time=573.383..573.436 rows=0 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=9713 read=4907 dirtied=15 written=8 -> Parallel Seq Scan on boarding_passes b (cost=0.00..24486.85 rows=3145 width=4) (actual time=549.651..549.651 rows=0 loops=3) Filter: ((seat_no)::text = 'A2'::text) Rows Removed by Filter: 631480 Buffers: shared hit=9713 read=4907 dirtied=15 written=8 Planning Time: 0.557 ms Execution Time: 672.123 ms (28 rows) ``` Что вы можете сказать про него? А что у нас в мониторинге? Можем ли мы увидеть там нашу проблему? 5. Посмотрим, какое значение параметра default_statistics_target выставлены сейчас в системе ```bash demo=> select * from pg_settings where name = 'default_statistics_target'; name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart ---------------------------+---------+------+--------------------------------------+-------------------------------------+--------------------------------------------------------------------------------------------------------------+---------+---------+---------+---------+---------+----------+----------+-----------+------------+------------+----------------- default_statistics_target | 100 | | Query Tuning / Other Planner Options | Sets the default statistics target. | This applies to table columns that have not had a column-specific target set via ALTER TABLE SET STATISTICS. | user | integer | default | 1 | 10000 | | 100 | 100 | | | f (1 row) ``` 6. Выставим параметр work_mem в 20МБ и сильно уменьшим параметр 'default_statistics_target' ```bash demo=> alter system set default_statistics_target=1; ALTER SYSTEM demo=> alter system set work_mem='20MB'; ALTER SYSTEM demo=> select * from pg_reload_conf(); pg_reload_conf ---------------- t (1 row) demo=> \q ``` 7. Сделаем анализ таблиц bookings.flights и bookings.boarding_passes ```bash postgres$ vacuumdb -d demo -t bookings.flights --analyze-only --verbose vacuumdb: vacuuming database "demo" INFO: analyzing "bookings.flights" INFO: "flights": scanned 300 of 2639 pages, containing 24426 live rows and 139 dead rows; 300 rows in sample, 214867 estimated total rows postgres$ vacuumdb -d demo -t bookings.boarding_passes --analyze-only --verbose vacuumdb: vacuuming database "demo" INFO: analyzing "bookings.boarding_passes" INFO: "boarding_passes": scanned 300 of 59065 pages, containing 40534 live rows and 266 dead rows; 300 rows in sample, 7980469 estimated total rows ``` 8. Выполним запрос и разберем план его выполнения ```bash postgres$ psql -d demo demo=> explain (analyze,buffers)select count(*) from (select distinct flight_id, flight_no from bookings.flights) f where flight_id in (select flight_id from bookings.boarding_passes b where seat_no = 'A2'); -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=140375.66..140375.67 rows=1 width=8) (actual time=17380.464..17380.466 rows=1 loops=1) Buffers: shared hit=598761 read=48480 written=3600 -> Nested Loop (cost=5864.65..111352.49 rows=11609269 width=0) (actual time=17380.461..17380.462 rows=0 loops=1) Buffers: shared hit=598761 read=48480 written=3600 -> HashAggregate (cost=5864.22..8013.70 rows=214948 width=11) (actual time=756.563..909.272 rows=214867 loops=1) Group Key: flights.flight_id, flights.flight_no Buffers: shared hit=2640 -> Seq Scan on flights (cost=0.00..4789.48 rows=214948 width=11) (actual time=0.013..284.882 rows=214867 loops=1) Buffers: shared hit=2640 -> Index Only Scan using boarding_passes_flight_id_seat_no_key on boarding_passes b (cost=0.43..2.50 rows=54 width=4) (actual time=0.071..0.071 rows=0 loops=214867) Index Cond: ((flight_id = flights.flight_id) AND (seat_no = 'A2'::text)) Heap Fetches: 0 Buffers: shared hit=596121 read=48480 written=3600 Planning Time: 0.537 ms Execution Time: 17383.021 ms (15 rows) ``` 9. Вернем обратно параметр 'default_statistics_target' ```bash alter system set default_statistics_target=100; ALTER SYSTEM select * from pg_reload_conf(); pg_reload_conf ---------------- t (1 row) demo=> \q ``` 10. Сделаем анализ таблиц bookings.flights и bookings.boarding_passes ```bash vacuumdb -d demo -t bookings.flights --analyze-only --verbose vacuumdb: vacuuming database "demo" INFO: analyzing "bookings.flights" INFO: "flights": scanned 2640 of 2640 pages, containing 214867 live rows and 1294 dead rows; 30000 rows in sample, 214867 estimated total rows vacuumdb -d demo -t bookings.boarding_passes --analyze-only --verbose vacuumdb: vacuuming database "demo" INFO: analyzing "bookings.boarding_passes" INFO: "boarding_passes": scanned 30000 of 59194 pages, containing 4013733 live rows and 53968 dead rows; 30000 rows in sample, 7919630 estimated total rows ``` 11. Выполним запрос и разберем план его выполнения ```bash postgres$ psql -d demo explain (analyze,buffers)select count(*) from (select distinct flight_id, flight_no from bookings.flights) f where flight_id in (select flight_id from bookings.boarding_passes b where seat_no = 'A2'); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=113088.10..113088.11 rows=1 width=8) (actual time=6848.021..6870.766 rows=1 loops=1) Buffers: shared hit=3881 read=57973 dirtied=305 written=19 -> Hash Join (cost=108149.80..113011.21 rows=30753 width=0) (actual time=6848.005..6870.749 rows=0 loops=1) Hash Cond: (flights.flight_id = b.flight_id) Buffers: shared hit=3881 read=57973 dirtied=305 written=19 -> HashAggregate (cost=5863.01..8011.68 rows=214867 width=11) (actual time=1693.340..1693.341 rows=1 loops=1) Group Key: flights.flight_id, flights.flight_no Buffers: shared hit=2640 -> Seq Scan on flights (cost=0.00..4788.67 rows=214867 width=11) (actual time=0.017..406.716 rows=214867 loops=1) Buffers: shared hit=2640 -> Hash (cost=102196.71..102196.71 rows=7207 width=4) (actual time=5154.604..5177.345 rows=0 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 64kB Buffers: shared hit=1241 read=57973 dirtied=305 written=19 -> Gather (cost=1000.00..102196.71 rows=7207 width=4) (actual time=5154.603..5177.334 rows=0 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=1241 read=57973 dirtied=305 written=19 -> Parallel Seq Scan on boarding_passes b (cost=0.00..100476.01 rows=3003 width=4) (actual time=5058.988..5058.988 rows=0 loops=3) Filter: ((seat_no)::text = 'A2'::text) Rows Removed by Filter: 2641937 Buffers: shared hit=1241 read=57973 dirtied=305 written=19 Planning Time: 0.573 ms Execution Time: 6873.942 ms (23 rows) ``` ## Домашнее задание. ## Запаздывание реплики 1. Создать слот репликации на сервере PostgreSQL на порту `5432`; 2. Создайте реплику на 12 версии PostgreSQL на порту `5433`, чтобы она работала через слот репликации; 3. На реплике выставите параметр `max_standby_streaming_delay = 1h`. Примените его 4. Дождитесь сбора метрик в pgwatch2 и убедитесь, что репликация у вас начала запаздывать. ### Чек-лист  1. Создан слот репликации на сервере PostgreSQL на порту `5432`; 2. Создана реплика на 12 версии PostgreSQL на порту `5433` и она работает через слот репликации; 3. На реплике выставлен параметр `max_standby_streaming_delay = 1h` 4. Выполнен внутри транзакции запрос с подсчетом количества записей в таблицах `bookings.ticket_flights`, `bookings.tickets`, `bookings.flights` и паузой в 120 секунд; 5. Подтверждено состояние отставание реплики.
Решение: 1. Создать слот репликации на сервере PostgreSQL на порту `5432`; ``` student$ sudo -u postgres -i postgres$ cd ~ postgres$ ./tank.sh DB="demo" CLUSTER_HOST="localhost" CLUSTER_PORT="5432" TIME="36000" CNT="10" postgres$ psql psql (12.6) Type "help" for help. postgres=# SELECT pg_create_physical_replication_slot('synch_slot5433'); pg_create_physical_replication_slot ------------------------------------- (synch_slot5433,) (1 row) ``` 2. Создайте реплику на 12 версии PostgreSQL на порту `5433`, чтобы она работала через слот репликации; ``` postgres=# \q postgres$ pg_basebackup -D /var/lib/pgsql/12/main5433 -R -S synch_slot5433 -v pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 2/13000028 on timeline 1 pg_basebackup: starting background WAL receiver pg_basebackup: write-ahead log end point: 2/13000138 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: syncing data to disk ... pg_basebackup: base backup completed ``` 3. На реплике выставите параметр `max_standby_streaming_delay = 1h`. Примените его Строка с `port=5433` может не корректно встать в конфиг `/var/lib/pgsql/12/main5433/postgresql.conf`. Нужно пройти и исправить это. ``` postgres$ echo "port=5433" >> /var/lib/pgsql/12/main5433/postgresql.conf postgres$ echo "max_standby_streaming_delay = 1h" >> ~/12/main5433/postgresql.conf postgres$ pg_ctl -D /var/lib/pgsql/12/main5433 start waiting for server to start....2021-04-24 14:54:28.832 UTC [23138] LOG: starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2021-04-24 14:54:28.833 UTC [23138] LOG: listening on IPv4 address "0.0.0.0", port 5433 2021-04-24 14:54:28.833 UTC [23138] LOG: listening on IPv6 address "::", port 5433 2021-04-24 14:54:28.836 UTC [23138] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433" 2021-04-24 14:54:28.848 UTC [23138] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" 2021-04-24 14:54:28.866 UTC [23138] LOG: redirecting log output to logging collector process 2021-04-24 14:54:28.866 UTC [23138] HINT: Future log output will appear in directory "log". done server started ``` 4. Выполните внутри транзакции запрос с подсчетом количества записей в таблицах `bookings.ticket_flights`, `bookings.tickets`, `bookings.flights` и паузой в 120 секунд; ``` postgres$ psql -p5433 demo psql (12.6) Type "help" for help. demo=# do $$ begin perform count(*) from bookings.ticket_flights; perform count(*) from bookings.tickets; perform count(*) from bookings.flights; perform pg_sleep(120); end; $$; DO ``` 5. Дождитесь сбора метрик в pgwatch2 и убедитесь, что репликация у вас начала запаздывать. В ДРУГОЙ СЕССИИ !!! ``` demo$ psql psql (12.6) Type "help" for help. demo=# \x Expanded display is on. demo=# select * from pg_stat_replication ; -[ RECORD 1 ]----+------------------------------ pid | 28087 usesysid | 10 usename | postgres application_name | walreceiver client_addr | client_hostname | client_port | -1 backend_start | 2021-04-24 15:03:41.419127+00 backend_xmin | state | streaming sent_lsn | 2/16FA3478 write_lsn | 2/16FA3478 flush_lsn | 2/16FA3478 replay_lsn | 2/16E559B8 write_lag | 00:00:00.000148 flush_lag | 00:00:00.002821 replay_lag | 00:00:00.937678 sync_priority | 0 sync_state | async reply_time | 2021-04-24 15:04:26.274246+00 demo=# select * from pg_stat_replication ; -[ RECORD 1 ]----+------------------------------ pid | 28087 usesysid | 10 usename | postgres application_name | walreceiver client_addr | client_hostname | client_port | -1 backend_start | 2021-04-24 15:03:41.419127+00 backend_xmin | state | streaming sent_lsn | 2/2F080000 write_lsn | 2/2F000000 flush_lsn | 2/2F000000 replay_lsn | 2/16E559B8 write_lag | 00:00:11.237752 flush_lag | 00:00:11.237752 replay_lag | 00:01:19.455937 sync_priority | 0 sync_state | async reply_time | 2021-04-24 15:05:44.78153+00 demo=# select * from pg_stat_replication ; -[ RECORD 1 ]----+------------------------------ pid | 28087 usesysid | 10 usename | postgres application_name | walreceiver client_addr | client_hostname | client_port | -1 backend_start | 2021-04-24 15:03:41.419127+00 backend_xmin | state | streaming sent_lsn | 2/305C0000 write_lsn | 2/30560000 flush_lsn | 2/30520000 replay_lsn | 2/16E559B8 write_lag | 00:00:11.638886 flush_lag | 00:00:11.64735 replay_lag | 00:01:21.058311 sync_priority | 0 sync_state | async reply_time | 2021-04-24 15:05:46.394854+00 demo=# ```
ЗАМЕЧАНИЕ Если на графиках pgwatch2 нет запаздывания, то можно выполнить следующие действия: 1. Нужно увеличить число секунд в параметре `pg_watch2` 2. На лидере выполнить дополнительную команду обвноления данных, например update `bookings.tickets set ticket_no = ticket_no;`