Falha no MySQL quando eu aumento o valor max_heap_table_size

Question 1

Com 120 GB de memória, parece possível que sua máquina também tenha múltiplas CPUs físicas e uma arquitetura Non-Uniform Memory Access (NUMA)... e se for esse o caso, você poderia ter bastante memória disponível, mas ainda assim, contra-intuitivamente, não há memória disponível suficiente.

Se estou no caminho certo até agora, você pode estar enfrentando esse problema quando o MySQL tentar aumentar a memória alocada para a MEMORYtabela e encontrar um erro relacionado à maneira como o Linux lida com alocações de memória ao executar nesta arquitetura - apenas considerando a memória disponível que está nos cartões de memória que estão diretamente conectados a uma CPU específica que foi escolhida de forma um tanto arbitrária para ser "aquela" a partir da qual as solicitações de memória seriam atendidas - apesar de outra memória física estar livre em outro lugar na placa-mãe .

NUMA é bom em teoria, mas talvez não seja ideal para processos únicos que necessitam de grandes quantidades de memória, como é o caso do MySQL... mas há uma solução alternativa.

A correção será modificar o mysqld_safescript adicionando esta linha:

cmd="/usr/bin/numactl --interleave all $cmd"

...imediatamentedepoisestá linha...

cmd="$NOHUP_NICENESS"

As razões são explicadas aqui, em um excelente artigo escrito originalmente para abordar por que os servidores MySQL com esta arquitetura estavam trocando intensamente apesar da memória livre; no entanto, isso foi um sintoma de uma questão maior que o autorsubseqüentementeapontou que "não é inteiramente um problema de troca", que, mesmo com a troca desativada, pode incluir "falha na alocação de memória".

http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

Estou especulando, é claro, se isso se aplica ao seu sistema, mas parece uma possibilidade que vale a pena. Usei exatamente essa correção quando encontrei um problema em uma nova máquina de 128 GB quando tentei provisionar um buffer pool InnoDB de 64 GB e o MySQL não conseguiu encontrar 64 GB livres em uma máquina comnadamais correndo. Quando percebi que o valor mais alto que poderia usar com sucesso era algo menor que 1/4 da memória total em uma máquina de 16 núcleos com 4físicoprocessadores... mais ou menos como você parece estar encontrando... foi quando juntei as peças quanto à natureza do problema.

Answer

Com 120 GB de memória, parece possível que sua máquina também tenha múltiplas CPUs físicas e uma arquitetura Non-Uniform Memory Access (NUMA)... e se for esse o caso, você poderia ter bastante memória disponível, mas ainda assim, contra-intuitivamente, não há memória disponível suficiente.

Se estou no caminho certo até agora, você pode estar enfrentando esse problema quando o MySQL tentar aumentar a memória alocada para a MEMORYtabela e encontrar um erro relacionado à maneira como o Linux lida com alocações de memória ao executar nesta arquitetura - apenas considerando a memória disponível que está nos cartões de memória que estão diretamente conectados a uma CPU específica que foi escolhida de forma um tanto arbitrária para ser "aquela" a partir da qual as solicitações de memória seriam atendidas - apesar de outra memória física estar livre em outro lugar na placa-mãe .

NUMA é bom em teoria, mas talvez não seja ideal para processos únicos que necessitam de grandes quantidades de memória, como é o caso do MySQL... mas há uma solução alternativa.

A correção será modificar o mysqld_safescript adicionando esta linha:

cmd="/usr/bin/numactl --interleave all $cmd"

...imediatamentedepoisestá linha...

cmd="$NOHUP_NICENESS"

As razões são explicadas aqui, em um excelente artigo escrito originalmente para abordar por que os servidores MySQL com esta arquitetura estavam trocando intensamente apesar da memória livre; no entanto, isso foi um sintoma de uma questão maior que o autorsubseqüentementeapontou que "não é inteiramente um problema de troca", que, mesmo com a troca desativada, pode incluir "falha na alocação de memória".

http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

Estou especulando, é claro, se isso se aplica ao seu sistema, mas parece uma possibilidade que vale a pena. Usei exatamente essa correção quando encontrei um problema em uma nova máquina de 128 GB quando tentei provisionar um buffer pool InnoDB de 64 GB e o MySQL não conseguiu encontrar 64 GB livres em uma máquina comnadamais correndo. Quando percebi que o valor mais alto que poderia usar com sucesso era algo menor que 1/4 da memória total em uma máquina de 16 núcleos com 4físicoprocessadores... mais ou menos como você parece estar encontrando... foi quando juntei as peças quanto à natureza do problema.

Question 2

A documentação paratmp_table_sizediz que esta configuração é o tamanho máximo que uma tabela temporária pode terem memória. Exceder esse tamanho não aciona erros de tabela completa; ele desencadeia uma mudança do uso da tabela na memória para o uso de uma tabela MyISAM no disco.

O sistema está travando porque você está permitindo que tabelas temporárias usem muito mais memória do que deveriam. O mesmo acontece com as tabelas de memória.

Você deve descartar o tmp_table_sizecaminhoabaixo. Lembre-se de que esta configuração não é um valor máximo geral. É um máximo por tabela temporária. Se você tiver 5 consultas criando uma tabela temporária enorme (digamos, pouco menos de 20 GB cada), agora você terá tabelas temporárias usando até 100 GB de sua RAM. Adicione um sexto e você usará mais RAM do que o servidor possui no total.

Se seus scripts estiverem realmente usando o mecanismo de armazenamento MEMORY, grave os dados em um arquivo MyISAM depois de tentar alterá-lo. Se realmente houver necessidade de desempenho tão rápido em uma tabela temporária tão grande, você deve procurar obter um armazenamento mais rápido (por exemplo.Fusão-io,Viridente outros). Se forem um exagero ou muito caros, eu pelo menos consideraria SSDs para consumidores.

MyISAM tem melhor desempenho quando seus dados são armazenados em cache na memória do sistema (RAM não utilizada). Se seus scripts estouram o cache toda vez que fazem uma consulta enorme (usando o mecanismo MEMORY), seu desempenho no MyISAM será prejudicado.

Suspeito que o script esteja usando o mecanismo de armazenamento MEMORY para 'melhorar' o desempenho, quando na verdade o está matando. Se for uma tabela temporária, ela deverá usar tabelas temporárias, e seu tmp_table_size deverá ser algo muito menor, forçando-o para o disco após quebrar o tmp_table_size.

Os padrões para max_heap_table_size e tmp_table_size são 16 MB. Se possível, sugiro alterar as configurações de volta aos padrões. Ajuste para cima em pequenos incrementos enquanto monitora o uso de recursos (E/S de disco, uso total de memória, uso de CPU, etc.) até encontrar configurações que funcionem para seu conjunto de dados.

Answer