Extremely slow access to information_schema
Hi, in Mysql 5.0 accessing information_schema.tables means almost certainly dropping down the DBMS. With my configuration 412 databases 357417 grants every query to information_schema.tables takes minutes, while the equivalent show tables from... (that I can't absolutely use) is immediate. Is there anything that I can do to speed it up? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Extremely slow access to information_schema
Make sure the tables that the information_schema are not locked. This is because MyISAM tables, that are constantly being inserted into (Each insert, update, delete on a MyISAM table does a table lock), must update the TABLE_ROWS column in information_schema.tables to have the latest count. If you have a Replication Slave, do your information_schema counts from that instead, since one table at a time is locked on a slave. Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM : RolandoLogicWorx Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Nico Sabbi [mailto:nicola.sa...@poste.it] Sent: Tuesday, April 28, 2009 10:05 AM To: MySql Subject: Extremely slow access to information_schema Hi, in Mysql 5.0 accessing information_schema.tables means almost certainly dropping down the DBMS. With my configuration 412 databases 357417 grants every query to information_schema.tables takes minutes, while the equivalent show tables from... (that I can't absolutely use) is immediate. Is there anything that I can do to speed it up? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Extremely slow access to information_schema
Hi, On Tue, Apr 28, 2009 at 1:04 PM, Rolando Edwards redwa...@logicworks.net wrote: Make sure the tables that the information_schema are not locked. This is because MyISAM tables, that are constantly being inserted into (Each insert, update, delete on a MyISAM table does a table lock), must update the TABLE_ROWS column in information_schema.tables to have the latest count. That's not how INFORMATION_SCHEMA works. Those aren't real tables. Behind the scenes it is just doing SHOW TABLE STATUS on each table and populating a temporary structure with the results. Nico, it is my opinion that I_S is not suitable for heavy production use :-) I have seen several cases of severe performance problems caused by it. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Extremely slow access to information_schema
Il giorno mar, 28/04/2009 alle 17.11 -0400, Baron Schwartz ha scritto: Hi, On Tue, Apr 28, 2009 at 1:04 PM, Rolando Edwards redwa...@logicworks.net wrote: Make sure the tables that the information_schema are not locked. This is because MyISAM tables, that are constantly being inserted into (Each insert, update, delete on a MyISAM table does a table lock), must update the TABLE_ROWS column in information_schema.tables to have the latest count. That's not how INFORMATION_SCHEMA works. Those aren't real tables. Behind the scenes it is just doing SHOW TABLE STATUS on each table and populating a temporary structure with the results. Nico, it is my opinion that I_S is not suitable for heavy production use :-) I have seen several cases of severe performance problems caused by it. Baron after what I'm seeing I can't avoid to agree completely. I also read of extremely serious DOS problems caused by this crazy (and severely castrated) implementation of the I_S. How did it end up in mainline? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
