Por que as consultas do MySQL se acumulam no estado “Enviando dados”?

Question

Estamos usando tabelas InnoDB como back-end de um aplicativo da web, e tudo ficou bem por cerca de dois anos, até que tivemos que reiniciar o MySQL algumas semanas atrás. (Não desativamos as pesquisas reversas de DNS, embora não as estivéssemos realmente usando, mas nosso sistema de hospedagem parou de responder a essas solicitações. Agora elas estão desativadas.) Infelizmente, o arquivo de configuração havia mudado, e nós não t tem uma cópia do seu estado original para comparação.

Depois de consertar os problemas mais significativos, ficamos com um verdadeiro quebra-cabeças: Sob alta carga, as consultas ao banco de dados começam a demorar muito mais do que o normal. Durante esses tempos, temos centenas de conexões abertas de nossos sete servidores apache. Executar SHOW PROCESSLIST revela que metade ou mais dessas conexões estão no estado "Enviando dados", freqüentemente com tempos de algumas centenas de segundos. Quase todas as suas consultas são SELECT, com consultas semelhantes tendendo a se agrupar. Na verdade, o menor clump na lista tendia a ser exatamente a mesma consulta (eu esperaria que ele estivesse no cache de consulta), retornando 1104 linhas de dois inteiros cada. Outros ofensores freqüentes são listas de algumas centenas de linhas com um único inteiro, várias linhas com um único inteiro ou até mesmo um único resultado de COUNT (*).

Nós tentamos desligar os servidores da web durante um desses períodos, mas o problema retornou dentro de um minuto depois de reiniciá-los. No entanto, reiniciar completamente o mysqld resolveu o problema até o dia seguinte. Qual poderia ser o problema e como podemos verificar e / ou corrigi-lo?

    

Answer 1

Isso se revelou uma falha na combinação de innodb_file_per_table , default-storage-engine = innodb e uma página acessada com freqüência que criou uma tabela temporária. Cada vez que uma conexão fechava, ela descartava a tabela, descartando páginas da LRU do buffer pool . Isso faria com que o servidor parasse um pouco, mas nunca na consulta que estava realmente causando o problema.

Pior, a configuração innodb_file_per_table estava definhando no arquivo my.cnf há meses, antes de o servidor precisar ser reiniciado por um motivo totalmente não relacionado, durante o qual estávamos usando essas tabelas temporárias sem nenhum problema. (O NOC subitamente derrubou o servidor DNS, fazendo com que cada nova conexão fosse interrompida porque não ativamos skip-name-resolve e não admitíamos por horas que algo havia mudado.)

Felizmente, fomos capazes de reescrever a página ofensiva para usar um conjunto de consultas ainda mais rápido que carregou a maior parte do trabalho nos servidores Web front-end e não encontrou nenhum problema desde então.

    

Answer 2

Bem, observe que, se bem me lembro (já faz um tempo desde que fiz o DB trabalhar), as consultas COUNT (*) sem uma cláusula WHERE nas tabelas innodb são notoriamente mais lentas que nas tabelas MyISAM e Memory.

Além disso, isso é por acaso um Xen Domu?

Qual é o idioma frontend? Se PHP, está usando MySQL ou MySQLi? Eles estão usando conexões persistentes?

Você não mencionou o sistema operacional subjacente, mas no caso do Linux eu começaria olhando para a saída de free -m , prestando especial atenção às duas últimas linhas para ver se a memória é compacta no geral.

[0:504] callisto:cyanotype $ free -m
             total       used       free     shared    buffers     cached
Mem:          3961       3816        144          0        184       1454
-/+ buffers/cache:       2177       1784
Swap:         2898          0       2898

Aqui temos um sistema que é saudável (é a minha estação de trabalho). A segunda coluna exclui buffers e cache, então estou usando 2177mb de memória e 1784 megabytes prontamente disponíveis.

A última linha mostra que eu não uso swap até agora.

Em seguida, dar vmstat(8) , para ver se o seu sistema está destruindo como louco, também seria útil.

[0:505] callisto:cyanotype $ vmstat 5 10
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 0  0      0 134116 189828 1499948    0    0    11     3   44   49  1  1 98  0
 0  0      0 143112 189836 1489688    0    0     0     6  526 2177  1  1 98  0
 0  0      0 139268 190504 1491864    0    0   512     4  663 4704  2  1 96  1
 2  0      0 136688 191084 1493484    0    0   473     5  641 3039  1  1 97  1
 0  0      0  52636 191712 1518620    0    0  5066     4 1321 6600  8  2 86  4
 5  0      0  72992 193264 1377324    0    0 10742    31 1602 7441 12  3 80  5
 2  1      0  84036 193896 1202012    0    0 10126    43 2621 4305 31  2 57 10
 3  0      0  42456 195812 1060904    0    0  3970    75 55327 9806 43 5 41 10
 8  1      0  34620 197040 942940     0    0  3554    64 50892 12531 43 6 44 6
