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

Basically MySQL is giving me many errors of "Got error 28 from storage engine" that means no more disk space is available.

The output of df -h is:

File system           Dim. Usati Disp. Uso% Montato su
/dev/md1               10G  7,9G  1,6G  84% /
tmpfs                 2,0G     0  2,0G   0% /lib/init/rw
udev                   10M  176K  9,9M   2% /dev
tmpfs                 2,0G     0  2,0G   0% /dev/shm
/dev/md2              683G  601G   48G  93% /home

Every filesystem here is not empty. Are there any other problem?

I am on
- a dedicated server (debian 64bit )
- and the errors happens doing an heavy query

share|improve this question
    
The title says "error 26" but the question says "error 28". Is that a typo or did you get two different errors at different times? –  Ladadadada Jun 15 '12 at 9:30
    
sorry of course error 28 –  dynamic Jun 15 '12 at 10:36

2 Answers 2

up vote 4 down vote accepted

It might be that your query makes MySQL create temporary tables. In the default configuration, these will be created alongside the other tables, which is likely on your / partition which has only 1.6GB left, and these tables can become larget than that really quickly.

Watch your free space while you do such a query.

See this documentation about it.

share|improve this answer
    
indeed that query are made on pretty large table... I will try to free some spaces in / –  dynamic Jun 15 '12 at 10:37

Two other possibilities:

  1. Filesystems often reserve 5% of the space and return an error when it reaches 95% full. Your /home is at 93%.
  2. You get the same error message when you run out of inodes, even if you still have disk space left. You can see inodes with df -i.

I think SvenW's answer is more likely to be correct. His reasoning is sound and I have run into exactly the same issue myself.

You can figure out whether the query is going to use a temporary table by running EXPLAIN <query> in your MySQL instance, replacing <query> with the actual query. You are looking for "Using temporary" in the Extra section. Temporary tables will be written to disk if they are larger than max_heap_table_size and/or tmp_table_size in your my.cnf.

You can find what directory MySQL is using for temporary tables by looking at the tmpdir variable either in the running instance (mysql> SHOW VARIABLES LIKE 'tmpdir';) or in your my.cnf (grep tmpdir my.cnf).

share|improve this answer
    
Hm, Do mysql uses /home or / ? Anyway i will check df -i too later! thanks you too –  dynamic Jun 15 '12 at 10:38
    
MySQL uses whatever you have configured in my.cnf for datadir. The default is /var/lib/mysql. –  Ladadadada Jun 15 '12 at 10:40
    
I have explain-ed my query, there is "using temporary; Using filesort" in the extra field. My tmpdir var is /tmp. But I have 1,6GB free on / how is that possible –  dynamic Jun 16 '12 at 15:21

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.