MySQL Bugs: #19588: INFORMATION_SCHEMA performs much too slow on large servers

Bug #19588	INFORMATION_SCHEMA performs much too slow on large servers
Submitted:	6 May 2006 23:47	Modified:	12 Dec 2007 3:25
Reporter:	Markus Popp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S5 (Performance)
Version:	5.0, 5.1	OS:	Any (all)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	affects_connectors, bfsm_2007_02_01, Q1

Description:
This is a known issue and has been discussed during the MySQL Users Conference - however, there should be an official bug report for it and the severity should definitely be S1!

The low performance of information_schema has several bad effects on servers that host high amounts of data - many of them, I've discovered through db4free.net. I wasn't able to update to phpMyAdmin 2.8.0.3 (databases could not be displayed because of timeouts) and found out that it resulted from changing to retrieve objects from information_schema instead of SHOW commands in phpMyAdmin. Many client applications make use of information_schema and they all suffer from this problem - one for example is MySQL Front.

The latest issue is that I can't even run "SELECT * FROM INFORMATION_SCHEMA.TABLES" on db4free's MySQL 5.0 server (ca. 5,500 databases, estimated 90,000 tables) anymore (Lost connection to MySQL server during query).

Also refer to http://db4free.blogspot.com/2006/04/downside-of-informationschema.html and the comments of this blog article and also to http://sourceforge.net/tracker/index.php?func=detail&aid=1466527&group_id=23067&atid=37740..., the bug report that I initially filed for phpMyAdmin. AFAIK, Robin Schumacher has been informed about this issue.

How to repeat:
Run queries from information_schema or use client applications like phpMyAdmin > 2.8.0 or MySQL Front on database servers with high amounts of data.

Output from db4free.net:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 495 to server version: 5.0.21-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use information_schema
Database changed
mysql> select count(*) from tables;
ERROR 2013 (HY000): Lost connection to MySQL server during query

I agree. it should be a bug with S1 and not a known problem.
Very annoying

Thank you for the bug report. I created 5500 databases and 15 tables for
each databases (see C application at the bottom) starting the servers
with their defaults values. I was unable to get the message error:
RROR 2013 (HY000): Lost connection to MySQL server during query;
However I got enough information regarding the performance:

miguel@hegel:~/dbs/5.1> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.10-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from tables;
+----------+
| count(*) |
+----------+
|    88061 | 
+----------+
1 row in set (2 hours 2 min 45.43 sec)

mysql> 

Verified on Windows and Linux.

#include <my_global.h>
#include <m_string.h>
#include "mysql.h"

#define DB_HOST			"hegel" //"localhost"
#define DB_USER			"miguel" //"root"
#define DB_PASSW		""
#define DB_NAME			"test"
#define DB_PORT			3306
#define DB_UNIX_SOCKET	"/tmp/mysql.sock" //NULL

void main( void )
{
  MYSQL mysql;
  int x, y;
	char my_sub[255],my_query[255];
 
  mysql_init(&mysql);
  
   
  if (!mysql_real_connect(&mysql,DB_HOST,DB_USER,DB_PASSW,DB_NAME,
						   DB_PORT,DB_UNIX_SOCKET,0))
  {
    printf("Error: %s\n",mysql_error(&mysql));
    return;
  }
  else
	printf("Connected to the server: %s\n",mysql_get_server_info(&mysql));

  for (y = 0; y <= 5500; y++)
  {
	  int10_to_str((int) y,strmov(my_sub,
          "Create Database if not exists db"), 10);
    
    if (mysql_query( &mysql, my_sub) )
		{
      printf("Error (query): %s\n", mysql_error( &mysql ));
      mysql_close( &mysql );
     return;
		}
	  
    int10_to_str((int) y,strmov(my_sub,
          "Use db"), 10);
    
    if (mysql_query( &mysql, my_sub) )
		{
      printf("Error (query): %s\n", mysql_error( &mysql ));
      mysql_close( &mysql );
     return;
		}
    for (x = 0; x <= 15; x++)
    {
	    int10_to_str((int) x,strmov(my_sub,
                  "Create Table if not exists tb"), 10);

	    strxnmov(my_query,sizeof(my_query)-1,my_sub,
		   " (id int not null auto_increment primary key, name varchar(200))engine=MyISAM",NullS);
	 	     
	 
	 	  if (mysql_query( &mysql, my_query) )
		  {
        printf("Error (query): %s\n", mysql_error( &mysql ));
        mysql_close( &mysql );
        return;
		  }
    }
  }
  mysql_close(&mysql);
}

