Em um futuro distante, a remoção automática de dados desnecessários será uma das tarefas importantes do SGBD [1]. Enquanto isso, nós mesmos precisamos cuidar de excluir ou mover dados desnecessários para sistemas de armazenamento mais baratos. Digamos que você decida excluir alguns milhões de linhas. Uma tarefa bastante simples, especialmente se a condição for conhecida e houver um índice adequado. "DELETE FROM table1 WHERE col1 = :value" - o que poderia ser mais simples, certo?
Vídeo:
-
Estou no comitê do programa Highload desde o primeiro ano, ou seja, desde 2007.
-
E estou no Postgres desde 2005. Usei em muitos projetos.
-
Grupo com RuPostges também desde 2007.
-
Crescemos para mais de 2100 participantes no Meetup. É a segunda do mundo depois de Nova York, superada há muito tempo por São Francisco.
-
Moro na Califórnia há vários anos. Eu lido mais com empresas americanas, inclusive grandes. Eles são usuários ativos do Postgres. E há todo tipo de coisas interessantes.
é minha empresa. Nosso negócio é automatizar tarefas que eliminam lentidão no desenvolvimento.
Se você está fazendo algo, às vezes há algum tipo de plugue no Postgres. Digamos que você precise esperar que o administrador configure uma unidade de teste para você ou que o DBA responda a você. E encontramos esses gargalos no processo de desenvolvimento, teste e administração e tentamos eliminá-los com a ajuda da automação e de novas abordagens.
Estive recentemente no VLDB em Los Angeles. Esta é a maior conferência sobre bancos de dados. E houve um relatório de que no futuro o DBMS não apenas armazenará, mas também excluirá automaticamente os dados. Este é um novo tópico.
Há cada vez mais dados no mundo dos zettabytes - são 1 de petabytes. E agora já se estima que tenhamos mais de 000 zettabytes de dados armazenados no mundo. E há cada vez mais deles.
E o que fazer com isso? Obviamente, ele precisa ser removido. Aqui está um link para este relatório interessante. Mas até agora isso não foi implementado no SGBD.
Aqueles que sabem contar dinheiro querem duas coisas. Eles querem que excluamos, então tecnicamente devemos ser capazes de fazer isso.
O que vou contar a seguir é uma situação abstrata que inclui um monte de situações reais, ou seja, uma espécie de compilação do que realmente aconteceu comigo e com os bancos de dados ao redor muitas vezes, muitos anos. Rakes estão por toda parte e todos pisam neles o tempo todo.
Digamos que temos uma base ou várias bases que estão crescendo. E alguns registros são obviamente lixo. Por exemplo, o usuário começou a fazer algo ali, mas não terminou. E depois de algum tempo sabemos que esse inacabado não pode mais ser armazenado. Ou seja, gostaríamos de limpar algumas coisas de lixo para economizar espaço, melhorar o desempenho etc.
Em geral, a tarefa é automatizar a remoção de coisas específicas, linhas específicas em alguma tabela.
E nós temos esse pedido, sobre o qual falaremos hoje, ou seja, sobre a coleta de lixo.
Pedimos a um desenvolvedor experiente para fazer isso. Ele aceitou esse pedido, verificou por si mesmo - tudo funciona. Testado em teste - está tudo bem. Lançado - tudo funciona. Uma vez por dia nós o executamos - está tudo bem.
O banco de dados cresce e cresce. Daily DELETE começa a funcionar um pouco mais devagar.
Então entendemos que agora temos uma empresa de marketing e o tráfego será várias vezes maior, então decidimos pausar temporariamente as coisas desnecessárias. E esqueça de voltar.
Alguns meses depois, eles se lembraram. E aquele desenvolvedor desistiu ou está ocupado com outra coisa, instruiu outro a devolvê-lo.
Ele verificou o desenvolvimento, a encenação - está tudo bem. Naturalmente, você ainda precisa limpar o que se acumulou. Ele verificou tudo funciona.
O que acontece depois? Então tudo desmorona para nós. Ele cai de forma que em algum momento tudo cai. Todos estão em choque, ninguém entende o que está acontecendo. E então descobri que o assunto estava neste DELETE.
Algo deu errado? Aqui está uma lista do que poderia ter dado errado. Qual destes é o mais importante?
-
Por exemplo, não houve revisão, ou seja, o especialista DBA não olhou. Ele encontraria imediatamente o problema com um olho experiente e, além disso, ele tem acesso ao prod, onde vários milhões de linhas se acumularam.
-
Talvez eles tenham verificado algo errado.
-
Talvez o hardware esteja desatualizado e você precise atualizar esta base.
-
Ou algo está errado com o próprio banco de dados e precisamos mudar do Postgres para o MySQL.
-
Ou talvez haja algo errado com a operação.
-
Talvez haja alguns erros na organização do trabalho e você precise demitir alguém e contratar as melhores pessoas?
Não houve verificação de DBA. Se houvesse um DBA, ele veria esses vários milhões de linhas e mesmo sem nenhum experimento diria: "Eles não fazem isso." Suponha que se esse código estivesse no GitLab, GitHub e houvesse um processo de revisão de código e não existisse tal coisa que sem a aprovação do DBA essa operação ocorresse no prod, obviamente o DBA diria: “Isso não pode ser feito .”
E ele diria que você terá problemas com IO de disco e todos os processos vão enlouquecer, pode haver bloqueios, e também você vai bloquear o autovacuum por alguns minutos, então isso não é bom.
O segundo erro - eles verificaram no lugar errado. Vimos depois que muitos dados inúteis se acumularam no prod, mas o desenvolvedor não acumulou dados nesse banco de dados e ninguém criou esse lixo durante a preparação. Assim, foram 1 linhas que funcionaram rapidamente.
Entendemos que nossos testes são fracos, ou seja, o processo que é construído não pega problemas. Um experimento DB adequado não foi realizado.
Um experimento ideal é preferencialmente realizado no mesmo equipamento. Nem sempre é possível fazer isso no mesmo equipamento, mas é muito importante que seja uma cópia em tamanho real do banco de dados. Isto é o que venho pregando há vários anos. E um ano atrás eu falei sobre isso, você pode assistir tudo no YouTube.
Talvez nosso equipamento seja ruim? Se você olhar, a latência aumentou. Vimos que a utilização é de 100%. Obviamente, se fossem unidades NVMe modernas, provavelmente seria muito mais fácil para nós. E talvez não nos deixássemos disso.
Se você tiver nuvens, a atualização é feita facilmente lá. Levantadas novas réplicas no novo hardware. transição. E está tudo bem. Bem fácil.
É possível tocar de alguma forma nos discos menores? E aqui, apenas com a ajuda do DBA, mergulhamos em um determinado tópico chamado ajuste de ponto de verificação. Acontece que não tínhamos ajuste de ponto de verificação.
O que é ponto de verificação? É em qualquer DBMS. Quando você tem dados na memória que mudam, eles não são gravados imediatamente no disco. As informações de que os dados foram alterados são primeiro gravadas no log write-ahead. E, em algum momento, o DBMS decide que é hora de despejar as páginas reais no disco, para que, se houver uma falha, possamos fazer menos REDO. É como um brinquedo. Se formos mortos, iniciaremos o jogo a partir do último checkpoint. E todos os DBMS o implementam.
As configurações no Postgres estão atrasadas. Eles são projetados para volumes de dados e transações de 10 a 15 anos. E o ponto de verificação não é exceção.
Aqui estão as informações do nosso relatório de check-up do Postgres, ou seja, verificação automática de integridade. E aqui está um banco de dados de vários terabytes. E pode-se ver bem que os postos de controle forçados em quase 90% dos casos.
O que isso significa? Existem duas configurações lá. O ponto de verificação pode ocorrer por tempo limite, por exemplo, em 10 minutos. Ou pode ocorrer quando muitos dados foram preenchidos.
E por padrão max_wal_saze é definido como 1 gigabyte. Na verdade, isso realmente acontece no Postgres após 300-400 megabytes. Você mudou muitos dados e seu ponto de verificação acontece.
E se ninguém sintonizou, e o serviço cresceu, e a empresa ganha muito dinheiro, tem muitas transações, então o posto de controle chega uma vez por minuto, às vezes a cada 30 segundos e às vezes até se sobrepõe. Isso é muito ruim.
E precisamos garantir que isso aconteça com menos frequência. Ou seja, podemos aumentar max_wal_size. E virá com menos frequência.
Mas desenvolvemos toda uma metodologia de como fazer isso da maneira mais correta, ou seja, como tomar uma decisão sobre a escolha das configurações, claramente com base em dados específicos.
Assim, estamos fazendo duas séries de experimentos em bancos de dados.
A primeira série - mudamos max_wal_size. E estamos fazendo uma operação massiva. Primeiro, fazemos isso na configuração padrão de 1 gigabyte. E fazemos um DELETE massivo de muitos milhões de linhas.
Você pode ver como é difícil para nós. Vemos que o disco IO é muito ruim. Olhamos quantos WALs geramos, porque isso é muito importante. Vamos ver quantas vezes o checkpoint aconteceu. E vemos que não é bom.
Em seguida, aumentamos max_wal_size. Nós repetimos. Aumentamos, repetimos. E tantas vezes. Em princípio, 10 pontos é bom, onde 1, 2, 4, 8 gigabytes. E olhamos para o comportamento de um sistema particular. É claro que aqui o equipamento deve ser como no prod. Você deve ter os mesmos discos, a mesma quantidade de memória e as mesmas configurações do Postgres.
E assim trocaremos nosso sistema, e saberemos como o SGBD vai se comportar em caso de DELETE em massa ruim, como vai fazer o checkpoint.
Checkpoint em russo são postos de controle.
Exemplo: DELETE vários milhões de linhas por índice, as linhas são "espalhadas" pelas páginas.
Aqui está um exemplo. Isso é alguma base. E com a configuração padrão de 1 gigabyte para max_wal_size, fica muito claro que nossos discos vão para a prateleira para gravação. Esse quadro é um sintoma típico de um paciente muito doente, ou seja, ele se sentia muito mal. E houve uma única operação, houve apenas um DELETE de vários milhões de linhas.
Se tal operação for permitida no prod, então simplesmente nos deitaremos, porque é claro que um DELETE nos mata na prateleira.
Além disso, onde 16 gigabytes, é claro que os dentes já se foram. Os dentes já estão melhores, ou seja, estamos batendo no teto, mas não tão mal. Havia alguma liberdade ali. À direita está o registro. E o número de operações - o segundo gráfico. E é claro que já estamos respirando um pouco mais facilmente com 16 gigabytes.
E onde 64 gigabytes podem ser vistos que ficou completamente melhor. Já os dentes são pronunciados, há mais oportunidades de sobreviver a outras operações e fazer algo com o disco.
Por que isso?
Vou me aprofundar um pouco nos detalhes, mas esse tópico, como fazer o ajuste do checkpoint, pode resultar em um relatório inteiro, então não vou carregar muito, mas vou delinear um pouco quais são as dificuldades.
Se o ponto de verificação acontecer com muita frequência e atualizarmos nossas linhas não sequencialmente, mas localizarmos por índice, o que é bom, porque não excluímos a tabela inteira, pode acontecer que primeiro toquemos na primeira página, depois na milésima, e depois voltou para o primeiro. E se entre essas visitas à primeira página, o checkpoint já a salvou no disco, ele a salvará novamente, porque sujamos uma segunda vez.
E forçaremos o ponto de verificação a salvá-lo várias vezes. Como haveria operações redundantes para ele.
Mas isso não é tudo. As páginas têm 8 kilobytes no Postgres e 4 kilobytes no Linux. E há uma configuração full_page_writes. Ele é ativado por padrão. E isso está correto, porque se desligarmos, existe o perigo de que apenas metade da página seja salva se ela travar.
O comportamento de gravação no WAL do log de encaminhamento é tal que quando temos um checkpoint e mudamos de página pela primeira vez, a página inteira, ou seja, todos os 8 kilobytes, entra no log de encaminhamento, embora tenhamos alterado apenas o linha, que pesa 100 bytes. E temos que anotar a página inteira.
Nas alterações subseqüentes, haverá apenas uma tupla específica, mas pela primeira vez anotamos tudo.
E, portanto, se o ponto de verificação acontecer novamente, teremos que começar tudo do zero novamente e empurrar a página inteira. Com checkpoints frequentes, quando percorremos as mesmas páginas, full_page_writes = on será mais do que poderia ser, ou seja, geramos mais WAL. Mais é enviado para réplicas, para o arquivo, para o disco.
E, consequentemente, temos duas redundâncias.
Se aumentarmos max_wal_size, tornamos mais fácil para o checkpoint e para o wal writer. E isso é ótimo.
Vamos colocar um terabyte e viver com isso. O que há de ruim nisso? Isso é ruim, porque em caso de falha, vamos subir por horas, porque o checkpoint foi há muito tempo e muita coisa já mudou. E precisamos fazer tudo isso REFAZER. E assim fazemos a segunda série de experimentos.
Fazemos uma operação e vemos quando o ponto de verificação está prestes a ser concluído, matamos -9 Postgres de propósito.
E depois disso a gente começa de novo, e vê quanto tempo vai subir nesse equipamento, ou seja, quanto vai REFAZER nessa situação ruim.
Duas vezes observarei que a situação é ruim. Primeiro, caímos logo antes do checkpoint terminar, então temos muito a perder. E em segundo lugar, tivemos uma operação massiva. E se os pontos de verificação estivessem no tempo limite, provavelmente, menos WAL seria gerado desde o último ponto de verificação. Ou seja, é um perdedor duplo.
Medimos essa situação para tamanhos diferentes de max_wal_size e entendemos que, se max_wal_size for 64 gigabytes, então, no pior caso duplo, subiremos por 10 minutos. E pensamos se nos convém ou não. Esta é uma questão de negócios. Precisamos mostrar essa imagem para os responsáveis pelas decisões de negócios e perguntar: “Quanto tempo podemos ficar deitados no máximo em caso de problema? Podemos deitar na pior situação por 3-5 minutos? E você toma uma decisão.
E aqui está um ponto interessante. Temos alguns relatórios sobre Patroni na conferência. E talvez você esteja usando. Este é um failover automático para Postgres. GitLab e Data Egret conversaram sobre isso.
E se você tiver um failover automático que ocorre em 30 segundos, talvez possamos nos deitar por 10 minutos? Porque mudaremos para a réplica neste ponto e tudo ficará bem. Este é um ponto discutível. Não sei uma resposta clara. Eu apenas sinto que este tópico não é apenas sobre recuperação de falhas.
Se tivermos uma longa recuperação após uma falha, ficaremos desconfortáveis em muitas outras situações. Por exemplo, nos mesmos experimentos, quando fazemos algo e às vezes temos que esperar 10 minutos.
Eu ainda não iria muito longe, mesmo se tivéssemos um failover automático. Via de regra, valores como 64, 100 gigabytes são bons valores. Às vezes vale até a pena escolher menos. Em geral, esta é uma ciência sutil.
Para fazer iterações, por exemplo, max_wal_size =1, 8, você precisa repetir a operação em massa várias vezes. Você conseguiu. E na mesma base você quer fazer de novo, mas já deletou tudo. O que fazer?
Falarei mais tarde sobre nossa solução, o que fazemos para iterar nessas situações. E esta é a abordagem mais correta.
Mas, neste caso, tivemos sorte. Se, como diz aqui "BEGIN, DELETE, ROLLBACK", podemos repetir DELETE. Ou seja, se nós mesmos cancelamos, podemos repeti-lo. E fisicamente em você os dados estarão no mesmo lugar. Você nem fica com nenhum inchaço. Você pode iterar sobre tais DELETEs.
Este DELETE com ROLLBACK é ideal para ajuste de ponto de verificação, mesmo se você não tiver laboratórios de banco de dados devidamente implantados.
Fizemos uma placa com uma coluna "i". Postgres tem colunas utilitárias. Eles são invisíveis, a menos que especificamente solicitados. São eles: ctid, xmid, xmax.
Ctid é um endereço físico. Página zero, a primeira tupla na página.
Pode-se observar que após o ROOLBACK a tupla permaneceu no mesmo lugar. Ou seja, podemos tentar de novo, vai se comportar da mesma forma. Esta é a principal coisa.
Xmax é o tempo de morte da tupla. Foi carimbado, mas o Postgres sabe que a transação foi revertida, então não importa se é 0 ou é uma transação revertida. Isso sugere que é possível iterar DELETE e verificar as operações em massa do comportamento do sistema. Você pode fazer laboratórios de banco de dados para os pobres.
Isso é sobre programadores. Também sobre o DBA, eles sempre repreendem os programadores por isso: “Por que você está fazendo operações tão longas e difíceis?”. Este é um tópico perpendicular completamente diferente. Costumava haver administração e agora haverá desenvolvimento.
Obviamente, não quebramos em pedaços. Está claro. É impossível não quebrar tal DELETE para um monte de milhões de linhas em partes. Isso será feito por 20 minutos e tudo ficará parado. Mas, infelizmente, mesmo desenvolvedores experientes cometem erros, mesmo em empresas muito grandes.
Por que é importante quebrar?
-
Se percebermos que o disco está rígido, vamos desacelerá-lo. E se estivermos quebrados, podemos adicionar pausas, podemos desacelerar o estrangulamento.
-
E não vamos bloquear os outros por muito tempo. Em alguns casos, não importa, se você estiver excluindo lixo real no qual ninguém está trabalhando, provavelmente não bloqueará ninguém, exceto o trabalho de autovacuum, porque ele aguardará a conclusão da transação. Mas se você remover algo que outra pessoa pode solicitar, eles serão bloqueados, haverá algum tipo de reação em cadeia. Transações longas devem ser evitadas em sites e aplicativos móveis.
Isto é interessante. Costumo ver que os desenvolvedores perguntam: "Qual tamanho de pacote devo escolher?".
É claro que quanto maior o tamanho do pacote, menor o overhead da transação, ou seja, o overhead adicional das transações. Mas, ao mesmo tempo, o tempo aumenta para essa transação.
Eu tenho uma regra muito simples: pegue o máximo que puder, mas não exagere nos executáveis por segundo.
Por que um segundo? A explicação é muito simples e compreensível para todos, mesmo para pessoas não técnicas. Vemos uma reação. Vamos pegar 50 milissegundos. Se algo mudou, nosso olho reagirá. Se menos, então mais difícil. Se algo responder após 100 milissegundos, por exemplo, você clicou com o mouse e ele respondeu após 100 milissegundos, você já sente esse pequeno atraso. Um segundo já é percebido como freios.
Conseqüentemente, se dividirmos nossas operações em massa em rajadas de 10 segundos, corremos o risco de bloquear alguém. E vai funcionar por alguns segundos, e as pessoas já vão perceber. Portanto, prefiro não fazer mais do que um segundo. Mas, ao mesmo tempo, não o divida muito finamente, porque a sobrecarga da transação será perceptível. A base ficará mais dura, podendo surgir outros problemas diversos.
Nós escolhemos o tamanho do pacote. Em cada caso, podemos fazer isso de maneira diferente. Pode ser automatizado. E estamos convencidos da eficiência do processamento de um pacote. Ou seja, fazemos DELETE de um pack ou UPDATE.
A propósito, tudo o que estou falando não é apenas sobre DELETE. Como você adivinhou, essas são quaisquer operações em massa nos dados.
E vemos que o plano é excelente. Você pode ver a varredura de índice, a varredura somente de índice é ainda melhor. E temos uma pequena quantidade de dados envolvidos. E menos de um segundo cumpre. Super.
E ainda precisamos garantir que não haja degradação. Acontece que os primeiros pacotes funcionam rapidamente e depois fica cada vez pior. O processo é tal que você precisa testar muito. É exatamente para isso que servem os laboratórios de banco de dados.
E ainda temos que preparar algo para que nos permita seguir isso direitinho na produção. Por exemplo, podemos escrever a hora no log, podemos escrever onde estamos agora e quem já excluímos. E isso nos permitirá entender o que está acontecendo mais tarde. E caso algo dê errado, encontre rapidamente o problema.
Se precisarmos verificar a eficiência das solicitações e precisarmos iterar muitas vezes, existe um bot companheiro. Ele já está pronto. É usado por dezenas de desenvolvedores diariamente. E ele sabe como fornecer um enorme banco de dados de terabytes a pedido em 30 segundos, sua própria cópia. E você pode excluir algo lá e dizer RESET e excluí-lo novamente. Você pode experimentar desta forma. Eu vejo um futuro para isso. E já estamos fazendo isso.
O que são estratégias de particionamento? Vejo 3 estratégias de particionamento diferentes que os desenvolvedores do pacote estão usando.
O primeiro é muito simples. Temos um ID numérico. E vamos dividi-lo em diferentes intervalos e trabalhar com isso. A desvantagem é clara. No primeiro segmento, podemos ter 100 linhas de lixo real, no segundo 5 linhas ou nenhuma, ou todas as 1 linhas serão lixo. Trabalho muito irregular, mas é fácil de quebrar. Eles pegaram o máximo de identificação e o quebraram. Esta é uma abordagem ingênua.
A segunda estratégia é uma abordagem equilibrada. É usado no Gitlab. Eles pegaram e examinaram a mesa. Encontramos os limites dos pacotes de ID para que cada pacote tivesse exatamente 10 registros. E colocá-los em uma fila. E então nós processamos. Você pode fazer isso em vários segmentos.
Aliás, na primeira estratégia também, você pode fazer isso em vários threads. Não é difícil.
Mas há uma abordagem mais legal e melhor. Esta é a terceira estratégia. E quando possível, é melhor escolher. Fazemos isso com base em um índice especial. Nesse caso, provavelmente será um índice de acordo com nossa condição de lixo e ID. Incluiremos o ID para que seja uma varredura apenas de índice para que não vámos para o heap.
Geralmente, a varredura somente de índice é mais rápida que a varredura de índice.
E rapidamente encontramos nossos IDs que queremos remover. BATCH_SIZE que selecionamos com antecedência. E não apenas os pegamos, como os pegamos de uma maneira especial e os hackeamos imediatamente. Mas estamos travando para que, se já estiverem trancados, não os bloqueamos, mas seguimos em frente e pegamos os próximos. Isto é para atualização bloqueada. Essa super funcionalidade do Postgres nos permite trabalhar em várias threads se quisermos. É possível em um fluxo. E aqui está um CTE - este é um pedido. E temos uma exclusão real acontecendo no segundo andar deste CTE - returning *. Você pode retornar o id, mas é melhor *se você não tiver muitos dados em cada linha.
Por que precisamos disso? Isso é o que precisamos relatar. Na verdade, agora excluímos tantas linhas. E temos bordas por ID ou por created_at assim. Você pode fazer min, max. Outra coisa pode ser feita. Você pode encher muito aqui. E é muito conveniente para monitoramento.
Há mais uma observação sobre o índice. Se decidirmos que precisamos de um índice especial para esta tarefa, precisamos garantir que ele não estrague apenas atualizações de tuplas de heap. Ou seja, o Postgres possui essas estatísticas. Isso pode ser visto em pg_stat_user_tables para sua tabela. Você pode ver se hot updates estão sendo usados ou não.
Há situações em que seu novo índice pode simplesmente cortá-los. E você tem todas as outras atualizações que já estão funcionando, desacelere. Não só porque o índice apareceu (cada índice desacelera um pouco as atualizações, mas um pouco), mas aqui ainda estraga. E é impossível fazer otimização especial para esta tabela. Isso acontece às vezes. Essa é uma sutileza que poucas pessoas se lembram. E este ancinho é fácil de pisar. Às vezes acontece que você precisa encontrar uma abordagem do outro lado e ainda ficar sem esse novo índice, ou fazer outro índice, ou de alguma outra forma, por exemplo, você pode usar o segundo método.
Mas esta é a estratégia mais ideal, como dividir em lotes e disparar em lotes com uma solicitação, deletar um pouco, etc.
Transações longas
Autovácuo bloqueado -
problema de bloqueio -
O erro nº 5 é grande. Nikolai da Okmeter falou sobre o monitoramento do Postgres. O monitoramento Postgres ideal, infelizmente, não existe. Alguns estão mais próximos, outros estão mais distantes. Okmeter está perto o suficiente de ser perfeito, mas falta muito e precisa ser adicionado. Você precisa estar pronto para isso.
Por exemplo, tuplas mortas são melhor monitoradas. Se você tem muitas coisas mortas na mesa, algo está errado. É melhor reagir agora, senão pode haver degradação e podemos deitar. Acontece.
Se houver um grande IO, fica claro que isso não é bom.
Transações longas também. Transações longas não devem ser permitidas em OLTP. E aqui está um link para um snippet que permite que você pegue esse snippet e já faça algum rastreamento de transações longas.
Por que transações longas são ruins? Porque todos os bloqueios serão liberados apenas no final. E ferramos com todo mundo. Além disso, bloqueamos o autovacuum para todas as mesas. Não é nada bom. Mesmo se você tiver o hot standby ativado na réplica, ainda é ruim. Em geral, em nenhum lugar é melhor evitar transações longas.
Se tivermos muitas tabelas que não foram limpas, precisamos ter um alerta. Aqui tal situação é possível. Podemos afetar indiretamente a operação do autovacuum. Este é um trecho do Avito, que melhorei um pouco. E acabou por ser uma ferramenta interessante para ver o que temos com o autovacuum. Por exemplo, algumas mesas estão esperando lá e não vão esperar a sua vez. Você também precisa colocá-lo em monitoramento e ter um alerta.
E emite blocos. Floresta de árvores de bloco. Eu gosto de pegar algo de alguém e melhorá-lo. Aqui eu peguei um CTE recursivo legal do Data Egret que mostra uma floresta de árvores de bloqueio. Esta é uma boa ferramenta de diagnóstico. E com base nisso, você também pode criar monitoramento. Mas isso deve ser feito com cuidado. Você precisa fazer um pequeno statement_timeout para si mesmo. E lock_timeout é desejável.
Às vezes, todos esses erros ocorrem em soma.
Na minha opinião, o principal erro aqui é organizacional. É organizacional, porque a técnica não puxa. Este é o número 2 - eles verificaram no lugar errado.
Verificamos no lugar errado, porque não tínhamos um clone de produção, o que é fácil de verificar. Um desenvolvedor pode não ter acesso à produção.
E não verificamos lá. Se tivéssemos checado lá, teríamos visto nós mesmos. O desenvolvedor via tudo mesmo sem DBA se verificasse em um bom ambiente, onde houvesse a mesma quantidade de dados e localização idêntica. Ele teria visto toda essa degradação e ficaria envergonhado.
Mais sobre autovácuo. Depois de fazermos uma varredura massiva de vários milhões de linhas, ainda precisamos fazer o REPACK. Isso é especialmente importante para índices. Eles vão se sentir mal depois que limparmos tudo lá.
E se você quiser trazer de volta o trabalho diário de limpeza, sugiro fazê-lo com mais frequência, mas em menor quantidade. Pode ser uma vez por minuto ou até mais frequentemente um pouco. E você precisa monitorar duas coisas: que essa coisa não tenha erros e que não fique para trás. O truque que mostrei resolverá isso.
O que fazemos é de código aberto. Está postado no GitLab. E fazemos com que as pessoas possam verificar mesmo sem um DBA. Estamos fazendo um laboratório de banco de dados, ou seja, chamamos o componente base no qual Joe está trabalhando atualmente. E você pode pegar uma cópia da produção. Agora existe uma implementação do Joe para slack, você pode dizer lá: “explique tal e tal solicitação” e obtenha imediatamente o resultado para sua cópia do banco de dados. Você pode até EXCLUIR lá e ninguém notará.
Digamos que você tenha 10 terabytes, fazemos laboratório de banco de dados também 10 terabytes. E com bancos de dados simultâneos de 10 terabytes, 10 desenvolvedores podem trabalhar simultaneamente. Cada um pode fazer o que quiser. Pode excluir, descartar, etc. Isso é uma fantasia. Falaremos sobre isso amanhã.
Isso é chamado de provisionamento thin. Isso é provisionamento sutil. Isso é algum tipo de fantasia que remove muito os atrasos no desenvolvimento, nos testes e torna o mundo um lugar melhor nesse aspecto. Ou seja, apenas permite evitar problemas com operações em massa.
Exemplo: banco de dados de 5 terabytes, obtendo uma cópia em menos de 30 segundos. E nem depende do tamanho, ou seja, não importa quantos terabytes.
Hoje você pode ir e explore nossas ferramentas. Você pode se registrar para ver o que está lá. Você pode instalar este bot. É grátis. Escrever.
perguntas
Muitas vezes, em situações reais, verifica-se que os dados que devem permanecer na tabela são muito menores do que os que precisam ser excluídos. Ou seja, em tal situação, muitas vezes é mais fácil implementar tal abordagem, quando é mais fácil criar um novo objeto, copiar apenas os dados necessários para lá e agrupar a tabela antiga. É claro que uma abordagem programática é necessária para este momento, enquanto você estará trocando. Como é essa abordagem?
Esta é uma abordagem muito boa e uma tarefa muito boa. É muito parecido com o que o pg_repack faz, é muito parecido com o que você tem que fazer quando cria IDs de 4 bytes. Muitos frameworks fizeram isso há alguns anos, e apenas as placas cresceram e precisam ser convertidas para 8 bytes.
Esta tarefa é bastante difícil. Conseguimos. E você tem que ter muito cuidado. Existem fechaduras, etc. Mas está sendo feito. Ou seja, a abordagem padrão é usar pg_repack. Você declara tal rótulo. E antes de começar a carregar os dados do instantâneo nele, você também declara uma placa que rastreia todas as alterações. Existe um truque que você pode nem acompanhar algumas alterações. Existem sutilezas. E então você muda rolando mudanças. Haverá uma pequena pausa quando desligarmos todos, mas em geral isso está sendo feito.
Se você olhar pg_repack no GitHub, então, quando havia uma tarefa para converter um ID de int 4 para int 8, surgiu a ideia de usar o próprio pg_repack. Isso também é possível, mas é um truque, mas também funcionará para isso. Você pode intervir no gatilho que o pg_repack usa e dizer lá: "Não precisamos desses dados", ou seja, só transferimos o que precisamos. E então ele simplesmente muda e é isso.
Com essa abordagem, ainda obtemos uma segunda cópia da tabela, na qual os dados já estão indexados e empilhados de maneira muito uniforme com belos índices.
Bloat não está presente, é uma boa abordagem. Mas sei que existem tentativas de desenvolver uma automação para isso, ou seja, fazer uma solução universal. Posso colocar você em contato com essa automação. Está escrito em Python, o que é bom.
Eu sou um pouco do mundo do MySQL, então vim ouvir. E nós usamos essa abordagem.
Mas é só se tivermos 90%. Se tivermos 5%, não é muito bom usá-lo.
Obrigado pelo relatório! Se não houver recursos para fazer uma cópia completa do prod, existe algum algoritmo ou fórmula para calcular a carga ou tamanho?
Boa pergunta. Até agora, conseguimos encontrar bancos de dados de vários terabytes. Mesmo que o hardware não seja o mesmo, por exemplo, menos memória, menos processador e discos não são exatamente os mesmos, mas ainda assim fazemos. Se não houver absolutamente nenhum lugar, você precisa pensar. Deixa eu pensar até amanhã, você veio, vamos conversar, essa é uma boa pergunta.
Obrigado pelo relatório! Você começou com o fato de que existe um Postgres legal, que tem tais e tais limitações, mas está se desenvolvendo. E tudo isso é uma muleta em geral. Isso tudo não está em conflito com o desenvolvimento do próprio Postgres, no qual algum deferente DELETE aparecerá ou algo mais que deve manter em um nível baixo o que estamos tentando manchar com alguns de nossos meios estranhos aqui?
Se dissermos no SQL para excluir ou atualizar muitos registros em uma transação, como o Postgres pode distribuí-lo lá? Estamos fisicamente limitados nas operações. Ainda o faremos por muito tempo. E vamos bloquear neste momento, etc.
Feito com índices.
Posso assumir que o mesmo ajuste de ponto de verificação pode ser automatizado. Algum dia pode ser. Mas então eu realmente não entendo a pergunta.
A questão é: existe um vetor de desenvolvimento que vai aqui e ali, e aqui o seu vai paralelo? Aqueles. Eles ainda não pensaram nisso?
Falei sobre os princípios que podem ser usados agora. tem outro bot , com isso você pode fazer o ajuste automatizado do ponto de verificação. Algum dia estará no Postgres? Não sei, ainda nem foi discutido. Ainda estamos longe disso. Mas há cientistas que fazem novos sistemas. E eles nos empurram para índices automáticos. Existem desenvolvimentos. Por exemplo, você pode ver o ajuste automático. Ele seleciona os parâmetros automaticamente. Mas ele não fará o ajuste do ponto de verificação para você ainda. Ou seja, ele irá buscar desempenho, buffer de shell, etc.
E para o ajuste do ponto de verificação, você pode fazer isso: se você tiver mil clusters e diferentes hardwares, diferentes máquinas virtuais na nuvem, pode usar nosso bot fazer automação. E max_wal_size será selecionado de acordo com suas configurações de destino automaticamente. Mas até agora isso não está nem perto do núcleo, infelizmente.
Boa tarde Você falou sobre os perigos de transações longas. Você disse que o autovacuum é bloqueado em caso de exclusões. De que outra forma isso nos prejudica? Porque estamos falando mais de liberar espaço e poder utilizá-lo. O que mais estamos perdendo?
Autovacuum talvez não seja o maior problema aqui. E o fato de uma transação longa poder bloquear outras transações, essa possibilidade é mais perigosa. Ela pode ou não se encontrar. Se ela conheceu, então pode ser muito ruim. E com autovacuum - isso também é um problema. Existem dois problemas com transações longas em OLTP: bloqueios e autovacuum. E se você tiver o feedback de espera ativa ativado na réplica, ainda receberá um bloqueio de vácuo automático no mestre, que chegará da réplica. Mas pelo menos não haverá fechaduras. E haverá loks. Estamos falando de alterações de dados, então os bloqueios são um ponto importante aqui. E se isso durar muito, muito tempo, mais e mais transações serão bloqueadas. Eles podem roubar outros. E as árvores lok aparecem. Forneci um link para o trecho. E esse problema se torna mais perceptível mais rápido do que o problema do autovacuum, que só pode se acumular.
Obrigado pelo relatório! Você começou seu relatório dizendo que testou incorretamente. Continuamos nossa ideia de que precisamos levar os mesmos equipamentos, com a base da mesma forma. Digamos que demos uma base ao desenvolvedor. E ele atendeu ao pedido. E ele parece estar bem. Mas ele não verifica ao vivo, mas ao vivo, por exemplo, temos uma carga de 60-70%. E mesmo que usemos essa afinação, ela não funciona muito bem.
Ter um especialista na equipe e usar especialistas em DBA que possam prever o que acontecerá com uma carga real em segundo plano é importante. Quando acabamos de conduzir nossas mudanças limpas, vemos a imagem. Mas uma abordagem mais avançada, quando fizemos a mesma coisa novamente, mas com uma carga simulada com produção. É muito legal. Até então, você tem que crescer. É como um adulto. Acabamos de ver o que temos e também se temos recursos suficientes. Esta é uma boa pergunta.
Quando já estamos fazendo uma seleção de lixo e temos, por exemplo, um flag deletado
Isso é o que o autovacuum faz automaticamente no Postgres.
Ah, ele faz isso?
Autovacuum é o coletor de lixo.
Obrigado!
Obrigado pelo relatório! Existe uma opção para projetar imediatamente um banco de dados com particionamento de forma que todo o lixo fique sujo da tabela principal em algum lugar ao lado?
Claro que existe.
É possível então nos proteger se tivermos bloqueado uma mesa que não deveria ser usada?
Claro que tem. Mas é como uma questão do ovo e da galinha. Se todos soubermos o que acontecerá no futuro, é claro que faremos tudo bem. Mas o negócio está mudando, há novas colunas, novos pedidos. E então - oops, queremos removê-lo. Mas essa situação ideal, na vida ocorre, mas nem sempre. Mas no geral é uma boa ideia. Basta truncar e pronto.
Fonte: habr.com