O MySQL não pode criar / gravar em arquivo

Question

Depois que o MySQL é executado há algum tempo (geralmente de 4 a 6 horas), algumas consultas maiores e com mais carga intensiva (até a reinicialização) falham, retornando o seguinte erro:

Can't create/write to file '#sql_75ed_0.MYD' (Errcode: 17)

Pesquisando este erro, ele retorna muitas causas prováveis, mas acho que isso pode ser resolvido em dois métodos fáceis;

1) Otimize as consultas. (Uma boa solução para o longo prazo.)

2) Ajuste a configuração do MySQL de alguma forma. (Isso é o que eu quero fazer)

À luz da pergunta, aqui estão os nossos valores não padrão atuais em my.cnf:

query_cache_size = 8M
query_cache_limit = 8M

thread_cache_size = 4
max_connections = 90
table_cache = 4096
innodb_buffer_pool_size = 900M

tmp_table_size = 512M
max_heap_table_size = 256M

innodb_file_per_table

Estou inclinado a acreditar que podemos notar ganhos significativos de desempenho ajustando essas variáveis ainda mais, mas essa é uma questão pendente de perguntas posteriores.

Aqui está uma consulta de exemplo que começa a falhar:

SELECT DISTINCT u.user_id, u.username, u.username_clean, u.user_colour, MAX(s.session_time) as online_time, MIN(s.session_viewonline) AS viewonline FROM (table_users u, table_zebra z) LEFT JOIN table_sessions s ON (s.session_user_id = z.zebra_id) WHERE z.user_id = 4 AND z.friend = 1 AND u.user_id = z.zebra_id GROUP BY z.zebra_id, u.user_id, u.username_clean, u.user_colour, u.username ORDER BY u.username_clean ASC

O resultado EXPLAIN da consulta acima retorna o seguinte:

1, 'SIMPLE', 'z', 'ref', 'PRIMARY,zebra_id_friend', 'PRIMARY', '3', 'const', 18, 'Using where; Using temporary; Using filesort'
1, 'SIMPLE', 's', 'ref', 'session_user_id', 'session_user_id', '3', 'database_name.z.zebra_id', 402, ''
1, 'SIMPLE', 'u', 'eq_ref', 'PRIMARY', 'PRIMARY', '3', 'database_name.z.zebra_id', 1, ''

Qualquer ajuda seria apreciada!

    

Answer 1

É bem obscuro, mas eu acho que você pode (talvez) ter um monte de tabelas temporárias do SQL morto como essa por aí, e eventualmente o mysqld tenta reutilizar o mesmo nome de arquivo e falha porque ele tem regras internas dizendo para nunca sobrescreva um .MYD .

O que eu recomendo é procurar ver onde esses arquivos #sql_XXXX_X.MYD vivem, desligar o mysqld, limpar os arquivos temporários e reiniciá-lo.

Se isso for de fato o que está acontecendo, otimizar sua consulta parecerá ajudar se você puder obtê-la para que ela não crie tabelas temporárias, mas a menos que você limpe os arquivos mortos, o benefício será ilusório. A otimização da consulta, portanto, é menos provável que você obtenha tabelas temporárias inativas no futuro é uma ótima ideia.

    

Answer 2

O MySQL cria arquivos temporários em determinadas situações para manipular os resultados da consulta. Uma situação comum em que isso ocorre é quando a classificação ocorre nos dados da coluna que são do tipo texto ou blob. Outros incluem quando o tamanho máximo permitido da tabela temporária é esgotado.

A criação dessas tabelas indica consultas que podem não ser totalmente otimizadas, mas, em alguns casos, não podem ser evitadas. No entanto, você não deve receber erros (em alguns ambientes de produção com os quais trabalho, dezenas deles podem ser criados a cada minuto).

Normalmente, os arquivos são criados em / tmp. Você pode assistir a esse diretório e vê-los sendo criados e destruídos. Se / tmp, ou qualquer diretório que esteja sendo usado, não é gravável pelo usuário mysql, talvez o problema seja seu.

Caso contrário, procure por problemas mais comuns, como falta de espaço em disco.

Aqui estão algumas informações adicionais dos documentos do MySQL.