On Thu, Aug 07, 2003 at 01:41:57PM -0700, Jennifer Goodie wrote: > I have a stand alone database server. It is a RAID5 running mySQL 3.23.55 > on FreeBSD 4.1 and has 768MB of RAM, I'm not sure on the processor speed, > but I think it's a P3 1GHz. It has several tables with 20-40 million rows > and a ton of smaller tables with less than 1 million rows. All tables are > MyISAM and we have fewer than 10 queries per second. The super large tables > are write only for the most part, with most reads taking place in off peak > hours (a cron to generate aggregate data). The smaller tables are > read/write. > > We've been experiencing a problem where mysqld stops responding to new > connections. Any active connection is fine and can run any query it wants, > but all new connections get stuck in the "authenticating user" phase. CPU > and load drop to about zero when this happens, so I don't think it's the > notorious threading issue. This "freeze" happens when more than 10-12 > connections drop at the same time, usually when a queue caused by a table > lock clears out.
Sounds familiar. > One of my coworkers insists that this is due to corrupt indexes, stating > that if an index points to a location outside of the record set mysql gets > confused and hangs. Does he have any evidence whatsoever for that? I'm 99% sure he's wrong--at least in *our* cases. :-) > It has also been stated that multicolumn indexes are a problem, > especially if they contain more than 3 columns. This goes against > everything I know about mysql. In my experience if there is file > corruption an error gets returned promptly. Right. MySQL can detect most corruption. > I also believe multicolumn indexes are a valuable feature. I have > been told that I need to get rid of all multicolumn indexes in order > to make the server stable. Needless to say, I am not very happy > with this solution and don't have a lot of faith in it working. > > Has anyone else experienced anything similar to this, and if so what > did you do to fix it? Anyone want to weigh in on the index theory > because it doesn't really sound right to me, but I'm not exactly an > expert. We've seen that happen too on more recent FreeBSD versions with LinuxThreads. So far it's not happening all that often and it seems that the chance of it happening is much greater right after MySQL has been [re]started. I haven't had much luck in tracking it down further. But I have a few more ideas next time I see it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,550,116 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
