Ajustando o desempenho do Postgresql e o uso de memória em um fluxo de trabalho python
Eu uso o Postgresql 9.4 para um banco de dados modelo. Minha tabela é mais ou menos assim:
CREATE TABLE table1 (
sid INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('table1_sid_seq'::regclass),
col1 INT,
col2 INT,
col3 JSONB);
Meu fluxo de trabalho Python 2.7 geralmente se parece com isso:
curs.execute("SELECT sid, col1, col2 FROM table1")
data = curs.fetchall()
putback = []
for i in data:
result = do_something(i[1], i[2])
putback.append((sid, result))
del data
curs.execute("UPDATE table1
SET col3 = p.result
FROM unnest(%s) p(sid INT, result JSONB)
WHERE sid = p.sid", (putback,))
Isso normalmente funciona muito bem e eficientemente. Entretanto, para consultas grandes, o uso de memória do Postgresql às vezes passa pelo teto (> 50 GB) durante oUPDATE
comando e acredito que está sendo morto pelo OS X, porque recebo oWARNING: terminating connection because of crash of another server process
. Meu Macbook Pro possui 16 GB de RAM e a consulta em questão possui 11 milhões de linhas, cada uma com cerca de 100 caracteres de dados a serem gravados.
Minhaspostgresql.conf
:
default_statistics_target = 50
maintenance_work_mem = 512MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 4GB
work_mem = 256MB
wal_buffers = 16MB
checkpoint_segments = 128
shared_buffers = 1024MB
max_connections = 80
Então eu me pergunto
Por que minha consulta está consumindo, às vezes, quantidades excessivas de RAM?Como posso controlar o uso da memória e ainda garantir um bom desempenho?Existe uma boa orientação ou ferramenta para ajustar o Postgresql?Atualizar:
Tenho certeza de que @wildplasser identificou meu problema. Nos comentários, ele sugere despejar os dados no banco de dados primeiro e descompactá-los a partir daí. Infelizmente, não consegui descobrir como implementar sua proposta.Se alguém tiver uma idéia de como fazer isso, sua resposta será aceita com prazer.