MySQL Forums
Forum List  »  MyISAM

Some queries never finish and prevent others from finishing--why?
Posted by: way_uhf
Date: December 14, 2006 03:21PM

UPDATE: it seems like this is a problem many other people have experienced. Here's an old thread with similar reports, but no resolution:
http://forums.mysql.com/read.php?24,39066,39066#msg-39066
If anyone has any insight, please post. Thanks.
------------------------------------------------
Hi, I'm a decently experienced MySQL user, but can't figure out this weird issue.

Configuration:
-MySQL 4.1.10a running on Fedora Core 2 (1 gb ram)
-2 databases, all MyISAM, totaling about 290 mb and 2.5 million rows

Issue:
At random times, with random queries, a query will lock a table, but never unlock it. Other queries then pile up, max connections is reached, and the server becomes unusable. I have to then reboot the server (because killing the first query doesn't do anything beyond the status changing to "killed" but it never goes away).

Relevant my.cnf entries:
skip-locking
skip-innodb
long_query_time=4
tmp_table_size = 64M
max_connections=50
wait_timeout=5
key_buffer_size = 128M
table_cache = 192
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
thread_cache = 8

I ran EXPLAIN on the queries in question and did not find any anomalies (i.e. indexes are present, etc.) Some queries are standard phpBB2 (a common bulletin board) queries, so I know that's not the issue. Even executing them manually returns the result immediately, so they're not slow. The only alarming thing I noticed is Created_tmp_tables to Created_tmp_disk_tables ration is about 10:1, so I am getting some temp tables created on disk. But I imagine, even if that was the cause, eventually the query would finish and unlock the table it's using.

So, I am stumped and would appreciate any tips. Thanks so much in advance!



Edited 4 time(s). Last edit at 12/15/2006 08:59AM by way_uhf.

Options: ReplyQuote


Subject
Views
Written By
Posted
Some queries never finish and prevent others from finishing--why?
2531
December 14, 2006 03:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.