Execution time of show databases increases when fewer databasesare visible
Hi, I have a strange and annoying problem with Mysql 4.0.26 that I hope someone will help me to fix: the more databases a user has the less time it takes to execute show databases and vice versa: show databases; +--+ 5 rows in set (7.97 sec) --- show databases; ++ 74 rows in set (5.87 sec) --- show databases; ++ 141 rows in set (3.66 sec) --- show databases; ++ 210 rows in set (1.45 sec) Is this behaviour due to a bug or maybe my mysql tables are messed up? Here are some stats: select count(*) from mysql.user; +--+ | count(*) | +--+ | 998 | +--+ 1 row in set (0.02 sec) select count(*) from mysql.host; +--+ | count(*) | +--+ | 0| +--+ 1 row in set (0.03 sec) select count(*) from mysql.db; +--+ | count(*) | +--+ | 1402 | +--+ 1 row in set (0.03 sec) select count(*) from mysql.columns_priv; +--+ | count(*) | +--+ | 0| +--+ 1 row in set (0.03 sec) mysql select count(*) from mysql.tables_priv; +--+ | count(*) | +--+ | 145894 | +--+ 1 row in set (0.01 sec) Nico -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Sei single e stai cercando lÂ’amore? Entra subito in Meetic, iscriviti gratis, consulta i profili di milioni di single e chatta con loro Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=4051d=21-4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Execution time of show databases increases when fewer databasesare visible
On 4/21/06, Nico Sabbi [EMAIL PROTECTED] wrote: Is this behaviour due to a bug or maybe my mysql tables are messed up? I'm not sure if we can call it a bug. But spending 8 seconds (and using 100% CPU) for a simple SHOW DATABASES query is a bit too much, and should be fixed. mysqld first reads directories name from its data directory, then loops over all names and does the following check: if (thd-master_access (DB_ACLS | SHOW_DB_ACL) || acl_get(thd-host, thd-ip, thd-priv_user, file_name,0) || (grant_option !check_grant_db(thd, file_name))) If a query runs faster when user has access to more DBs, then the problem most likely lies within check_grant_db function. But it all needs more careful investigation. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Execution time of show databases increases when fewer databasesare visible
On 4/21/06, Nico Sabbi [EMAIL PROTECTED] wrote: the more databases a user has the less time it takes to execute show databases and vice versa: If no rows in `show databases` output appear because some user has some table/columns privs but no db privs for a database in question, then I think that check_grant_db I mentioned in previous email may be safely hacked away. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
