Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
is it possible that you actually run out of space? can you run df -h when this happens next time? –  pQd Jun 8 '09 at 18:15
    
Are they all referring to the same file or are they different? –  David Pashley Jun 8 '09 at 18:30
    
This is not a disk space issue, but that does not rule out a memory issue. The errors do refer to the same file. –  martindale Jun 8 '09 at 21:11
    
Can you view the files in /tmp or other temporary directory location? Are they getting removed? They should only exist for several seconds at most, often for much less. –  Jon St. John Jun 8 '09 at 21:22
    
No, I have not been able to locate these files in the /tmp folder. I'm not sure how to capture them in time, before they are cleaned up? –  martindale Jun 9 '09 at 17:54

2 Answers 2

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.

share|improve this answer
    
Restarting the SQL server does resolve the issue. –  martindale Jun 8 '09 at 19:10
    
Well, it sounds like it 'restarts' it rather than resolving it. I would assume that's because a counter that it uses to determine its temp file name gets reset, and takes a while to get back to where it's causing collisions. To resolve it will, if I'm correct about what's happening, require cleaning out the dead temp tables. –  chaos Jun 8 '09 at 19:15

MySQL creates temporary files in certain situations to handle query results. One common situation where this occurs is when sorting occurs on column data that is of text or blob type. Others include when the maximum allowed temporary table size is exhausted.

The creation of these tables indicate queries that may not be fully optimized, but in some cases they cannot be avoided. However, you should not receive errors (in some production environments that I work with, dozens of these might get created every minute).

Usually the files are created in /tmp . You can watch that directory and see them being created and destroyed. If /tmp, or whichever directory is being used, is not writeable by the mysql user, that maybe your problem.

Otherwise, look for more mundane issues, such as out of disk space.

Here is some addition information from the MySQL docs.

share|improve this answer
    
"DISTINCT combined with ORDER BY may require a temporary table." Looks like it matches the given query :) –  David Pashley Jun 8 '09 at 18:34
    
It's almost certainly an issue with tuning the temporary table size, but I'm very uncertain about which direction I want to tune it... and what exactly the best approach would be. Obviously, fixing it for the long term would involve refactoring the queries - but I'd like to get the server stable before that takes place. Any ideas on what the best angle of attack would be? –  martindale Jun 8 '09 at 19:12
    
Okay, 2 things. First, there appears to be something wrong on the server - the temporary tables should be written to disk without an error. Second, if you want to avoid them being written at all you can optimize the queries (as you already know). Can you post the explain output from the query? Are any of the columns text or blob? (if text/blob, MySQL will always write the temporary table to disk, regardless of settings) –  Jon St. John Jun 8 '09 at 19:23
    
No, none of the output is in text/blob format - it's all varchar and int fields. Interestingly enough, and this may help isolate the actual issue - I've noticed a second error that seems less frequent but still troublesome: Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug [1135] I'm wondering what might be causing that, too. Also: I have edited in the EXPLAIN output of the query. It does look like it is using a filesort - which I am attributing to insufficient memory resources. –  martindale Jun 8 '09 at 21:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.