2014-08-06 0:28 GMT-03:00 Danilo Silva <[email protected]>:
> Cenário:
> PostgreSQL: 9.2.4
>
Primeiro. Atualize imediatamente (tipo pra ontem) para a versão 9.2.9.
Sempre mantenha seu servidor PG no último lançamento da versão, ou seja,
mantenha o terceiro número sempre o maior possível (ele é incrementado para
correção de bugs ou pequenas melhorias que *não* causam incompatibilidade,
então é só atualizar os binários, dê uma lida nos release notes também,
irão dizer se precisa de alguma manutenção para resolução de bugs).
> S.O: Windows server 2008 R2 - 64 bit
> Servidor com 4GB de ram
>
Me parece um pequeno servidor para um banco com 167K tabelas, não acha?
Além disso, Windows...
> shared_buffers: 1228MB
>
Muito alto para o seu servidor e também para o Windows. No Windows é
recomendado não usar mais do que 512MB, então eu diria para usar exatamente
512MB.
> 1 base de dados de 32GB com 167 mil tabelas
>
> Problema:
> Não consigo efetuar dump, dá out of memory (a mensagem diz que devo
> aumentar o parâmetro max_locks_per_transaction) que atualmente está em 2048;
>
>
Por favor, envie a mensagem exata que encontrou.
Quanto ao max_locks_per_transaction, quando o pg_dump executa ele irá
realizar um LOCK de cada tabela para garantir que não haja inconsistências,
assim ele irá necessitar ao menos um lock por tabela (na verdade é mais do
que isso, não me lembro exatamente o número). Para bancos de dados com
muitas tabelas é comum ter que aumentar esse parâmetro.
O problema maior é que o max_locks_per_transaction não funciona exatamente
como o nome sugere, ao invés de ser reservado esse número de locks por
transação ou sessão, são criados logo ao iniciar a instância o total de
(max_locks_per_transaction * max_connections) de locks (pense em semáforos)
na memória compartilhada (mais um motivo para diminuir o shared_buffers).
Ou seja, você terá que aumentar bastante para ter um valor considerável.
> Pelo pgadmin, só para conectar na base, demora mais de 5 minutos;
>
>
pgadmin... Afff... Talvez ele leia o catálogo inteiro ao conectar, sei lá...
> Fiz um script para efetuar dump tabela por tabela,
>
Não. Não faça isso. Executando um dmp tabela por tabela vai resultar num
backup inconsistente, já que não há um "snapshot" do início, alterações
executadas durante o processo de backup causará inconsistência (pense em
chaves estrangeiras).
> mas o processo demora em média 1 minuto para cada tabela, parei o script
> após 30 minutos (processou apenas 30 tabelas);
>
>
O "overhead" para iniciar o pg_dump será carregado para cada uma dessas
chamadas. Você simplesmente está indo pelo caminho errado.
> Creio que a lentidão ocorre se a query envolver tabelas do catálogo, pois
> pelo psql, um simples \d demora uma eternidade;
>
>
Correto. No psql há uma solução simples, ao invés de usar o \d, se você
saber parte do nome da tabela (o início na verdade), você pode usar:
\dt foo_*
Assim ele irá buscar mais rapidamente (de forma indexada) toda tabela que
inicie com "foo_". Você pode colocar um asterisco no início, mas isso será
lento, pois não será possível indexar.
> Pergunta:
> Existe alguma configuração que possa ser feita para melhorar o desempenho?
>
Bem, quanto à configurações, já expliquei um pouco sobre o shared_buffers e
o max_locks_per_transaction, seria interessante você nos passar as
configurações que está usando para revisarmos. Além disso, creio que
realizar algumas consultas no catálogo e verificar o plano de execução pode
ajudar. Você está tendo lentidão na aplicação também? Algo como um simples
SELECT * FROM tabela, numa tabela pequena é lento?
Uma estratégia que pode usar, é separar as tabelas em esquemas. Para
sistemas multi-tenant isso ajuda bastante. Precisaríamos de mais detalhes
para analisar com precisão.
Outro ponto. Você está usando pool de conexões? Se não, considere um (o
pgbouncer seria minha sugestão inicial), assim você consegue tirar vantagem
do relcache do PostgreSQL (não vai ajudar no dump entretanto).
> Ou alguma estratégia de backup para este cenário?
>
Sim. Utilize backup físico, você ainda pode realizar backup incremental e
ter um super-backup-atualizado-quase-100%. Daí o pg_dump você executa em
períodos menores, mesmo que demore, tudo bem.
> Imagino que um dos passos seria colocar linux, mas somente isso iria
> resolver?
>
Sem dúvida ajudaria bastante.
Atenciosamente,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
