Every hour, we saw the diskspace on our root partition go up from 45 % to 90 % and come back to 45 %. Initially thought mysql was processing some temp tables and in the next hour we kept watching the temp location. In terms of files / diskspace usage using “du / ls” nothing changed. But lsof provided an interesting output.
Output 1 :
mysqld 31719 mysql 6u REG 8,1 0 149240 /var/tmp/ibOLLuRa (deleted)
mysqld 31719 mysql 7u REG 8,1 102 149243 /var/tmp/ibNuSvI9 (deleted)
mysqld 31719 mysql 8u REG 8,1 0 149245 /var/tmp/ibqq2wz8 (deleted)
mysqld 31719 mysql 9u REG 8,1 0 149255 /var/tmp/ibVN66Rh (deleted)
mysqld 31719 mysql 13u REG 8,1 0 149256 /var/tmp/ibslSJJA (deleted)
mysqld 31719 mysql 15u unix 0×000001085e445680 19044143 /tmp/mysql.sock
mysqld 31719 mysql 18u REG 8,1 1128267776 146656 /var/tmp/MLHbDVOq (deleted)
Output 2 :
mysqld 31719 mysql 6u REG 8,1 0 149240 /var/tmp/ibOLLuRa (deleted)
mysqld 31719 mysql 7u REG 8,1 102 149243 /var/tmp/ibNuSvI9 (deleted)
mysqld 31719 mysql 8u REG 8,1 0 149245 /var/tmp/ibqq2wz8 (deleted)
mysqld 31719 mysql 9u REG 8,1 0 149255 /var/tmp/ibVN66Rh (deleted)
mysqld 31719 mysql 13u REG 8,1 0 149256 /var/tmp/ibslSJJA (deleted)
mysqld 31719 mysql 15u unix 0×000001085e445680 19044143 /tmp/mysql.sock
mysqld 31719 mysql 18u REG 8,1 1396703232 146656 /var/tmp/MLHbDVOq (deleted)
The size occupied by a file marked deleted kept increasing 
Strange but it seems to be happening on every dataload. The size change in this file corroborates perfectly with the space usage reported by “df” but not with “du”.
We are setting up the tmp location to a filesystem with more space for now before we investigate further.
If I’m understanding your post correctly, it makes perfect sense. On a Unix system, to make your temp file the most secure you open it, then immediately delete it. As long as it’s open, you still have read/write access to the file, but no one else can open it since the file does not show up in the directory listing.
Thus, df would show the space loss because it looks at the block device. du would not show it because it walks the tree to find its file size counts.
Thanks Joshua for the interesting explanation.
It is not so much for security. But by deleting the file immediately, it is ensured
that the file will be removed by the operating system when it is closed, also if the
application should crash during processing the file. This avoids leaving temporary
files around on disk in case of crash.