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.
Oh.
You didn't mention crashes in your first note. That changes
everything.
> 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.
Ugh.
> 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
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
<Jeremy@stripped> | http://jeremy.zawodny.com/
MySQL 4.0.13: up 6 days, processed 213,656,247 queries (397/sec. avg)
