Re: HUGE load when user with few privileges execs show databases
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. done, but with no improvement I wonder also if you would see something logged in the slow query log as this happens? done, but as you can see there's nothing in the log we didnt already know: cat *slow* /usr/sbin/mysqld-max, Version: 4.0.26-Max-log, started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument # Time: 060824 15:08:14 # [EMAIL PROTECTED]: X[X] @ nico.abc.loc [192.168.0.34] # Query_time: 20 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 show databases; Perhaps the tables_priv table could use an additional index, if OPTIMIZE doesn't do the trick. Hope this helps. Dan done, but still no improvement :( Thanks for your help -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Conquista e fatti conquistare aderendo al Club PER DUE di Blinko Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5490d=24-8 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: HUGE load when user with few privileges execs show databases
Bummer! I was hoping that you'd see what actually gets executed during a SHOW DATABASES, like select * from user, table_privs where so you could then run an EXPLAIN on it. For me on 5.0.21 EXPLAIN SHOW DATABASES gives me an error so no joy there. I suspect you've got a lot more entries in there than most folks, and the command simply isn't running well with the default indexes on the tables. Not knowing exactly how it queries, it's hard to optimize indexes though. I'd file a bug report on this, personally. For due diligence I would first make sure to CHECK TABLE on each table first to ensure no problems (though I would think problems would have surfaced during OPTIMIZE if there were any). I'd also restart the mysql server software to see if that makes a difference, if you haven't. Then, assuming no problems found and no change, file a bug. Dan On 8/24/06, Nico Sabbi [EMAIL PROTECTED] wrote: 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. done, but with no improvement I wonder also if you would see something logged in the slow query log as this happens? done, but as you can see there's nothing in the log we didnt already know: cat *slow* /usr/sbin/mysqld-max, Version: 4.0.26-Max-log, started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument # Time: 060824 15:08:14 # [EMAIL PROTECTED]: X[X] @ nico.abc.loc [192.168.0.34] # Query_time: 20 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 show databases; Perhaps the tables_priv table could use an additional index, if OPTIMIZE doesn't do the trick. Hope this helps. Dan done, but still no improvement :( Thanks for your help -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Conquista e fatti conquistare aderendo al Club PER DUE di Blinko Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5490d=24-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]
Re: HUGE load when user with few privileges execs show databases
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=5176d=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]
