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!
<Jeremy@stripped> | http://jeremy.zawodny.com/
MySQL 4.0.13: up 6 days, processed 212,550,116 queries (399/sec. avg)

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.