Uma rede inteira de websites ficou lenta de uma vez, e nem um servidor estava ocupado.

Uma rede de websites de recrutamento amoleceu ao mesmo tempo. Não uma página, não um site: todos, em conjunto, aquele tipo de nevoeiro em toda a quinta de servidores que leva um operador a presumir que os servidores estão a afogar-se e que chegou a hora de comprar maiores. Essa presunção é cara, é comum, e neste caso estava errada. Todas as máquinas da quinta estavam entediadas. Toda a quinta esperava pela base de dados, e a base de dados esperava por uma única query.

Todas as camadas estavam paradas exceto uma

Num abrandamento de toda a quinta, leia a utilização de cada camada antes de qualquer outra coisa, e veja qual está realmente no limite. Os load balancers estavam ao ralenti. Os servidores web estavam entediados. Nada no caminho do pedido trabalhava o suficiente para explicar um site lento.

Este é o sinal que encerra a discussão sobre capacidade antes de ela começar. Se todas as camadas estão paradas exceto a base de dados, um servidor web maior compra-lhe uma máquina parada mais rápida. O constrangimento nunca foi a computação, e nenhuma encomenda de hardware o ia corrigir.

A resposta cara era comprar mais máquinas

Mais hardware teria escalado o desperdício, não o teria removido. O único passo seguinte honesto era descobrir em que é que a base de dados estava realmente a gastar o seu tempo.

Agregámos o histórico de queries por impressão digital

Uma aplicação raramente escreve a mesma query da mesma forma duas vezes. A mesma pesquisa aparece parametrizada de várias formas diferentes, pelo que contar o texto bruto das queries esconde o culpado no ruído. A solução é agregar por uma impressão digital que sobreviva a essa inconsistência, e depois ordenar cada statement pela sua quota do tempo total da base de dados. Corremo-lo sobre todo o histórico de queries.

-- reconstructed, de-identified rendering of cleared facts. not a real capture.
-- full query history, grouped by query_hash
-- (a fingerprint that survives inconsistent parameterisation)
SELECT TOP 5
  query_hash,
  SUM(execution_count)                AS calls,
  SUM(total_logical_reads)            AS pages_read,
  SUM(total_elapsed_time) * 100.0
    / SUM(SUM(total_elapsed_time)) OVER () AS pct_of_db_time
FROM   sys.dm_exec_query_stats
GROUP  BY query_hash
ORDER  BY pct_of_db_time DESC;

Uma linha sobressaiu: mais de metade de tudo o que a base de dados fazia, gasto num só statement. O método é deliberadamente aborrecido, não adivinha, e é o mesmo todas as vezes.

Uma query era mais de metade da base de dados

O statement que saiu é do tipo que nunca olharia duas vezes. Uma pesquisa de cidade, duas colunas, um filtro.

-- reconstructed, de-identified. table/column names renamed from the real schema.
SELECT CityID, CityName
FROM   Cities
WHERE  RoutingPath = @p;

-- Execution plan: Clustered Index Scan (a full scan)
--   Returns:          ONE row
--   Filtered column:  RoutingPath --> no index

A coluna pela qual filtrava não tinha index. Por isso cada chamada percorria a tabela inteira para devolver uma única linha. Por si só isso é um custo pequeno; repetido em cada visualização de página em toda a rede, tornou-se a maior coisa que a base de dados fazia.

Uma resposta de uma linha deve ler duas ou três páginas, não a tabela inteira

“Uma resposta de uma linha deve ler duas ou três páginas, não centenas.”

Essa frase é todo o diagnóstico, comprimido em algo que se pode dizer à pessoa que é dona do código. Nada mais faz uma pesquisa de uma linha ler tanto. Nem disco lento, nem CPU ocupado, nem uma noite má. Uma resposta de uma linha que lê a tabela inteira é um full scan, e um full scan numa coluna filtrada é uma assinatura inconfundível: a coluna precisa de um index. Não precisámos de discutir hardware, limitámo-nos a mostrar as leituras.

Um index online, de um scan para um seek

-- reconstructed, de-identified. index name illustrative, on the renamed identifiers.
-- Index the column in the WHERE clause; cover the two columns it returns
CREATE NONCLUSTERED INDEX IX_Cities_RoutingPath
  ON Cities (RoutingPath)
  INCLUDE (CityID, CityName)
  WITH (ONLINE = ON);   -- applied without interrupting the site

-- New execution plan: Index Seek

Um index, na única coluna pela qual a aplicação realmente filtrava. Foi aplicado online, sem interromper os sites, e a pesquisa que tinha dominado a base de dados tornou-se uma das coisas mais baratas que ela fazia.

O engenheiro do cliente acrescentou ao diagnóstico

A melhor parte deste trabalho não foi o nosso index. Foi o que aconteceu quando o próprio engenheiro de bases de dados do cliente se juntou e acrescentou ao diagnóstico: trouxe um padrão de bloqueio reader/writer do lado dele do sistema. Integrámos a correção dele com a nossa, e as duas entraram juntas.

“O engenheiro do cliente saiu da chamada como colaborador.”

Essa é a diferença entre um ticket de suporte e um diagnóstico. Quando o achado é verdadeiro e se mostram as provas, os dois lados deixam de defender posições e começam a melhorar a plataforma juntos.

Quando todas as camadas estão paradas exceto uma, pare de olhar para a capacidade

A lição é curta e é o contrário do reflexo. Lentidão em toda a quinta com máquinas paradas não é um problema de capacidade, é uma questão de software vestida de capacidade. Eis o que verificar antes de assinar uma encomenda de hardware.

Durante tudo isto, a máquina manteve-se calma. Os load balancers ao ralenti, os servidores web entediados, e a correção foi um index na coluna pela qual a aplicação filtrava. O servidor de base de dados estava saudável; um statement dentro dele precisava de um index.

O instinto numa quinta lenta é comprar a saída. A resposta honesta é quase sempre mais barata: leia cada camada, encontre a que está no limite, e vá ler as queries dela.