Nico, as a first stab, I would try optimizing the tables in question.
OPTIMIZE TABLE mysql.user
etc.
The one with 194,177 entries would be a good candidate for this especially.
I wonder also if you would see something logged in the slow query log
as this happens? Perhaps the tables_priv table could use an
additional index, if OPTIMIZE doesn't do the trick.
Hope this helps.
Dan
On 8/23/06, Nico Sabbi <[EMAIL PROTECTED]> wrote:
Hello,
as the title reads, when a user X with access to few tables runs "show
databases"
the query executes _very_ slowly and there's a sudden HUGE load (mysqld
takes 99% cpu for the whole
duration of the query):
show databases;
+--------------------+
| Database |
+--------------------+
| A |
| B |
| C |
| D |
| E |
| F |
+--------------------+
6 rows in set (18.35 sec)
Now if I connect as super_user:
mysql> show databases;
...
286 rows in set (0.00 sec)
There are a lot of users defined with very fine grained grants:
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 1025 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from mysql.db;
+----------+
| count(*) |
+----------+
| 1975 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from mysql.tables_priv;
+----------+
| count(*) |
+----------+
| 194177 |
+----------+
1 row in set (0.00 sec)
This anomaly is a very big problem for me. What can I do to solve it?
Thanks,
Nico
--
Email.it, the professional e-mail, gratis per te: http://www.email.it/f
Sponsor:
Ascolta tutta la musica che vuoi gratis!
* Clicca su www.radiosnj.com
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5176&d=23-8
--
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]
