{"id":35259,"date":"2019-10-31T22:03:17","date_gmt":"2019-10-31T19:03:17","guid":{"rendered":"https:\/\/prohoster.info\/blog\/istoriya-odnogo-sql-rassledovaniya\/"},"modified":"2019-10-31T22:03:17","modified_gmt":"2019-10-31T19:03:17","slug":"istoriya-odnogo-sql-rassledovaniya","status":"publish","type":"post","link":"https:\/\/prohoster.info\/pt\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","title":{"rendered":"A hist\u00f3ria de uma investiga\u00e7\u00e3o SQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Em dezembro do ano passado, recebi um relat\u00f3rio interessante sobre um erro da equipe de suporte da VWO. O tempo de carregamento de um dos relat\u00f3rios anal\u00edticos para um grande cliente corporativo parecia excessivamente alto. Como essa \u00e9 minha \u00e1rea de responsabilidade, imediatamente me concentrei em resolver o problema.<\/p>\n

<\/p>\n

Antecedentes<\/h2>\n

<\/p>\n

Para que fique claro sobre o que estou falando, vou contar um pouco sobre a VWO. \u00c9 uma plataforma que permite lan\u00e7ar diferentes campanhas segmentadas em seus sites: realizar testes A\/B, rastrear visitantes e convers\u00f5es, fazer an\u00e1lise de funil de vendas, exibir mapas de calor e reproduzir grava\u00e7\u00f5es de visitas.<\/p>\n

<\/p>\n

Mas o mais importante na plataforma \u00e9 a gera\u00e7\u00e3o de relat\u00f3rios. Todas as fun\u00e7\u00f5es mencionadas est\u00e3o interligadas. Para clientes corporativos, uma enorme quantidade de informa\u00e7\u00f5es seria simplesmente in\u00fatil sem uma plataforma poderosa que as apresentasse em uma forma \u00fatil para an\u00e1lise.<\/p>\n

<\/p>\n

Usando a plataforma, \u00e9 poss\u00edvel fazer consultas arbitr\u00e1rias em um grande conjunto de dados. Aqui est\u00e1 um exemplo simples:<\/p>\n

<\/p>\n

Mostrar todos os cliques na p\u00e1gina \"abc.com\" DE <data d1> AT\u00c9 <data d2> para pessoas que usaram Chrome OU (estiveram na Europa E usaram iPhone)<\/pre>\n
<\/p>\n
Observe os operadores booleanos. Eles est\u00e3o dispon\u00edveis para clientes na interface de consulta, permitindo fazer consultas complexas para obter amostras.<\/p>\n
<\/p>\n
Consulta lenta<\/h2>\n
<\/p>\n
O cliente em quest\u00e3o estava tentando fazer algo que intuitivamente deveria funcionar rapidamente:<\/p>\n
<\/p>\n
Mostre todos os registros de sess\u00f5es para usu\u00e1rios que visitaram qualquer p\u00e1gina com a URL contendo \"\/jobs\"<\/pre>\n
<\/p>\n
Este site tinha um enorme volume de tr\u00e1fego, e armazen\u00e1vamos mais de um milh\u00e3o de URLs \u00fanicas apenas para ele. E eles queriam encontrar um padr\u00e3o de URL bastante simples relacionado ao seu modelo de neg\u00f3cios.<\/p>\n
\n<\/a><\/noindex><\/p>\n
Investiga\u00e7\u00e3o preliminar<\/h2>\n
<\/p>\n
Vamos ver o que est\u00e1 acontecendo no banco de dados. Abaixo est\u00e1 a consulta SQL lenta original:<\/p>\n
<\/p>\n
SELECT \n    count(*) \nFROM \n    acc_{account_id}.urls as recordings_urls, \n    acc_{account_id}.recording_data as recording_data, \n    acc_{account_id}.sessions as sessions \nWHERE \n    recording_data.usp_id = sessions.usp_id \n    AND sessions.referrer_id = recordings_urls.id \n    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\/jobs%')::text[]   ) \n    AND r_time > to_timestamp(1542585600) \n    AND r_time = 5 \n    AND recording_data.num_of_pages > 0 ;<\/code><\/pre>\n
<\/p>\n
Aqui est\u00e3o os tempos:<\/p>\n
<\/p>\n
Tempo estimado: 1.480 ms\nTempo de execu\u00e7\u00e3o: 1431924.650 ms<\/pre>\n
<\/p>\n
A consulta percorreu 150 mil linhas. O planejador de consultas destacou alguns detalhes interessantes, mas n\u00e3o havia gargalos \u00f3bvios.<\/p>\n
<\/p>\n
Vamos continuar analisando a consulta. Como pode ser visto, ela faz JOIN<\/code> tr\u00eas tabelas:<\/p>\n
<\/p>\n
    \n
  1. sessions<\/strong>: para exibir informa\u00e7\u00f5es da sess\u00e3o: navegador, agente do usu\u00e1rio, pa\u00eds e assim por diante.<\/li>\n
  2. recording_data<\/strong>: URLs gravados, p\u00e1ginas, dura\u00e7\u00e3o das visitas<\/li>\n
  3. urls<\/strong>: para evitar a duplica\u00e7\u00e3o de URLs extremamente longas, as armazenamos em uma tabela separada.<\/li>\n<\/ol>\n
    <\/p>\n
    Observe tamb\u00e9m que todas as nossas tabelas j\u00e1 est\u00e3o divididas por account_id<\/code>. Dessa forma, elimina-se a situa\u00e7\u00e3o em que um \u00fanico conta muito grande causa problemas para as outras.<\/p>\n
    <\/p>\n
    Em busca de pistas<\/h2>\n
    <\/p>\n
    Ao analisar mais de perto, vemos que algo est\u00e1 errado em uma consulta espec\u00edfica. Vale a pena prestar aten\u00e7\u00e3o nesta linha:<\/p>\n
    <\/p>\n
    urls && array(\n\tselect id from acc_{account_id}.urls \n\twhere url ILIKE '%enterprise_customer.com\/jobs%'\n)::text[]<\/code><\/pre>\n
    <\/p>\n
    O primeiro pensamento foi que talvez, por causa do ILIKE<\/code> em todas essas URLs longas (temos mais de 1,4 milh\u00e3o de URLs \u00fanicas, coletadas para esta conta) o desempenho poderia estar comprometido.\u00a0<\/strong>Mas n\u00e3o, n\u00e3o \u00e9 isso!<\/p>\n
    <\/p>\n
    SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com\/jobs%';\n  id\n--------\n ...\n(198661 linhas)\n\nTempo: 5231.765 ms<\/p>\n
    <\/p>\n
    A pr\u00f3pria consulta de busca por padr\u00e3o leva apenas 5 segundos. A busca por padr\u00e3o em um milh\u00e3o de URLs \u00fanicas claramente n\u00e3o \u00e9 um problema.<\/code><\/pre>\n
    <\/p>\n
    O pr\u00f3ximo suspeito na lista s\u00e3o alguns<\/p>\n
    <\/p>\n
    . Ser\u00e1 que seu uso excessivo levou a uma desacelera\u00e7\u00e3o? Normalmente JOIN<\/code>\u2018as s\u00e3o os candidatos mais \u00f3bvios para problemas de desempenho, mas eu n\u00e3o acreditava que nosso caso fosse t\u00edpico. JOIN<\/code>\u2018Eles s\u00e3o os candidatos mais \u00f3bvios a problemas de desempenho, mas eu n\u00e3o acreditava que o nosso caso fosse t\u00edpico.<\/p>\n
    <\/p>\n
    E esse tamb\u00e9m n\u00e3o era o nosso caso.<\/code><\/pre>\n
    <\/p>\n
    \u2018as se mostraram bastante r\u00e1pidas. JOIN<\/code>\u2018Eles se mostraram bastante r\u00e1pidos.<\/p>\n
    <\/p>\n
    Eu estava preparado para come\u00e7ar a modificar a consulta para alcan\u00e7ar quaisquer melhorias de desempenho poss\u00edveis. N\u00f3s da equipe desenvolvemos duas ideias principais:<\/h2>\n
    <\/p>\n
    Usar EXISTS para a subconsulta de URL<\/p>\n
    <\/p>\n