[CentOS] MySQL/file system question

Mon Feb 13 21:58:24 UTC 2012
Adam Tauno Williams <awilliam at whitemice.org>

On Mon, 2012-02-13 at 16:45 -0500, Boris Epstein wrote:
> Hello listmates,
> I got a rather strange situation that I can't quite make sense of. OK, I've
> got a very large data file to sort (hundreds of millions of lines) and I
> decided to use MySQL for the purpose. I inserted the stuff into a table
> easily enough. Then I decided to sort it and got stuck as it turned out
> that MySQL, unless specifically configured to do otherwise, puts temporary
> files in /tmp which simply was not sufficiently large. Then I changed that
> directory to a partition that had more space (let's call the new temp
> driectory /home/big-temp) and now as I am running the query aimed at
> sorting the data it seems like space, according to the df, is no longer
> being used up under / (which was there /tmp was) but is now being used up
> in the right partition ( /home, the large one). Yet /home/big-temp is still
> empty!
> So how is that possible? 

Easy.  It is using temporary files the *correct* way.

1. Open file
2. Unlink file
3. Use file
4. Close file

This means (a) even if the process abends the resources allocated to the
file are released and (b) an external process can't see [or modify] the
temporary file.

When a file is unlinked it remains 'active' until all references to the
file are released - but the daemon is still holding a reference [because
it is using the file].

There is a file there, but nobody, not even root, can see it.

Actually you can; if you look in /proc/{pid#}/fd ...

System & Network Administrator [ LPI & NCLA ]
OpenGroupware Developer <http://www.opengroupware.us>
Adam Tauno Williams