^C
[0:506] callisto:cyanotype $ 

(Meu desktop realmente não está fazendo tanto assim aqui, desculpe. Que desperdício de 8 núcleos perfeitamente bons)

Se você encontrar muito tempo de processo na coluna 'b', isso significa que eles estão bloqueados, esperando por algo. Muitas vezes isso é IO. As colunas importantes aqui são si e so . Verifique se eles estão preenchidos com valores altos. Se assim for, isso pode ser o seu problema - algo está consumindo muita memória, mais do que você pode realmente fazer. Usando top(4) e ordenando as colunas pela memória% (shift + m enquanto no topo) pode mostrar o culpado (s).

Não é impossível que o seu sistema esteja destruindo o swap e saturando os discos, causando encadeamentos e processos bloqueados. A ferramenta iostat(8) (parte do pacote sysstat , normalmente) deve receber um giro para ver se você tem processos bloqueados, presos em IO_WAIT. Um disco saturado pode significar más notícias para todo o sistema sob alta carga, especialmente se o sistema estiver trocando muito.

Você pode executar o iostat com estatísticas estendidas, a cada cinco segundos, por exemplo:

[0:508] callisto:cyanotype $ iostat -x 5
Linux 2.6.35-23-generic (callisto)  2010-11-30  _x86_64_    (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          16,55    0,12    2,70    2,60    0,00   78,02

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm      %util
sdc               0,00     2,00    1,00    0,80    27,20    22,40    27,56     0,01    3,33   3,33       0,60
sdd               0,00    12,60   67,60    4,80  4222,40   139,20    60,24     0,62    8,62   3,29      23,80
sde               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00   0,00       0,00
sdf               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00   0,00   0,00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          32,02    0,10    1,83    0,44    0,00   65,61

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sdc               0,60     3,20   11,00    0,80   265,60    32,00    25,22     0,05    3,90   2,88   3,40
sdd               0,00     8,20    0,00    3,00     0,00    89,60    29,87     0,02    8,00   7,33   2,20
sde               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00   0,00   0,00
sdf               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00   0,00   0,00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          49,26    0,22    3,12    0,12    0,00   47,28

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sdc               6,20     3,00    7,40    3,80   208,00    54,40    23,43     0,09    7,86   2,50   2,80
sdd               0,00    15,20    0,20    4,00     1,60   152,00    36,57     0,03    6,67   6,19   2,60
sde               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00   0,00   0,00
sdf               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00   0,00   0,00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          16,00    0,54    1,05    1,07    0,00   81,35

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sdc               4,20     0,00   31,40    0,00  3204,80     0,00   102,06     0,17    4,90   2,68   8,40
sdd               0,00    28,20    0,20    2,60     1,60   246,40    88,57     0,02    7,14   7,14   2,00
sde               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00   0,00   0,00
sdf               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00   0,00   0,00

^C

Isso deve permitir que você veja facilmente se seus volumes estão saturados. Por exemplo, aqui, você pode ver que meus discos são terrivelmente subutilizados, que o sistema gasta a maioria de seus ciclos de CPU em marcha lenta, etc etc. Se essa porcentagem estiver principalmente na coluna% IOWAIT, você terá um gargalo de E / S aqui. Você provavelmente já sabe tudo isso, mas apenas cobrindo todas as bases para ter certeza.

A idéia é que seu arquivo de configuração foi alterado, e você não tem histórico (colocar seus arquivos de configuração sob controle de versão é uma ótima idéia por essa razão) - e não é impossível alterar o tamanho de um buffer Assim, fazer consultas caras como COUNT (*) sem o SELECT começarão a engolir recursos.

Com base no que você aprendeu com o uso anterior das ferramentas, provavelmente você deve inspecionar o arquivo de configuração (sendo a única coisa que mudou, é muito provável que seja o culpado) para ver se os valores do buffer são estáveis para sua carga média.

Qual é o tamanho dos buffers, como o valor query_cache_size e, especialmente, os tamanhos sort_buffer ? (Se isso não couber na memória, será executado no disco, a um custo enorme, como eu tenho certeza que você pode imaginar).

Qual é o tamanho do innodb_buffer_pool_size ?

Qual é o tamanho de table_cache e, o mais importante, esse valor se enquadra nos limites do sistema para identificadores de arquivo? (ambos open-files-limit no [mysqld] e no nível do SO).

Além disso, eu não me lembro do topo da minha cabeça, se isso ainda é verdade, mas estou bastante certo de que innodb realmente bloqueia a tabela inteira sempre que tem que cometer um campo de auto-incremento. Eu pesquisei e não consegui descobrir se isso era verdade ou não.

Você também pode usar innotop(1) para ver o que está acontecendo mais detalhadamente também.

Espero que isso ajude de alguma forma ou forneça um ponto de partida:)