List:General Discussion« Previous MessageNext Message »
From:Jennifer Goodie Date:August 7 2003 8:41pm
Subject:Corrupt index = mysqld freeze?
View as plain text  
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.

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.  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.  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.

Thread
Corrupt index = mysqld freeze?Jennifer Goodie7 Aug
  • Re: Corrupt index = mysqld freeze?Jeremy Zawodny7 Aug
    • RE: Corrupt index = mysqld freeze?Jennifer Goodie8 Aug
      • Re: Corrupt index = mysqld freeze?Jeremy Zawodny8 Aug
        • RE: Corrupt index = mysqld freeze?Jennifer Goodie8 Aug
        • Re: Corrupt index = mysqld freeze?Dan Nelson8 Aug
          • Re: Corrupt index = mysqld freeze?Jeremy Zawodny9 Aug
            • Re: Corrupt index = mysqld freeze?Jeremy Zawodny9 Aug
              • RE: Corrupt index = mysqld freeze?Jennifer Goodie14 Aug
                • Re: Corrupt index = mysqld freeze?Jeremy Zawodny18 Aug
  • Re: Corrupt index = mysqld freeze?Daniel Kasak8 Aug
  • Re: Corrupt index = mysqld freeze?Ask Bjørn Hansen11 Aug