Uma breve história sobre a solicitação "pesada" e a solução elegante para o problema
Recentemente, à noite, os alertas começaram a nos acordar: não havia espaço em disco suficiente. Logo descobrimos que o problema está nas tarefas de ETL.
A tarefa ETL foi executada em uma tabela na qual os registros e despejos binários são armazenados. Todas as noites, essa tarefa era remover despejos duplicados e liberar espaço.
Para procurar dumps duplicados, usamos esta consulta:
id, MIN(id) OVER (PARTITION BY blob ORDER BY id) FROM dumps
A consulta combina os mesmos despejos pelo campo BLOB. Usando a função window, obtemos o identificador da primeira aparência de cada despejo. Então, com essa solicitação, excluímos todos os dumps duplicados.
A solicitação foi executada por algum tempo e, como pode ser visto nos logs, consumiu muita memória. O gráfico mostra como ele obteve espaço livre em disco todas as noites:
Com o tempo, a solicitação exigiu mais memória, as falhas se aprofundaram. E, olhando o plano de execução, vimos imediatamente onde tudo vai:
Buffers: shared hit=3916, temp read=3807 written=3816 -> Sort (cost=69547.50..70790.83 rows=497332 width=36) (actual time=107.607..127.485 rows=39160) Sort Key: blob, id Sort Method: external merge Disk: 30456kB Buffers: shared hit=3916, temp read=3807 written=3816 -> Seq Scan on dumps (cost=0..8889.32 rows=497332 width=36) (actual time=0.022..8.747 rows=39160) Buffers: shared hit=3916 Execution time: 159.960 ms
A classificação ocupa muita memória. Em termos de execução, a classificação requer aproximadamente 30 MB de memória de um conjunto de dados de teste.
Porque
O PostgreSQL aloca memória para hash e classificação. A quantidade de memória é controlada pelo parâmetro work_mem . O tamanho padrão de work_mem é 4 MB. Se forem necessários mais de 4 MB para hash ou classificação, o PostgreSQL consome temporariamente espaço em disco.
Nossa consulta consome claramente mais de 4 MB, portanto, o banco de dados usa muita memória. Decidimos: não nos apressaremos e não aumentamos o parâmetro e expandimos o armazenamento. É melhor procurar outra maneira de aparar a memória para classificação .
Classificação econômica
"A quantidade de classificação consumida depende do tamanho do conjunto de dados e da chave de classificação. Você não pode reduzir o conjunto de dados, mas o tamanho da chave é possível .
Para o ponto de referência, usamos o tamanho médio da chave de classificação:
avg ---------- 780
Cada chave pesa 780. Para reduzir a chave binária, ela pode ser hash. No PostgreSQL, há md5 para isso (sim, não segurança, mas, para o nosso propósito, ele fará). Vamos ver quanto pesa o BLOB com o MD5:
avg ----------- 36
O tamanho da chave com hash no md5 é de 36 bytes. Uma chave com hash pesa apenas 4% da opção original .
Em seguida, lançamos a solicitação original com uma chave de hash:
id, MIN(id) OVER ( PARTITION BY md5(array_to_string(blob, '') ) ORDER BY id) FROM dumps;
E o plano de implementação:
Buffers: shared hit=3916 -> Sort (cost=7490.74..7588.64 rows=39160 width=36) (actual time=349.383..353.045 rows=39160) Sort Key: (md5(array_to_string(blob, ''::text))), id Sort Method: quicksort Memory: 4005kB Buffers: shared hit=3916 -> Seq Scan on dumps (cost=0..4503.40 rows=39160 width=36) (actual time=0.055..292.070 rows=39160) Buffers: shared hit=3916 Execution time: 374.125 ms
Com uma chave com hash, a solicitação consome apenas 4 megabytes extras, ou seja, pouco mais de 10% dos 30 MB anteriores. Portanto, o tamanho da chave de classificação afeta bastante a quantidade de memória que a classificação consome .
Ainda mais
Neste exemplo, fizemos o hash do BLOB usando o md5 . Hashes criados com o MD5 devem pesar 16 bytes. E nós temos mais:
md5_size ------------- 32
Nosso hash tinha exatamente o dobro do tamanho, porque o md5 produz um hash na forma de texto hexadecimal.
No PostgreSQL, você pode usar o MD5 para fazer hash com a extensão pgcrypto . pgcrypto cria MD5 do tipo bytea (em binário) :
select pg_column_size( digest('foo', 'md5') ) as crypto_md5_size crypto_md5_size --------------- 20
O hash ainda é 4 bytes a mais do que deveria. Só que o tipo bytea usa esses 4 bytes para armazenar o comprimento do valor, mas não o deixaremos assim.
Acontece que o tipo de uuid no PostgreSQL pesa exatamente 16 bytes e suporta qualquer valor arbitrário, então nos livramos dos quatro bytes restantes:
uuid_size --------------- 16
Isso é tudo. 32 bytes com md5 transformam em 16 com uuid .
Eu verifiquei os efeitos da mudança usando um conjunto de dados maior. Os dados em si não podem ser mostrados, mas compartilharei os resultados:
Como você pode ver na tabela, a solicitação problemática original pesava 300 MB (e nos acordou no meio da noite). Com a chave uuid , a classificação levou apenas 7 MB.
Considerações de acompanhamento
Uma solicitação com uma chave de classificação de memória com hash consome menos, mas funciona muito mais devagar:
O hash usa mais CPU; portanto, uma solicitação com um hash é mais lenta. Mas tentamos resolver o problema com espaço em disco; além disso, a tarefa é realizada à noite, para que o tempo não seja um problema. Comprometemo-nos a economizar memória.
Este é um ótimo exemplo do fato de que você nem sempre precisa tentar acelerar as consultas ao banco de dados . É melhor usar o que é equilibrado e extrair o máximo de um mínimo de recursos.