Como crescer 10 vezes com o número de consultas ao banco de dados sem mudar para um servidor mais eficiente e manter o sistema funcionando? Vou contar como lutamos com a degradação do desempenho de nosso banco de dados, como otimizamos as consultas SQL para atender ao maior número possível de usuários e não aumentar o custo dos recursos de computação.
Presto um serviço para gerenciar processos de negócios em empresas de construção. Cerca de 3 mil empresas trabalham conosco. Mais de 10 mil pessoas trabalham com nosso sistema por 4-10 horas todos os dias. Ele resolve várias tarefas de planejamento, alertas, avisos, validações ... Utilizamos o PostgreSQL 9.6. Temos cerca de 300 tabelas no banco de dados e todos os dias são enviados até 200 milhões de solicitações (10 mil diferentes). Em média, temos de 3 a 4 mil solicitações por segundo, nos momentos mais ativos, mais de 10 mil solicitações por segundo. A maioria dos pedidos é OLAP. Há muito menos adições, modificações e exclusões, ou seja, a carga OLTP é relativamente pequena. Forneci todos esses números para que você possa avaliar o escopo do nosso projeto e entender como nossa experiência pode ser útil para você.
A primeira foto. Lyrical
Quando começamos o desenvolvimento, não pensávamos realmente sobre o tipo de carga no banco de dados e o que faríamos se o servidor parar de puxar. Ao projetar o banco de dados, seguimos as recomendações gerais e tentamos não dar um tiro no pé, mas além de dicas gerais como “não use o padrão
Entity Attribute Values , não fomos. Projetado com base nos princípios da normalização, evitando a redundância de dados e não se preocupava em acelerar determinadas consultas. Assim que os primeiros usuários chegaram, encontramos um problema de desempenho. Como sempre, não estávamos preparados para isso. Os primeiros problemas foram simples. Como regra, tudo foi decidido adicionando um novo índice. Mas chegou um momento em que patches simples pararam de funcionar. Tendo percebido que não há experiência suficiente e está se tornando cada vez mais difícil entender qual é a causa dos problemas, contratamos especialistas que nos ajudaram a configurar o servidor corretamente, conectar o monitoramento e mostrar onde procurar para obter
estatísticas .
A segunda foto. Estatística
Portanto, temos cerca de 10 mil consultas diferentes que são executadas em nosso banco de dados por dia. Desses 10 mil, existem monstros que rodam de 2 a 3 milhões de vezes, com um tempo médio de execução de 0,1 a 0,3 ms e há consultas com um tempo médio de execução de 30 segundos, chamadas 100 vezes por dia.
Não foi possível otimizar todas as 10 mil consultas, por isso decidimos descobrir onde direcionar esforços para melhorar o desempenho do banco de dados corretamente. Após várias iterações, começamos a dividir solicitações em tipos.
TOP consultas
Essas são as consultas mais difíceis que levam mais tempo (tempo total). Essas são consultas que são chamadas com muita frequência ou que demoram muito tempo (consultas longas e frequentes foram otimizadas mesmo nas primeiras iterações da luta pela velocidade). Como resultado, o servidor gasta mais tempo em sua execução no total. Além disso, é importante separar as principais solicitações pelo tempo total de execução e separadamente pelo tempo de IO. As maneiras de otimizar essas consultas são ligeiramente diferentes.
A prática usual de todas as empresas é trabalhar com solicitações TOP. Existem alguns deles, a otimização de até uma solicitação pode liberar 5 a 10% dos recursos. No entanto, à medida que o projeto envelhece, otimizar as consultas TOP se torna uma tarefa cada vez mais não trivial. Todos os métodos simples já foram elaborados, e mesmo a solicitação mais "difícil" retira "apenas" 3-5% dos recursos. Se, no total, as consultas TOP demorarem menos de 30 a 40% do tempo, provavelmente você já fez esforços para que elas funcionem rapidamente e é hora de otimizar as consultas do próximo grupo.
Resta responder à pergunta quantas consultas principais devem ser incluídas neste grupo. Normalmente, tomo não menos que 10, mas não mais que 20. Tento garantir que o tempo do primeiro e do último no grupo TOP não difira mais que 10 vezes. Ou seja, se o tempo de execução da consulta cair acentuadamente de 1 lugar para 10, eu tomo TOP-10, se a queda for mais suave, aumentarei o tamanho do grupo para 15 ou 20.
Os camponeses médios (médios)
Todos esses são pedidos imediatamente após o TOP, com exceção dos últimos 5 a 10%. Geralmente, na otimização dessas solicitações específicas, está a capacidade de aumentar bastante o desempenho do servidor. Essas consultas podem "pesar" até 80%. Mas mesmo que sua participação tenha excedido 50%, é hora de analisá-los mais de perto.
Cauda
Como foi dito, esses pedidos vão no final e levam de 5 a 10% do tempo. Você pode esquecê-los apenas se você não usar ferramentas de análise automática de consultas, então a otimização delas também pode ser barata.
Como avaliar cada grupo?
Uso uma consulta SQL que ajuda a fazer uma avaliação do PostgreSQL (tenho certeza de que, para muitos outros DBMSs, você pode escrever uma consulta semelhante)
Consulta SQL para estimar o tamanho dos grupos TOP-MEDIUM-TAILSELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail FROM ( SELECT CASE WHEN rn <= 20 THEN tt_percent ELSE 0 END AS time_top, CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium, CASE WHEN rn > 800 THEN tt_percent ELSE 0 END AS time_tail FROM ( SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query, ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn FROM pg_stat_statements ORDER BY total_time DESC ) AS t ) AS ts
O resultado da consulta são três colunas, cada uma das quais contém uma porcentagem do tempo gasto no processamento de solicitações desse grupo. Dentro da consulta, existem dois números (no meu caso, 20 e 800) que separam solicitações de um grupo de outro.
É assim que as proporções de solicitações no momento do início da otimização se correlacionam aproximadamente.
O diagrama mostra que a parcela de pedidos TOP diminuiu bastante, mas os “camponeses do meio” cresceram.
Inicialmente, os erros mais comuns atingem as consultas mais importantes. Com o tempo, as doenças infantis desapareceram, a parcela de pedidos TOP foi reduzida e foram necessários mais esforços para acelerar pedidos difíceis.
Para obter o texto das solicitações, usamos essa solicitação SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query FROM pg_stat_statements ORDER BY total_time DESC ) AS T WHERE rn <= 20
Aqui está uma lista dos truques mais usados que nos ajudaram a acelerar as consultas TOP:
- Redesenhar sistemas, por exemplo, processando a lógica de notificação no intermediário de mensagens em vez de consultas periódicas ao banco de dados
- Adicionando ou modificando índices
- Reescrever consultas ORM em SQL puro
- Reescrever a lógica de carregamento lento de dados
- Armazenamento em cache através da desnormalização de dados. Por exemplo, temos um link entre as tabelas Entrega -> Fatura -> Solicitação -> Solicitação. Ou seja, cada entrega está associada ao aplicativo por meio de outras tabelas. Para não vincular todas as tabelas em cada solicitação, duplicamos o link para o aplicativo na tabela Entrega.
- Armazenando em cache tabelas estáticas com diretórios e raramente alterando tabelas na memória do programa.
Às vezes, as mudanças se arrastavam em um redesenho impressionante, mas forneciam de 5 a 10% da descarga do sistema e eram justificadas. Com o tempo, o escapamento tornou-se cada vez menos, e o redesenho precisou ser cada vez mais sério.
Em seguida, chamamos a atenção para o segundo grupo de pedidos - o grupo de camponeses médios. Ele tem muito mais solicitações e parecia que levaria muito tempo para analisar todo o grupo. No entanto, a maioria das consultas acabou sendo muito simples para otimização e muitos problemas foram repetidos dezenas de vezes em diferentes variações. Aqui estão alguns exemplos de otimizações típicas que aplicamos a dezenas de consultas semelhantes e cada grupo de consultas otimizadas descarregou o banco de dados em 3-5%.
- Em vez de verificar a presença de registros com COUNT e uma verificação completa da tabela, EXISTS começou a ser usado.
- Nos livramos do DISTINCT (não há receita geral, mas às vezes você pode se livrar dele facilmente, acelerando a solicitação de 10 a 100 vezes).
Por exemplo, em vez de consultar para selecionar todos os drivers em uma grande tabela de entrega (ENTREGA)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
solicitou uma tabela PERSON relativamente pequena
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
Parece que usamos uma subconsulta correlacionada, mas ela fornece uma aceleração de mais de 10 vezes.
- Em muitos casos, COUNT e
substituído pelo cálculo do valor aproximado
- em vez de
UPPER(s) LIKE JOHN%'
usar
s ILIKE “John%”
Cada solicitação específica às vezes era acelerada em 3-1000 vezes. Apesar do desempenho impressionante, a princípio pareceu-nos que não havia sentido em otimizar a consulta, executada por 10 ms, incluída na terceira centena de consultas mais pesadas e no tempo total de carregamento do banco de dados, em centésimos de um por cento. Mas, aplicando a mesma receita a um grupo de solicitações semelhantes, recuperamos vários por cento. Para não perder tempo visualizando manualmente todas as centenas de consultas, escrevemos vários scripts simples que, usando expressões regulares, encontraram consultas semelhantes. Como resultado, a pesquisa automática de grupos de consultas nos permitiu melhorar ainda mais nosso desempenho, gastando esforços modestos.
Como resultado, trabalhamos no mesmo hardware há três anos. A carga média diária é de cerca de 30%, em picos atinge 70%. O número de solicitações e o número de usuários cresceram cerca de 10 vezes. E tudo isso graças ao monitoramento constante desses mesmos grupos de consultas TOP-MEDIUM. Assim que uma nova solicitação aparece no grupo TOP, imediatamente a analisamos e tentamos acelerá-la. Analisamos o grupo MEDIUM uma vez por semana usando scripts de análise de consulta. Se você encontrar novas solicitações que já sabemos otimizar, então as alteramos rapidamente. Às vezes, encontramos novos métodos de otimização que podem ser aplicados a várias consultas ao mesmo tempo.
De acordo com nossas previsões, o servidor atual suportará um aumento no número de usuários em mais 3-5 vezes. É verdade que temos mais um trunfo na manga; ainda não traduzimos as consultas SELECT no espelho, conforme recomendado. Mas não fazemos isso conscientemente, pois queremos esgotar completamente as possibilidades de otimização "inteligente" antes de ativar a "artilharia pesada".
Uma análise crítica do trabalho realizado pode sugerir o uso da escala vertical. Compre um servidor mais poderoso, em vez de perder o tempo de especialistas. O servidor pode não custar muito, especialmente porque os limites da escala vertical ainda não foram esgotados. No entanto, apenas o número de solicitações aumentou 10 vezes. Por vários anos, a funcionalidade do sistema aumentou e agora há mais variedades de solicitações. A funcionalidade que foi causada pelo armazenamento em cache é realizada por menos solicitações, além disso, por solicitações mais eficientes. Assim, você pode multiplicar com segurança por outros 5 para obter o coeficiente de aceleração real. Portanto, de acordo com as estimativas mais conservadoras, podemos dizer que a aceleração foi 50 ou mais vezes. Agitar verticalmente o servidor 50 vezes custaria mais. Especialmente considerando que, uma vez que a otimização é realizada o tempo todo, é cobrada uma fatura de um servidor alugado todo mês.