show tables is slow/uncached

We have a production database that has 4,000+ tables. For a variety of reasons, our CGIs always
have to run "show tables" to generate a list of all tables in the database.

This is by far our slowest query; it currently takes about 100 milliseconds. It is
apparently never cached; here's a relevant transcript:

mysql ...
use hg18
show tables
4421 rows in set (0.09 sec)
show tables
4421 rows in set (0.13 sec) <-- note about same speed (i.e. not cached)

I have seen this in versions 4.0.27-standard-log and 5.0.41-community

As an alternative, I tried the new SELECT ... FROM INFORMATION_SCHEMA.TABLES,
but it is much slower:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'hg18';
4421 rows in set (1.73 sec)
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'hg18';
4421 rows in set (1.67 sec) <-- second run; not cached and still very slow

Here's an unindexed query in a large table to show that caching is working for normal queries:

flush table gc5Base;
select count(*) from gc5Base;
562977 rows
select count(*) from gc5Base where sumSquares = 1684400;
1 row in set (0.38 sec)
select count(*) from gc5Base where sumSquares = 1684400;
1 row in set (0.00 sec)

This is an almost exclusively read-only database (there shouldn't be any locking issues).
All the tables are MyISAM.

Does anyone know why "show tables" is so slow?

Is there any way to speed this up?

We can create our own table with a cached copy of the results of "show tables",
but we would prefer to avoid that because it is kludgy.

thanks!

-larry

Larry Meyer larrym@soe.ucsc.edu
UCSC Genome Bioinformatics Group http://genome.ucsc.edu

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: show tables is slow/uncached

----- Original Message ----=0A> From: Laurence Meyer e.ucsc.edu>=0A> To: mysql@lists.mysql.com=0A> Sent: Sunday, 16 August, 2009=
7:09:25=0A> Subject: show tables is slow/uncached=0A> =0A> We have a produ=
ction database that has 4,000+ tables. For a variety of reasons, =0A> our C=
GIs always=0A> have to run "show tables" to generate a list of all tables i=
n the database.=0A> > Does anyone know why "show tables" is so slow?=
=0A> =0A> Is there any way to speed this up? Unless it's a general dat=
abase performance issue, or something where there's a mysql specific workar=
ound you could use memcached to save hitting the database at all. =0A=
=0A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: show tables is slow/uncached

In the last episode (Aug 15), Laurence Meyer said:
> We have a production database that has 4,000+ tables. For a variety of
> reasons, our CGIs always have to run "show tables" to generate a list of
> all tables in the database.
>
> This is by far our slowest query; it currently takes about 100
> milliseconds. It is apparently never cached; here's a relevant
> transcript:
>
> mysql ...
> use hg18
> show tables
> 4421 rows in set (0.09 sec)
> show tables
> 4421 rows in set (0.13 sec) <-- note about same speed (i.e. not cached)

"SHOW TABLES" can't be cached; it has to check for tables that have been
added since the previous query (possibly by someone copying .MYI files into
the databse directory). If you set the open_files_limit and
table_open_cache variables large enough that mysqld can keep all the tables
open at the same time, it might run faster, but I'm not sure that "SHOW
TABLES" even uses the table cache. It looks like it does a directory scan.
Querying INFORMATION_SCHEMA is even slower since it has to generate a
virtual table containing all tables in all databases, then filter it on your
WHERE clause.

In your case, caching the results in your own table might be the best
solution.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org