PostgreSQL - Agilizando a restauração de um DUMP

-Definições-

Nome da database que trabalharemos: producao
Owner: postgresql
Versão do banco: 9.1

-Primeiras avaliações-

Tipo de dump a se utilizar:

Tamanho da Database no PostgreSQL:  45MB
Dump em plaintext: 51MB
Em custom format: 20MB

Logo, defini que o tipo de dump que utilizarei é o custom para o restore.

Configuração atual do PostgreSQL: 

shared_buffers = 24MB
maintenance_work_mem = 16MB
checkpoint_segments = 3
effective_cache_size = 128MB

Tempo de restore:


 time pg_restore -d producao -U postgres -j 2 /opt/resources/producao.dmpc

real    0m9.264s
user    0m0.562s
sys     0m0.427s

-j : São jobs em paralelo, como tenho dois processadores, no caso vCPUs utilizei 2, se tiver mais utilize mais. 

Dimensionando o PostgreSQL:  


shared_buffers = 48MB
maintenance_work_mem = 32MB
checkpoint_segments = 6
effective_cache_size = 128MB



Tempo de restore:


 time pg_restore -d producao2 -U postgres -j 2 /opt/resources/producao.dmpc




real    0m8.295s
user    0m0.532s
sys     0m0.429s

Resultado: baixamos 1 segundo

Vamos adiante: 


shared_buffers = 96MB
maintenance_work_mem = 64MB
checkpoint_segments = 12
effective_cache_size = 128MB

Tempo de restore:


time pg_restore -d producao3 -U postgres -j 2 /opt/resources/producao.dmpc

real    0m8.367s
user    0m0.552s
sys     0m0.420s

Estranho, piorou, pode ser relacionado aos checkpoints, então vamos manter os seguimentos de checkpoints em 6 e duplicar os itens de memória.

Ajustes no PostgreSQL:


shared_buffers = 192MB
maintenance_work_mem = 128MB
checkpoint_segments = 6
effective_cache_size = 128MB

Tempo de restore: 


time pg_restore -d producao4 -U postgres -j 2 /opt/resources/producao.dmpc

real    0m8.699s
user    0m0.547s
sys     0m0.415s

Não funcionou como esperávamos, então baixamos o shared_mem, e só aumentamos o maintenance_work_mem. 

Vamos lá:

shared_buffers = 64MB
maintenance_work_mem = 256MB
checkpoint_segments = 6
effective_cache_size = 128MB

Tempo de restore:  


time pg_restore -d producao5 -U postgres -j 2 /opt/resources/producao.dmpc

real    0m9.683s
user    0m0.563s
sys     0m0.473s

Em momento algum o servidor fez swap: 

             total       used       free     shared    buffers     cached

Mem:           996        919         77          0         45        747

-/+ buffers/cache:        125        871

Swap:         2015          0       2015

Bom, nada funcionou, então vamos voltar a segunda configuração onde obtive 8.2s de restore:

time pg_restore -d producao8 -U postgres -j 2 /opt/resources/producao.dmpc

real    0m9.862s
user    0m0.598s
sys     0m0.461s

Ih, agora deu problema, como o tempo mudou tanto? Minha explicação é, utilização de recursos do servidor, por ser uma VM limitada os resultados variam de acordo com a utilização do hospedeiro e da VM, por exemplo, IO de disco, memória, processamento e etc. 

Bueno, mas lembra do -j, vamos utilizar mais do que 2 processos em paralelo, vamos para 4 sem alterar as configurações de memória do banco:

time pg_restore -d producao9 -U postgres -j 4 /opt/resources/producao.dmpc

real    0m7.559s

user    0m0.601s

sys     0m0.506s

Olha, que loucura! Vamos para 8? 

time pg_restore -d producao10 -U postgres -j 8 /opt/resources/producao.dmpc

real    0m7.601s

user    0m0.580s

sys     0m0.467s

Humm... melhor resultado obtido foi quando duplicamos os jobs em paralelo para o dobro do número de processadores. Mas vamos fazer uma loucura:

Tenho 100 conexões disponíveis, vamos rodar com -j em 50:

time pg_restore -d producao11 -U postgres -j 50 /opt/resources/producao.dmpc

real    0m6.959s

user    0m0.585s

sys     0m0.407s

Ok, melhor resultado obtido, então vamos aos 90? 

time pg_restore -d producao12 -U postgres -j 90 /opt/resources/producao.dmpc

real    0m5.216s

user    0m0.494s

sys     0m0.395s

Que loucura, estamos chegando a uma conclusão. Mas calma, vamos aos 100? 

time pg_restore -d producao13 -U postgres -j 100 /opt/resources/producao.dmpc

real    0m5.523s

user    0m0.547s

sys     0m0.414s

É, perdemos. Agora vamos para as conclusões: 

DICA 1 - Utilização do servidor: Não é novidade que o servidor do PostgreSQL deve ser dedicado, bem como deve ser feito um "hardening" dos processos em execução no servidor, quando menos processos rodando, melhor. 

DICA 2 - Tuning de restauração: Apesar dos tempos obtidos não deixarem claro quais parâmetros do banco devemos dimensionar para uma melhor performance, sugiro atuar nos parâmetros abaixo:

shared_buffers : não utilize valores muito grandes, deixe a maior parte de sua memória para o 'cache_effective_size'. 
maintenance_work_mem : Lembre-se que alterar este valor impacta na quantidade de memória utilizada nas conexões, então cuidado.
work_mem: Ajuste de forma compatível com o 'maintenance_work_mem'. 
checkpoint_segments : Não esqueça desse cara também, mas não aumente muito, pois lembre que este possui outros amigos, o 'checkpoint_timeout', entre outros que devem crescerem juntos. 
effective_cache_size : Esse cara faz com que seus índices funcionem, então deixe um bom valor de sua memória reservada para ele. 


DICA 3 - Jamais faça SWAP, se estiver fazendo, reduza os valores de memória. 


DICA 4 - Como restauração de uma base normalmente não é feita em horário de produção, utilize um bom valor de suas conexões disponíveis para os jobs paralelos do 'pg_restore', isso reduz muito o tempo de restauração. 

Espero não ter escrito muita besteira. Abraços.