Algum erro de configuração pode levar à criação de muitas tabelas temporárias pelo sintetizador mysql..mysql mostra
Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 17158 temp tables, 30% were created on disk
table_open_cache = 125 tables
table_definition_cache = 256 tables
You have a total of 97 tables
You have 125 open tables.
Current table_cache hit rate is 3%
A tabela temporária anterior foi of the 23725 temp tables 38% were created on disk , mas alterei max_heap e tmp_table para 200m de 16m e diminuiu para 30%.
engine myisam
group_concat_max_len = 32768
key_buffer_size = 3.7 GB,
thread_stack = 256k,
table_cache = 125
query_cache_limit = 1M
query_cache_size = 16M
join_buffer_size = 2.00 M
max_connections = 800
Outro sistema com configuração padrão está sendo exibido
of 23725 temp tables, 1% were created on disk
Mas eu tentei mudar para o padrão na máquina com este problema e ainda mostra Of 580 temp tables, 16% were created on disk
Estou usando o Ubuntu 11.4 de 64 bits com ram de 48 GB ... Alguém pode sugerir uma solução?
A alteração do mecanismo do banco de dados do myisam para a memória nas tabelas usando "group by" corrige isso?
As tabelas temporárias são criadas e eliminadas conforme necessário, dependendo das consultas que estão sendo executadas. A figura que você está vendo é a soma total das tabelas temporárias criadas desde a última vez que o MySQL foi iniciado, não o número existente simultaneamente.
O MySQL usa tabelas temporárias quando a consulta não pode ser calculada em uma única passagem. Mudar o mecanismo de armazenamento não vai mudar isso. O problema é com a consulta , não com a configuração.
Aumentar o valor de tmp_table_size só impedirá que alguns deles sejam gravados no disco, eles ainda serão criados na memória e preenchidos com dados. Esses dados provavelmente vêm do disco em primeiro lugar, embora com 48 GB de RAM você provavelmente tenha muito do cache. Mesmo em cache, uma vez que 30% dessas tabelas temporárias são maiores que 200 MB, copiar essa quantidade de dados na RAM ainda leva tempo.
Você pode determinar antes mesmo de executar uma consulta se ela usará uma tabela temporária ou não, usando a sintaxe EXPLAIN . Basta colocar EXPLAIN antes de sua consulta e irá gerar um monte de informações sobre o plano de execução e a eficiência da consulta sem realmente executá-la.
Provavelmente você pode encontrar as consultas que estão causando essas tabelas temporárias, pois elas provavelmente serão consultas lentas e, portanto, acabarão no seu log de consultas lentas.
Se você precisar de ajuda para definir consultas específicas, o DBA.SE é um bom lugar para ir.
TL; DR
Ajuste suas consultas.