Hi David, DAVID DECESARE writes: > 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:
Please check your tables with myisamchk, some of them maybe corrupted and may cause mysqld to take up all the CPU. Database size is no limitation for MySQL, a particular table size on the other hand may be. If any your tables is growing close to 4 Gb size, you should make sure that you've configured those tables to be able to grow beoynd that size. The default maximum size for MyISAM tables is the 4Gb. Please search for words 'MAX_ROWS' and 'AVG_ROW_LENGTH' from the MySQL manual. You can change those later on with 'ALTER TABLE'. The maximum table size for MyISAM tables is 9223372036854775808 bytes. You can check the maximum size for a particular table with myisamchk -dv tblname > 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. If you run into problems that mysqld won't shutdown with mysqladmin shutdown, you may likely have a corrupted table around. By shutting MySQL down the hard way (like you described above), you may have corrupted some table. Normally, one should always be able to run 'mysqladmin -uroot proc' when mysqld takes CPU. This would help us diagnose the problem. If your tables are OK and you cannot run the command while MySQL is eating CPU, further debugging may be needed. (try running 'mysqladmin -u root -i 1 proc' same time mysqld is taking cpu) > 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.) 80.000 tables = 240.000 files. Doesn't sound impossible it might take nearly 2 minutes for SHOW TABLES to complete. The speed should be comparable to 'ls'. Try running 'ls' in the datadir and see how long it takes. > Thanks in advance for any help! > > David DeCesare Regards, - Jani For technical support contracts, visit https://order.mysql.com/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Jani Tolonen <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com --------------------------------------------------------------------- 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
