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-...
• http://dinglin.iteye.com/blog/1575840
• http://www.mysqlsupport.cn/how-to-mysql-load-veryhigh/
• http://groups.drupal.org/node/218704#comment-806463/