A utilização da CPU afeta o custo do acesso NUMA estrangeiro?

Uma pergunta pesada :-) Vou delinear alguns dos fatores envolvidos. Em qualquer contexto, esses fatores e outros podem variar e produzir um resultado interessante.

Desculpe, eu não era capaz de fazer isso muito mais curto ...

1. CPU acumulada ms vs IO lógica
2. Alinhamento do nó de memória lógica do SQL Server com nós NUMA físicos
3. Contenção de spinlock na alocação de memória do espaço de trabalho de consulta
4. Atribuição de tarefas a agendadores
5. Posicionamento de dados relevantes no buffer pool
6. Colocação de memória física

1. CPU acumulada ms vs IO lógica

   Eu uso gráficos de E/S lógica (ou na terminologia perfmon "pesquisas de página do pool de buffer") em relação à utilização da CPU com muita frequência, a fim de avaliar a eficiência da CPU das cargas de trabalho e procurar casos propensos a spinlock.

   Mas o SQL Server acumula tempo de CPU com muitas atividades além de pesquisas de página e spinlocks:

   • Os planos são compilados e recompilados.
   • O código CLR é executado.
   • As funções são executadas.

   Muitas outras atividades consomem um tempo significativo da CPU sem serem refletidas nas pesquisas de página.

   Nas cargas de trabalho que observo, a principal dessas atividades "intensivas de E/S não lógicas, mas que consomem a CPU" é a atividade de classificação/hashing.

   É lógico: considere um exemplo artificial de duas consultas em uma tabela de hash sem índices não agrupados. As duas consultas têm conjuntos de resultados idênticos, mas um dos conjuntos de resultados é completamente desordenado e o segundo conjunto de resultados é ordenado por mais de uma das colunas selecionadas. Espera-se que a segunda consulta consuma mais tempo de CPU, mesmo que faça referência ao mesmo número de páginas no conjunto de buffers.

   Mais informações sobre a memória do espaço de trabalho e quanto do espaço de trabalho concedido foi usado nestas postagens:

   • http://sql-sasquatch.blogspot.com/2015/08/sql-server-grantedreriedstolen_4.html

   • http://sql-sasquatch.blogspot.com/2015/08/sql-server-workspace-memory-with-twist.html

   • http://sql-sasquatch.blogspot.com/2015/03/resource-governor-to-restrict-max-query.html

2. Alinhamento do nó de memória lógica do SQL Server com nós NUMA físicos

   O SQL Server (desde que incorporou suas estratégias com reconhecimento de NUMA) por padrão cria um nó de memória SQLOS para cada nó NUMA no servidor. À medida que as alocações de memória aumentam, cada alocação é controlada por um dos nós de memória do SQLOS.

   Idealmente, os nós de memória SQLOS estão completamente alinhados com os nós NUMA físicos. Ou seja, cada nó de memória SQLOS contém memória de um único nó NUMA, sem que nenhum outro nó de memória SQLOS também contenha memória desse mesmo nó NUMA.

   No entanto, essa situação ideal nem sempre é o caso.

   A seguinte postagem do blog CSS SQL Server Engineers (também incluída na resposta de Kin) detalha o comportamento que pode levar a alocações persistentes de memória entre nós NUMA para os nós de memória SQLOS. Quando isso acontece, o impacto no desempenho pode ser devastador.

   • http://blogs.msdn.com/b/psssql/archive/2012/12/13/how-it-works-sql-server-numa-local-foreign-and-away-memory-blocks.aspx

   Houve algumas correções para o caso particularmente doloroso de referência de nó cruzado NUMA persistente. Provavelmente outros além desses dois também:

   • CORREÇÃO: problemas de desempenho ocorrem em ambientes NUMA durante o processamento de página externa no SQL Server 2012 ou SQL Server 2014

   • CORREÇÃO: problemas de desempenho do SQL Server em ambientes NUMA

