Slow query on information_schema.tables

Question

I run a set of self-developed applications on a typical shared hosting service offering. I moved from a static configured table list of allowed tables to one based on a list of tables bases on a prefix from the D/B metadata. When I did promote this version to the public service, my per-request latency increased by an average 2.3–2.4 secs. Some instrumentation revealed that this was entirely down to one SQL query:

SELECT TABLE_NAME AS name
FROM information_schema.tables
WHERE TABLE_SCHEMA = '<DBname>'
AND TABLE_NAME LIKE '<TablePrefix>%';

I used this because I wanted to explicitly name the column in the result set. However, coding around this with an alternate query adds an extra line of code which ran in <2 mSec:

SHOW TABLES LIKE '<TablePrefix>%';

My service provider uses Enterprise MySql 5.0.92-50 so I can't do any profiling. This is a scaling issue as it doesn't occur on my dev environment and test VM where I can profile. They support thousands of users so the live schema will be extremely large, but even so connection and most queries only take milliseconds.

Does anyone know why querying the memory-based information_schema on a large multi-user system would take so long?

Answer 1

For those who may want a hack with a minor drawback: http://www.mysqlperformanceblog.com/2011/12/23/solving-information_schema-slowness/

What it does is it disable some stats that get updated if you query the schema, more information here: http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata

And to make things easier for lazy bums who don't want to read, you only need to make a change to a setting:

innodb_stats_on_metadata=0

You can do this in the config file or dynamically:

mysql> set global innodb_stats_on_metadata=0;

Answer 2

The information schema is not optimized, there are no indexes, just tables with metadata, and usually, when you run SELECT from the schema, it opens and reads files.

Have a look at this article - Optimizing INFORMATION_SCHEMA Queries; in some cases it will help you.