Eu tenho algumas consultas SQL loucas em meus scripts PHP que precisam de alguma otimização. No entanto, a questão não é sobre como otimizá-los.
Estas consultas têm muitos "join" e algumas "ordens" bizarras, e são feitas em tabelas com várias dezenas de milhares de registros. A coisa lots_of_joins + crazy_order_by faz com que eles precisem de uma etapa "copy to tmp table".
Agora, o mais estranho é que a mesma consulta às vezes é executada rapidamente (menos de um segundo) e, às vezes, leva muito tempo (dezenas de segundos). Em ambos os casos, "explicar" e os perfis mostram uma etapa "copiar para a tabela de tmp". Quando a consulta demora, 99% do tempo é gasto na fase "copiar para a tabela tmp". O STRANGE THING é que, durante esse tempo, o mysql consome quase 100% da CPU.
Então, eu entendo que a tabela tmp às vezes é mantida na memória e às vezes é gravada no disco (dependendo da disponibilidade atual da memória). Então, isso explicaria perfeitamente por que a mesma consulta às vezes pode ser rápida e às vezes demorada. No entanto, há duas coisas que não entendo.
-
Se o gargalo estiver na escrita da tabela temporária em disco, isso deve significar muito tempo gasto em E / S, mas a carga média da CPU durante esse tempo deve ser relativamente baixa, certamente distante de 100%. Como a CPU pode ficar tão ocupada ao fazer tanto I / O?
-
Eu aumentei em my.cnf:
max_heap_table_size = 1024M
tmp_table_size = 1024M
(acho que os padrões foram 16M)
e eu realmente não acredito que a tabela tmp está exigindo mais do que essa quantidade de RAM.
Tanto quanto eu entendo, uma tabela tmp é gravada no disco em vez de memória:
a- se a consulta e a tabela exigirem porque não satisfazem certos critérios
b- se exceder o mínimo entre max_heap_table_size e tmp_table_size
Se (a) fosse o caso, então aconteceria sempre , não de tempos em tempos.
Por outro lado, parece-me improvável que seja (b), porque eu aumentei muito os parâmetros acima sem nenhuma mudança perceptível. O tamanho da tabela necessária não deve mudar muito entre as ocorrências da mesma consulta (os dados são quase os mesmos). Então, se antes de aumentar os tamanhos de memória, isso acontecia de vez em quando (isso significaria que o tamanho da tabela tmp requerida estava em torno do máximo), então o comportamento errático deveria ter desaparecido completamente após um aumento tão drástico.
Então minhas perguntas basicamente são:
-
A criação de tabela de disco é realmente a única causa que pode fazer com que a etapa de copiar para tabela-tmp demore muito? (Ou pode demorar muito, mesmo que seja feito na memória? E, em caso afirmativo, por que e por que aleatoriamente?)
-
Se for, então
- como ele pode usar muita CPU?
- por que uma tabela tmp pode ser gravada em disco mesmo que não seja maior que min (tmp_table_size, max_heap_table_size)?
Eu realmente não acho que a consulta de exemplo e os perfis são necessários, mas posso publicá-los se forem.