A desativação do hyperthreading melhorará o desempenho em nossa instalação do SQL Server

Question

Relacionado a: Conhecimento atual sobre SQL Server e Hyperthreading

Recentemente, atualizamos nosso servidor de banco de dados do Windows 2008 R2 de um X5470 para um X5560 . A teoria é que ambos os processadores têm desempenho muito semelhante, se o X5560 é um pouco mais rápido.

No entanto, o desempenho do SQL Server 2008 R2 foi muito ruim no último dia e o uso da CPU foi bastante alto.

A expectativa de vida da página é enorme, estamos obtendo quase 100% de cache para as páginas, então a memória não é um problema.

Quando eu corri:

SELECT * FROM sys.dm_os_wait_stats 
order by signal_wait_time_ms desc

Eu tenho:

wait_type                                                    waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms
------------------------------------------------------------ -------------------- -------------------- -------------------- --------------------
XE_TIMER_EVENT                                               115166               2799125790           30165                2799125065
REQUEST_FOR_DEADLOCK_SEARCH                                  559393               2799053973           5180                 2799053973
SOS_SCHEDULER_YIELD                                          152289883            189948844            960                  189756877
CXPACKET                                                     234638389            2383701040           141334               118796827
SLEEP_TASK                                                   170743505            1525669557           1406                 76485386
LATCH_EX                                                     97301008             810738519            1107                 55093884
LOGMGR_QUEUE                                                 16525384             2798527632           20751319             4083713
WRITELOG                                                     16850119             18328365             1193                 2367880
PAGELATCH_EX                                                 13254618             8524515              11263                1670113
ASYNC_NETWORK_IO                                             23954146             6981220              7110                 1475699

(10 row(s) affected)