3. Contenção de spinlock durante a alocação de memória do espaço de trabalho

   É aqui que começa a ficar divertido. Já descrevi que o trabalho de classificação e hash na memória do espaço de trabalho consome CPU, mas não é refletido nos números de pesquisa do bpool.

   A contenção Spinlock é outra camada para esta diversão particular. Quando a memória é roubada do buffer pool e alocada para uso em uma concessão de memória de consulta, o acesso à memória é serializado com um spinlock. Por padrão, isso ocorre com um recurso particionado no nível do nó NUMA. Portanto, todas as consultas no mesmo nó NUMA usando a memória do espaço de trabalho podem experimentar contenção de spinlock ao roubar memória contra concessões. É muito importante observar: esse não é o risco de contenção "uma vez por consulta", como seria se o ponto de contenção estivesse no momento da concessão real. Em vez disso, é quando a memória é roubada contra a concessão - portanto, uma consulta com uma concessão de memória muito grande terá muitas oportunidades para contenção de spinlock se usar a maior parte de sua concessão.

   O sinalizador de rastreamento 8048 faz um ótimo trabalho ao aliviar essa contenção, particionando ainda mais o recurso no nível principal.

   A Microsoft diz "considere o sinalizador de rastreamento 8048 se 8 ou mais núcleos por soquete". Mas ... não é realmente quantos núcleos por soquete (desde que sejam múltiplos), mas sim quantas oportunidades de contenção no trabalho que está sendo feito em um único nó NUMA.

   Nos processadores AMD colados (12 núcleos por soquete, 2 nós NUMA por soquete), havia 6 núcleos por nó NUMA. Eu vi um sistema com 4 dessas CPUs (portanto, oito nós NUMA, 6 núcleos cada) que estava preso no comboio spinlock até que o sinalizador de rastreamento 8048 fosse ativado.

   Eu vi essa contenção de spinlock reduzir o desempenho em VMs tão pequenas quanto 4 vCPUs. O sinalizador de rastreamento 8048 fez o que deveria quando ativado nesses sistemas.

   Considerando que ainda existem CPUs otimizadas para frequência de 4 núcleos, com a carga de trabalho correta, eles também se beneficiariam do sinalizador de rastreamento 8048.

   As esperas CMEMTHREAD acompanham o tipo de contenção de spinlock que o sinalizador de rastreamento 8048 alivia. Mas uma palavra de cautela: as esperas do CMEMTHREAD são um sintoma corroborante, não a causa principal desse problema específico. Já vi sistemas com altos "inícios de espera" de CMEMTHREAD em que o sinalizador de rastreamento 8048 e/ou 9024 foi atrasado na implantação porque o tempo de espera acumulado de CMEMTHREAD era bastante baixo. Com spinlocks, o tempo de espera acumulado geralmente é a coisa errada a se observar. Em vez disso, você deseja observar o tempo de CPU desperdiçado - representado principalmente pelos próprios giros, secundariamente pelas esperas associadas que representam trocas de contexto potencialmente desnecessárias.

   • Como funciona: CMemThread e depurando-os

