{"id":36704,"date":"2019-10-31T22:13:15","date_gmt":"2019-10-31T19:13:15","guid":{"rendered":"https:\/\/prohoster.info\/blog\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\/"},"modified":"2019-10-31T22:13:15","modified_gmt":"2019-10-31T19:13:15","slug":"optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","status":"publish","type":"post","link":"https:\/\/prohoster.info\/pt\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Otimiza\u00e7\u00e3o de consultas de banco de dados com base em um servi\u00e7o B2B para construtores","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Como crescer 10 vezes na quantidade de consultas ao banco de dados sem mudar para um servidor mais poderoso e mantendo a funcionalidade do sistema? Vou explicar como lutamos contra a queda de desempenho do nosso banco de dados, como otimizamos as consultas SQL para atender o maior n\u00famero poss\u00edvel de usu\u00e1rios e n\u00e3o aumentar os custos com recursos computacionais.
\n<\/a><\/noindex>
\nEstou desenvolvendo um servi\u00e7o para gerenciar processos de neg\u00f3cios em empresas de constru\u00e7\u00e3o. Trabalhamos com cerca de 3.000 empresas. Mais de 10.000 pessoas utilizam nosso sistema todos os dias por 4-10 horas. Ele resolve diversas tarefas de planejamento, notifica\u00e7\u00e3o, alerta, valida\u00e7\u00e3o... Usamos PostgreSQL 9.6. Em nosso banco de dados, temos cerca de 300 tabelas e a cada dia recebemos at\u00e9 200 milh\u00f5es de consultas (10.000 diferentes). Em m\u00e9dia, temos de 3-4 mil consultas por segundo, e nos momentos mais ativos, mais de 10.000 consultas por segundo. A maior parte das consultas \u00e9 OLAP. Adi\u00e7\u00f5es, modifica\u00e7\u00f5es e remo\u00e7\u00f5es s\u00e3o muito menores, ou seja, a carga OLTP \u00e9 relativamente pequena. Todos esses n\u00fameros que citei servem para que voc\u00ea possa avaliar a escala do nosso projeto e entender qu\u00e3o \u00fatil nossa experi\u00eancia pode ser para voc\u00ea.<\/p>\n

Cena um. L\u00edrica<\/h3>\n

\nQuando come\u00e7amos o desenvolvimento, n\u00e3o pensamos muito sobre a carga que recairia sobre o banco de dados e o que far\u00edamos se o servidor parasse de suportar. Ao projetar o banco de dados, seguimos as recomenda\u00e7\u00f5es gerais e tentamos n\u00e3o nos prejudicar, mas n\u00e3o fomos al\u00e9m de conselhos gerais como \"n\u00e3o use o padr\u00e3o Entity Attribute Values<\/a><\/noindex> ,\" n\u00e3o exploramos. Projetamos com base nos princ\u00edpios de normaliza\u00e7\u00e3o, evitando a redund\u00e2ncia de dados e n\u00e3o nos preocupamos em acelerar certas consultas. Assim que os primeiros usu\u00e1rios chegaram, nos deparamos com problemas de desempenho. Como de costume, est\u00e1vamos absolutamente despreparados para isso. Os primeiros problemas eram simples. Geralmente, tudo se resolvia com a adi\u00e7\u00e3o de um novo \u00edndice. Mas chegou um momento em que as solu\u00e7\u00f5es simples deixaram de funcionar. Percebendo que nos faltava experi\u00eancia e que era cada vez mais dif\u00edcil entender a causa dos problemas, contratamos especialistas que nos ajudaram a configurar corretamente o servidor, ativar o monitoramento e nos mostraram onde olhar para obter estat\u00edsticas<\/a><\/noindex>.<\/p>\n

Cena dois. Estat\u00edstica<\/h3>\n

\nTemos cerca de 10 mil consultas diferentes que s\u00e3o executadas em nosso banco de dados diariamente. Destas 10 mil, existem monstros que s\u00e3o executados de 2 a 3 milh\u00f5es de vezes, com um tempo m\u00e9dio de execu\u00e7\u00e3o de 0,1 a 0,3 ms, e h\u00e1 consultas com um tempo m\u00e9dio de execu\u00e7\u00e3o de 30 segundos, que s\u00e3o chamadas 100 vezes por dia.<\/p>\n

Otimizar todas as 10 mil consultas n\u00e3o era vi\u00e1vel, ent\u00e3o decidimos entender onde direcionar nossos esfor\u00e7os para aumentar a performance do banco de dados corretamente. Ap\u00f3s v\u00e1rias itera\u00e7\u00f5es, come\u00e7amos a classificar as consultas por tipos.<\/p>\n

CONSULTAS TOP<\/h4>\n

\nEstas s\u00e3o as consultas mais pesadas, que consomem mais tempo (tempo total). S\u00e3o consultas que ou s\u00e3o muito frequentemente chamadas ou que demoram muito para serem executadas (consultas longas e frequentes j\u00e1 foram otimizadas nas primeiras itera\u00e7\u00f5es de busca por velocidade). No total, o servidor gasta mais tempo na execu\u00e7\u00e3o dessas consultas. \u00c9 importante separar as consultas top por tempo total de execu\u00e7\u00e3o e separadamente por tempo de I\/O. As formas de otimiza\u00e7\u00e3o para essas consultas s\u00e3o um pouco diferentes.<\/p>\n

A pr\u00e1tica comum de todas as empresas \u00e9 trabalhar com as CONSULTAS TOP. Elas s\u00e3o poucas e a otimiza\u00e7\u00e3o de mesmo uma \u00fanica consulta pode liberar de 5 a 10% dos recursos. No entanto, \u00e0 medida que o projeto \"amadurece\", a otimiza\u00e7\u00e3o das CONSULTAS TOP se torna uma tarefa cada vez mais n\u00e3o trivial. Todos os m\u00e9todos simples j\u00e1 foram explorados e a consulta mais \u201cpesada\u201d consome \u201capenas\u201d 3 a 5% dos recursos. Se as consultas TOP, em soma, ocupam menos de 30 a 40% do tempo, \u00e9 prov\u00e1vel que voc\u00ea j\u00e1 tenha se esfor\u00e7ado para que funcionem rapidamente e \u00e9 hora de passar para a otimiza\u00e7\u00e3o das consultas do pr\u00f3ximo grupo.
\nResta responder \u00e0 pergunta de quantas consultas superiores incluir neste grupo. Eu geralmente considero n\u00e3o menos de 10, mas n\u00e3o mais de 20. Procuro garantir que o tempo do primeiro e do \u00faltimo na lista TOP n\u00e3o difira mais do que 10 vezes. Ou seja, se o tempo de execu\u00e7\u00e3o das consultas cai abruptamente do 1\u00ba para o 10\u00ba lugar, eu pego o TOP-10; se a queda for mais gradual, aumento o tamanho do grupo para 15 ou 20.
\n\"Otimiza\u00e7\u00e3o
\n<\/p>\n

CONSULTAS M\u00c9DIAS<\/h4>\n

\nEstas s\u00e3o todas as consultas que v\u00eam imediatamente ap\u00f3s as TOP, exceto as \u00faltimas 5-10%. Normalmente, a otimiza\u00e7\u00e3o exatamente dessas consultas \u00e9 onde se encontra a possibilidade de aumentar significativamente a performance do servidor. Essas consultas podem \u201cpesar\u201d at\u00e9 80%. Mas mesmo que sua participa\u00e7\u00e3o ultrapasse 50%, \u00e9 hora de olh\u00e1-las com mais aten\u00e7\u00e3o.<\/p>\n

CAUDA<\/h4>\n

\nComo foi mencionado, essas consultas ocorrem no final e consomem de 5 a 10% do tempo. Voc\u00ea pode esquec\u00ea-las, a menos que utilize ferramentas autom\u00e1ticas de an\u00e1lise de consultas, pois a otimiza\u00e7\u00e3o tamb\u00e9m pode sair barata.<\/p>\n

Como avaliar cada grupo?<\/p>\n

Eu uso uma consulta SQL que ajuda a fazer essa avalia\u00e7\u00e3o para PostgreSQL (tenho certeza de que para muitos outros SGBDs \u00e9 poss\u00edvel escrever uma consulta semelhante).<\/p>\n

Consulta SQL para avaliar o tamanho dos grupos TOP-MEDIUM-TAIL<\/b><\/p>\n

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail\nFROM\n(\n  SELECT CASE WHEN rn  20 AND rn  800              THEN tt_percent ELSE 0 END AS time_tail\n  FROM (\n    SELECT total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,\n    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn\n    FROM pg_stat_statements\n    ORDER BY total_time DESC\n  ) AS t\n)\nAS ts\n<\/code><\/pre>\n
O resultado da consulta \u00e9 tr\u00eas colunas, cada uma contendo a porcentagem do tempo gasto no processamento das consultas desse grupo. Dentro da consulta, h\u00e1 dois n\u00fameros (neste caso, 20 e 800), que separam as consultas de um grupo das do outro.<\/p>\n
Assim, aproximadamente, as propor\u00e7\u00f5es das consultas no in\u00edcio do trabalho de otimiza\u00e7\u00e3o em compara\u00e7\u00e3o com agora.<\/p>\n
\"Otimiza\u00e7\u00e3o
\n
\nDo gr\u00e1fico, fica evidente que a participa\u00e7\u00e3o das consultas TOP diminuiu acentuadamente, enquanto os 'intermedi\u00e1rios' cresceram.
\nInicialmente, as consultas TOP inclu\u00edam erros grosseiros. Com o tempo, as doen\u00e7as infantis desapareceram, a participa\u00e7\u00e3o das consultas TOP foi reduzida e esfor\u00e7os cada vez maiores foram necess\u00e1rios para acelerar as consultas mais pesadas. <\/p>\n
Para obter o texto das consultas, usamos a seguinte consulta:<\/b><\/p>\n
SELECT * FROM (\n  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\n  FROM pg_stat_statements\n  ORDER BY total_time DESC\n) AS T\nWHERE\nrn  20 AND rn  800  -- TAIL\n<\/code><\/pre>\n
Aqui est\u00e1 uma lista das t\u00e9cnicas mais frequentemente utilizadas que nos ajudaram a acelerar as consultas TOP:<\/p>\n