Recentemente analiando o desempenho de um banco de dados no período de processamento de algumas rotinas de fechamento diário, notei um alto volume de waits em operação de commit, tendo os eventos “log file sync” e “log file parallel write” com tempo médio de resposta elevado.
Cenário Inicial
No top 5 eventos com maior representatividade no tempo de atividade do banco de dados, log file sync aparecia com mais de 330 mil ocorrênicas e um tempo médio de 27 ms. Aqui tinha dois objetivos, um seria tentar melhorar o tempo médio de resposta de uma escrita do LGWR e a outra seria reduzir a quantidade de commits que a rotina executava (mais difícil, ninguém queria mexer).
O banco de dados é um 10gR2 (10.2.0.4) rodando em HP-UX, sem ASM, com todos os datafiles/controlfiles/logfiles em File System). Sabendo que não usava ASM, a primeira coisa que chequei foi o parâmetro filesystemio_options (em ASM o Oracle usa Direct I/O independentemente deste parâmetro), que controla o comportamento de operação de I/O no Oracle Database, tendo os seguintes valores possíveis:
- none: (default) – Emite uma requisição de I/O normal para o sistema operacional, síncrona e passando pelo cache do File System;
- directIO: Emite uma requisição de I/O sem passar pelo cache do File System;
- asynch: Emite múltiplas requisições de I/O de forma paralela, sem que uma precise aguardar a resposta da outra;
- setall: É equivalente a directIO + async, onde o Oracle emite múltiplas requisições de I/O de forma paralela e não passa pelo cache do File System
O valor recomendado pela Oracle e o que normalmente configuro em ambientes que suportam esses recursos é SETALL. Até então já havia configurado esse parâmetro em diversos ambientes em Oracle Linux, onde os File Systems mais utilizados já suportam Direct I/O e Async I/O sem qualquer configuração adicional a nível de SO (xfs, ext4, etc), precisando apenas configurar o parâmetro filesystemio_options no banco de dados. Mas, em HP-UX era a primeira vez implementando essa modificação e logo notei que não era tão simples como no Linux.
Configuração
Revisei a documentação e algumas notas no MOS sobre o tema, e o documento que forneceu uma abordagem simples e direta foi: Slow I/O On HP Unix (Doc ID 457063.1)
Em resumo, no HP-UX precisamos incluir dois parâmetros na montagem do File System (comando mount ou editando o fstab):
mincache=direct,convosync=direct
O parâmetro mincache controla as operações de leitura, o valor direct indica que o SO não vai ler dados do cache do File System, enquanto o parâmetro convosync controla as operações de escrita.
No fstab, a montagem dos File Sytem foi configurado conforme exemplo a seguir:
/dev/vg01/lvol3 /ora2 vxfs nodatainlog,mincache=direct,convosync=direct 0 2 /dev/vg01/lvol4 /ora3 vxfs nodatainlog,mincache=direct,convosync=direct 0 2
Para efetivar a alteração, como esperado, é necessário desmontar e montar os File Systems novamente (com a instância Oracle baixada, claro).
No Oracle, podemos alterar o parâmetro antes de reiniciar a instância e ter apenas um único restart para ambas as alterações:
SQL> ALTER SYSTEM SET filesystemio_options = 'SETALL' SCOPE=SPFILE;
Resultado
Após a modificação e reinicialização da instância (e dos File Systems), o comportamento do banco de dados durante o processamento da mesma rotina ficou dessa forma:
Apesar de proporcionalmente ainda haver muita contenção em operaões de commit, o tempo médio de resposta de “log file sync” reduziu para 15ms e “log file paralle write” diminuiu para 9ms. Da perspecetiva do negócio, a execução do processo reduziu de 6 para 3 horas. Da perspectiva do banco de dados, o alto volume de wait é inevitável, tendo em vista que o processo em questão executa um commit para cada registro alterado (e não consideraram mudar isso), mas foi possível obter ganho significativo com a configuração do Direct I/O.