MySQL Can’t Create/Write To File

lampMySQLweb-server

After MySQL has been running for some time (usually 4-6 hours), some larger and more load-intensive queries permanently (until restart) fail, returning the following error:

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

Googling this error returns many likely causes, but I feel this may be resolvable in two easy methods;

1) Optimize the queries. (A good solution for the long term.)

2) Tune MySQL configuration in some way. (This is what I want done)

In light of the question, here are our current non-default values in 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

I am inclined to believe that we may notice significant performance gains by tuning these variables further, but that is a question pending later question-asking.

Here is an example query that begins to fail:

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

The EXPLAIN result of the above query returns the following:

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

Any assistance would be appreciated!

Best Answer

It's pretty obscure, but I think you might (just might) have a bunch of dead SQL temporary tables like that one hanging around, and eventually mysqld tries to reuse the same filename and fails because it has internal rules telling it never to overwrite a .MYD.

What I would recommend is looking to see where these #sql_XXXX_X.MYD files live, shut down mysqld, clean out the temp files and restart it.

If this is in fact what's going on, optimizing your query will seem to help if you can get it so that it doesn't create temp tables, but unless you clean out the dead files the benefit will be illusory. Optimizing the query so you're less likely to get dead temp tables in the future is a fine idea, though.

Related Topic