This blog post has moved. Please find it at:
http://www.mpopp.net/2006/04/the-downside-of-information_schema/.
Subscribe to:
Post Comments (Atom)
skip to main |
skip to sidebar
This is all about db4free.net and the database systems that it offers. The main focus are practical examples of the new features of MySQL 5 and 6 that should show you how to use them for your advantage in your daily life ;-).
About Me
Links
Blog Archive
-
▼
2006
(124)
-
▼
April
(12)
- Returning home from the MySQL UC
- db4free.net offers MySQL 5.1.10
- The countdown is running
- My first live webinar experience
- Something broken at the development source tree?
- Alternative privilege system including ROLES and s...
- A little (?) brain exercise
- User XYZ != User XYZ
- Events work much better now
- The downside of information_schema
- QueryBrowser 1.2.7?
- Woooohoooooo - the 21st century has just begun!
-
▼
April
(12)
7 comments:
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?
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.
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)
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.
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.
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
Markus,
FWIW, PostgreSQL's information_schema is pretty snappy. And we're working on improvements for 8.2.
--Josh, PostgreSQL Project
Post a Comment