Eu tenho dois servidores com as seguintes especificações:
- 8 vCPU, 32768 MB de RAM, SSD de 640 GB
O banco de dados mestre Postgres 13.3 (db1) é instalado no primeiro servidor (Ubuntu 16.04.7) com a seguinte configuração:
shared_buffers = 16GB
work_mem = 128MB
maintenance_work_mem = 8GB
effective_cache_size = 16GB
effective_io_concurrency = 400
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
wal_level = logical
synchronous_commit = on
max_wal_size = 4GB
min_wal_size = 32MB
wal_keep_size = 16384
wal_sender_timeout = 60s
checkpoint_completion_target = 0.7
synchronous_standby_names = 'FIRST 1 (db2_slave)'
max_standby_archive_delay = 1800s
max_standby_streaming_delay = 1800s
O standby é um banco de dados Postgres 13.4 (db2) instalado no segundo servidor (Ubuntu 20.04.3) com a seguinte configuração:
shared_buffers = 24GB
work_mem = 128MB
maintenance_work_mem = 16GB
effective_cache_size = 24GB
effective_io_concurrency = 400
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
wal_level = logical
synchronous_commit = on
max_wal_size = 4GB
min_wal_size = 32MB
checkpoint_completion_target = 0.7
primary_conninfo = 'host=... port=5432 user=repluser passfile=''...'' application_name=db2_slave'
primary_slot_name = 'db2'
hot_standby = on
max_standby_archive_delay = 1800s
max_standby_streaming_delay = 1800s
Se eu executar iotop -u postgresql no modo de espera, vejo dois processos:
2229172 postgres: 13/main: walreceiver streaming DDFD/8E9FE9E0
2229138 postgres: 13/main: startup recovering 000000010000DDFD0000008E
Depois de executar a solicitação de leitura que leva alguns segundos no modo de espera ( SELECT COUNT(*) FROM big_table;
), o streaming do walreceiver continua funcionando, mas a réplica para de sincronizar:
2229138 postgres: 13/main: startup recovering 000000010000DE0400000017 waiting
Eu executei esta consulta no mestre:
SELECT client_addr as client,
usename as user,
application_name as name,
state,
sync_state as mode,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) as pending,
pg_size_pretty(pg_wal_lsn_diff(sent_lsn, write_lsn)) as write,
pg_size_pretty(pg_wal_lsn_diff(write_lsn, flush_lsn)) as flush,
pg_size_pretty(pg_wal_lsn_diff(flush_lsn, replay_lsn)) as replay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as total_lag
FROM pg_stat_replication;
E a saída foi:
client | user | name | state | mode | pending | write | flush | replay | total_lag
-------------+----------+-----------+-----------+------+---------+---------+---------+--------+-----------
... | repluser | db2_slave | streaming | sync | 0 bytes | 0 bytes | 0 bytes | 21 MB | 21 MB
(1 row)
Se eu executar esta requisição várias vezes, o replay e o lag total aumentam o tempo todo durante a execução desta query ( SELECT COUNT(*) FROM big_table
). Por isso, quero saber as respostas para as perguntas:
- Por que o atraso de repetição continua aumentando durante a execução de uma consulta analítica para réplica?
- Por que o processo de recuperação está no estado "aguardando" assim que eu inicio uma solicitação para o modo de espera?
O motivo são os conflitos de recuperação . O banco de dados pode atrasar a replicação ou cancelar a consulta conflitante. Isso é governado pelo
max_standby_streaming_delay
parâmetro no modo de espera, que determina por quanto tempo o PostgreSQL está pronto para atrasar a reprodução das informações replicadas antes de cancelar a consulta incorreta.Você pode reduzir o número de conflitos definindo
hot_standby_feedback
comoon
em espera (com o risco de aumentar as tabelas no primário), mas isso não eliminará completamente o problema.Essencialmente, você não poderá executar consultas ininterruptas no modo de espera e não terá atraso de replicação ao mesmo tempo.
Veja meu artigo sobre o tema para mais detalhes.
A reprodução precisa remover uma tupla que a consulta que executa a contagem ainda tem direito de ver. Então a repetição vai esperar.