How do I kill all the processes in Mysql “show processlist”?

When MariaDB Galera Master/Master replication facing deadlock, new connection may not able to process and so looks killed. This can be checked by:

mysql -e 'show processlist'

Temporary Solution

If you see some tables are in status of "Waiting for table metadata lock", then you need to kill all process with:

mysqladmin processlist | \
awk '$2 ~ /^[0-9]/ {print "KILL "$2";"}' | \
mysql

How to Prevent Deadlock?

I am still trying to figure out a long term solution, and now trial with following my.cnf variables, which at least able to pass stress (ab) and availability (httrack) test for 4+ hours continuously:

innodb_stats_on_metadata = 0
wsrep_retry_autocommit = 0

Reference

  • http://stackoverflow.com/a/10608839
  • http://www.mysqlperformanceblog.com/2013/02/01/implications-of-metadata-locking-changes-in-mysql-5-5/
  • http://dinglin.iteye.com/blog/1575840
  • http://www.mysqlsupport.cn/how-to-mysql-load-veryhigh/
  • http://groups.drupal.org/node/218704#comment-806463/
Tags: 

Add new comment

Restricted HTML

  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <h4> <h5> <h6>
  • Lines and paragraphs break automatically.
  • Web page addresses and e-mail addresses turn into links automatically.
  • HTML tags will be transformed to conform to HTML standards.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
  • Web page addresses and e-mail addresses turn into links automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.