Query failed:
SELECT
e.id,
...
ORDER BY timestamp DESC
LIMIT 15
/ Can't create/write to file '/tmp/#sql_6ee_0.MYI' (Errcode: 28)
I was kind of puzzled. All other sites were working fine (such us wiki and list of pim prototypes). I sshed to a server and checked the disk usage:
myusername@pim:~$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 3.8G 3.0G 685M 82% /
Everything fine here. I checked the /tmp folder
myusername@pim:/tmp# ls -la
total 32
drwxrwxrwt 4 root root 20480 Apr 18 12:42 .
drwxr-xr-x 22 root root 4096 Apr 18 11:20 ..
drwxrwxrwt 2 root root 4096 Mar 24 22:42 .ICE-unix
Nothing strange here either. I tried to login to mysql:
myusername@pim:/tmp$ mysql -u myusername -p
Enter password:
Welcome to the MySQL monitor
Everything worked fine. So I checked the ERROR 28 the web page was giving me
myusername@pim:/tmp$ perror 28
OS error code 28: No space left on device
I tried to create a file with no success:
myusername@pim:/tmp$ touch bla.txt
touch: cannot touch `bla.txt': No space left on device
Then I googled a bit and found out that I might be running out of inodes.
myusername@pim:/tmp$ df -i
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/sda1 250976 250957 19 100% /
Yep, that was it. Now I had to find which folder had a lot of files that were taking up the inodes (as root). Not all results are shown in the below output.
myusername@pim:/var$ cd /
myusername@pim:/$ sudo bash
root@pim:/$ for i in /*; do echo $i; find $i -type f | wc -l; done
/bin 91
/boot 23
/dev 346
/etc 745
/home 5
/lib 1709
/media 0
/proc 11435
/root 5
/sbin 103
/sys 3863
/tmp 0
/usr 45188
/var 186774
I went to /var and run the same command
root@pim:/var$ for i in ; do echo $i; find $i -type f | wc -l ;done
backups 1
cache 55
lib 173682
log 47
mail 2
run 16
spool 19
www 12950
So I needed to check /var/lib
mkljun@pim:/var/lib$ for i in ; do echo $i; sudo find $i -type f | wc -l ;done
apt 9
doc-base 37
dpkg 2342
misc 2
mlocate 1
mysql 665
php5 170453
screen-profiles 21
ucf 23
xml-core 2
The folder /var/lib/php5 contained 170k files!
It was kind of strange why all these files were not deleted after a
session timeout. I certainly did not have 170k users visiting my pages
(I wished this to be true though).
root@pim:/var/lib# cd php5
root@pim:/var/lib/php5# ls -l | wc -l
170453
I checked the maximum time set for a php5 sessions to live.
root@pim:/var/lib/php5# /usr/lib/php5/maxlifetime
24
24 minutes. I decided to delete older files.
root@pim:/var/lib/php5# find /var/lib/php5/ -type f -cmin +$(/usr/lib/php5/maxlifetime) -print0 -exec rm {} \;
root@pim:/var/lib/php5# ls -la
total 11768
drwx-wx-wt 2 root root 12017664 Apr 18 12:35 .
drwxr-xr-x 38 root root 4096 Sep 22 2010 ..
-rw------- 1 www-data www-data 168 Apr 18 12:23 sess_389b8191e2563e8bb43c41e20e015c0d
-rw------- 1 www-data www-data 118 Apr 18 12:11 sess_52c9acc7779f5cfd7e7e92a900fe7f19
-rw------- 1 www-data www-data 160 Apr 18 12:27 sess_bb8cfa842ceb0027cc9e4082ad8b0419
I checked the inodes usage.
root@pim:/tmp# df -i
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/sda1 250976 80524 170452 33% /
That was it.
Why PHP is not deleting expired sessions is another question. I went to my php.ini file and found this:
; After this number of seconds, stored data will be seen as 'garbage' and
; cleaned up by the garbage collection process.
session.gc_maxlifetime = 1440
; NOTE: If you are using the subdirectory option for storing session files
; (see session.save_path above), then garbage collection does not
; happen automatically. You will need to do your own garbage
; collection through a shell script, cron entry, or some other method.
So all I needed to do was to create a cron job (I decided to rut it every hour as root)
root@pim:/tmp# crontab -e
0 /usr/bin/find /var/lib/php5/ -type f -cmin +$(/usr/lib/php5/maxlifetime) -print0 -exec /bin/rm {} \;
Hopefully this will help some other soul out there.
Example-2
Clean up inodes in linux
root@server1:/var/www/wespell.com# df -i
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/sda 1966080 1957683 8397 100% /
udev 126290 405 125885 1% /dev
tmpfs 127451 320 127131 1% /run
none 127451 4 127447 1% /run/lock
none 127451 1 127450 1% /run/shm
Lets find which folder has the most inodes
root@server1:/var/www/wespell.com# cd /; for i in *; do echo -n $i,; find $i -print | wc -l; done
backup,5
bin,130
boot,11
dev,407
etc,2331
home,421
initrd.img,1
lib,2063
lib64,2
lost+found,1
media,1
mnt,1
opt,1
proc,47112
root,6227
run,322
sbin,146
selinux,1
srv,1
sys,17406
tmp,8025
usr,57375
var,1881350
vmlinuz,1
We see that that was VAR folder, run the same command under var.
In our case it was spool/postfix and deferred emails. Here what I did to clean up.
I don’t use postfix at all, so I will basically clear it and disable.
Lets Flush postfix mail queue
postsuper -d ALL
postsuper -d ALL deferred
And now we have
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/sda 1966080 201470 1764610 11% /
udev 126290 405 125885 1% /dev
tmpfs 127451 320 127131 1% /run
none 127451 4 127447 1% /run/lock
none 127451 1 127450 1% /run/shm
Yappp, now we have enough space.