Certas consultas SQL com desempenho muito ruim no ambiente hospedado ESXI

Question

Recentemente, configuramos uma nova máquina com 8 CPUs dual-core, 20 GB de RAM e 3 unidades de 1 TB configuradas em um RAID de algum tipo, resultando em 2 unidades de 1 TB que realmente conseguimos usar (I ' não sou o cara do hardware aqui). Ele é configurado como um host ESXi e temos vários ambientes de teste configurados nele. Os testes atuais estão sendo executados no Windows 2003 de 64 bits com o SP3 de 64 bits do SQL Server 2005 Standard. De todos os relatórios, esse sistema deve hospedar ambientes com desempenho melhor do que a nossa configuração anterior e, no entanto, certas tarefas estão com desempenho muito pior. Eu encontrei um script SQL específico que executa de maneira confiável muito lentamente sob certas condições, o que não consigo entender. O script SQL é uma simples série de 1700 instruções UPDATE que começam assim:

UPDATE SrfItem SET fkSrfItem = 5 WHERE id = 4
UPDATE SrfItem SET fkSrfItem = 8 WHERE id = 7
UPDATE SrfItem SET fkSrfItem = 10 WHERE id = 9

Descobri que, se eu seguir o procedimento a seguir em um desses ambientes virtuais, a execução do script demora de 9 a 12 segundos:

Caso de teste # 1

1. Restaurar banco de dados de teste a partir de um backup no ambiente virtual do SQL Server
2. Conectar-se ao banco de dados localmente
3. Executar script - essa etapa leva 9 segundos

O mesmo procedimento na minha área de trabalho executou a etapa 3 em menos de um segundo.

Caso de teste # 2

1. Restaurar banco de dados de teste a partir de um backup no ambiente físico do SQL Server
2. Conectar-se ao banco de dados localmente
3. Executar script - essa etapa leva menos de um segundo

Mas a execução do script em uma transação é rápida

Caso de teste nº 3

1. Restaurar banco de dados de teste a partir de um backup no ambiente virtual do SQL Server
2. Conectar-se ao banco de dados localmente
3. Adicione "BEGIN TRAN" no início do script
4. Adicione "COMMIT TRAN" no final do script
5. Executar script - essa etapa leva menos de um segundo

O que eu acho interessante é que ele ainda corre devagar mesmo depois de eu executar uma transação e revertê-la

Caso de teste # 4

1. Restaurar banco de dados de teste a partir de um backup no ambiente virtual do SQL Server
2. Conectar-se ao banco de dados localmente
3. Adicione "BEGIN TRAN" no início do script
4. Adicione "ROLLBACK TRAN" no final do script
5. Executar script - essa etapa leva menos de um segundo
6. Execute apenas a parte do script que não inclui a transação - essa etapa leva 9 segundos.

Eu executei testes em um sistema virtual com Windows 2003 de 32 bits e SQL 2005 de 32 bits e em um sistema virtual com Windows 2008 de 64 bits e SQL 2008 de 64 bits. Eu tenho executado testes em um sistema físico com o Windows 2003 e SQL 2005 e em um sistema físico com o Windows 7 de 64 bits e SQL 2008 R2 de 64 bits. Todos os sistemas virtuais que experimentei exibem essa lentidão e são hospedados no novo ambiente do ESXi. Todos os sistemas físicos não exibem essa lentidão.

Alguém pode me ajudar a entender o que está acontecendo aqui? Temo que problemas de desempenho semelhantes estejam afetando outras áreas e devemos reconfigurar algo nos ambientes host ou guest. A única coisa em que podemos pensar até agora é desligar o hyperthreading no BIOS da máquina host para corresponder à configuração de outro ambiente virtual e seu host, onde não conseguimos ver o comportamento lento (não observei o teste em o outro ambiente virtual e host onde não foi lento). Isso poderia criar uma diferença de desempenho tão grande?

Editar: Após algumas revisões da minha pergunta e da primeira resposta, concordo que o que consegui demonstrar é provavelmente uma diferença no desempenho da latência de E / S entre nossos ambientes físico e virtual. Eu também percebi que deveria ter fornecido alguns outros detalhes: essas imagens estão usando o provisionamento thin e têm dois ou três instantâneos embaixo delas. Isso poderia afetar essa estatística tão significativamente? A questão agora é: é normal que essa estatística seja tão drasticamente diferente entre ambientes virtuais e ambientes físicos? Devo ser capaz de otimizar isso no ambiente ou na configuração de SQL, ou cabe ao próprio software ser escrito de forma mais otimizada para sistemas virtuais com extrema latência de E / S?

O cliente vSphere informa que a latência de gravação no disco virtual é de 11 a 40 ms com uma média de 21 ms. Essa é uma estatística útil? Isso é extremo?

Editar: Parece que o nosso hardware (DL380 G6) tem problemas de desempenho, conforme descrito em link e só precisamos fazer alguma reconfiguração para melhorar o desempenho. Aceito a resposta que nos levou na direção correta de ver que a latência de E / S do disco era o problema.

    

Answer 1

Para resumir:

• no seu servidor real, você pode fazer 1700 atualizações de tabela + 1700 confirmações em menos de um segundo,
• no seu servidor virtual, você pode fazer 1700 atualizações de tabela + 1700 confirmações em 9 segundos,
• no seu servidor virtual, você pode fazer 1700 atualizações de tabela + 1 confirmação em menos de um segundo.

Então, parece-me que o seu problema pode ser redefinido como "em um servidor real, posso fazer 1700 commits em menos de um segundo, mas o desempenho cai dez vezes no meu servidor virtual".

Qual é a diferença entre 1700 atualizações de tabela e 1700 confirmações? As atualizações da tabela são totalmente armazenadas em cache e não dependem de E / S de disco. Com commits isso é bem diferente. Pela própria natureza dos bancos de dados transacionais, o mecanismo de banco de dados tem que ter certeza de que o commit foi salvo no disco (salvo em um arquivo de log), antes mesmo de começa a comprometer a próxima transação. Portanto, para cada um dos 1700 commits, é preciso esperar a ida e volta inteira. Resumindo, em seu cenário a latência de E / S desempenha um papel muito importante, e deve ser analisada (não confunda a latência com a taxa de E / S ou throughput em bytes; estes três são todos animais totalmente diferentes; são sempre sintonizado separadamente).

É um bom plano testar seu armazenamento com o IOMeter. Ele trava na inicialização porque tenta preencher todo o disco com o arquivo de teste. Apenas espere até que o arquivo cresça até um valor considerável e reinicie o IOMeter, ele funcionará corretamente com o arquivo de teste "incompleto".

    

Answer 2

Seus esclarecimentos lançam alguma luz sobre o assunto.

Um pacote SATA RAID 5 de 3 unidades não é uma configuração de disco ideal para o desempenho de gravação. Cada IO de escrita incorre [até] 4 E / S do disco (leia o bloco atual, leia a paridade atual, escreva novo bloco, escreva nova paridade). Com efeito, isso transforma seus três discos de 7200 rpm em um disco com desempenho semelhante a um único disco de 5400 rpm, supondo que suas unidades de base tenham 7200 rpm.

Em segundo lugar, você diz que possui vários snapshots ativos nas VMs do SQL. Os snapshots do VMware ESXi incorrem em uma sobrecarga que não é trivial - dependendo do que você está fazendo, haverá uma sobrecarga de IO de 50 a 100% quando você tiver snapshots ativos. Isso afeta tanto as leituras quanto as gravações.

Em terceiro lugar, você diz que está usando o provisionamento thin - que tem um impacto no desempenho de IO, mas não é tão significativo quanto os outros dois.

Finalmente, você não diz se há outras VMs em execução no host ESXi - se houver, elas obviamente afetarão o desempenho geral, especialmente com a configuração de disco RAID5 x 1TB SATA.

    

Answer 3

Eu não acho que seu teste seja tão robusto para determinar que há um problema com o sistema virtualizado. Um teste de um segundo não dá tempo suficiente para forçar o sistema a mostrar quaisquer gargalos reais.

Existem muitas partes móveis em um mundo virtualizado e no SQL Server. Eu acho que o disco IO é um grande jogador aqui, mas também RAM. O ESX pode fornecer e receber RAM do convidado sob demanda e, às vezes, leva alguns segundos para que o ESX reaja, produzindo pausas curtas. Se um servidor estiver sob uma certa carga constante, o ESX estabiliza a RAM, mas se o teste for curto e estourar, pode levar algum tempo para aumentar a velocidade.

Antes de começar a jogar o bebê com a água do banho, faça testes mais longos e monitore com o ESX e realize o uso de RAM, latências de E / S do disco, comprimentos de fila de CPU etc. Um bom teste levaria de 30 a 60 segundos para ser executado. máquina física, e eu esperaria que a máquina virtual estivesse dentro de 150% disso.