MySQL无法创建/写入文件

Question

在MySQL运行了一段时间(通常为4-6小时)之后，一些较大且负载密集型更强的查询永久失败(直到重新启动)，返回以下错误：

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

谷歌这个错误返回许多可能的原因，但我觉得这可以通过两种简单的方法解决；

1)优化查询。(从长远来看是一个很好的解决方案。)

2)以某种方式优化MySQL配置。(这就是我想要做的)

鉴于这个问题，下面是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

我倾向于相信，通过进一步调整这些变量，我们可能会注意到显著的性能提高，但这是一个有待以后提出的问题。

下面是一个开始失败的示例查询：

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

上述查询的解释结果返回以下内容：

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, ''

如能提供任何帮助，将不胜感激！

Answer 1

这很难理解，但我认为您可能(可能)会有一堆死气沉沉的SQL临时表，比如那个表，最终mysqld尝试重用同一个文件名，但是失败了，因为它有内部规则，告诉它永远不要覆盖.MYD。

我建议的是查看这些#sql_XXXX_X.MYD文件的位置，关闭mysqld，清除临时文件并重新启动它。

如果这实际上是正在发生的事情，优化您的查询似乎会有所帮助，如果您能够获得它，以便它不会创建临时表，但除非您清除死文件，其好处将是虚幻的。但是，优化查询以减少将来得到死临时表的可能性是个好主意。

Answer 2

MySQL在某些情况下创建临时文件来处理查询结果。发生这种情况的一个常见情况是，对文本或blob类型的列数据进行排序。其他方法包括在最大允许的临时表大小耗尽时。

这些表的创建表明查询可能不是完全优化的，但在某些情况下它们是无法避免的。但是，您不应该收到错误(在我所使用的某些生产环境中，可能每分钟都会创建几十个错误)。

通常，文件是在/tmp中创建的。您可以查看该目录，并看到它们正在创建和销毁。如果mysql用户无法写入/tmp或任何正在使用的目录，那么这可能是您的问题。

否则，寻找更多平凡的问题，例如磁盘空间外。

以下是来自MySQL文档的一些附加信息.