mysqll 数据库相互堵塞问题

192.168.11.186 远程访问192.168.11.185 数据库

186上看到:

centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"show processlist" | grep 186
Warning: Using a password on the command line interface can be insecure.
1692	root	192.168.11.186:38167	NULL	Sleep	61		NULL

185上开启session 1692:
zabbix:/root# netstat -nap | grep 3306 | grep 185
tcp        0      0 192.168.11.186:38167        192.168.11.185:3306         ESTABLISHED 14778/mysql         
zabbix:/root# 


185上开启session1706:

Warning: Using a password on the command line interface can be insecure.
1692	root	192.168.11.186:38167	DEVOPS	Sleep	40		NULL
1706	root	192.168.11.186:53609	DEVOPS	Sleep	13		NULL


185上开启session1711:

centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"show processlist" | grep 186
Warning: Using a password on the command line interface can be insecure.
1692	root	192.168.11.186:38167	DEVOPS	Sleep	204		NULL
1706	root	192.168.11.186:53609	DEVOPS	Sleep	177		NULL
1711	root	192.168.11.186:32798	NULL	Sleep	3		NULL



zabbix:/root/mysql# perl mon_transaction.pl  kjk123123 192.168.11.185 
,,,,,
2016-11-24 09:08:32,31,1706,root,192.168.11.186:53609,DEVOPS
 mysql[192.168.11.185]  processid[1706] root@192.168.11.186:53609 in db[DEVOPS] hold  transaction time 31 
 
2016-11-24 09:08:32,275,1692,root,192.168.11.186:38167,DEVOPS
 mysql[192.168.11.185]  processid[1692] root@192.168.11.186:38167 in db[DEVOPS] hold  transaction time 275 
 
2016-11-24 09:08:32,25,1711,root,192.168.11.186:32798,DEVOPS
 mysql[192.168.11.185]  processid[1711] root@192.168.11.186:32798 in db[DEVOPS] hold  transaction time 25 
 
5548,1711,delete from test where username='admin',5547,1706,delete from test where username='admin'
  blocking_thread[1706] blocking waiting_thread[1711]'s delete from test where username='admin'
  
5548,1711,delete from test where username='admin',5546,1692,
  blocking_thread[1692] blocking waiting_thread[1711]'s delete from test where username='admin'
  
5547,1706,delete from test where username='admin',5546,1692,
  blocking_thread[1692] blocking waiting_thread[1706]'s delete from test where username='admin'

转载于:https://www.cnblogs.com/zhaoyangjian724/p/6198967.html