quinta-feira, 31 de janeiro de 2013

Múltiplos arquivos por filegroup

Melhor escrever alguma coisa para vocês saberem que ainda estou por aqui, não é?
Treinamento de internals (errr, Mastering) sempre é massa, muitas perguntas novas, ex-alunos que passaram X meses estudando e voltam com perguntas escabrosas, e um material que não consigo diminuir por dor no coração de deixar alguma coisa bacana de fora. Geek total.
Ontem falávamos de múltiplos arquivos por filegroup, distribuição das operações entre eles, etc., um bom prelúdio para falarmos da tempdb, e aí veio a pergunta: E se eu tiver muitos arquivos dentro do mesmo filegroup, ajuda ou atrapalha o desempenho?
Ter mais de um arquivo pode sim te beneficiar em diversas operações (como um backup ou restore) por conta de você ter mais threads para te ajudar ou ajudar na utilização proporcional/balanceamento, mas a discussão encaminhou para: o overhead para o SQL Server identificar o espaço livre em cada arquivo para uma distribuição proporcional é significativo?
Fiz o teste na minha máquina com um disco local tomando os cuidados: não ter crescimento do arquivo de log (está em auto truncate mode), não ter crescimento dos arquivos de dados (mesmo com IFI) e executar duas baterias de teste para cada banco com a máquina relativamente parada. Eis o resultado para uma inserção de 100.000 registros (um registro por página):
·         5 arquivos de 200MB = 1:24 / 1:20
·         50 arquivos de 20MB = 1:57 / 1:50
·         100 arquivos de 20MB = 2:12 / 2:20
·         500 arquivos de 20MB = 4:12 / 3:59
De aproximadamente 1 minuto e 20 segundos para quatro minutos é uma diferença bem significativa. Então existe um overhead, mesmo para um workload simples, e quanto mais “exagerado” você for com múltiplos arquivos dentro do mesmo filegroup, maior o impacto. O problema aqui é identificar onde é o overhead, o caso mais provável é que seja uma saturação do meu subsistema de I/O e não um overhead no balanceamento, mas independente da causa fica o aviso. 

Note que esse teste foi focado em mostrar o impacto de múltiplos arquivos. Vale ressaltar que ter mais de um arquivo de dados pode beneficiar o seu workload sim! O Paul Randal já escreveu sobre isso: http://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/ e em seu benchmark 8 arquivos apresentou o melhor desempenho. Então a regra é: faça testes em seu ambiente para encontrar a melhor configuração.
Se quiser brincar no seu ambiente, abaixo está o script que eu criei. E não deixe de compartilhar se encontrar algo novo ou que passou despercebido neste post.
USE master
GO

IF (DB_ID('MultipleFiles') IS NOT NULL)
      DROP DATABASE MultipleFiles
GO

CREATE DATABASE MultipleFiles
  ON PRIMARY
      (NAME = N'MultipleFiles',
      FILENAME = N'C:\Temp\MultipleFiles.mdf',
      SIZE = 15MB,
      MAXSIZE = 1GB,
      FILEGROWTH = 10MB)
  LOG ON
  (NAME = N'MultipleFiles_Log',
      FILENAME = N'C:\Temp\MultipleFiles_log.ldf',
      SIZE = 50MB,
      MAXSIZE = 300MB,
      FILEGROWTH = 100MB)    
go

ALTER DATABASE MultipleFiles
ADD FILEGROUP FG01
GO

DECLARE @i INT = 1
DECLARE @FileSize VARCHAR(10) = '20MB'
DECLARE @sql VARCHAR(8000)

WHILE @i <= 500
BEGIN

      SET @sql = '
      ALTER DATABASE MultipleFiles
      ADD FILE (NAME = N''MultipleFiles_Data' + CAST(@i AS VARCHAR) + ''',
            FILENAME = N''C:\Temp\MultipleFiles_Data' + CAST(@i AS VARCHAR) + '.ndf'',
            SIZE = ' + @FileSize + ',
            MAXSIZE = 1GB,
            FILEGROWTH = 10MB)
      TO FILEGROUP FG01'

      -- PRINT @sql
      EXEC (@sql)

      SET @i = @i + 1
END
GO

ALTER DATABASE MultipleFiles
MODIFY FILEGROUP FG01 DEFAULT
GO

use MultipleFiles
go

SELECT * FROM sys.database_files
SELECT * FROM sys.filegroups
go   

IF (OBJECT_ID('TabelaTeste') IS NOT NULL)
      DROP TABLE TabelaTeste
go

CREATE TABLE TabelaTeste
(Codigo INT IDENTITY NOT NULL,
 Texto CHAR(8000) NOT NULL,
 Hora DATETIME2 NOT NULL DEFAULT SYSDATETIME())
go

-- 5x 200MB = 1:24 / 1:20
-- 50x 20MB = 1:57 / 1:50
-- 100x 20MB = 2:12 / 2:20
-- 500x 20MB = 4:12 / 3:59
INSERT INTO TabelaTeste (Texto) VALUES ('Sr. Nimbus')
GO 100000

Abraços
sr. Nimbus Serviços em Tecnologia - www.srnimbus.com.br

3 comentários:

  1. Agora me surgiu uma duvida.

    Você fez o teste todo em cima de um mesmo disco correto? E mudando para discos fisicos diferentes essa criação, teríamos alguma diferença?

    Existe algum tipo de wait type especifico para quando ele faz esse round-robin das informações?

    Abraços,
    Marcos Freccia

    ResponderExcluir
  2. A minha dúvida é a mesma do Marcos Freccia, porém, além de ter discos físicos diferentes, teria também algumas controladoras de discos a mais, para que não haja sobrecarga na controladora.

    Abraços,
    Cleber K. N. Yamamoto

    ResponderExcluir
  3. Sim, aqui é um único arquivo/disco. Com um subsistema maior de I/O com certeza os resultados serão diferentes, mas mesmo assim o número de arquivos deve ser exagerado. Não tenho neste momento um ambiente semelhante para testar, quem sabe uma boa alma não nos ajuda e manda o resultado?

    Com relação ao wait type, para a parte de escrita em disco basicamente você vai ver PAGEIOLATCH_?? e WRITELOG, com relação a espera no round Robin você não deve ver, pois efetivamente a thread não estará parada, mas sim trabalhando para descobrir onde vai colocar as páginas.

    []s
    Luti

    ResponderExcluir