4. Atribuição de tarefas a agendadores

   Em sistemas NUMA, as conexões são distribuídas para nós NUMA (bem - na verdade, para os grupos de agendadores SQLOS associados a eles) round-robin, supondo que não haja pontos finais de conexão associados a nós NUMA específicos. Se uma sessão executa uma consulta paralela, há uma forte preferência para usar trabalhadores de um único nó NUMA. Hmmm... considere um servidor de nó 4 NUMA com uma consulta complexa dividida em 4 caminhos e padrão 0 MAXDOP. Mesmo que a consulta usasse apenas threads de trabalho MAXDOP, haveria 4 threads de trabalho para cada CPU lógica no nó NUMA. Mas existem 4 caminhos no plano complexo - portanto, cada CPU lógica no nó NUMA pode ter 16 trabalhadores - tudo para uma única consulta!

   É por isso que às vezes você verá um nó NUMA trabalhando duro enquanto outros estão vadiando.

   Existem algumas outras nuances na atribuição de tarefas. Mas o principal argumento é que a CPU ocupada não será necessariamente distribuída uniformemente pelos nós NUMA. (Também é bom perceber que as inserções de página do bpool (leituras ou gravações da primeira página) irão para o bpool no nó de memória SQLOS associado ao agendador em que o trabalhador está. E as páginas roubadas virão preferencialmente da memória SQLOS "local" nó também.

   Descobri que trazer maxdop de 0 para não mais que 8 é útil. Dependendo do perfil da carga de trabalho (principalmente imo no número de consultas simultâneas esperadas potencialmente de longa duração), pode ser garantido ir até MAXDOP=2.

   Ajustar o limite de custo para paralelismo também pode ser útil. Os sistemas em que trabalho tendem a ser consumidos com consultas de alto custo e raramente encontram um plano abaixo de 50 ou 100, então tive mais tração ajustando maxdop (muitas vezes no nível do grupo de carga de trabalho) do que ajustando o limite de custo.

   • SQL Server PLE com 8 nós NUMA

     Nesta postagem, é discutida uma combinação de contenção de spinlock em torno da memória do espaço de trabalho e distribuição desigual de tarefas. Veja - essas coisas realmente se entrelaçam :-)

   • 40 consultas paralelas simultâneas do SQL Server + (2 soquetes * 10 núcleos por soquete) = spinlock convoy

5. Colocação de dados relevantes no bpool

   Essa é a condição que considero mais intuitiva ao lidar com servidores NUMA. Também, normalmente, não é extremamente significativo para o desempenho da carga de trabalho.

   O que acontece se a tabela for lida no bpool no nó NUMA 3 e, posteriormente, uma consulta no nó NUMA 4 verificar a tabela executando todas as pesquisas de bpool nos nós NUMA?

   Linchi Shea tem um ótimo post sobre esse impacto no desempenho:

   • http://sqlblog.com/blogs/linchi_shea/archive/2012/01/30/performance-impact-the-cost-of-numa-remote-memory-access.aspx

   O acesso à memória em nós NUMA incorre em uma pequena quantidade de latência de memória adicional. Tenho certeza de que algumas cargas de trabalho precisam eliminar essa latência de memória básica adicional para obter um desempenho ideal - não tem sido um problema nos sistemas com os quais trabalho.

   Mas - o acesso cross-node também traz outro ponto de transferência que pode potencialmente saturar. Se houver tanta atividade que a largura de banda da memória entre os nós NUMA esteja saturada, a latência da memória entre os nós aumentará. O mesmo trabalho exigirá ciclos de CPU adicionais.

   Mais uma vez - tenho certeza de que existem cargas de trabalho em que a largura de banda da memória é uma consideração crítica. Para meus sistemas, porém, as outras considerações que estou listando foram mais significativas.

6. Colocação de memória física

   Este é raro, mas quando importa, realmente importa. Na maioria dos servidores, a instalação da memória quase naturalmente se equilibra entre os nós NUMA. Mas, em alguns casos, é necessária atenção especial para equilibrar a memória entre os nós. O desempenho em alguns sistemas pode ser totalmente prejudicado se a memória for encaixada de forma que não seja balanceada. Isso é definir e esquecer, no entanto. Muito raro descobrir um problema como esse após meses de serviço de produção, em vez de após o primeiro dia realmente agitado :-)

O GRANDE FINAL!

Outra pessoa observou que a má escolha do plano, talvez devido a estatísticas desatualizadas, pode resultar nos sintomas que você viu. Esse não foi o caso em minha experiência. Planos ruins podem facilmente fazer com que uma consulta demore mais do que o esperado - mas geralmente porque mais IOs lógicos do que o necessário estão sendo executados. Ou devido a vazamento para tempdb. O derramamento maciço no tempdb deve ser evidente ao observar o servidor - e, em vez da CPU alta, seria de esperar um tempo de espera mensurável para as gravações de disco relacionadas ao derramamento.

