>>>>> "Naveen" == Naveen Nalam <nalam@stripped> writes:
Naveen> I have this event occur about once a day where it appears that a SELECT
Naveen> query will get stuck for some reason, then when UPDATE queries occur they
Naveen> block waiting for access to the table. Then after the UPDATEs get blocked,
Naveen> future SELECTs get blocked (I guess since SELECT is lower priority that
Naveen> UPDATE) until max-connections is reached - at which point nothing can
Naveen> occur. I've tried using no LOCK statements at all and the problem still
Naveen> occurs. Below is output of when I'm using LOCK statements before and after
Naveen> every query.
Naveen> The output of mysqladmin looks like:
Naveen> +-------+--------+-------------------------+-----
Naveen> | db | Command | Info
Naveen> +-------+--------+-------------------------+-----
Naveen> | media | Locked | LOCK TABLES HostInfo WRITE |
Naveen> | media | Locked | LOCK TABLES HostInfo WRITE |
Naveen> | media | Query | SELECT filename,size FROM HostInfo h |
Naveen> | media | Locked | LOCK TABLES HostInfo AS h READ|
Naveen> | media | Locked | LOCK TABLES HostInfo AS h READ|
Naveen> | media | Locked | LOCK TABLES HostInfo READ|
Naveen> | media | Locked | LOCK TABLES HostInfo AS h READ|
Naveen> | media | Locked | LOCK TABLES HostInfo AS h READ|
Naveen> ...
Naveen> Any thoughts on what might be the problem? For some reason the Query (on
Naveen> 3rd line) doesn't go away. After I noticed the problem, I waited a minute
Naveen> to see if it would go away, but it didn't - so I'm thinking for some
Naveen> reason it gets "stuck" in the mysql server. Is there a way to have mysqld
Naveen> terminate queries that last longer than 10 seconds?
Not for the moment; You can however do this with a simple script (perl ?)
that monitors MySQL and uses 'kill' to kill queries that has been
going on for a long time.
Naveen> I think that by changing the WRITES to low priority, at least people would
Naveen> still be able to execute SELECTs, but since that one thread never release
Naveen> the READ lock, the WRITEs will never occur.
Naveen> I'm using 3.21.33c w/ Red Hat 5.2 (I can't upgrade to 3.22.x cus for some
Naveen> reason it runs about 5-10 times slower on the same query, same db data,
Naveen> same machine.)
The right way to solve this is to use MySQL 3.22; I will shortly
comment about your problem with MySQL 3.22 and 3.21 when it comes to
speed; I can see no reason why MySQL 3.22 would be slower; If this
is the case for you, this can probable be fixed with some simple
optimizer hints for your case...
Regards,
Monty
