0

I have the following pt-kill daemon setup. It works but doesn't kill a query such as SELECT GET_LOCK(300). If I remove --ignore-state 'User lock' will this fix it?

sudo /usr/bin/pt-kill --ignore-state 'User lock' --rds --match-command Query --victims all --match-user phppoint --daemonize --busy-time 15 --kill --print h=HOST,u=master,p=PASSWORD,P=3306

It looks like if I don't specify --ignore-state the default is locked. Will this still work?

From manual:

The default is to keep threads from being killed if they are locked waiting for another thread.

What are the downsides of killing locked queries?

Chris Muench
  • 487
  • 3
  • 10
  • 31

1 Answers1

1
User lock

The thread is going to request or is waiting for an advisory lock requested with a GET_LOCK() call. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it).

So I guess your query doesn't fall under this category. Please find appropriate state for thread state and apply same https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

If you kill locked query only major downsides I can see is dependent applications/backends may behaviour may change. Before killing check SHOW FULL PROCESSLIST and EXPLAIN the query so you know the impact(Who/When/What).

Edit1: So first check what type of thread SELECT GET_LOCK(300) is and change your command accordingly, if you choose correct state for sure it will work until it hit some bug.

Hope this will help.

asktyagi
  • 2,860
  • 2
  • 8
  • 25
  • I am ignoring User lock but I think I want to kill these queries too. What should I put for `--ignore-state` type: string; group: Query Matches; default: Locked Ignore queries whose State matches this Perl regex. The default is to keep threads from being killed if they are locked waiting for another thread. – Chris Muench Sep 05 '19 at 15:37
  • I updated my answer, what I meant was check the thread type and change your command accordingly. Based on need you can select multiple states. – asktyagi Sep 06 '19 at 03:54