I have number of tables in the Locked state all of the time. An example:
| 14442 | dbtable1 | localhost | dbname | Query | 1291 | Locked | SELECT * FROM table1 WHERE topic_s='hooks-and-variables' | | 14443 | dbtable1 | localhost | | Sleep | 1291 | | | | 14461 | dbtable1 | localhost | dbname | Query | 1283 | Locked | SELECT table3.forum_id, forum_name, forum_slug, table4.topic_id, topic_name, topic_slug |
When I have too many such processes, all sites begin throwing database connection errors and I have to restart MySQL server to make them working again.
I use MyISAM engine for my databases, CentOS 5.7, WordPress and MySQL 5.1 if it matters.
I know what you probably suggest: switch to InnoDB or optimize the code. Unfortunately, it's beyond of my scope. I have to work with the configuration I have.
So, the question to you. How can I flush such queries periodically? I am sure it will help at least to prevent the server from overload.
I have tried
flush_time=300 with no luck. Do you need my.cnf here or other configs?
UPDATE 1. my.cnf
[mysqld] default-character-set=utf8 max_connections=100 set-variable = long_query_time=10 query_cache_limit = 32M query_cache_size = 128M query_cache_type = 1 table_cache=8K key_buffer_size=100M thread_cache_size=128 join_buffer_size = 1M innodb_buffer_pool_size = 100M join_buffer_size=4M tmp_table_size=32M max_heap_table_size=32M flush_time=200
UPDATE 2. first queries in the processes list before locks
| Id | User | Host | db | Command | Time | State | Info | 1515 | dbtable1 | localhost | dbname | Query | 2511 | Sending data | SELECT table1.forum_id, forum_slug, forum_name, forum_status, group_id, topic_count, forum_ic | | 1571 | dbtable1 | localhost | dbname | Query | 2478 | Sending data | SELECT table1.forum_id, forum_slug, forum_name, forum_status, group_id, topic_count, forum_ic |