Eu também corri

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS (
   SELECT 
        wait_type, 
        wait_time_ms / 1000. AS [wait_time_s],
        100. * wait_time_ms / SUM(wait_time_ms) OVER() AS [pct],
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [rn]
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE',
    'SLEEP_TASK','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE',
    'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH',
    'BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE',
    'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))

SELECT W1.wait_type, 
    CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
    CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
    CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

E tenho

wait_type           wait_time_s     pct  running_pct
CXPACKET              554821.66   65.82        65.82
LATCH_EX              184123.16   21.84        87.66
SOS_SCHEDULER_YIELD    37541.17    4.45        92.11
PAGEIOLATCH_SH         19018.53    2.26        94.37
FT_IFTSHC_MUTEX        14306.05    1.70        96.07

Isso mostra grandes quantidades de tempo sincronizando consultas envolvendo paralelismo (alto CXPACKET). Além disso, muitas dessas consultas de problemas estão sendo executadas em vários núcleos (não temos dicas do MAXDOP em nenhum lugar do nosso código)

O servidor não está sob carga há mais de um dia. Estamos experimentando uma grande quantidade de variação com execuções de consulta, normalmente muitas consultas parecem ser mais lentas do que eram no nosso servidor de banco de dados anterior e a CPU é realmente alta.

A desativação do Hyperthreading ajudará a reduzir o uso de CPU e aumentar o rendimento?

    

Answer 1

Eu ainda sinto que testar sua carga de trabalho específica , de acordo com a resposta original, é a única maneira de ter certeza. Não é uma resposta ideal quando você está tentando ajustar um sistema de produção (então eu perguntaria se era possível obter um testbed idêntico em sistemas em que tanto o desempenho quanto a disponibilidade realmente importassem), mas é o único em que estou realmente confortável com.

Podemos falar sobre a teoria de que o Hyperthreading deve ou não prejudicar ou melhorar as coisas em geral (acho mais provável que doa mais do que ajudar em servidores, então, para uma implantação "genérica", provavelmente o desabilitarei), mas há apenas uma maneira de ver com certeza se isso fará diferença no seu caso específico, e isso é tentar e ver.

    

Answer 2

Concordo que

• no melhor a recomendação é "tente o HyperThreading em sua carga de trabalho e veja o que acontece". Estamos fazendo isso agora enquanto digito e ... não é bom!
• provavelmente você deve começar sempre com o HyperThreading desativado, pois isso é mais seguro

Parece que devemos ajustar duas coisas:

1. MAXDOP (Graus Máximos de Paralelismo). Tudo o que eu li indica que ter este ilimitado provavelmente é uma má idéia, e a documentação da Microsoft diz:

   Setting this option [MAXDOP] to a larger value [than 8] often causes unwanted resource consumption and performance degradation.

   qualquer valor superior a 8 geralmente não é recomendado ... por isso, defini-o como 4 por enquanto. Foi zero (sem limites) inicialmente.

2. Limite de custo para paralelismo. Aparentemente, o padrão de 5 aqui é considerado um padrão bem baixo, de acordo com algumas postagens de SQL MVP que eu encontrei - nós podemos ajuste-o para reduzir o quanto de paralelismo é tentado pelo agendador.

Mas honestamente, estes parecem soluções alternativas; Acho que a verdadeira solução para nossa carga de trabalho (índice de texto completo) é desabilitar o HT.

    

Answer 3

Anandtech descobriu que, com a pura carga de leitura, doía um pouco, e com uma carga pesada de escrita, era uma pequena vitória. Eu não vi nada que me fizesse pensar que isso te faria um acerto muito pior do que -5%, ou uma vitória muito melhor do que 15%. Note que com um Atom, é uma grande vitória, mas isso é uma cpu muito estranha.

Tudo o que você mudou foi o cpu? Você passou de 12 MB de cache e 4 threads, então 3 MB de cache por thread, 8 MB de cache e 8 threads, então 1 MB por thread. Agora, isso é simplista demais, mas aposto que isso é o que está matando você, você costumava executar consultas no cache e agora executá-las da RAM porque elas precisam de mais de 1 MB, mas menos de 3 MB. Desligar o HT provavelmente ajudará, mas eu voltaria ao antigo processador. Desligue o HT e você obtém 2MB por thread, mas se a carga de trabalho se debater com isso, não ajudará. Pode muito bem ser que a antiga CPU do cache de 12MB seja extremamente mais rápida para sua carga de trabalho.

Eu tentaria desligar o HT e ver se isso é uma melhoria, mas suspeito que o cache é o melhor para a sua carga de trabalho, e você pode precisar voltar ao chip de 12 MB.

    

Answer 4

O Hyperthreading é, na melhor das hipóteses, apenas uma maneira de abstrair a troca de tarefas do sistema operacional e colocá-lo on-die, com acesso direto ao cache L1 e L2, o que torna a troca de tarefa um carregamento mais rápido.

Testes com VMWare indicaram que a desativação do HT não fazia diferença perceptível sob carga padrão e um aumento de 5% sob carga pesada, devido ao fato de que o ESXi é inteligente o suficiente para saber a diferença entre o thread "real" e o " Fake "thread (há um lote mais do que isso, mas isso é em termos de laymens). O SQL Server 2005 não é tão inteligente, mas combinado com um sistema operacional atualizado, deve haver pouca vantagem em desabilitar o HT.

Tudo o que disse, eu concordo com Ronald que provavelmente será seu cache L2. Uma queda de 33% no tamanho do cache é substancial, e quando especificamos nossos SQL Servers, sempre procuramos pelo cache a velocidade do clock bruto a cada vez.

    

Answer 5

Com base em minha experiência, o HT estava fazendo com que as operações de E / S levassem uma eternidade nos meus nós ativos em um cluster do Windows 2008 R2 (executando o SQL Server 2008 R2). Um fato interessante foi que não foi refletido nas estatísticas de espera nem no pssdiag que eu corri para o suporte da Microsoft.

A maneira que eu notei baixa E / S foi apenas observando os contadores do sistema operacional para o disco físico. Como Sam apontou, eu escrevi sobre isso aqui e aqui

Se você não tiver problemas de E / S e estiver ligado à CPU, sugiro que comece assim:

Identifique quais processos e blocos T-SQL estão causando a maior utilização da CPU. Em nossa experiência, depois que corrigimos o problema com a E / S (desativando o HT), identificamos código que estava funcionando horrivelmente em 2008 R2 e indo bem em 2005. Escrevi sobre isso aqui .

Enquanto sob carga alta, execute sp_whoisactive de Adam Machanic. Você pode baixá-lo de aqui . Estávamos experimentando uma utilização muito alta da CPU devido à quantidade excessiva de leituras lógicas (20 milhões por consulta) devido a um plano realmente ruim. Nossos processos estavam realizando associações anti-semi com tabelas que foram particionadas.

Minha próxima recomendação é executar o profiler para identificar um conjunto de códigos T-SQL que sejam altos nas leituras lógicas de CPU e E / S.

Com os passos acima, conseguimos ajustar os processos ofensivos e passar de uma utilização sustentada da CPU de 85% para quase zero.

Boa sorte e por favor, sinta-se à vontade para me dar uma dica se encontrar uma solução, já que gostaria de adicionar o caso ao meu blog.

Obrigado

Oscar

    

Answer 6

Se o HT é bom ou ruim, é difícil de definir.

Isso realmente depende do padrão de carga do servidor com base na experiência e na leitura. Ou seja, quando afeta o desempenho, o faz mal : caso contrário, você não o notará.

A teoria que eu li é que os threads compartilham o cache, o que significa que sob condições adversas cada thread pode sobrescrever o cache do outro thread. Se você não tiver muito paralelismo, ou se sua carga for muitas consultas curtas, isso pode não afetar você.

Eu tentei com o MAXDOP e a afinidade do processador (de volta à minha última função real de DBA no SQL Server 2000), mas nunca consegui encontrar nada conclusivo: mas apenas para minha loja na época.

Como um teste rápido, você pode definir a afinidade do processador para usar somente núcleos físicos (os números mais baixos) e ver o que acontece.

No entanto, no máximo, você perde metade dos seus núcleos. Hoje em dia isso pode não importar em comparação com o que eu estava jogando há alguns anos atrás, quando era 2 vs 4 ou 4 vs 8. Agora é 8 vs 16 ou 16 vs 32.

Editar: Um teste de Slava Oks

    

Answer 7

Infelizmente, eu não acho que você vai ter uma resposta mais definitiva do que "tente desligar o hyperthreading e ver se isso ajuda".

Apesar da resposta útil de Jonathan no meu tópico original (que você relacionou em sua pergunta), nunca consegui obter nenhuma evidência definitiva sobre o impacto do HT nos servidores específicos que eu estava investigando. No meu caso, os servidores já estavam programados para substituição, então simplesmente deixamos esses substitutos "resolverem o problema", por assim dizer.

Meu conselho:

Tente uma configuração de nível máximo de paralelismo no nível do servidor de 1 . Paralelismo em SQL é mais útil para consultas maiores e mais longas de qualquer maneira, e sua carga (eu suponho) consiste em um número maciçamente alto de consultas menores de qualquer maneira. Isso deve eliminar totalmente as esperas do CXPACKET. Isso pode fazer com que determinadas consultas individuais sejam executadas um pouco mais, mas permitir mais "throughput" do total de consultas no servidor.

Eu tive bons resultados fazendo isso em servidores OLTP. Outros tipos de servidores (servidores de relatórios, servidores de processamento, data warehousing) definitivamente precisam do conjunto MAXDOP mais alto.

E só para deixar claro, essa configuração ainda permitiria que o SQL usasse vários threads para cada tabela individual em um JOIN, então você não está realmente eliminando o paralelismo por completo.

Pelo menos vale a pena tentar, já que essa alteração de configuração entra em vigor imediatamente e nem mesmo exige que você reinicie o serviço SQL: link
Isso significa que você pode voltar imediatamente se as coisas começaram a ir para o inferno.

Desativar o hyperthreading no BIOS exigiria uma reinicialização completa do servidor, portanto, é um pouco mais arriscado.

    

Answer 8

Para o registro, também tivemos um desempenho inesperadamente ruim após uma atualização do servidor. Acabou sendo devido a problemas com a economia de energia da BIOS e da CPU. A configuração padrão no servidor (HP) era ignorar o controle do sistema operacional da velocidade da CPU e usar seu próprio algoritmo. Alterar isso para o controle do SO e atualizar o BIOS resultou em melhorias significativas. Houve algumas notas de lançamento (não é possível encontrá-las agora) que havia um bug da BIOS que estava bloqueando a CPU no estado de desempenho mais baixo.

link