David, I don't know the answers to your biggest problem, but do know the answer to your last question: Yes, it seems perfectly normal to nail one CPU and take 2 minutes to show tables with 80,000 tables. Somewhere on your system is a directory that has 240,000 files (80,000 tables times 3 files per table if they are MyISAM tables). You are doing some serious multiple-level file system indirection to wade through all the inodes allocated across thousands of indirect and indirect-indirect blocks in the filesystem. Yikes!
On the other hand, one database over 6GB should not be a problem, as far as I know but I don't know everything. What's your configuration file got for settings for various parameters such as caching and what-not? Maybe you're running out of memory and thrashing the swap devices? ..chris ----- Original Message ----- From: "DAVID DECESARE" <[EMAIL PROTECTED]> I have run into an intermittent problem since one of our MySQL databases grew over 5 GB. Basically, about once to twice a day the CPU will hit 100% utilization (and stay there) and MySQL will stop accepting queries (I couldn't even do a normal shutdown of MySQL). The problem happened more frequently as the database grew over 6 GB. I end up rebooting the computer each time this happens (since I can shutdown MySQL normally) and the problem goes away for about 10 hours. Here's a list of what I am running: MySQL version: mysql-3.23.36-sun-solaris2.7-sparc OS: Solaris 8 (04/01) System: SunFire 280; Single 750 MHz; 512 MB RAM; 18 GB 10,000 RPM internal drive Number of Tables in database: approx. 8,000 Last database size: 6.3 GB The reason I think my problem has to do with the size of the database is that we have another system that has not had any problems. It has ten times the number of tables (about 80,000), but the database size is only 3.2 GB. Again, no problems with this machine at all. Has anyone else seen this problem? I would like to try version 3.23.43. Unfortunately these are full production systems that I can't take offline (and I don't have a spare SunFire). I've read about tables growing over 4 GB so I don't know why I should be having this problem. My temporary fix is to remove tables not immediately needed to keep the database size under 3 GB. However we add about 500 MB of data every 24 hours so it doesn't last long. (One other quick note: On the system with 80,000 tables, running "SHOW TABLES" takes about 2 minutes on a dual SunFire box (maxing out one of the CPUs). Is that amount of time normal? I know 80,000 tables is a lot, but it's either that or create fewer tables having more than 1 billion rows each.) Thanks in advance for any help! David DeCesare --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
