Friday, April 07, 2006

The downside of information_schema

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/04/the-downside-of-information_schema/.

7 comments:

Kolbe said...

How did you determine that the presence of references to information_schema was the primary cause for slowdown in the new version? I'm not saying it's *not* the cause, but I don't see any evidence.

Which information_schema tables are taking a particularly long time to access?

Markus Popp said...

SHOW PROCESSLIST returned queries like this, while opening the table:

SELECT TABLE_NAME FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'pma280' AND TABLE_NAME = 'tt2'

So it's actually the VIEWS table that's involved here. I reported the behavior in the phpMyAdmin bug tracker.

I don't believe that it's specifically the VIEWS table that's slow, it's just - if there are thousands of databases and tables, it takes a long time until MySQL can retrieve the data from these tables.

Roland Bouman said...

Hi Markus,

I don't if it helps, but I noticed remarkable differences in performance of the information_schema across Windows XP pro and Ubuntu Linux.

For Windows, the first time you access the information schema within a session takes a considerable amount of time - seconds, even when the server hasnt got any databases. For Linux, repsonse is almost instantaneous.

As for downsides on the information schema - I think the identifiers are rather lenghty. Not that MySQL is to blame - it's naming according to the standard. However the standard also presents a short identifier version - it would be great to have that too, especially for ad-hoc querying (I do not want to change databases each and every time)

Anonymous said...

In the future we'll be looking to speed this up by having the code that generates the results for the I_S query look at the conditions you have in the SELECT statement.

Much like condition-pushdown is for Cluster now.

pabloj said...

Information schema is the way to go, the fact that MySQL's implementation is not mature, subject to frequent changes and not performant means ... well, you know what.

Jay Pipes said...

Hi Markus! Looking forward to meeting you at the Users Conference in a couple weeks! Re: the I_S stuff, I have reported your findings to Robin Schumacher (who was looking for input on performance-related issues). I'll keep abreast of the situation and let you know if anything changes. In the meantime, if you (and anyone else reading this) could put together some specifics on the platform and versions that experience slowdown and any other comments you have, please email me... :)

Cheers,

Jay

Anonymous said...

Markus,

FWIW, PostgreSQL's information_schema is pretty snappy. And we're working on improvements for 8.2.

--Josh, PostgreSQL Project