Bug: http://bugs.mysql.com/bug.php?id=15883 was marked as duplicate
of this one.

The databases don't even need to be "large" before the slowness
of INFORMATION_SCHEMA retrievals become noticeable.  For a
database with just a couple of hundred tables, I observe response
times of several seconds for a query like this:

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'db_name' ORDER BY TABLE_NAME;
+-------------------+
| TABLE_NAME        |
+-------------------+
...
+-------------------+
203 rows in set (3.45 sec)

The equivalent SHOW TABLES FROM db_name is virtually
instantaneous.

The problem is not that the server has lots of tables in other databases.
There are fewer than 400 tables total:

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES;+----------+
| COUNT(*) |
+----------+
|      390 | 
+----------+
1 row in set (9.03 sec)

i must agree, this should really S1

I have the same problem.

On a large server, this makes the server very slow.

With 10 requests like that
( select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'foo' and `TABLE_TYPE` = 'BASE TABLE' ; )
, the server do not response anymore to other queries & go to crash after a "deadlock" of > 20 minutes.

This is a real S1 bug.
There are a need to restrict that to an admin for example....

SHOW TABLE STATUS ON  301 Table size Database costed the mysql server 51 seconds !!! (just in the mysql client

Indeed S1
Not acceptable 

Brought it down to  214 tables and the response was within 20 seconds (which is still slow but acceptable)

However the Query
SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`='DatabaseName'

(the same database name) took up some 63 seconds
Something is not good here

Bug: http://bugs.mysql.com/bug.php?id=21871 was marked duplicate of this one.

Also us have the same problem.
With phpmyadmin 2.8.x, that use some select in information_scheme tables, all became slow and with a lot of customer, mysql server crash.
I think we must give us the possibility to ihibits access to information_schema db via some privileges, or simply change the way information_schema harvest information in fs.
It is a serious bug not simply a S5.
A customer(not superuser and with only access to his db) can, via multiple select to information_schema, crash server and this is not so usefull in a large enviroment.
Please dedicate some time to this S1 bug.

I agree, this should be an S1. Using 5.0.24a it took well over a minute to run SELECT TABLE_NAME FROM TABLES; and this is with a small number of databases and 2400 tables.

I agree aswell. Select from information_schema took way too long even on small instances. Not to mention the cpu resources these selects need. 3 selects and our server is nearly off for about 30 minutes (on the bigger instances).

Running 5.0.24a

Is anybody actually looking into this? This is VERY serious!!

Dear Mr. Ashcroft,

As the bugs status says, the bug is "In Progress", this means that
the assigned developer is working on the bug. We are very well aware
that this is a serious problem and are doing our best to solve it.

Regards,
Timour

With all due respect, this bug was created 5-6 months ago and STILL has only an S5 severity.

Maybe an update from MySQL/Developer on it?

before my duplicate (since 2months) nothing was done about it.

In 6 months noone was be able to add a directive like skip_show_database..to forbid the readdir....

New versions of phpmyadmin can DoS the server with less than 10 clients connected...

FYI, I have been told by the support guys that this problem will be fixed early November.

Hopefully, it will be. I'm getting really bad performance because of this and all my shared hosting customers are getting pissed... :(

Just thought i'd let you guys in the know...

Ther's some more news about?
It's mid november and nothing in 6 month was done about this SERIOUS and CRITICAL S1 bug.
Please give us an alternative if is a real big problem to fix it.

The preliminary fix is almost done.
The fix speeds up most of 'SELECT' requests
listed in bug report and some others.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15600

ChangeSet@1.2302, 2006-11-21 11:42:23+04:00, gluh@mysql.com +11 -0
  Bug#19588 INFORMATION_SCHEMA performs much too slow on large servers
  optimization of 'WHERE' condition for I_S tables:
  extract base name & table name values from 'WHERE' when it's possible
  optimization of table opening:
  open .frm file only if we can obtain necessary values from .frm file
  skip table opening if we need base name & table name only(for I_S.TABLES table)

I don't see in the patch any remove of the "readdir".....

Hi, status is still 'In progress'. Are there any updates?
Is there any idea in which version and when the bug is fixed?

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19550

ChangeSet@1.2368, 2007-02-08 14:33:02+04:00, gluh@mysql.com +11 -0
  Bug#19588 INFORMATION_SCHEMA performs much too slow on large servers
  optimization of 'WHERE' condition for I_S tables:
  extract base name & table name values from 'WHERE' when it's possible
  optimization of table opening:
  open .frm file only if we can obtain necessary values from .frm file
  skip table opening if we need base name & table name only(for I_S.TABLES table)

This bug will be fixed when 'WL#3732 Information schema optimization'
will be implemented.

I understand from the WL that many things need to be done to speed up access to I_S. But, why can't we cache it? I am sure a lot of people have status I_S over multiple days, if not months or even years.

It can be cached, of course, but it's not as easy as it may look. A cache, for example, must be invalidated on DDL statements. There's a more difficult problem - our users expect that if a table is copied into data directory, it becomes visible in SHOW statements and I_S tables. This makes caching a very tricky business.

IMHO, no sane person will drop in some files _into_ a directory owned by a running DB instance and expect the files to be detected by the DB on-the-fly.

[7 Aug 11:57] Alan Tam

"IMHO, no sane person will drop in some files _into_ a directory owned by a running DB instance and expect the files to be detected by the DB on-the-fly."

Why ...
... should others expect the same as you?
... should someone think this should not work?
... should it not work if it could work?
... do you call people relying on this insane?
... does your mail client shows newly dropped mails in your inbox without restart?

cause it is a feature!

I said it is insane to expect such a feature. No implication for fact that the DB really offers such a feature, or other people depending on it when the DB says it can. I simply have no intention to start a flame war.

Perhaps the original designer didn't realize (s)he needs to support fast INFORMATION_SCHEMA retrieval. Not until recently we have lots of Object-Relational mappers which requires access to the fields and keys of all tables during every build/run of an application.

Now we realize the importance of the bug, and maybe there is no quick solution. Can we simply drop the perhaps-unintended feature so that more users are happy?

Personally, I don't think it should be a problem to not have this 'feature' working for a information_schema cache. 

I mean, normally people expect to be able to create stored routines in databases - that is something that is currently not supported either for the information_schema. I guess my point is that it is acceptable to allow exceptions for these system databases.

As for being able to add a directory into the data directory and have the results show up - is that documented anywhere?

Since one year i asked for a configuration parameter to disable the use of INFORMATION_SCHEMA... 

On a public service, i really don't care about information_schema & actually it can be a DoS for me... 

So until MySQL AB understand that... wait & see (& pray ?) :-)

Tordjman: as Alan already said, many ORM (could or should) rely on I_S - and it would be a drawback not giving your customers this feature

disabling I_S would not help much ... IMHO ... better fix this speed/DoS issue ...

I provide one of the biggest free mutualised web hosting in France, and i say that  i really don't want any I_S, cache on I_S, stupid readdir over 60000 directories...

Explain that: "When a user X with rights to db Y, witch plays with I_S, does a readdir of the directory of ALL the DB of the server ?"

If one of them does that, the server is dead. 

So, i'm going to continue praying .... :-/

i know this problem, we got reports about this issue too (phpMyAdmin), but i would not prefer disabling I_S ... but i also do not understand why MySQL is taking this problem not more seriously and fix this 'DoS' exploit ...

Sebastian Mendel: Yes i can't upgrade my phpmyadmin without patch it to disable the use of I_S
"but i would not prefer disabling I_S ..." , you, i speak for my 2.5 millions accounts... 
This is the same thing of "skip/safe_show_database"

> patch it to disable the use of I_S

just hiding the I_S from being listed or scan all queries for I_S access?

hiding databases can be configured in phpMyAdmin ...

> "but i would not prefer disabling I_S ..." , you, i speak for my 2.5
> millions accounts... 

hiding on the UI and disabling in the server is not the same ...

of course, if i was you i would hide/disable I_S too - but just as workaround and would still prefer fixing this issue and not hide I_S ...

> This is the same thing of "skip/safe_show_database"

i don't think so, this was about showing databases without proper rights, or?

for phpmyadmin , the last time i did that, i simply impose that the DB version is prior the use of I_S.

I want to disable it, not hide...
hiding = can be accessible & DoS able...

for the skip/safe show db, i hope that mysql will not do like pgsql that everyone can see the name of all dbs...

Just to mention that although the patch http://lists.mysql.com/commits/19550 won't help in the case, described by Tordjman, we'll try to come with a solution for this too.

Very annoying, I have ran my box for almost a half year with a torrent tracker that has phpmyadmin integrated, not to mention it have gone "away" somewhat too often, and made all other sites to suffer as well..
Somehow (lets call it LUCK) I spotted the I_S database and was wondering what does it do (and found this thread), otherwise I'd have been wasted more money into that machine with no results at all, as it happened before, and probably happens to many other guys all over.
Even worse that it's in the stable Debian tree, I just can't understand how does a bug like that make its way into stable releases..

Hi Sergey,

How can I apply this path to the 5.0.45?

We have a productoin server that is hitting this issue.

Is there any posibility to apply this patch (19588) and how?

Thanks in advance.

V.R.

I didn't see any *good* patch for that...

The only thing to do, is to pray... (1.5 years to correct this... awfull thing... for very small servers ;-) )

Hi all

But Sergey have a patch, can this patch be applied to the source of 5.0.45?

Isn't that the case?

Thanks in advance.
V.R.

=> Target Version:   5.1.23
& i do not see anything that bypass (or comment) the ugly readdir...

Tordjman: if you looking at the line with readdir() and is waiting for a patch that removes that line - I'm sorry, it won't happen anytime soon. The feature is called in our todo "a super-database", it is being considered, we understand that it is necessary, still it's not planned for 5.x (or even 6.x) releases.

What this patch does (and there will be more patches like this) - it significantly reduces the number of times when the server gets to readdir() call, the optimization works on the upper level and you won't see it by looking at readdir() line alone. Nevertheless, certain queries work tens or hundreds of times faster with this patch.

But, frankly speaking, the first patch doesn't help much in your case :(
(but it's not the last yet)

Victor: it should be possible. I doubt it'll apply automatically, must the code didn't change much since 5.0, so it should be doable. This is the final changeset with the patch, if you need it:
http://mysql.bkbits.net:8080/mysql-5.1/?PAGE=cset&REV=46b2572dt4Ob1t4wEK83IdmrmmHWLQ

I understant but, for me, only one "readdir" will kill all the db...

i have something like 60 000 db on it...and... how much tables? :-)

(could i do something like a return faillure instead of the readdir ? )

pushed addon for WL#3732 into 6.0
http://lists.mysql.com/commits/37177

pushed in 5.1.21

Excellent improvement noticed in 5.1.22

I am Genuinly confused, although 5.1.22 worked few days ago, I am now seeing 5.0.52 as faster server for I_S querys.

What would cause 5.1 to go back to its old ways?

SELECT             information_schema.columns.column_name,
information_schema.columns.data_type,
information_schema.key_column_usage.referenced_table_name
FROM               information_schema.columns
LEFT OUTER JOIN  information_schema.key_column_usage ON
information_schema.key_column_usage.table_schema = information_schema.columns.table_schema
AND information_schema.key_column_usage.table_name = information_schema.columns.table_name
AND information_schema.key_column_usage.column_name =
information_schema.columns.column_name

Noted in 5.1.23, 6.0.4 changelogs.

INFORMATION_SCHEMA implementation changes were made that optimize
certain types of queries for INFORMATION_SCHEMA tables so that they
execute more quickly. 
http://dev.mysql.com/doc/refman/5.1/en/information-schema-optimization.html
provides guidelines on how to take advantage of these optimizations
by writing queries that minimize the need for the server to access
the filesystem to obtain the information contained in
INFORMATION_SCHEMA tables. By writing queries that enable the server
to avoid directory scans or opening table files, you will obtain
better performance.

5.0\5.1 Benchmark, shows heavy IO.

Attachment: I_S Benchmark.jpg (image/pjpeg, text), 169.33 KiB.

Hi, could you please provide some numbers:
- execution time in both versions, and
- cardinality of the I_S tables involved in the query

1000 MS+ on *P4*
1000 MS- on C2
----------------
< 900 results on 5.1
< 800 results on 5.0

I think the improvement I notice in 5.1 was some testing I did on my home computer which is very fast.

I strongly suggest that this bug goes to QA, where a dedicated machine with stagment databases can be used to determine if this hotfix has made the problem worse or better and by how much.

A P4 or lower should be used in testing.

Hi, i have a question: while it is really wonderfull that this bug is being addressed in the 5.1 version ... will this be solved for the 5.0 ? 

thank you,
Moszi

Running MySQL 5.1.26-rc it still takes 7+ seconds to return the 73 foreign key constraints defined in a database with 89 tables. 

I am using the following query:

SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name
 FROM information_schema.key_column_usage
 WHERE table_schema = SCHEMA()
 AND referenced_table_schema = table_schema
 ORDER BY constraint_name, ordinal_position;

I have tried replacing SCHEMA() and table_schema with constants (strings) – it does not affect performance.

Is there any way to improve the performance?

you can use following queries, they should work faster:

SELECT constraint_name, table_name, column_name, referenced_table_name,
referenced_column_name
 FROM information_schema.key_column_usage
 WHERE table_schema = SCHEMA()
 AND table_schema = referenced_table_schema   // change field places
 ORDER BY constraint_name, ordinal_position;

or

SELECT constraint_name, table_name, column_name, referenced_table_name,
referenced_column_name
 FROM information_schema.key_column_usage
 WHERE table_schema = SCHEMA()
 AND referenced_table_schema = SCHEMA()
 ORDER BY constraint_name, ordinal_position;

see also
http://bugs.mysql.com/bug.php?id=39270

The latter executes about a second faster than the original. Still, it seems amazingly slow (7+ seconds).

Bug #42248 was marked as a duplicate of this one (on 6.0.x).

Still there using 5.1.36.

SELECT COUNT(*) FROM TABLES;

Takes hours to complete :(

I have the same problem, it's possible to fix this bug ?

We just upgraded to 5.1.43 from 5.0. This problem persists. An example from mysqldumpslow against msyql-slow log:

Count: 31  Time=166.34s (5156s)  Lock=0.00s (0s)  Rows=0.0 (0), 8users@2hosts
  SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING, ACTION
_STATEMENT, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE FROM information_schema.TRIG
GERS WHERE TRIGGER_SCHEMA= 'S' and EVENT_OBJECT_TABLE = 'S'

This query does have constant condition, but it is still very slow and blocking other applications to use the service.

If no short term solution, any work-around?

Xueshan

This was never really fixed and needs to be looked at again... Still extremely slow in 5.1.45!

The worst thing is that INFORMATION_SCHEMA is used in all sorts of places, such as the MySQL GUI Tools so certain things take far too long to run (that is if they even get a chance to complete).

...since this bug is now marked as closed, does that mean folks at MySQL ignore new comments on it? If so, we need to open a new bug!

Mysql 5.1.45 32bit Here:

This -> select TRIGGER_NAME from information_schema.TRIGGERS where TRIGGER_NAME='tr_LastSeen_debr'\G

Took me more than 411 seconds and got killed with 'lost connection' 

I also tried this on 5.1.47-MariaDB x86_64 (on a slave with nothing to do) and same there, the query actually never came back.  Always hanging on 'Opening tables' which I see elsewhere all the time, locking all my tables for an unknown reason.

It's so incredible that I see 2006 as first report then 4 years later I try selecting from the information scheme and nothing changed really...

I was hoping for MariaDB to help me out but unfortunately that didn't help either.   What an illusion this all is.

I totally agree with Glenn ..
I think the situation is getting more complicated now. I don't know if Oracle would ever wish to help solving such serious problems.

See bug #56178

I have the same problem.
My system has 700 databases, each with 100+ tables, and doing anything with the information_schema is just painful!

Why is this bug closed ? It's not work at all. SQL:2003 works good only for simple databases.

this bug was never really fixed, these queries take hours on 5.1.54, please reopen the bug and fix it!

Please PLEASE open this bug back up and fix it!

i made a patch for mine,

in mysql-5.0.92/sql/sql_show.cc

return 0 instead of return (find_files(... (2 times)
and return 0 instead of return files->push_back(thd->strdup(INFORMATION_SCHEMA_NAME.str));

when a request is done to INFORMATION_SCHEMA it does an error... (not a pb for me)

Has this bug been propagated to v8.x?