Em vez disso, se a situação que você observou estiver relacionada ao NUMA, esperaria que fosse uma combinação dos fatores enumerados acima, principalmente:

1. uso da memória do espaço de trabalho (que não aparecerá nas contagens lógicas de E/S)

2. que pode ser um nó NUMA cruzado devido à condição de memória externa persistente (se for esse o caso, procure correções relevantes)

3. e que pode incorrer em contenção de spinlock dentro do nó NUMA cada vez que uma alocação é feita contra uma concessão (correção com T8048)

4. e pode ser executado por trabalhadores em CPUs lógicas sobrecarregadas por outros trabalhadores de consulta paralela (ajuste maxdop e/ou limite de custo de paralelismo conforme necessário)

( atualize sua pergunta com coreinfo -va saída (um utilitário sysinternal) para obter um melhor contexto de sua CPU/soquetes e distribuição NUMA )

Analisamos a utilização geral da CPU, que estava em torno de 60%. Não analisamos as métricas de CPU específicas do soquete. As métricas de E/S foram medianas.

Parece-me que você está latindo para a árvore errada. O SQL Server está NUMAciente. Há uma penalidade de desempenho muito menor para fazer acesso cruzado à memória NUMA . Você também pode usar esta consulta para ver quantos NUMAnós você tem e quais CPUs e núcleos estão atribuídos a quais NUMA:

SELECT parent_node_id, scheduler_id, cpu_id
FROM sys.dm_os_schedulers WITH (NOLOCK) 
WHERE [status] = N'VISIBLE ONLINE';

Ou apenas quantos NUMA:

select COUNT(distinct Parent_node_id)
from sys.dm_os_schedulers
where [STATUS] = 'VISIBLE ONLINE'
    and Parent_node_ID < 64

Tivemos consultas com poucas leituras lógicas demorando mais de 1 minuto.

Isso normalmente acontece quando você tem planos de consulta ruins gerados devido a estatísticas desatualizadas. Certifique-se de ter suas estatísticas atualizadas e seus índices estão desfragmentados corretamente .

Além disso, você precisa definir MAXDOP para um valor mais sensato para evitar a inanição do thread de trabalho .

Defina seu cost threshold of parallelismpadrão de 5 para um bom valor inicial como 45 e, em seguida, monitore esse valor e ajuste-o de acordo com seu ambiente.

Se você estiver executando muitas consultas ad hoc, ative (defina como 1) optimize for ad hoc workloadspara evitar o inchaço do cache do plano.

Use com cuidado: você pode usar o T8048 se estiver executando o SQL Server 2008/2008 R2 em máquinas mais recentes com mais de 8 CPUs apresentadas por nó NUMA e houver um hotfix se você estiver no SQL Server 2012 ou 2014 .

É altamente recomendável que você comece a coletar informações de estatísticas de espera sobre sua instância do servidor de banco de dados.

Consulte: Como funciona: SQL Server (blocos de memória NUMA locais, externos e ausentes)

Puramente de uma perspectiva de hardware, o gerenciamento da memória principal da arquitetura Nehalem em diante é gerenciado por um controlador de memória integrado, na parte "Un-core" da matriz da CPU, separada da parte em que os núcleos reais vivem, como a memória é efetivamente 'conectada' a cada CPU, o acesso à memória externa AFAIK é feito por meio da interconexão de caminho rápido (novamente de Nehalem em diante), eu diria, portanto, que a saturação do núcleo da CPU em um nó NUMA local não deve afetar o acesso remoto a essa memória.

Você pode achar este link útil:

http://cs.nyu.edu/~lerner/spring10/projects/NUMA.pdf

cris