Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

Thanks all.

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 |
share|improve this question
    
You say you can't switch to InnoDB -- why? –  Aaron Brown Nov 25 '11 at 16:44
    
Because the table which causes most problems uses the MyISAM full text searches. I am not a programmer and cannot change the SQL queries in the PHP code. –  Andrew Dec 2 '11 at 7:36
1  
What does the rest of the process list show? If tables are locked, there is something locking them, likely a long running insert, update, delete, insert select, or select for update. Also, please post your my.cnf settings. attack the cause (queries that are causing locking), not the symptom ( locked tables) –  Aaron Brown Dec 3 '11 at 3:36
    
Thank you for answer. I have updated my post. –  Andrew Dec 3 '11 at 10:35
1  
What does the entire SHOW FULL PROCESSLIST look like while the locks are occurring? What you have shown above only shows SELECT statements, which, unless they are FOR UPDATE, won't lock anything and don't provide any insight. You have 128MB query cache but only a 100MB key buffer. How much memory is available on this server? What is the ratio of reads to writes? The query cache is almost never valuable. The biggest thing you can tune on a MyISAM workload is the key_buffer_size and you should make it as large as you can. –  Aaron Brown Dec 6 '11 at 2:33

1 Answer 1

up vote 1 down vote accepted

You can use pt-kill from the Percona Toolkit ( http://www.percona.com/software/percona-toolkit/ ) to kill queries matching a specified criteria. You could setup a cron job to run every 300 seconds to do pt-kill --busy-time 300 to kill queries running over 300 seconds.

share|improve this answer
    
I have tried pt-kill --kill --busy-time 200 from the command line to test it and it just hung up. Do I use correct syntax? –  Andrew Nov 24 '11 at 19:12
1  
Sorry, I was mistaken. You don't run it from cron. You can run it as a daemon by passing the --daemonize option, and you can have it log to a log file with the --log <logfile> option. You can test this by setting a short busy-time such as pt-kill --busy-time 5 --kill then logging into your database and issuing a sleep query like select sleep(30);. –  slillibri Nov 25 '11 at 15:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.