先看看问题 show processlist
+-------+-------+----------------------+-----------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------+----------------------+-----------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 8918 | slave | h71:60662 | NULL | Binlog Dump | 2679345 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 26858 | root | jstumax2yk6fgt:50296 | qnmj_config | Sleep | 3 | | NULL |
| 26859 | root | jstumax2yk6fgt:33402 | qnmj_account | Sleep | 15 | | NULL |
| 26860 | root | h53:59114 | xyy_cluster_04 | Sleep | 234 | | NULL |
| 26862 | root | jstumax2yk6fgt:43004 | xyy_cluster_04 | Query | 0 | Locked | insert into items (itemId,sellerId,title,cid,sellerCats,picUrl,num,price,onsaleStatus,uv,sales,analy |
| 26863 | root | jstumax2yk6fgt:43005 | xyy_cluster_04 | Query | 0 | Locked | insert into items (itemId,sellerId,title,cid,sellerCats,picUrl,num,price,onsaleStatus,uv,sales,analy |
| 26864 | root | jstumax2yk6fgt:43006 | xyy_cluster_04 | Query | 0 | Locked | insert into items (itemId,sellerId,title,cid,sellerCats,picUrl,num,price,onsaleStatus,uv,sales,analy |
| 26867 | root | jstumax2yk6fgt:44227 | qnmj_pay | Sleep | 15 | | NULL |
| 26868 | root | jstumax2yk6fgt:35824 | qnmj_pay | Sleep | 15 | | NULL |
| 26869 | root | jstumax2yk6fgt:50773 | qnmj_account | Sleep | 15 | | NULL |
| 26870 | root | h53:44269 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26871 | root | jstumax2yk6fgt:33283 | qnmj_stat | Sleep | 8 | | NULL |
| 26872 | root | jstumax2yk6fgt:33292 | qnmj_mf | Sleep | 1 | | NULL |
| 26873 | root | jstumax2yk6fgt:57522 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26874 | root | h53:56481 | qnmj_config | Sleep | 5 | | NULL |
| 26875 | root | jstumax2yk6fgt:36538 | qnmj_cluster_01 | Sleep | 1 | | NULL |
| 26884 | root | jstumax2yk6fgt:60497 | qnmj_cluster_01 | Sleep | 5 | | NULL |
| 26885 | root | jstumax2yk6fgt:60498 | qnmj_mf | Sleep | 9281 | | NULL |
| 26887 | root | jstumax2yk6fgt:60502 | qnmj_mf | Sleep | 9281 | | NULL |
| 26888 | root | jstumax2yk6fgt:60503 | qnmj_cluster_01 | Sleep | 4 | | NULL |
| 26889 | root | jstumax2yk6fgt:60504 | qnmj_mf | Sleep | 9281 | | NULL |
| 26890 | root | jstumax2yk6fgt:60506 | qnmj_mf | Sleep | 9281 | | NULL |
| 26891 | root | jstumax2yk6fgt:60512 | qnmj_mf | Sleep | 9283 | | NULL |
| 26892 | root | jstumax2yk6fgt:60715 | qnmj_mf | Sleep | 9283 | | NULL |
| 26896 | root | jstumax2yk6fgt:36447 | qnmj_cluster_01 | Sleep | 1 | | NULL |
| 26905 | root | h53:41286 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26906 | root | h52:49593 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 26909 | root | h52:49620 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 26916 | root | jstumax2yk6fgt:56174 | qnmj_cluster_01 | Sleep | 2 | | NULL |
| 26917 | root | jstumax2yk6fgt:56175 | qnmj_cluster_01 | Sleep | 14 | | NULL |
| 26918 | root | jstumax2yk6fgt:55585 | qnmj_cluster_01 | Sleep | 11 | | NULL |
| 26919 | root | jstumax2yk6fgt:55920 | qnmj_cluster_01 | Sleep | 8 | | NULL |
| 26920 | root | jstumax2yk6fgt:48918 | qnmj_config | Sleep | 5 | | NULL |
| 26921 | root | jstumax2yk6fgt:49919 | qnmj_project | Sleep | 1 | | NULL |
| 26922 | root | jstumax2yk6fgt:37703 | qnmj_btqh | Sleep | 7868 | | NULL |
| 26923 | root | h51:53845 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 26927 | root | h51:53888 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26928 | root | h51:54055 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26931 | root | jstumax2yk6fgt:38193 | qnmj_cluster_01 | Sleep | 1 | | NULL |
| 26935 | root | h52:50845 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 26941 | root | h52:50856 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 26944 | root | h53:51592 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26949 | root | h51:35869 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 26950 | root | jstumax2yk6fgt:45435 | qnmj_config | Sleep | 2 | | NULL |
| 26951 | root | jstumax2yk6fgt:58197 | qnmj_pay | Sleep | 6600 | | NULL |
| 26952 | root | h52:49936 | qnmj_config | Sleep | 1 | | NULL |
| 26954 | root | jstumax2yk6fgt:42192 | qnmj_cluster_01 | Sleep | 0 | | NULL |
| 26955 | root | jstumax2yk6fgt:42193 | qnmj_cluster_01 | Sleep | 0 | | NULL |
| 26956 | root | jstumax2yk6fgt:42380 | xyy_cluster_04 | Query | 0 | Sending data | select sum(iuv) as iuv, sum(alipayTradeNum) as alipayTradeNum from items_target where itemId = 36353 |
| 26957 | root | jstumax2yk6fgt:42381 | xyy_cluster_04 | Query | 0 | Locked | insert into items (itemId,sellerId,title,cid,sellerCats,picUrl,num,price,onsaleStatus,uv,sales,analy |
| 26958 | root | jstumax2yk6fgt:42382 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26959 | root | jstumax2yk6fgt:42465 | qnmj_cluster_01 | Sleep | 0 | | NULL |
| 26960 | root | jstumax2yk6fgt:43124 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26961 | root | jstumax2yk6fgt:43504 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26962 | root | jstumax2yk6fgt:38898 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26963 | root | jstumax2yk6fgt:40461 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26964 | root | jstumax2yk6fgt:40881 | qnmj_cluster_01 | Sleep | 0 | | NULL |
| 26965 | root | jstumax2yk6fgt:40882 | qnmj_cluster_01 | Sleep | 0 | | NULL |
| 26966 | root | jstumax2yk6fgt:41853 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26967 | root | jstumax2yk6fgt:41890 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26968 | root | h51:59362 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26970 | root | h51:59372 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 26972 | root | h52:42916 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26974 | root | jstumax2yk6fgt:43186 | xyy_cluster_04 | Query | 0 | Locked | select count(1) as c from items WHERE sellerId =1574544923 and status = 0 and hasShowcase = 0 |
| 26975 | root | jstumax2yk6fgt:43248 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26976 | root | jstumax2yk6fgt:40921 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26979 | root | jstumax2yk6fgt:45593 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26980 | root | jstumax2yk6fgt:45683 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26986 | root | jstumax2yk6fgt:38902 | qnmj_mf | Sleep | 1 | | NULL |
| 26987 | root | jstumax2yk6fgt:44557 | qnmj_cluster_01 | Sleep | 1 | | NULL |
| 26988 | root | jstumax2yk6fgt:44622 | qnmj_cluster_01 | Sleep | 1 | | NULL |
| 26989 | root | jstumax2yk6fgt:45892 | qnmj_cluster_01 | Sleep | 1 | | NULL |
| 26990 | root | h53:38342 | xyy_cluster_04 | Sleep | 233 | | NULL |
| 26991 | root | h53:38853 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 26992 | root | h52:50695 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 26993 | root | h52:51047 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 26994 | root | jstumax2yk6fgt:46848 | xyy_cluster_04 | Query | 0 | Locked | insert into items (itemId,sellerId,title,cid,sellerCats,picUrl,num,price,onsaleStatus,uv,sales,analy |
| 26995 | root | jstumax2yk6fgt:51760 | xyy_cluster_04 | Query | 0 | Locked | delete from items WHERE itemId =39039237708 |
| 26996 | root | h53:52876 | xyy_cluster_04 | Sleep | 233 | | NULL |
| 26997 | root | h51:43488 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 26999 | root | h53:36819 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27001 | root | jstumax2yk6fgt:32843 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27003 | root | jstumax2yk6fgt:32865 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27005 | root | jstumax2yk6fgt:57584 | qnmj_pay | Sleep | 8 | | NULL |
| 27006 | root | h53:58862 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27008 | root | h53:59324 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 27010 | root | jstumax2yk6fgt:40815 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27011 | root | jstumax2yk6fgt:40816 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27012 | root | jstumax2yk6fgt:40820 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27013 | root | jstumax2yk6fgt:40825 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27016 | root | h52:35303 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27017 | root | h51:53416 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27018 | root | h51:57564 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27021 | root | h51:41533 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27022 | root | h51:41550 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27024 | root | h53:55702 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27025 | root | h53:55710 | xyy_cluster_04 | Query | 0 | Locked | select b.itemId,title,cid,picUrl,num,price,onsaleStatus,uv,sales,analyzeType,improveTime,score,listT |
| 27026 | root | h53:55712 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27027 | root | h53:55714 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 27028 | root | h52:48622 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27029 | root | h52:48633 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27030 | root | h52:48632 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27031 | root | h52:48634 | xyy_cluster_04 | Query | 0 | Sending data | select count(1) as c from items WHERE sellerId =82004764 and status = 0 and hasShowcase = 0 |
| 27032 | root | h53:55750 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 27033 | root | h53:55751 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27034 | root | h53:55752 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27035 | root | h52:48809 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27036 | root | h53:55795 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27037 | root | jstumax2yk6fgt:33253 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27038 | root | jstumax2yk6fgt:33307 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27039 | root | jstumax2yk6fgt:33461 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27040 | root | jstumax2yk6fgt:33462 | xyy_cluster_04 | Query | 0 | Locked | insert into items (itemId,sellerId,title,cid,sellerCats,picUrl,num,price,onsaleStatus,uv,sales,analy |
| 27041 | root | h51:42858 | xyy_cluster_04 | Query | 0 | Sending data | select count(1) as c from items WHERE sellerId =1761734592 and status = 0 and hasShowcase = 0 |
| 27042 | root | h51:42872 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27043 | root | h52:50369 | xyy_cluster_04 | Query | 0 | Locked | update items set title='??????????????',score=0.0729,improveTime=unix_ti |
| 27045 | root | h52:50411 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 27046 | root | jstumax2yk6fgt:35020 | xyy_cluster_04 | Query | 0 | Locked | select b.itemId,b.status,b.delistTime from items_listplan a left join items b on a.itemId = b.itemId |
| 27047 | root | jstumax2yk6fgt:35033 | xyy_cluster_04 | Query | 0 | Locked | update items set hasShowcase=0,hasShowcaseTime=unix_timestamp() WHERE itemId =39181813076 |
| 27048 | root | h52:53747 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 27049 | root | h53:33776 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 27050 | root | jstumax2yk6fgt:35376 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27052 | root | h51:35981 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 27053 | root | jstumax2yk6fgt:35954 | qnmj_mf | Sleep | 1 | | NULL |
| 27054 | root | jstumax2yk6fgt:52130 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27055 | root | jstumax2yk6fgt:52135 | xyy_cluster_04 | Sleep | 0 | | NULL |
| 27056 | root | jstumax2yk6fgt:53270 | qnmj_config | Sleep | 5 | | NULL |
| 27057 | root | h52:40686 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 27058 | root | h53:56489 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 27059 | root | h51:38676 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 27060 | root | h51:38686 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 27061 | root | jstumax2yk6fgt:52088 | xyy_cluster_04 | Query | 0 | Locked | insert into items (itemId,sellerId,title,cid,sellerCats,picUrl,num,price,onsaleStatus,uv,sales,analy |
| 27062 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 27063 | root | h52:56839 | xyy_cluster_04 | Sleep | 1 | | NULL |
| 27064 | root | jstumax2yk6fgt:53086 | xyy_cluster_04 | Query | 0 | Locked | insert into items (itemId,sellerId,title,cid,sellerCats,picUrl,num,price,onsaleStatus,uv,sales,analy |
+-------+-------+----------------------+-----------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
134 rows in set (0.00 sec)
有个表锁严重，基本都是同一张表导致 的
vmstat 下看到 wa很大，正常情况应该是小于10的，io比较大
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
2 4 0 45292 5540 656836 0 0 232 0 2694 5750 2 2 81 15 0
0 1 0 44208 5560 658344 0 0 1232 1932 3428 6419 2 3 80 15 0
0 6 0 45860 5528 655648 0 0 2580 208 4819 15548 10 5 58 27 0
2 5 0 44068 5528 656940 0 0 168 0 2552 5893 2 2 41 55 0
4 0 0 46120 5508 656044 0 0 2056 1444 5163 18830 11 5 59 25 0
0 3 0 42964 5508 659340 0 0 2596 0 4211 15052 9 5 60 27 0
1 4 0 44540 5472 656524 0 0 360 2936 2661 5220 2 2 65 32 0
0 9 0 43744 5472 657568 0 0 160 516 2129 5525 1 1 45 52 0
2 4 0 44076 5488 658296 0 0 752 4228 4395 7601 3 3 78 17 0
1 9 0 44728 5452 655976 0 0 440 3344 3056 6578 2 3 77 18 0
0 5 0 45892 5488 656332 0 0 356 1140 2671 5623 2 2 78 18 0
0 4 0 42944 5504 658992 0 0 2696 756 3192 6186 2 2 78 18 0
0 2 0 44432 5504 657496 0 0 1660 180 3042 6322 2 2 66 31 0
4 8 0 45160 5476 656676 0 0 3264 0 3311 7403 3 3 75 19 0
0 12 0 43948 5484 657352 0 0 512 900 2851 5596 2 2 50 46 0
free -m
看到剩余内存不足700M，内存不足了
total used free shared buffers cached
Mem: 7989 7945 43 0 5 643
-/+ buffers/cache: 7296 692
Swap: 0 0 0
所以应该是内存不足导致io变大，从而导致mysql处理变慢，然后导致各种lock，不管是什么原因，内存不足应该是个问题
关闭了其中一个比较好资源的服务（占用内存500M），内存回升到1500M，io降到5以下了，数据库processlist 基本没有lock了
![IntelliJ IDEA 2020.1.2 设置多个project共享database插件[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)