Justin wrote:
> Sometimes I get about 300 connections to the server, all are selects
> and all select and get the data returned but the connection doesn't go
> away and the website doesn't load up.. usually if there is a lock, the
> selects wait 2-3 secs and build up, but once unlocked the queries all
> are performed and then go away.. and the sites load fine.. 3 times
> today this has happened and I'm trying to figure out what could be the
> cause of it. if I restart MySQL everything is good.
>
> Anyone have any ideas or any ideas on how I can trace where the
> culprit would be..
>
> it's a LAMP backend..
>
>
Are you classing insert...select or create table....select as selects?
They have very different locking effects.
Is the database in replication? if so is it a master or slave?
Do you have NFS mounts anywhere on the database server?
Are you using exclusively innodb tables? if not the source is probably
table contention.
Get a couple of 'show full processlist', 'show innodb status' query
outputs during the lockups and run vmstat 1 -S M in another terminal.
With the outputs from both you've something to work with.

Nigel Wood