List:General Discussion« Previous MessageNext Message »
From:Jeremy Zawodny Date:August 7 2003 11:15pm
Subject:Re: Corrupt index = mysqld freeze?
View as plain text  
On Thu, Aug 07, 2003 at 03:58:37PM -0700, Jennifer Goodie wrote:
> > > 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. :-)
> A crash was recreated by running a specific query.


You didn't mention crashes in your first note.  That changes

> When myisamchk ran upon restart it said the index file for the table
> that was being queried was corrupt.  After careful observation, it
> was discovered that this is often the case, indexes for tables
> mentioned in the update log right before a crash were corrupt upon
> restart.  I'm more inclined to believe that they are corrupt due to
> us killing mysqld with the tables still open, since we can't
> authenticate to shutdown.  We also get a lot of table handler errors
> from myisamchk after a crash and kill, go figure.

Yeah, if you're killing MySQL by force, you really ought to check all
tables and repir broken ones.  Otherwise it's a craps shoot.

> > 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.
> We aren't running Linux threads.  We didn't seem to be experiencing
> any of the issues it helps.

At least not the obvious ones. :-)

We've found that on moderately busy machines here, upgrading to a
LinuxThreads-based MySQL reduced CPU utiliization by 30% or so.

> For a while we'd only have this happen once a month, then it was
> once a week.  Lately it has been a few times a day, but everyone is
> messing with box.


> In my opinion, for us it definitely happens when an expensive query
> is run on an active table.  Looking at the logs, there's always a
> bunch of disconnects all at once right before connections stop.

Hmm.  I hadn't noticed that yet.  But I hadn't thought to look at
disconnect rates either.

> I've been on the box at the mysql prompt quite a few times when it
> has happened and there was always a large amount of threads waiting
> for a lock to clear, and as soon as they went through nothing could
> connect, but this doesn't happen everytime we have a large queue, so
> there must be something else in the mix.  If you think any info I
> have might help you, let me know.  I'd love to hear any ideas you
> have.

I don't know how to do this with pthreads but with LT, I'd like to
identify a few of the pids for the struck threads and then get a
snapshot of the call stack to see where they're waiting.

Jeremy D. Zawodny     |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<Jeremy@stripped>  |

MySQL 4.0.13: up 6 days, processed 213,656,247 queries (397/sec. avg)
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