Gráficos, relatórios e análises - tudo isso está de alguma forma presente no back-office de qualquer empresa, mesmo que muito pequena. Quando as tabelas usuais no Excel / Numbers / Libre ficam lotadas, mas os dados ainda não são muito grandes, as soluções tradicionais para as necessidades internas da empresa geralmente são criadas usando bancos de dados relacionais como PostgreSQL, MySQL ou MariaDB.
Esses bancos de dados são gratuitos, graças ao SQL, eles se integram convenientemente a outros componentes do sistema, são populares e a maioria dos desenvolvedores e analistas pode trabalhar com eles. A carga (tráfego e volumes) que eles conseguem digerir é volumosa o suficiente para aguentar com calma até que a empresa possa oferecer soluções mais complexas (e caras) para análises e relatórios.
Posição inicial
No entanto, mesmo em uma tecnologia que tem sido repetidamente estudada, sempre existem nuances diferentes que podem subitamente aumentar as preocupações dos engenheiros. Além da confiabilidade, o problema mais freqüentemente mencionado nos bancos de dados é o desempenho deles. Obviamente, com um aumento na quantidade de dados, a taxa de resposta do banco de dados diminui, mas se isso acontecer de forma previsível e for consistente com o aumento da carga, isso não será tão ruim. Você sempre pode ver com antecedência quando o banco de dados começa a exigir atenção e planejar uma atualização ou transição para um banco de dados fundamentalmente diferente. Muito pior se o desempenho do banco de dados diminuir de forma imprevisível.
O tópico de melhorar o desempenho do banco de dados é tão antigo quanto o mundo e muito extenso, e neste artigo eu gostaria de focar em apenas uma direção. Ou seja, na avaliação da eficácia dos planos de consulta em um banco de dados PostgreSQL, bem como na alteração dessa eficiência ao longo do tempo para tornar o comportamento do planejador de banco de dados mais previsível.
Apesar do fato de que muitas das coisas que serão discutidas são aplicáveis a todas as versões recentes desse banco de dados, os exemplos abaixo significam a versão 11.2, a última no momento.
Antes de nos aprofundarmos nos detalhes, faz sentido discordar e dizer algumas palavras sobre a origem dos problemas de desempenho nos bancos de dados relacionais. Com o que exatamente o banco de dados está ocupado quando "desacelera"? Falta de memória (um grande número de acessos ao disco ou à rede), um processador fraco, todos esses são problemas óbvios com soluções claras, mas o que mais pode afetar a velocidade de execução da consulta?
Refresque memórias
Para que o banco de dados responda à consulta SQL, ele precisa criar um plano de consulta (em quais tabelas e colunas para ver quais índices são necessários, o que escolher a partir daí, o que comparar, com que quantidade de memória é necessária etc.). Esse plano é formado na forma de uma árvore, cujos nós são apenas algumas operações típicas, com diferentes complexidades computacionais. Aqui estão alguns deles, por exemplo (N é o número de linhas com as quais executar a operação):
| Operação | O que é feito | Custo |
|---|
| SELECIONE ... ONDE ... operações de busca de dados |
| Análise Seq | Carregamos cada linha da tabela e verificamos a condição. | O (N) |
Varredura de índice
(índice da árvore b) | Os dados estão diretamente no índice, portanto, pesquisamos por condição os elementos necessários do índice e coletamos os dados a partir daí. | O (log (N)), procure por um elemento em uma árvore classificada. |
Varredura de índice
(índice de hash) | Os dados estão diretamente no índice, portanto, pesquisamos por condição os elementos necessários do índice e coletamos os dados a partir daí. | O (1), procurando um item em uma tabela de hash, excluindo o custo de criação de hashes |
| Verificação de heap de bitmap | Selecionamos os números das linhas necessárias por índice, depois carregamos apenas as linhas necessárias e executamos verificações adicionais com elas. | Varredura de Índice + Varredura Seq (M),
Onde M é o número de linhas encontradas após a verificação do índice. Supõe-se que M << N, isto é, O índice é mais útil que o Seq Scan. |
| Operações de junção (JOIN, SELECT de várias tabelas) |
| Loop aninhado | Para cada linha da tabela esquerda, procure uma linha adequada na tabela direita. | O (N2).
Mas se uma das tabelas for muito menor que a outra (dicionário) e praticamente não aumentar com o tempo, o custo real poderá diminuir para O (N). |
| Hash join | Para cada linha das tabelas esquerda e direita, consideramos o hash, o que reduz o número de pesquisas de possíveis opções de conexão. | O (N), mas no caso de uma função hash muito ineficiente ou um grande número de campos idênticos para a conexão, pode haver O (N 2 ) |
| Mesclar junção | Por condição, classificamos as tabelas esquerda e direita, após as quais combinamos as duas listas classificadas | O (N * log (N))
Classificação de custos + passando pela lista. |
| Operações de agregação (GROUP BY, DISTINCT) |
| Agregado de grupo | Classificamos a tabela de acordo com a condição de agregação e, na lista classificada, agrupamos as linhas adjacentes. | O (N * log (N)) |
| Agregado de hash | Consideramos o hash para a condição de agregação para cada linha. Para linhas com o mesmo hash, realizamos agregação. | O (N) |
Como você pode ver, o custo de uma consulta depende muito de como os dados estão localizados nas tabelas e de como essa ordem corresponde às operações de hash usadas. O loop aninhado, apesar do custo em O (N
2 ), pode ser mais rentável que a junção de hash ou a junção de mesclagem quando uma das tabelas unidas degenera em uma ou várias linhas.
Além dos recursos da CPU, o custo também inclui o uso de memória. Como são recursos limitados, o planejador de consultas precisa encontrar um compromisso. Se duas tabelas são matematicamente mais lucrativas para se conectar via Hash Join, mas simplesmente não há espaço para uma tabela de hash tão grande na memória, o banco de dados pode ser forçado a usar o Merge Join, por exemplo. Um loop aninhado "lento" geralmente não requer memória adicional e está pronto para produzir resultados logo após o lançamento.
O custo relativo dessas operações é mostrado mais claramente no gráfico. Estes não são números absolutos, apenas uma proporção aproximada de diferentes operações.
O gráfico de loop aninhado "começa" abaixo, porque não requer cálculos adicionais ou alocação de memória ou cópia de dados intermediários, mas possui um custo de O (N
2 ). A junção de mesclagem e a junção de hash têm custos iniciais mais altos; no entanto, após alguns valores de N, eles começam a superar o loop aninhado a tempo. O planejador tenta escolher o plano com o menor custo e, no gráfico acima, adere a diferentes operações com N diferente (seta tracejada verde). Com o número de linhas até N1, é mais lucrativo usar o Nested Loop; de N1 para N2 é mais lucrativo mesclar Join, depois que N2 se torna mais rentável para Hash Join, no entanto, o Hash Join exige memória para criar tabelas de hash. E ao atingir N3, essa memória se torna insuficiente, o que leva ao uso forçado do Merge Join.
Ao escolher um plano, o planejador estima o custo de cada operação no plano usando um conjunto de custos relativos de algumas operações "atômicas" no banco de dados. Como, por exemplo, cálculos, comparações, carregamento de uma página na memória, etc. Aqui está uma lista de alguns desses parâmetros da configuração padrão, não há muitos:
| Constante de custo relativo | Valor padrão |
|---|
| seq_page_cost | 1.0 |
| random_page_cost | 4.0 |
| cpu_tuple_cost | 0,01 |
| cpu_index_tuple_cost | 0,005 |
| cpu_operator_cost | 0,0025 |
| parallel_tuple_cost | 0,1 |
| parallel_setup_cost | 1000,0 |
É verdade que apenas essas constantes são poucas, você ainda precisa saber o "N", ou seja, exatamente quantas linhas dos resultados anteriores terão que ser processadas em cada uma dessas operações. O limite superior é óbvio aqui - o banco de dados "sabe" quantos dados há em qualquer tabela e sempre pode calcular "ao máximo". Por exemplo, se você tiver duas tabelas de 100 linhas cada, a união delas poderá produzir de 0 a 10.000 linhas na saída. Assim, a próxima operação de entrada pode ter até 10.000 linhas.
Mas se você souber pelo menos um pouco sobre a natureza dos dados nas tabelas, esse número de linhas poderá ser previsto com mais precisão. Por exemplo, para duas tabelas de 100 linhas do exemplo acima, se você souber com antecedência que a junção não produzirá 10 mil linhas, mas as mesmas 100, o custo estimado da próxima operação será bastante reduzido. Nesse caso, esse plano poderia ser mais eficaz que outros.
Otimização imediata
Para que o planejador possa prever com mais precisão o tamanho dos resultados intermediários, o PostgreSQL usa a coleção de estatísticas em tabelas, que são acumuladas em pg_statistic ou em sua versão mais legível - em pg_stats. Ele é atualizado automaticamente quando o vácuo é iniciado ou explicitamente com o comando ANALYZE. Esta tabela armazena uma variedade de informações sobre quais dados e que tipo de natureza estão nas tabelas. Em particular, histogramas de valores, porcentagem de campos vazios e outras informações. O planejador usa tudo isso para prever com mais precisão a quantidade de dados para cada operação na árvore do plano e, assim, calcular com mais precisão o custo das operações e o plano como um todo.
Tome por exemplo a consulta:
SELECT t1.important_value FROM t1 WHERE t1.a > 100
Suponha que o histograma dos valores na coluna “t1.a” tenha revelado que valores maiores que 100 são encontrados em aproximadamente 1% das linhas da tabela. Então podemos prever que essa amostra retornará cerca de um centésimo de todas as linhas da tabela "t1".
O banco de dados oferece a oportunidade de analisar o custo previsto do plano por meio do comando EXPLAIN e o tempo real de sua operação - usando EXPLAIN ANALYZE.
Parece que, com as estatísticas automáticas, tudo deve ficar bem agora, mas pode haver dificuldades. Há um
bom artigo sobre isso
no Citus Data , com um exemplo da ineficiência de estatísticas automáticas e a coleta de estatísticas adicionais usando CREATE STATISTICS (disponível no PG 10.0).
Portanto, para o planejador, existem duas fontes de erros no cálculo de custos:
- O custo relativo das operações primitivas (seq_page_cost, cpu_operator_cost e assim por diante) por padrão pode ser muito diferente da realidade (custo da cpu 0,01, custo de carregamento da página srq - 1 ou 4 para carregamento aleatório da página). Longe do fato de que 100 comparações serão iguais a 1 carregamento de página.
- Erro ao prever o número de linhas em operações intermediárias. O custo real da operação, neste caso, pode ser muito diferente da previsão.
Em consultas complexas, a elaboração e a previsão de todos os planos possíveis podem demorar bastante tempo. Para que serve retornar dados em 1 segundo se o banco de dados estava planejando apenas uma solicitação minuciosa? O PostgreSQL possui um otimizador Geqo para essa situação, é um agendador que não cria todas as opções possíveis para os planos, mas começa com algumas aleatórias e completa as melhores, prevendo maneiras de reduzir custos. Tudo isso também não melhora a precisão da previsão, embora acelere a busca de pelo menos algum plano mais ou menos ideal.
Planos repentinos - concorrentes
Se tudo correr bem, sua solicitação será atendida o mais rápido possível. À medida que a quantidade de dados aumenta, a velocidade de execução da consulta no banco de dados aumenta gradualmente e, após algum tempo, observando-o, você pode prever aproximadamente quando será necessário aumentar a memória ou o número de núcleos da CPU ou expandir o cluster, etc.
Mas devemos levar em conta o fato de que o plano ideal possui concorrentes com custos de execução próximos, o que não vemos. E se o banco de dados mudar repentinamente o plano de consulta para outro, isso é uma surpresa. É bom se o banco de dados pular para um plano mais eficiente. E se não? Vejamos a foto, por exemplo. Esse é o custo previsto e o tempo real da implementação de dois planos (vermelho e verde):
Aqui, um plano é mostrado em verde e o seu "concorrente" mais próximo em vermelho. A linha pontilhada mostra um gráfico dos custos projetados, a linha sólida é o tempo real. A seta tracejada cinza mostra a seleção do planejador.
Suponha que em uma boa sexta à noite o número previsto de linhas em alguma operação intermediária chegue a N1 e a previsão "vermelha" comece a superar a "verde". O planejador começa a usá-lo. O tempo real de execução da consulta aumenta imediatamente (alternando de uma linha sólida verde para uma vermelha), ou seja, o cronograma de degradação do banco de dados assume a forma de uma etapa (ou talvez uma "parede"). Na prática, esse “muro” pode aumentar o tempo de execução da consulta em uma ordem de magnitude ou mais.
Vale ressaltar que essa situação é provavelmente mais típica para o back office e as análises do que para o front-end, uma vez que o último geralmente é adaptado para consultas mais simultâneas e, portanto, utiliza consultas mais simples no banco de dados, onde o erro nas previsões do plano é menor. Se este for um banco de dados para relatórios ou análises, as consultas podem ser arbitrariamente complexas.
Como viver com isso?
Surge a questão: era possível prever, de alguma maneira, planos invisíveis "subaquáticos"? Afinal, o problema não é que eles não são ótimos, mas que mudar para outro plano pode ocorrer imprevisivelmente e, de acordo com a lei da maldade, no momento mais infeliz para isso.
Infelizmente, você não pode vê-los diretamente, mas pode procurar planos alternativos alterando os pesos reais pelos quais eles são selecionados. O significado dessa abordagem é remover de vista o plano atual, que o planejador considera ideal, para que um de seus concorrentes mais próximos se torne ideal e, portanto, ele possa ser visto através da equipe EXPLAIN. Periodicamente, verificando mudanças nos custos desses "concorrentes" e no plano principal, é possível avaliar a probabilidade de que o banco de dados em breve "salte" para outro plano.
Além de coletar dados sobre previsões de planos alternativos, você pode executá-los e medir seu desempenho, o que também fornece uma idéia do "bem-estar" interno do banco de dados.
Vamos ver quais ferramentas temos para esses experimentos.
Primeiro, você pode "proibir" explicitamente operações específicas usando variáveis de sessão. Convenientemente, eles não precisam ser alterados na configuração e o banco de dados é recarregado, seus valores mudam apenas na sessão aberta atual e não afetam outras sessões, para que você possa experimentar diretamente com dados reais. Aqui está uma lista deles com valores padrão. Quase todas as operações estão incluídas:
| Operações Utilizadas | Valor padrão |
|---|
enable_bitmapscan
enable_hashagg
enable_hashjoin
enable_indexscan
enable_indexonlyscan
enable_material
enable_mergejoin
enable_nestloop
enable_parallel_append
enable_seqscan
enable_sort
enable_tidscan
enable_parallel_hash
enable_partition_pruning | em |
enable_partitionwise_join
enable_partitionwise_aggregate | fora |
Proibindo ou permitindo determinadas operações, forçamos o planejador a selecionar outros planos que podemos ver com o mesmo comando EXPLAIN. De fato, a “proibição” de operações não proíbe seu uso, mas simplesmente aumenta muito seu custo. No PostgreSQL, cada operação "proibida" acumula automaticamente um custo igual a 10 bilhões de unidades convencionais. Além disso, no EXPLAIN, o peso total do plano pode ser proibitivamente alto, mas no contexto dessas dezenas de bilhões, o peso das operações restantes é claramente visível, pois geralmente se encaixa em pedidos menores.
De particular interesse são duas das seguintes operações:
- Hash Join. Sua complexidade é O (N), mas com um erro com uma previsão na quantidade do resultado, você não pode caber na memória e precisará fazer a junção de mesclagem, com um custo de O (N * log (N)).
- Loop aninhado. Sua complexidade é O (N 2 ), portanto, o erro na previsão de tamanho afeta quadraticamente a velocidade dessa conexão.
Por exemplo, vamos pegar alguns números reais de consultas, cuja otimização estávamos envolvidos em nossa empresa.
Plano 1. Com todas as operações permitidas, o custo total do plano mais ideal foi de 274962,09 unidades.
Plano 2. Com o loop aninhado "proibido", o custo aumentou para 40000534153.85. Esses 40 bilhões que compõem a maior parte do custo são 4 vezes o Nested Loop usado, apesar da proibição. E o restante 534153,85 - essa é precisamente a previsão do custo de todas as outras operações no plano. Como vemos, é cerca de duas vezes maior que o custo do plano ideal, ou seja, está próximo o suficiente.
Plano 3. Com a Hash Join "proibida", o custo foi de 383253,77. O plano foi realmente elaborado sem o uso da operação Hash Join, pois não vemos bilhões. Seu custo, no entanto, é 30% superior ao do ótimo, o que também é muito próximo.
Na realidade, os tempos de execução da consulta foram os seguintes:
Plano 1 (todas as operações permitidas) concluído em ~ 9 minutos.
O plano 2 (com o loop aninhado "proibido") foi concluído em 1,5 segundos.
O plano 3 (com uma junção de hash "proibido") foi concluído em ~ 5 minutos.
O motivo, como você pode ver, é a previsão incorreta do custo do Nested Loop. De fato, ao comparar EXPLAIN com EXPLAIN ANALYZE, um erro é detectado com a definição desse N malfadado na operação intermediária. Em vez de uma única linha prevista, o Nested Loop encontrou milhares de linhas, o que causou um aumento no tempo de execução da consulta em algumas ordens de magnitude.
A economia com o Hash Join "proibido" está associada à substituição do hash pela classificação e Merge Join, que funcionaram mais rápido nesse caso que o Hash Join. Observe que esse plano 2, na realidade, é quase duas vezes mais rápido que o plano "ideal" 1. Embora tenha sido previsto que será mais lento.
Na prática, se sua solicitação repentinamente (após uma atualização do banco de dados ou apenas por si só) começar a ser executada por muito mais tempo do que antes, tente primeiro negar a Hash Join ou o Nested Loop e veja como isso afeta a velocidade da consulta. Em um caso de sucesso, você poderá pelo menos banir um novo plano não ideal e retornar ao rápido anterior.
Para fazer isso, você não precisa alterar os arquivos de configuração do PostgreSQL com uma reinicialização do banco de dados; é simples em qualquer console alterar o valor da variável desejada para uma sessão aberta do banco de dados. As sessões restantes não serão afetadas, a configuração será alterada apenas para a sua sessão atual. Por exemplo, assim:
SET enable_hashjoin='on'; SET enable_nestloop='off'; SELECT … FROM … ( )
A segunda maneira de influenciar a escolha do plano é alterar os pesos das operações de baixo nível. Não existe uma receita universal aqui, mas, por exemplo, se você tiver um banco de dados com um cache "aquecido" e todos os dados forem armazenados na memória, é provável que o custo do carregamento seqüencial de páginas não seja diferente do custo do carregamento de uma página aleatória. Enquanto na configuração padrão, aleatório é 4 vezes mais caro que seqüencial.
Ou, outro exemplo, o custo condicional da execução do processamento paralelo é 1000 por padrão, enquanto o custo do carregamento de uma página é 1,0. Faz sentido começar alterando apenas um dos parâmetros por vez para determinar se isso afeta a escolha do plano. As maneiras mais fáceis são começar definindo o parâmetro como 0 ou com algum valor alto (1 milhão).
No entanto, lembre-se de que, ao melhorar o desempenho em uma solicitação, é possível degradá-lo em outra. Em geral, existe um amplo campo para experimentos. É melhor tentar alterá-los um de cada vez, um de cada vez.
Opções alternativas de tratamento
Uma história sobre um agendador seria incompleta sem mencionar pelo menos duas extensões do PostgreSQL.
O primeiro é
SR_PLAN , para salvar o plano calculado e forçar seu uso posterior. Isso ajuda a tornar o comportamento do banco de dados mais previsível em termos de opções de plano.
O segundo é o
Adaptive Query Optimizer , que implementa feedback para o planejador a partir da execução em tempo real da consulta, ou seja, o planejador mede os resultados reais da consulta executada e ajusta seus planos no futuro com isso em mente. O banco de dados é, portanto, "auto-ajustável" para dados e consultas específicas.
O que mais o banco de dados faz quando fica lento?
Agora que classificamos mais ou menos o planejamento da consulta, vamos ver o que mais pode ser aprimorado no próprio banco de dados e nos aplicativos que o utilizam para obter o máximo desempenho dele.
Suponha que o plano de consulta já seja ideal. Se excluirmos os problemas mais óbvios (pouca memória ou um disco / rede lento), ainda haverá custos para calcular os hashes. Provavelmente existem grandes oportunidades para futuras melhorias no PostgreSQL (usando a GPU ou mesmo as instruções SSE2 / SSE3 / AVX da CPU), mas até agora isso não foi feito e os cálculos de hash quase nunca usam os recursos de hardware do hardware. Você pode ajudar um pouco nesse banco de dados.
Se você notar, por padrão, os índices no PostgreSQL são criados como b-tree. Sua utilidade é que eles são bastante versáteis. Esse índice pode ser usado tanto com condições de igualdade quanto com condições de comparação (mais ou menos). Encontrar um item nesse índice é um custo logarítmico. Mas se sua consulta contiver apenas uma condição de igualdade, os índices também poderão ser criados como um índice de hash, cujo custo é constante.
Além disso, você ainda pode tentar modificar a solicitação para usar sua execução paralela. Para entender exatamente como reescrevê-lo, é melhor se familiarizar com a lista de casos em que o paralelismo é automaticamente proibido pelo agendador e evitar tais situações.
O manual deste tópico descreve brevemente todas as situações, portanto não faz sentido repeti-las aqui.
O que fazer se a solicitação ainda não for boa em paralelo? É muito triste ver como, em seu poderoso banco de dados com vários núcleos, onde você é o único cliente, um núcleo é 100% ocupado e todos os outros kernels apenas olham para ele. Nesse caso, você precisa ajudar o banco de dados na lateral do aplicativo. Como cada sessão possui um núcleo próprio, é possível abrir várias delas e dividir a consulta geral em partes, fazendo seleções mais curtas e rápidas, combinando-as em um resultado comum já no aplicativo. Isso ocupará o máximo de recursos disponíveis da CPU no banco de dados PostgreSQL.
Concluindo, gostaria de observar que as opções de diagnóstico e otimização acima são apenas a ponta do iceberg, no entanto, são bastante fáceis de usar e podem ajudar a identificar rapidamente o problema diretamente nos dados operacionais, sem arriscar prejudicar a configuração ou interromper a operação de outros aplicativos.
Consultas bem-sucedidas, com planos precisos e breves.