Analisando problemas de performance - Um estudo de caso (Parte 2 de 4)
Parte 2: Identificando o gargalo
Depois dos últimos 45 dias bem turbulentos (veja post anterior), vamos continuar com o estudo de caso. Em primeiro lugar, obrigado a todos que colaboraram com os comentários, o Nilton foi bem objetivo e tocou em um ponto bem importante, o Agnaldo listou uma série de boas práticas e o Ricardo apontou uma questão problemática: a análise dos contadores requer um bom entendimento do SQL Server, sistema operacional e uma experiência relevante. Espero realmente ajudar a diminuir esse gap e aprender um pouco com os cenários de vocês.
Ao fim desta série eu vou escrever artigos específicos sobre alguns dos comentários/recomendações colocados aqui e também vou formalizar uma proposta (antigo sonho): criar um repositório de contadores e dicas de análise, para que seja possível compararmos performance de diversos servidores e aprendermos com isso.
Analisando os contadores:
Conforme descrito no primeiro post da série, o cliente reportou que eles estavam passando por um gargalo de disco, então partindo desta hipótese podemos olhar os contadores e ter uma visão geral de como está o sistema operacional e o SQL Server.
Sistema Operacional
· Logical Disks: notamos aqui que o disco G está com um tempo de resposta ruim (Avg. Disk sec/Read e Avg. Disk sec/Write), principalmente porque isso é uma média de quase 24 horas. Durante momentos de carga no servidor esse número piora e muito.
o Atualmente damos mais foco nesses contadores ao invés da fila, porque a fila está relacionada com o número de spindles trabalhando (número muitas vezes desconhecido pelo DBA!) e o SQL Server pode, por exemplo, optar por colocar de uma só vez 100 operações de IOs assíncronas. Então nunca olhe para esse contador sozinho, uma vez que o tempo de resposta está alto a fila deve te ajudar a ver que realmente existe um gargalo.
· Memória (SO): temos 804 MB de memória física disponível neste servidor, então não temos o que reclamar neste ponto. Também está sobrando PTE.
· Interface de rede: 0.08% de utilização da largura de banda disponível.
· Arquivo de paginação: 0.8% de utilização, baixíssimo.
· Processador: 22% de utilização média, sendo que praticamente tudo está sendo utilizado pelo processo do SQL Server.
o Pegue o contador “Process\% Processor Time” da instância (processo) sqlservr e divida pelo número de processadores expostos pelo sistema operacional. No nosso caso: 78 / 4 = 19,5 – então somente 2,5% do tempo de processamento está sendo utilizado por outros processos.
· System: sem fila nos processadores.
Analisando até aqui nós podemos ver que o disco está com a pior situação, mas como eu já havia dito antes, NUNCA olhe para somente um contador, veja sempre o cenário todo e correlacione contadores.
SQL Server
Aqui temos vários contadores para analisarmos e frequentemente eles dependem da característica das aplicações que utilizam os bancos de dados, então é complicado olharmos para o contador “Index searches” ou “full scans” e dizer: esse número está alto.
Usualmente utilizamos o contador “Batch Requests/sec” para termos uma noção do volume de requisições sendo processadas pelo SQL Server. Porém neste caso temos uma aplicação com características batch, que faz poucas e pesadas requisições... Peguei 80% do documento de recomendações de contadores, joguei pela janela e pensei “Massa!”.
Olhando os diversos contadores do SQL Server não percebi nada que me chamasse a atenção, a relação entre index searches / full scans está razoável (1:100), como não sabemos se o full scan é feito sobre uma tabela de 10 ou 10.000.000 de registros, o contador não ajuda muito. Utilização razoável da tempdb (pode ser um ponto a ser atacado para melhorarmos o gargalo em disco), nada agressivo em locks ou latches, compilação ok, range scan um pouco alto. Nada de alarmante (mais ou menos como esperado).
Agora o que realmente me interessava: Buffer Manager.
Sempre que o tempo de resposta de um disco estiver muito alto, devemos nos perguntar se é um problema de disco ou se ele está sendo muito exigido, afinal tudo é física e possui um limite. Será que o SQL Server não está sobrecarregando o disco? Vamos aos contadores...
· Buffer cache hit ratio: conforme descrito no último artigo, esse número deve sempre ficar acima de 90% ~ 95%. Agora confiem em mim, se eu vejo esse número abaixo de 98% eu já fico preocupado. No nosso caso a média foi 96%, chegando a 90% em alguns momentos no dia.
o Esse número indica a porcentagem de vezes que o SQL Server tenta acessar uma página e ela se encontra no buffer do SQL Server. É mais do que normal ver esse número com 99,8% em ambientes trabalhando bem.
· Page reads/sec: Indica o número de páginas em disco que o SQL Server lê por segundo. No nosso caso temos 2880 páginas (2880 * 8K = 23MB) por segundo em média! Se toda a memória do buffer fosse alocada para dados – o que não é - (Total Server Memory (KB) = 1.663 MB) o SQL Server estaria supostamente substituindo todas as páginas a cada 72 segundos (isso em média, imagine um pico durante o dia).
· Lazy writer/sec: O lazy writer é responsável por determinar quais são as páginas que devem ser retiradas da memória (as alteradas, são escritas em disco) e idealmente esse valor deve estar perto de zero. Uma média de 22 passagens por segundo é alta e mostra que o SQL Server está trabalhando para tirar as páginas da memória, liberando espaço para poder colocar 23 MB a cada segundo.
· Page life expectancy: já que o SQL Server fica a todo momento retirando e colocando páginas em memória, a expectativa de vida da página tende a diminuir. Esse é um ótimo contador para vermos que existe uma pressão no buffer do SQL Server! O valor de 122 segundos é muito ruim.
o Minha experiência mostra que o valor recomendado no último artigo é o mínimo aceitável (300 segundos). Analisando diversos contadores dos clientes, eu vejo pelo menos 600s e usualmente um valor ainda maior. O que vocês têm no ambiente de vocês?
Agora uma coisa é intrigante... Com essa grande pressão no buffer de dados, como o SQL Server consegue manter um cache hit ratio relativamente alto (96%)? Veja o próximo contador.
· Readahead pages/sec: esse mecanismo possibilita que páginas de dados e índice possam ser lidas antecipadamente e colocadas no buffer de dados. Dessa forma quando o SQL Server precisa acessar a página, ela já está no buffer e temos um hit (ao invés de um miss). Então o alto número de readaheads ajuda a manter o cache hit ratio alto.
o Nota: há algumas semanas eu fui a um cliente e a DBA comentou que eles não detectaram o mesmo problema no ambiente deles porque eles viram um hit ratio alto, “enganados” pelo contador o problema foi atribuído ao disco. Acreditem, isso acontece com uma freqüência incrível.
o Mas no fim, fiquem felizes, porque mostra que o SQL Server trabalha muito bem com o Read Ahead para manter o outro contador com um valor alto.
Neste momento a análise dos contadores fez com que puséssemos o foco no SQL Server e deixássemos o disco de lado por enquanto. Definimos duas possíveis abordagens para seguir:
1 – Trabalhar na configuração do SQL Server / adição de mais memória.
2 – Analisar os procedimentos mais pesados e tentar aperfeiçoar-los para minimizar a leitura das páginas.
Como a segunda opção usualmente é mais demorada e existia um pedido formal para que a aplicação não fosse alterada (e nem os índices), vamos atacar o primeiro ponto, ainda mais porque a bola estava quicando na frente do gol.
Entendendo um pouco de gerenciamento memória
Para quem não entende muito sobre memória virtual, /3GB, AWE, PAE, etc. Recomendo entender esse assunto direitinho, pois é um problema muito comum que vejo em todo lugar. Não vou explicar passo a passo, se for necessário depois podemos refinar o assunto. Até lá fiquem com esse excelente post do Fartura (amigo e ex-PFE aqui no Brasil):
https://blogs.technet.com/marcelofartura/archive/2006/09/14/3gb-pae-awe-what-basic.aspx
No nosso ambiente nós temos 800 MB livres na memora física, enquanto o SQL Server está utilizando 1.6 GB para o seu buffer, que é dividido entre data buffer, procedure cache, locks, query workspace, etc.
Isso acontece porque o SQL Server (e qualquer outro processo em uma arquitetura de 32 bits) somente enxerga por padrão 2GB de memória virtual, que o sistema operacional vai ser virar para mapear isso na memória física + arquivo de paginação. Se levarmos em conta que desses 2GB disponíveis para alocação, 256 MB é reservado para a área MemToLeave e 128 MB para thread stack (512 KB por thread x 256 threads definidas na configuração do SQL Server), sobram +/- 1,6 GB de memória para o buffer do SQL Server (já vimos esse número acima).
Então mesmo tendo 2500 MB configurado no SQL Server, esse total não está sendo utilizado devido a limitação do endereçamento virtual. Para alterarmos a divisão da memória virtual de 2GB para user e 2GB para kernel, para 3GB e 1GB, deixamos a seguinte recomendação definida:
· Adicionamos a flag /3GB no boot.ini do servidor. Isso vai permitir que o SQL Server (e qualquer outro processo do sistema operacional) utilize até 3GB de memória virtual.
· Como também temos 800 MB sobrando, podemos configurar o SQL Server para usar 2600 MB ou até 2650 MB (Max Server Memory), sempre tendo o cuidado de deixar uma quantidade de memória física livre e evitar paginação.
o Se for possível colocar mais 1GB de memória física, configurar o SQL Server para utilizar os 3GB disponíveis para o endereçamento user mode.
Neste momento nós poderíamos fazer uma série de outras recomendações, como colocar mais 4GB de memória no servidor e usar AWE/PAE, mudar a configuração dos discos, separar a tempdb, analisar os índices, etc.
Porém quando estamos resolvendo um problema e queremos entender o que está acontecendo, NUNCA faça diversas alterações de uma vez, pois se houver uma melhora você nunca vai saber aquela que mais te ajudou. Todo processo de troubleshooting deve ser certeiro, se as alterações por acaso não surtirem efeito, documente o acontecido, crie uma nova hipótese, trabalhe em cima dela e faça outra alteração. Repita quantas vezes for necessário J.
Preciso ressaltar...
· Pode ser que exista problema também com o disco, mas se aliviarmos a carga sobre ele poderemos ter uma análise mais imparcial e verificar se está respondendo bem ou não.
· Upgrade de hardware nem sempre é a solução, mas nesse caso vai ajudar. Chega um ponto onde as características da aplicação não podem ser negligenciadas e a adição de mais memória, processador ou nova storage não vai trazer benefícios. A maior verdade: aplicação mal escrita NÃO ESCALA!
PS: nem olhei a aplicação do cliente, pode ser que ela esteja bem escrita e o volume dos dados esteja causando essa pressão que vimos.
· Resolvendo um gargalo de memória e disco, pode ser que apareça um gargalo de processamento, pois não havendo outras contenções o SO não vai precisar ficar parado esperando informação. Isso acontece sim... Bem vindos ao fantástico mundo da otimização de desempenho.
Espero realmente que esse artigo tenha conseguido mostrar os passos que segui para chegar à recomendação. É claro que deixei de fora uma série de contadores que não estavam relacionados e uma série de tópicos que discutimos no cliente (em uma pequena sessão de quase 6 horas), senão eu iria escrever um livro inteiro. Se eu não fui claro, perguntem...
[]s
Luciano Caixeta Moreira
=============================================================
This posting is provided "AS IS" with no warranties, and confers no rights
=============================================================
20070830 - Analisando problemas de performance - Um estudo de caso (Parte 2 de 4).pdf