List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:August 7 2003 11:37pm
Subject:Re: Corrupt index = mysqld freeze?
View as plain text  
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
>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
>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.
I'm not sure how related this is, but we have some relatively small 
tables ( 50,000 rows ) that exhibited the same behaviour when using 
MyISAM and MS Access front-ends. My solution was to change all affected 
tables to InnoDB. I hadn't noticed anything about indexes as you 
described, but then I wasn't looking.

When it used to happen to us, there would be one update or insert 
process that mysqladmin claimed was 'locked' ( I think - this was a 
while ago ), and then a backlog of other user processes - often in 
unrelated tables - would start appearing. mysqladmin shutdown didn't 
work ( but mysqladmin processlist did - go figure ) - I had to kill -9 
all mysql processes and restart the server.

I always put the problem down to MS Access and it's record-locking 'style'.

Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped

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