To avoid making code changes every time we have a database change, we just use the SHOW FIELDS (Alias of SHOW COLUMNS) to get all of the columns for Inserts. We call this multiple times for multiple tables in various scripts.

Recently we have discovered speed issues so we started to look at the temp table disk report and found that the SHOW FIELDS FROM 'sometable' all had 100% hits to the disk temp table.

I've tried turning up our query cache size (yes it is enabled), and I've disabled the meta calculations 'SET GLOBAL innodb_stats_on_metadata = OFF;'

We are still getting 100% disk temp table hits on all of these show queries. Is there an easy way to fix these without a code change? We would like to still do our inserts without updating the columns in the code, and the SHOW FIELDS call is part of a framework and we would like to avoid rewriting it if possible.



Would you consider a RAM disk for temp tables ?

First, create the RAM disk (16GB)

mkdir /var/tmpfs
echo "none   /var/tmpfs              tmpfs   defaults,size=16g        1 2" >> /etc/fstab
mount -t tmpfs -o size=16g none /var/tmpfs

Next, add this to my.cnf


Then, just restart mysql

service mysql restart

Now, all temp tables will land in RAM

I have discussed this before

Give it a Try !!!

| improve this answer | |
  • Thanks for the solution, but these feels a bit like a hack. It seems like there should be an easy way to get column names without a disk temp table on mysql 5.6. Creating a RAM disk to get around that seems like overkill just to get a column name. – user46103 Aug 26 '14 at 6:06

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.