Quanto os VLFs podem afetar o backup de log?

No post de hoje, vou falar brevemente sobre o impacto que um grande número de VLFs (Virtual Log Files) pode ter sobre os backups de log através de um caso real. Não pretendo explicar detalhadamente o que são os VLFs, como funcionam ou como dimensioná-los, mas certamente não irei deixar você sem fontes para sanar quaisquer dúvidas.

A grosso modo, os VLFs são como o arquivo de log do seu banco de dados é subdividido. Desta forma, o SQL Server gerencia quais partes do log estão sendo utilizadas no momento, quais já foram escritas em disco e podem ser reutilizadas, etc. Se quiser ler mais sobre como o SQL Server registra as informações no arquivo de log, veja esse ótimo artigo [1] do Paul Randal publicado na TechNet Magazine. Recomendo também este vídeo [2] da Jes Schultz Borland da família Brent Ozar que explica de uma forma simples o assunto.

Enfim, no momento em que o arquivo de log cresce – supondo que ele tenha espaço pra isso –, o SQL Server utiliza o incremento definido na opção Autogrowth do arquivo de log para fornecer espaço. Cada vez que isso ocorre, um número de VLFs é criado dependendo do incremento utilizado como é pontuado abaixo.

• Se o incremento for de até 64MB, são criados mais 4 VLFs;
• Se o incremento for maior que 64MB e de até 1GB, são criados 8 VLFs;
• Se o incremento for maior que 1GB, são criados 16 VLFs;

Por padrão, a opção Autogrowth para o arquivo de log é definida como 10% do tamanho inicial que, por sua vez, se não for informado, é definido como 1MB. Para uma base OLTP movimentada, utilizar os valores padrão não é uma boa idéia. Imagine quantas vezes seria necessário que o arquivo de log crescesse para ter 1GB, 2GB, 10GB… Além disso, milhares de VLFs seriam criados, podendo causar a fragmentação do arquivo de log. Sobre este assunto, você encontra um material bem didático neste excelente post [3] do Leandro Ribeiro.

“Ok. Mas qual é o problema em ter vários VLFs?” Bom, para começar, aumento do tempo de recuperação em caso de desastre como mostra esse artigo [4] do Graham Kent da Microsoft. Também pode afetar a performance dos INSERTs, UPDATEs e DELETEs como demonstra Linchi Shea em seu post [5]. Dentre outras implicações de ter VLFs demais, temos o impacto no backup de log como irei mostrar.

“Calma aí! Quantos são VLFs demais?” Aí vou recorrer à máxima do meio SQL Server: Depende. Não vou lhe dizer qual é o número ideal de VLFs pois esse número varia de acordo com o ambiente, mas você pode ler as recomendações que a Kimberly Tripp do SQL Skills descreve neste post [6]. No post, ela recomenda VLFs de 500MB para arquivos de logs com 8GB ou mais. Isso dá 16 VLFs se o arquivo fosse dimensionado no momento da criação, ou pouco mais que 16 caso tivesse que reajustar depois que estiver em uso.

“Certo… Mas como eu conto os VLFs?” Para isso você pode utilizar o comando não documentado DBCC LOGINFO. O resultado será uma linha para cada VLF dentro do banco de dados.

Hm... apenas 6 VLFs? Que bom!

Hm… apenas 6 VLFs? Que bom!

Na figura, executei o comando para o msdb e verifiquei que tenho 6 VLFs para este banco. Não vou detalhar o significado de todas as colunas aqui, mas vale ressaltar a coluna FileSize que retorna o tamanho do VLF em Bytes e a coluna Status que indica que o VLF está sendo usado (= 2) ou não (= 0).

No entanto, é bem provável que você queira verificar isso para todas as suas bases de uma vez e pra isso recomendo esse script no post [7] do David Levy. Neste post, ele também dá dicas – e um script – de como reduzir e controlar o número de VLFs do banco.

Agora que estabelecemos o ponto de partida, vamos ao caso. Na instância haviam por volta de 20 bases bem distintas, variando o tamanho dos arquivos de log de poucos MBs até pouco mais de 90GBs. Os backups de log variavam de base para base de acordo com a necessidade, indo de 5 em 5 minutos para as bases mais ativas até de hora em hora para as bases mais tranquilas. O número de backups de log de todas as bases somadas num dia era de pouco mais de 500 e a duração média destes backups beirava os 40 segundos. Isso pode não parecer muito, mas no final do dia todos esses backups somavam quase 6 horas. Em outras palavras, durante um quarto do dia a instância estava realizando backups de log.

Analisando melhor o arquivo de log, foi constatado que o número de VLFs por base variava de poucas centenas até pouco menos de 50 MIL! Será que isso atrapalhava? Na primeira janela de manutenção, reduzimos ao máximo os arquivos de log e aumentamos de volta para o tamanho original de 8000MB em 8000MB (leia o post da Kimberly Tripp). Desta forma, reduzimos os VLFs para poucas dezenas e com isso os backups de log que antes duravam em média 40 segundos, agora duram em média menos de 1 segundo. Aproveitando os resultados, aumentamos a frequência dos backups de log e agora são realizados por volta de mil backups por dia, quase o dobro de antes. O melhor? O tempo gasto com backups de log que era de 6 horas para 500 backups/dia passou para menos de 15 minutos para 1000 backups/dia.

Moral da história: um número excessivo de VLFs pode afetar seus backups de log e muito! Sempre os mantenha sob controle e avalie bem as ocorrências de crescimento do arquivo de log. E aí, qual foi a última vez que você verificou os VLFs?

REFERÊNCIAS

[1] Paul S. Randal – Understanding Logging and Recovery in SQL Server

[2] Jes S. Borland – How SQL Server Works: Log File (Video)

[3] Leandro Ribeiro – Fragmentação do Transaction Log – Parte I

[4] Graham Kent – Slow recovery times and slow performance due to large numbers of Virtual Log Files

[5] Linchi Shea – Performance impact: a large number of virtual log files – Part I

[6] Kimberly L. Tripp – Transaction Log VLFs – too many or too few?

[7] David Levy – A Busy/Accidental DBA’s Guide to Managing VLFs

Anúncios

3 pensamentos sobre “Quanto os VLFs podem afetar o backup de log?

    • Realmente está difícil de ver, Nane!

      Mas os links estão nas palavras que representam a fonte, como “artigo”, “post”, “video”, etc.

      Vou atualizar o post e incluir os links como referência no final!

      Obrigado!

  1. Muito bom o post e as recomendações (excelentes, como o post do Leroy). Soube resumir bem um assunto que costuma ser extenso. Na última pergunta, tenho certeza que muitos coçaram a cabeça: realmente, é um ponto importante e no dia a dia e poucos realmente se importam em administrar também a fragmentação do t-log (acredito eu pelas outras prioridades e demandas diárias e a cultura do tuning reativo). É um ótimo texto de alerta pro que de fato estamos perdendo enquanto deixamos a fragmentação rolar no ambiente.
    []’s

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s