That's what I actually did now. We have got the "databases start with
usernames + number appended" situation here so i patched sql_show.cc
code to only do acl checks on databases starting with the username.
Still not optimal but cuts down a show databases on a server with 60.000
databases from 15 seconds to 0.14 seconds which is ok.
Alexey Polyakov schrieb:
That's not a bug, that's a missing feature (or we can call it
unoptimal behaviour).
I think that having 10000 databases and 10000 users on a single mysqld
and doing "show databases" query often isn't what developers see as
intended usage for MySQL. :)
Here's what happens when you do "show databases" query:
1) mysqld gets a list of subdirectories of mysql data dir (usually
/var/lib/mysql). Each directory is a database
2) It loops through all entries, and adds an entry to result set if:
a) an user has global show databases privilege
b) an user has been granted access for this database
Part b) is what actually takes time. For each entry the server first
checks ACL cache. It's a hash table and lookups against it are very
fast. But for "show databases" query most lookups will return a miss,
and a miss means full scan of the whole acl db. So for 10000 databases
it scans table with 10000 rows 10000 times, which means 10 million
scanned records. That's why it's slow.
As a workaround, if one has some rules regarding which user can see
which DB (for example, if usernames and database names start with same
substring), they can add this check to the code (so scans will be
avoided for most entries). That's still far from optimal, but at least
"show databases" will take dozens of milliseconds instead of seconds.
On 5/6/06, sheeri kritzer <[EMAIL PROTECTED]> wrote:
Perhaps it's time to file a bug report, then?
-Sheeri
On 5/3/06, Alex <[EMAIL PROTECTED]> wrote:
> This problem is indeed not related to OS / Hardware Problems.
>
> Take a look at this thread:
>
> http://lists.mysql.com/mysql/197542
>
> Read the part about show databases as root vs standard user
>
> + observed file system activity.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Alexey Polyakov
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
