Maravilha Matheus! Acredito ter entendido esses pontos de atenção que vc mencionou. Os arquivos órfãos são sim a diferença para o tamanho da base, fiz essa conferência. Bom, eu não comentei antes, mas gostaria que soubessem que não sou DBA, sou um simples desenvolvedor C++ que gosta de modelagem e de quebrar a cabeça com lógica em programas/procedures... rss Como tenho certa facilidade com modelagem e criação de queries, acabo 'ajudando' o pessoal em alguns assuntos relacionados ao banco, mesmo que esse meu perfil não seja de administrador. Aproveitando as dicas que vcs me deram, gostaria de ajudar um mínimo as pessoas que se interessaram pelo assunto dos índices e seus problemas, relatando o que pretendo fazer em adição a solução do problema: Vou conversar com os responsáveis por popular essa tabela, e tentar também reavaliar a necessidade dos índices. Além disso, como temos uma tabela relativamente grande, e indices consequentemente grandes, vou avaliar também o conteúdo dos campos pra ver se vale a pensa criar indices parciais (pra talvez agilizar as consultas e diminuir esse tamanho físico do índice). Obrigado a todos e parabéns a comunidade!!!! []´s
From: [email protected] Date: Thu, 5 Dec 2013 09:20:23 -0200 To: [email protected] Subject: Re: [pgbr-geral] HD lotou no meio de um reindex 2013/12/5 Fabio Barros <[email protected]> Bom dia!!! Pessoal, agradeço os comentários!!! Vamos lá, pretendo não prolongar mais o assunto, apenas vou registrar algumas observações em cima das respostas dadas, e pedir apenas uma confirmação se o que pretendo fazer está coerente. Para o real problema, disco cheio, estamos em processo de compra e troca dos HD, mas todas as trocas vão demorar uns 3 meses (trocamos 2 sites por semana), por isso a necessidade de uma solução temporária. Ok. Trabalhar com pouco espaço em disco é realmente um trabalho herculano. Sobre como identifiquei arquivos mortos, não usei a consulta mencionada, apenas rodei um 'ls -l' na pasta do database com problema, e cruzei a informação com o que recuperei do catálogo, aí achei os arquivos 'perdidos'. Veja que é exatamente o que a consulta faz. Aliás, fiz um teste aqui e vi que minha consulta está incorreta. Aqui vai uma versão correta e mais completa: SELECT * FROM (SELECT pg_ls_dir('base/'||(SELECT oid FROM pg_database WHERE datname = current_database())) AS datafile) ls WHERE ls.datafile ~ '^[0-9]+' AND NOT EXISTS (SELECT 1 FROM pg_class r WHERE pg_relation_filenode(r.oid)::text = substring(ls.datafile FROM '^[0-9]+')); Só pedi para você confirmar como fez, pois é comum usar a coluna relfilenode da pg_class, mas essa não traz todos os arquivos. O correto mesmo seria usar a função pg_relation_filenode(pg_class.oid), como na consulta acima. Como gostaria de não ter que excluir manualmente esses arquivos, acredito que a solução temporária de excluir e recriar os índices seja a melhor pra gente. Excluir o índice não vai adiantar nesse caso. Veja que os arquivos apareceram "órfãos" porque o PostgreSQL teve uma queda brusca (creio que é a única forma que isso poderia acontecer, a não ser que você deparou-se com um bug do PG), algo como um "kill -9" durante o REINDEX (fiz um teste assim aqui, e aconteceu isso mesmo). Nesse caso, quando ele voltou, o mesmo já não sabia mais que aqueles arquivos eram o índice sendo reconstruído, só tem a informação do índice anterior (pelo menos nos testes que fiz esse foi o comportamento). Ou seja, se você excluir o índice, ele irá excluir apenas o arquivo original, mas não os "fantasmas", ele não os conhece mais. Pensei até em fazer um dump/restore da base, pois fizemos esse teste e todo o lixo sumiu, mas temos o problema de ter que parar o site. Será que não vale a pena? Você está com uma situação um tanto crítica, e eu não diria que a culpa é sua, pois trabalhar com discos tão minúsculos (como eu já disse) não é algo fácil. Mas, já fica a dica (para todos) que o REINDEX (assim como o CLUSTER e VACUUM FULL) vão, de fato, liberar espaço em disco, mas para fazer isso eles irão precisar consumir um bom espaço antes. Logo, usá-los porque o disco está lotado é a pior coisa que pode-se fazer. xD Bom, tenho ainda 2 perguntas, que acredito ser bem pontuais: 1 ) Qual o % de tuplas mortas que justificam executar o reindex / vacuum? É a resposta de sempre. Depende. Nesse caso de vários fatores, como a altura da árvore-B, a quantidade de tuplas, etc. Resumindo, só vale a pena se o REINDEX irá melhorar a performance de forma considerável. O VACUUM normal (sem o FULL), você pode rodar sempre. Aliás, se sua base é de apenas ~10G eu diria para fazer um VACUUM ANALYZE agendado frequentemente (o autovacuum faz bem seu trabalho, mas esse V_A não faria mal). O mesmo não vale para VACUUM FULL, REINDEX nem CLUSTER, ok? Quanto ao REINDEX, o maior problema é que ele bloqueia a operação normal. Nas versões mais novas você pode fazer o seguinte ao invés do REINDEX. Supondo que você tem um índice foo_idx na tabela foo e campo bar: CREATE INDEX CONCURRENTLY foo_idx_tmp ON foo (bar); DROP INDEX foo_idx; ALTER INDEX foo_idx_tmp RENAME TO foo_idx; É mais demorado, de fato, que o REINDEX, mas não bloqueia a operação normal. De fato, é basicamente o que o REINDEX faz, mas sem o CONCURRENTLY. 2 ) Estou correto sobre como pretendo dropar/recriar os indices? Basicamente, minha intenção é aproveitar a sintaxe de criação armazenada no catálogo de cada site que tem a tabela, para evitar interferência humana (criação errada ou falta de algum índice), e com isso garantir que os índices estarão exatamente como antes. Como disse antes, creio que não irá funcionar como você espera. Aliás, você conferiu o tamanho desses arquivos "fantasmas"? Realmente bate com o tamanho que aumentou? Você verificou o diretório pg_xlog também para ver se não é nele o espaço a mais que você está vendo? (OBS: Não apague arquivos ali, NUNCA, se for nele, apenas responda aqui e ajudaremos a resolver). 2013/12/3 Fabio Barros <[email protected]> Boa tarde! Estou postando minha primeira dúvida na lista, e agradeço possíveis comentários. Opa. Seja bem-vindo. Fiz um REINDEX em uma tabela com cerca de 15 milhões de registros, com cerca de meia dúzia de índices, e como meu disco é pequeno, acabou o espaço no mesmo. Isso realmente pode acontecer. Pois para fazer um REINDEX, o PostgreSQL de fato reescreve cada índice num novo arquivo, e, somente ao final, apaga o arquivo anterior. Ou seja, um REINDEX vai ocupar pelo menos o dobro de espaço em disco (fora os logs de transação). Percebi que o tamanho físico do database subiu de 9GB para 15GB, e ao pesquisar, identifiquei vários arquivos perdidos na mesma, que justificam esse crescimento. Acredito que os arquivos se referem aos indices da tabela em questão, e agora preciso 'limpar' esses arquivos do database. Como você verificou que esses arquivos estão sobrando? Tem certeza que não há logs de transação (diretório pg_xlog) que não foram arquivados? Para verificar os data files, uma consulta que pensei aqui é a seguinte (não vai funcionar com tablespaces, teria que adaptar): SELECT * FROM (SELECT pg_ls_dir('base/'||(SELECT oid FROM pg_database WHERE datname = current_database())) AS datafile) ls WHERE ls.datafile ~ '^[0-9]+$' AND ls.datafile NOT IN (SELECT pg_relation_filenode(oid)::text FROM pg_class); Elá irá retornar arquivos que são "fantasmas". Além desses podem ter outros forks, por exemplo, a consulta pode retornar o 1234, daí pode ter de fato também o 1234.1, 1234.2, 1234_vm, 1234_fsm, etc. Olhando assim eu não vejo como esses arquivos poderiam estar sendo usados pelo PostgreSQL, e, por isso, poderia apagá-los. Mas... Isso pode ser ARRISCADO. Faça um backup base e faça testes em outro ambiente (não em produção). Para testes, fizemos um dump/restore e o espaço ocupado fisicamente voltou para os 9GB, mas temos o inconveniente de não poder fazer nada na base de dados enquanto o processo é feito. Posso simplesmente remover os arquivos 'perdidos'? Primeiro mapeie quais são esses arquivos, com a consulta acima. Poste o resultado aqui e vamos analisar. Ok? Há outro meio, mais seguro, de se fazer isso? Desde já, agradeço as possíveis sugestões. []´s 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
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
