Mysql speed issues with SHOW COLUMNS or SHOW FIELDS

MySQL

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.

Thanks

Best Answer

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

[mysqld]
tmpdir=/var/tmpfs

Then, just restart mysql

service mysql restart

Now, all temp tables will land in RAM

I have discussed this before

Oct 09, 2012 : Inserting query result to another table hangs on "Copying to temp table on disk" on MySQL
Nov 30, 2012 : Is it bad to create many mysql temporary tables simultaneously?
Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk?
Nov 15, 2013 : MySQL creates temporary tables on disk. How do I stop it?

Give it a Try !!!

Related Solutions

Mysql – Repair with keycache in thesql runs EVERY time. Can’t even log why it’s happening

Your my.conf says the following: key_buffer_size=32M That's really small. You could afford to set it higher: Run this query please:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will give you a recommended size of key_buffe_size. Please you common sense when setting this value.

Here is something else: I don't see a bulk_insert_buffer_size. The default is 8MB. Try setting it to 512MB:

[mysqld]
bulk_insert_buffer_size=512M

I just noticed your myisam_sort_buffer_size is only 300M. Set it to 4G (max value)

[mysqld]
myisam_sort_buffer_size=4G

Mysql – How to speed up “show columns” on MySQL

MySQL recalculates table statistics for certain operations that access INFORMATION_SCHEMA tables (SHOW COLUMNS is just a convenient alias for querying INFORMATION_SCHEMA.COLUMNS). Set innodb_stats_on_metadata to be false, which will prevent this recalculation from occurring when you ask for metadata from the table.

SET GLOBAL innodb_stats_on_metadata=0;

and add the following to my.cnf

[mysqld]
innodb_stats_on_metadata = 0

Best Answer

Give it a Try !!!

Related Solutions

Mysql – Repair with keycache in thesql runs EVERY time. Can’t even log why it’s happening

Mysql – How to speed up “show columns” on MySQL

Related Question