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.