Nós estamos correndo:
user@host:~$ psql -d database -c "SELECT version();"
version
---------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.7 (Ubuntu 10.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)
sobre:
user@host:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 16.04.6 LTS
Release: 16.04
Codename: xenial
e ter a seguinte configuração:
database=# \d+ schema.table
Table "schema.table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------------+-----------------------------+-----------+----------+-------------------------------------------------+----------+--------------+-------------
column_1 | bigint | | not null | nextval('table_id_seq'::regclass) | plain | |
column_2 | character varying | | not null | | extended | |
column_3 | character varying | | not null | | extended | |
column_4 | character varying | | not null | | extended | |
column_5 | timestamp without time zone | | not null | | plain | |
column_6 | timestamp without time zone | | | | plain | |
column_7 | character varying | | not null | | extended | |
column_8 | jsonb | | not null | | extended | |
column_9 | jsonb | | | | extended | |
column_10 | character varying | | not null | | extended | |
column_11 | character varying | | not null | | extended | |
column_12 | character varying | | | | extended | |
column_13 | character varying | | | | extended | |
column_14 | timestamp with time zone | | not null | | plain | |
column_15 | timestamp with time zone | | not null | | plain | |
Indexes:
"table_pkey" PRIMARY KEY, btree ( column_1 )
"table_idx_1" btree ( column_11)
"table_idx_2" btree ( column_4, column_2, column_7, column_5, column_6 )
"table_idx_3" btree ( column_7, column_11, column_15 )
"table_idx_4" btree ( column_7, column_11, column_14 )
"table_idx_5" btree ( column_7, column_11, column_5 )
"table_idx_6" btree ( column_7, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER ), column_5 )
"table_idx_7" btree ( column_15 )
"table_idx_8" btree ( column_4, column_2, column_7, column_5, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER ) )
"table_idx_9" btree ( column_4, column_2, column_7, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER) )
"table_idx_a" btree ( column_7, column_4, column_2, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER), ( ( column_8 ->> 'string_2'::TEXT )::INTEGER ) ) WHERE column_7::TEXT = 'string_3'::TEXT
Check constraints:
"table_check_constraints" CHECK ( lower( column_10::TEXT ) <> 'string_4'::TEXT OR column_9 IS NOT NULL AND column_6 IS NOT NULL )
Autovacuum está ligado e configurado com:
user@host:~$ psql -d database -c "SELECT name, setting, pending_restart FROM pg_settings WHERE NAME ILIKE '%autovacuum%' ORDER BY name;"
name | setting | pending_restart
-------------------------------------+-----------------------+-----------------
autovacuum | on | f
autovacuum_analyze_scale_factor | 0.002 | f
autovacuum_analyze_threshold | 10 | f
autovacuum_freeze_max_age | 200000000 | f
autovacuum_max_workers | 5 | f
autovacuum_multixact_freeze_max_age | 400000000 | f
autovacuum_naptime | 30 | f
autovacuum_vacuum_cost_delay | 10 | f
autovacuum_vacuum_cost_limit | 1000 | f
autovacuum_vacuum_scale_factor | 0.001 | f
autovacuum_vacuum_threshold | 25 | f
autovacuum_work_mem | -1 | f
log_autovacuum_min_duration | 0 (env 1) /-1 (env 2) | f
(13 rows)
A seguinte sequência de eventos ocorreu no ambiente 1 , durante o qual autovacuumestava ligado e configurado como acima:
- Todas as noites
VACUUM (VERBOSE, ANALYZE)do banco de dados adicionado. - Algum tempo passa durante o qual o inchaço está no nível operacional normal.
- Nightly
VACUUM (VERBOSE, ANALYZE)do banco de dados é removido. - O índice
table_idx_8que inclui uma coluna de tipo de dados JSONB é adicionado. - O índice
table_idx_9que inclui uma coluna de tipo de dados JSONB é adicionado. - O surto de crescimento do inchaço começa e continua por 2 dias até atingir o pico.
VACUUM (VERBOSE, FULL)de mesa.- Bloat retorna aos níveis operacionais normais e permanece lá.
O tamanho do banco de dados (GB) ficou assim no ambiente 1 durante esta sequência de eventos:
E é assim que o bloat (GB) se parecia em ambiente 1 :
O número de linhas ativas no ambiente 1 :
O número de linhas mortas no ambiente 1 :
A seguinte sequência de eventos ocorreu em ambiente 2 , durante todo o qual autovacuumestava ligado e configurado como acima:
- Todas as noites
VACUUM (VERBOSE, ANALYZE)do banco de dados adicionado. - Algum tempo passa durante o qual o inchaço está no nível operacional normal.
- Todas as noites
VACUUM (VERBOSE, ANALYZE)do banco de dados é removido. - Índice
table_idx_8que inclui uma coluna de tipo de dados JSONB é adicionado. - Índice
table_idx_9que inclui uma coluna de tipo de dados JSONB é adicionado. - O surto de crescimento do inchaço começa e continua por 2 dias até atingir o pico e derrubar o DB (disco cheio).
TRUNCATE TABLE schema.table.- A tabela schema.table é preenchida novamente.
- O inchaço não se estabiliza e cresce até atingir o pico novamente.
TRUNCATE TABLE schema.tableantes que o disco encha novamente.- VACUUM (VERBOSE, FULL) do banco de dados.
- A tabela schema.table é preenchida novamente.
- Bloat continua a crescer!
O tamanho do banco de dados (GB) no ambiente 2 ficou assim durante esta sequência de eventos:
E esta é a aparência do bloat (GB) no ambiente 2 :
A única diferença entre esses dois ambientes é que eles são especificados de forma ligeiramente diferente (sendo 2 menos poderosos). Durante essas sequências de eventos, os volumes de gravação/leitura permaneceram inalterados em cada ambiente. Estamos usando essa consulta para medir o inchaço em bytes.
Eu verifiquei os logs do PostgreSQL, os logs de monitoramento e os logs de confirmação (Git) e identifiquei a adição dos dois índices como o gatilho para o inchaço, mas:
- Isso está certo? A adição de um índice pode desencadear um surto de crescimento tão inchado?
- Por que adicionar os índices acionou o inchaço, se foi?
- Por que o ambiente 1 se estabilizou e o ambiente 2 não?
- Como podemos estabilizar o ambiente 2?
Qualquer ajuda para responder a essas perguntas seria muito apreciada e escusado será dizer que estou feliz em fornecer qualquer outra informação que eu tenha perdido que possa ser útil.
Eu não acho que a abordagem arqueológica será muito útil aqui. Há muitas informações ausentes e variáveis de confusão. Por exemplo, as pessoas geralmente não adicionam índices sem motivo. Se uma mudança na carga de trabalho motivou a criação do índice, pode ser a mudança na carga de trabalho, independente do índice, que está causando o inchaço.
Existem muitas teorias que explicam o que você vê, mas realmente não há como distinguir entre elas com base na história dada. Cada índice dá mais trabalho ao vácuo, então seus novos índices podem ter apenas empurrado para o ponto de inflexão apenas porque já estava próximo, sem levar em consideração qual é o conteúdo dos índices. Ou talvez muito trabalho acumulado enquanto a tabela estava bloqueada para a criação do índice, e então o frenesi de atividade uma vez que a trava foi liberada a levou ao limite. Não são apenas mais índices que criam mais trabalho para o vácuo – o inchaço também. Isso pode levar a um ciclo vicioso em que mais inchaço diminui o vácuo, levando a mais inchaço ainda. Essa pode ser a razão pela qual o ambiente 1 se estabilizou após o VACUUM FULL, quebrou o ciclo vicioso a ponto de aspiradores regulares agora poderem acompanhar.
Essas configurações parecem bastante ridículas à primeira vista. Existe uma razão para eles? Pode estar gastando tanto tempo limpando e analisando tabelas que realmente não precisam delas, que não consegue acompanhar a mesa que precisa ser limpa (mas se você tiver apenas uma mesa grande, isso pode não ser muito interesse). A redução dos fatores de escala pode fazer sentido, mas geralmente apenas em conjunto com um aumento dos limites.
Eu rotineiramente defino "vacuum_cost_page_hit" para zero "vacuum_cost_page_miss" para zero. Na minha experiência, os problemas de desempenho simultâneos causados pelo autovac geralmente são causados pela escrita, não pela leitura, portanto, não faz sentido limitar o lado da leitura. Isso pode ser especialmente importante quando você tem tabelas e índices que já estão inchados, pois assim você pode ter muito mais leitura do que escrita.
A saída da configuração log_autovacuum_min_duration=0 pode ajudar a distinguir entre as várias teorias. Além disso, usar o pg_freespacemap para ver quanta soma (avail) o PostgreSQL acha que a tabela tem, enquanto está em um estado de inchaço, pode ser informativo.