Discos dedicados de Dados e Log

 

Nas visitas a clientes, é muito comum ouvir perguntas sobre como otimizar a performance dos discos.

A primeira recomendação que fazemos é para manter os arquivos de Dados e Log em discos separados. Por exemplo:

• Disco L: arquivos LDF (Log)
• Disco R: arquivos MDF/NDF (Dados)
• Disco S: arquivos MDF/NDF (Dados)
• Disco T: arquivos MDF/NDF (Dados)

Em um disco de 15k RPM, o tempo médio de escrita é de 5,5ms. Se colocarmos o arquivo de Log em um disco dedicado, é possível obter tempos de 2ms por escrita!

Vamos aos cenários:
 

Cenário 1: Leitura de Arquivos de Dados

Consideramos as operações de leitura de dados, que cada hora é lida uma página diferente e em posição distinta no disco. Para cada requisição de leitura, será necessário posicionar no track e, em seguida, posicionar no setor.  

Operação: Leitura	Tempo (ms)
Posicionar o track	3,2
Posicionar o setor	2,0
Tempo Total	5,2

O tempo médio para a operação completar será de 5,2ms.

Cenário 2: Escrita no Transaction Log

As escritas no arquivo de transaction log são sequenciais. Isso significa que, após o primeiro posicionamento do cabeçote no track, as operações seguintes precisam apenas realizar o posicionamento de setor.
 

Operação: Escrita	Tempo (ms)
Posicionar o setor	2.0
Tempo Total	2.0

Por isso, dizemos que as escritas no transaction log tiram vantagem do fato de serem sequenciais e o tempo médio da operação é de apenas 2ms.

Cenário 3: Arquivos de Dados e Log no mesmo disco físico

Quando colocamos os arquivos de Data (MDF/NDF) e Log (LDF) no mesmo disco físico, observamos que o tempo de escrita em log aumenta para 5,5ms. Isso ocorre porque as operações de leitura/escritas são intercaladas e o cabeçote deve sempre ser reposicionado, deixando de tirar proveito do fato das escritas em log serem sequenciais.

Operação: Escrita	Tempo (ms)
Posicionar o track	3,5
Posicionar o setor	2,0
Tempo Total	5,5

Conclusão:

Nunca misture os arquivos de Dados e Log, porque há impacto direto no tempo de escrita do Transaction Log. Como observamos, toda operação de escrita terá a necessidade de reposicionar o cabeçote no track correto, aumentando o tempo de resposta.

Ao colocar o arquivo de Transaction Log em um disco dedicado, teremos uma performance otimizada para as escritas sequenciais.

Comments

• Anonymous
  March 09, 2010
  Aproveitando que você tem comentado sobre performance, e vi noutros posts voce falando sobre o HD, qual a sua avaliação sobre uso de SSD's em servidores? Eu sei que escrita não é o forte dos SSD's, mas em muitos cenários possuímos milhares de leituras aleatórias para cada gravação. []'s Henrique

• Anonymous
  March 10, 2010
  The comment has been removed

• Anonymous
  July 23, 2010
  Fabrício, Em uma análise de discos com várias bases críticas, o que mais compensa no seguinte ambiente: Soponha que eu tenha vários discos para distribuir entre os datafiles e logs e possua tres databases de tamanho igual e com quantidade de uso de disco, cpu, etc apeoximado. Qual seria a divisão ideal em relação à escolha do tipo de RAID ( desconsidere qtd de leitura e escrita de cada um e local da tempdb ) Exxmplo: Seria mais viável monytar um RAID 1+0 com 12 discos e colocar todos os logs no mesmo array ou criar 3 raids 1+0 com 4 discos cada? Mesmo exemplo para datafiles: Mais viável RAID 5 com 9 discos para todos os datafiles ou 3 raids 5 sendo cada raid um datafile? Abraço

• Anonymous
  August 08, 2010
  Fabrício, achei interessante a pergunta do DBA SQL. O que você acha do que o mesmo citou?

