Hi,
we've had a strange problem with mysql for quite some time which could
not be solved so far.
The problem is with servers with a lot of databases (10.000+)
If one connects to mysql and issues a show database this will take ages
and eat up 100% cpu.
It does not matter if you connect as root or as a standard user that has
only privileges for one database.
If you set no-auto-rehash in the cnf then show databases will be fast
again as root.
It still takes ages as a normal user though.
e.g.
mysql -uroot -p
mysql> show databases;
...
+------------------+
55693 rows in set (0.21 sec)
mysql>
mysql -uproftpd -p
mysql> show databases;
+----------+
| Database |
+----------+
| proftpd |
+----------+
1 row in set (4.70 sec)
If auto-rehash is not disabled then show databases / use <database>
takes ages for both root / standard users (around 15 sek in the example).
This is not really a hardware / filesystem problem I think. We have
thrown any possible hardware at it. The examples above are on a
Dual Opteron 280
16GB RAM
12x 18GB 15K RPM SCSI @ Mylex 600 U320 RAID 10
Filesystem XFS (also tested ReiserFS, other people with the same problem
also tested ext3)
30-60k Databases (problem also exists a bit slighter on a small set of
7000 databases - hardware independend no matter what you throw at it)
MySQL 4.0.26 AMD64 (problem also verified on i386 with different sets of
databases + servers etc)
Server is not in use, not other processes running etc.
Almost no filesytem activity during show databases. Only 100% CPU.
I have also found other references to this problem:
http://lists.mysql.com/mysql/196396
http://lists.mysql.com/mysql/197140
http://www.rootforum.de/forum/viewtopic.php?t=39984 (german, examples +
same problem)
If you need any additional information I can provide you with whatever
you need.
Thank you for your time and efforts.
Alex
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
