0

I set my MySQL tmpdir setting to use /run/mysqld which is a tmpfs, however while running a query I know copies data to a temp table (which takes 5-6 seconds based on MySQL's INFORMATION_SCHEMA.PROFILE records) I am not seeing any temporary file(s) being written out to that directory when doing an ls -la at the command prompt.

Additionally, the query speed did not change when moving my tmpdir from an on-disk location to an in memory location (tmpfs) which it clearly should have improved.

Question: How do I know for sure that my tmpdir is being used?

1

In *nix, a file can be creat'd, rm'd, then written to and read from by the same process. No flavor of ls can see it, since it is already rm'd from the directory, yet the program can see it. This is a trick that makes sure that the file is not left behind if the program crashes. MySQL often uses the rm trick.

ls cannot see the rm'd-but-not-closed files, but lsof can see them as (deleted).

df will change because of the space being consumed.

If you show us the SELECT, SHOW CREATE TABLE and EXPLAIN SELECT. We may be able to give you tips on avoiding the tmp table. (I assume your real question is about performance?)

  • Yes, MySQL often uses that trick, and lsof should show them. The files will show in the list as (deleted). df will show the space change. – Michael - sqlbot Dec 28 '16 at 11:54
  • @Michael-sqlbot - Thanks. I folded your comment in; your comment can be removed. Note: now a community wiki. – Rick James Dec 28 '16 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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