mysql show processlist 进行中的sql过多的问题解决思路
来源：互联网 发布：python mimetext 中文 编辑：程序博客网 时间：2024/04/20 04:50
先看看问题 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 cachedMem: 7989 7945 43 0 5 643-/+ buffers/cache: 7296 692Swap: 0 0 0
所以应该是内存不足导致io变大，从而导致mysql处理变慢，然后导致各种lock，不管是什么原因，内存不足应该是个问题
关闭了其中一个比较好资源的服务（占用内存500M），内存回升到1500M，io降到5以下了，数据库processlist 基本没有lock了
0 0
- mysql show processlist 进行中的sql过多的问题解决思路
- mysql show processlist如何显示完整sql
- MySQL的show full processlist命令
- mysql的show processlist命令大作用
- 关于mysql---- show processlist的总结
- mysql show processlist state
- mysql show processlist详解
- mysql show processlist 使用
- Mysql Show Processlist 解读
- mysql show processlist state
- mysql show processlist分析
- mysql SHOW PROCESSLIST介绍
- mysql show processlist
- mysql "show processlist"命令
- mysql:show [full] processlist
- mysql Show processlist 整理
- mysql show processlist 过程
- mysql show processlist分析
- 【不断添加】keil（MDK）使用说明
- 【转载】软件设计思想
- 阅读者之JAVA小记---关于阅读者名称的由来
- Linux下更新android sdk
- eclipse svn插件与本地svn互通
- mysql show processlist 进行中的sql过多的问题解决思路
- UNION/UNION ALL和order by 连接使用
- Leetcode 题目类型分布
- 陇南怀孕30天可以做人流
- JavaScript的深拷贝与浅拷贝
- 关于引用分析(二)
- linux kernel编译生成zImage过程详解
- File、FilenameFilter示例
- linux下hook函数