• Anonymous
  August 09, 2010
  Oi DBA! Não sei o que aconteceu com meu comentário que sumiu e obrigado ao Augusto por relembrar aqui. Simplificando um pouco, é ideal sempre separar os arquivos de LOG e DADOS. Portanto, coloque os arquivos em grupos distintos de disco. Por exemplo, evite colocar todos arquivos debaixo do mesmo volume. Um fator importante sobre o RAID é que o RAID5 apresenta uma performance muito ruim durante o processo de reconstrução, tornando a performance (quase) inaceitável se ocorrer uma falha de disco. Por esse motivo, adotaria preferencialmente o RAID1+0. Uma possibilidade seria usar um RAID1 (2 discos) para armazenar os logs, enquanto que os demais discos formam um RAID1+0. Abraços, Fabricio Correção: Como mencionado pelo Demétrio Silva, se os discos estiverem em um storage Mid/High-end, então a história muda. A dependência de RAID1 ou 5 fica menos importante. Logo postarei um artigo mais detalhado sobre o assunto.

• Anonymous
  August 09, 2010
  Olá Fabrício, Eu já tinha visto este post e achei muito interessante. Inclusive comentei com alguns amigos meus (na verdade figuras em SQL Server). Sabemos que hoje em dia existem Storages que possuem um cache tão alto e sequer podemos especificar o tipo de RAID ( neste tipo de storage é indiferente. Sei que a IBM já possui este tipo ). Daí, vai a pergunta, será que ainda vale a pena separar databases críticos, tabelas grandes, arquivos de dados e logs, tembdb, etc dos demais databases? De maneira geral, seguindo a teoria dos discos, quanto mais volumes isolados tivermos, maior a performance, no entanto, vejo isso acontecer muito na teoria e não muito na prática. Tivemos algumas experiências em clientes com bases críticas onde o isolamento de bases de sistema, tempdb, e alocação de índices, tabelas grandes, etc foi de grande proveito. Daí, gostaría de sua opinião sobre a relação custo benefício em isolar tabelas, índices, etc. Imagine uma situação onde apenas uma das bases possua datafiles, índices e logs isolados? Isso resultaria em ao menos 3 arrays separados apenas para esta base ( até onde sei e realizei testes, esse seria o cenário ideal. Um exemplo seria p particionamento, particionar tabelas e manter no mesmo array não surte efeito algum. No entanto, o custo para um isolamento neste porte é alto e gostaría que você compartilhasse seus conhecimentos e experiências conosco). Abraço, Demétrio Silva MCP | MCTS | MCITP | MCT SQL Server

• Anonymous
  August 11, 2010
  Excelente comentário. Vou inclusive fazer uma correção no meu comentário anterior: se tiver storage envolvido na história, então o cenário muda completamente. Vou adicionar seus comentários em um Post futuro. Respondendo rapidamente sua pergunta: sempre separar DADOS e LOG. Utilizar diferentes Filegroups para tabelas e índices é uma cultura do Oracle ("coloque a tabela e índice em diferentes tablespaces"). Eu nunca vi ganhos nisso no mundo SQL - na verdade, prefiro sempre misturar os dados com índices e, assim, tentar aumentar o número de spindles dentro do mesmo array de discos. Obrigado pela sua mensagem. Fabricio

• Anonymous
  August 11, 2010
  Respondendo a parte do storage com cache alto: a decisão final fica com o fornecedor do storage (no seu caso, a IBM). Se eles disserem que usam RAID6 e isso é suficiente, então temos que aceitar. Isso é mais um tema para post futuro. :)

• Anonymous
  February 12, 2011
  Fabrício, Se eu tenho mais de um arquivo de log no mesmo disco a leitura/escrita no log deixa de ser sequencial ou não ? Abraço

• Anonymous
  February 13, 2011
  Olá Alexandre, As escritas de log continuam sequenciais, mas são intercaladas com leituras aleatórias dos dados. Isso provoca uma degradação de desempenho na escrita de log, podendo duplicar o tempo gasto. Abraços, Fabricio

• Anonymous
  November 28, 2012
  Boa tarde Fabricio estou trabalhando com storages da EMC² aqui em minha empresa, e queria tirar uma duvida com vc. estarei dividido meu storage em dois volumes, ex: C: D: em um sera armazenado os meus arquivos mdf e no outro os meus ldf, saberia me informar qual dos dois devem ter prioridade de velocidade, e no que se refere a tamanho como devo dividi-los? Meu storage terá uma capacidade minima de 13 TB, e meu fornecedor irá agilizar cada volume de acordo com o que eu solicitar. desde já agradeço!

• Anonymous
  December 16, 2012
  Olá Douglas, respondi por email. Abraços, Fabricio