mysql 锁死,有经验的帮分析一下?

todayhero

硬件配置:
DELL 2850  8G内存.


情况:经常出现MYSQL锁死的情况,导致大量select堆积,最后无法服务,目前的解决办法,是写了一个监控脚本,10S运行一次,发现问题后,就自动kill到select语句.临时解决问题.

mysqladmin status状态

Uptime: 129365  Threads: 38  Questions: 44113207  Slow queries: 27382  Opens: 2038  Flush tables: 27  Open tables: 492  Queries per second avg: 340.998

MYSQL5.1目前使用的MYISAM.
使用myisam update会导致锁表,然后select堆积严重.
改为INNODB后.SELECT执行效率非常地低.服务器负载过重,load值都保持在10左右.
鉴于上面的情况.还用MYISAM还好些.

解决办法:我认为使用memcached缓存数据.

+---------+------------------+---------------------+-------+-------------+--------+-------------------------------------------------
----------------------+------------------------------------------------------------------------------------------------------+
| Id      | User             | Host                | db    | Command     | Time   | State                                          
                      | Info                                                                                                 |
+---------+------------------+---------------------+-------+-------------+--------+-------------------------------------------------
----------------------+------------------------------------------------------------------------------------------------------+
| 1       | system user      |                     |       | Connect     | 114038 | Waiting for master to send event               
                      |                                                                                                      |
| 2       | system user      |                     |       | Connect     | 20     | Has read all relay log; waiting for the slave I/
O thread to update it |                                                                                                      |
| 3       | replication_user | 192.168.1.103:39614 |       | Binlog Dump | 114038 | Has sent all binlog to slave; waiting for binlog
to be updated        |                                                                                                      |
| 1051074 | root             | 124.0.5.1:15364 |       | Sleep       | 3765   |                                                
                      |                                                                                                      |
| 1051101 | root             | 124.0.5.1:15365 | go.cn | Sleep       | 1228   |                                                
                      |                                                                                                      |
| 1051592 | root             | 124.0.5.1:15367 | go.cn | Sleep       | 2165   |                                                
                      |                                                                                                      |
| 1067124 | root             | 124.0.5.1:15532 | go.cn | Sleep       | 2159   |                                                
                      |                                                                                                      |
| 1091673 | root             | 124.0.5.1:15806 | go.cn | Sleep       | 1225   |                                                
                      |                                                                                                      |
| 1109945 | ktx              | 192.168.1.105:19561 | go.cn | Query       | 20     | Locked                                          
                      | INSERT INTO  jd_adminlog  SET  adminname='xijing', operate='/admin.php?m=oders&app=list', ip= |
| 1109966 | ktx              | 192.168.1.105:19575 | go.cn | Query       | 20     | update                                          
                      | INSERT INTO  jd_adminlog  SET  adminname='xijing', operate='/admin.php?m=oders&app=list', ip= |
| 1109989 | ktx              | 192.168.1.105:19588 | go.cn | Query       | 18     | Locked                                          
                      | INSERT INTO  jd_adminlog  SET  adminname='xijing', operate='/admin.php?m=oders&app=list', ip= |
| 1110067 | ktx              | 192.168.1.104:64849 | go.cn | Query       | 20     | end                                             
                      | UPDATE jd_order SET is_pay='1',is_suss='1',paytime=1305690269 WHERE orderid='803937' AND uid='80 |
| 1110133 | ktx              | 192.168.1.105:19695 | go.cn | Query       | 20     | Locked                                          
                      | UPDATE jd_group SET bought=bought+1,goodsnum=goodsnum-1 WHERE gid='2483'                         |
| 1110134 | ktx              | 192.168.1.105:19697 | go.cn | Query       | 20     | Locked                                          
                      | SELECT * FROM jd_order   WHERE is_pay=1  AND uid ='2'                                            |
| 1110136 | ktx              | 192.168.1.107:64925 | go.cn | Query       | 20     | Locked                                          
                      | SELECT * FROM jd_order   WHERE uid='801449' AND gid=1913 AND is_pay=1                            |
| 1110147 | ktx              | 192.168.1.105:19710 | go.cn | Query       | 20     | Locked                                          
                      | SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.goodsnum,b.sussessnum,b.bought,b.ptypeid FROM jiu |
| 1110184 | ktx              | 192.168.1.105:19733 | go.cn | Query       | 20     | Locked                                          
                      | SELECT * FROM jd_order   WHERE is_pay=1  AND uid ='2'                                            |
| 1110221 | ktx              | 192.168.1.107:64978 | go.cn | Query       | 20     | Locked                                          
                      | SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.goodsnum,b.sussessnum,b.bought,b.ptypeid FROM jiu |
| 1110228 | ktx              | 192.168.1.105:19758 | go.cn | Query       | 20     | Locked                                          
                      | SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.goodsnum,b.sussessnum,b.bought,b.ptypeid FROM jiu |
| 1110232 | ktx              | 192.168.1.105:19761 | go.cn | Query       | 20     | Locked                                          
                      | SELECT * FROM jd_order   WHERE uid='468193' AND gid=2437 AND is_pay=1                            |
| 1110250 | ktx              | 192.168.1.105:19774 | go.cn | Query       | 20     | Locked                                          
                      | SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| 1110265 | ktx              | 192.168.1.107:5023  | go.cn | Query       | 20     | Locked                                          
                      | SELECT SQL_BUFFER_RESULT * FROM jd_group WHERE gid='1799'                                        |
| 1110277 | ktx              | 192.168.1.105:19792 | go.cn | Query       | 18     | Locked                                          
                      | SELECT a.conpwd,a.couponsid,a.couponinfo,a.orderid,a.addtime,a.indate,c.gid,c.goodsname,c.miniurl,d. |
| 1110280 | ktx              | 192.168.1.104:64974 | go.cn | Query       | 18     | Locked                                          
                      | SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.goodsnum,b.sussessnum,b.bought,b.ptypeid FROM jiu |
| 1110281 | ktx              | 192.168.1.107:5037  | go.cn | Query       | 18     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2279'                      |
| 1110283 | ktx              | 192.168.1.105:19794 | go.cn | Query       | 18     | Locked                                          
                      | SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.goodsnum,b.sussessnum,b.bought,b.ptypeid FROM jiu |
| 1110284 | ktx              | 192.168.1.105:19796 | go.cn | Query       | 18     | Locked                                          
                      | select * from jd_group where gid=2403                                                            |
| 1110286 | ktx              | 192.168.1.107:5041  | go.cn | Query       | 18     | Locked                                          
                      | SELECT gid,bought,isrel,sustime,residue,endtime,starttime FROM jd_group WHERE gid=2207           |
| 1110289 | ktx              | 192.168.1.105:19800 | go.cn | Query       | 18     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2519'                      |
| 1110292 | ktx              | 192.168.1.105:19802 | go.cn | Query       | 18     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2585'                      |
| 1110295 | ktx              | 192.168.1.105:19804 | go.cn | Query       | 18     | Locked                                          
                      | select goodsnum from jd_group where gid=2207                                                     |
| 1110296 | ktx              | 192.168.1.104:64980 | go.cn | Query       | 17     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2545'                      |
| 1110303 | ktx              | 192.168.1.107:5056  | go.cn | Query       | 17     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2605'                      |
| 1110304 | ktx              | 192.168.1.104:64988 | go.cn | Query       | 18     | Locked                                          
                      | SELECT gid,bought,isrel,sustime,residue,endtime,starttime FROM jd_group WHERE gid=2605           |
| 1110305 | ktx              | 192.168.1.104:64990 | go.cn | Query       | 17     | Locked                                          
                      | SELECT  a.*,b.saleprice,b.ptypeid,b.cityid,b.goodsname,b.howusecoup FROM jd_order a LEFT JOIN ji |
| 1110306 | ktx              | 192.168.1.104:64992 | go.cn | Query       | 18     | Locked                                          
                      | SELECT sql_buffer_result a.gid,a.ptypeid,a.Delivery,a.hot_area,a.cityid,a.supplierid,a.goodsname,a.m |
| 1110308 | ktx              | 192.168.1.105:19808 | go.cn | Query       | 18     | Locked                                          
                      | SELECT gid,bought,isrel,sustime,residue,endtime,starttime FROM jd_group WHERE gid=1913           |
| 1110311 | ktx              | 192.168.1.104:64994 | go.cn | Query       | 18     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2523'                      |
| 1110312 | ktx              | 192.168.1.105:19812 | go.cn | Query       | 18     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2541'                      |
| 1110314 | ktx              | 192.168.1.104:64996 | go.cn | Query       | 17     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2135'                      |
| 1110315 | ktx              | 192.168.1.104:64998 | go.cn | Query       | 17     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2199'                      |
| 1110316 | ktx              | 192.168.1.105:19815 | go.cn | Query       | 18     | Locked                                          
                      | SELECT sql_buffer_result a.*,b.* FROM jd_group a LEFT JOIN jd_supplier b ON a.supplierid=b.s |
| 1110317 | ktx              | 192.168.1.105:19816 | go.cn | Query       | 18     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2513'                      |
| 1110320 | ktx              | 192.168.1.104:5001  | go.cn | Query       | 18     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2465'                      |
| 1110321 | ktx              | 192.168.1.107:5062  | go.cn | Query       | 17     | Locked                                          
                      | SELECT sql_buffer_result a.gid,a.ptypeid,a.Delivery,a.hot_area,a.cityid,a.supplierid,a.goodsname,a.m |
| 1110324 | ktx              | 192.168.1.104:5003  | go.cn | Query       | 18     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2563'                      |
| 1110330 | ktx              | 192.168.1.107:5068  | go.cn | Query       | 17     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='1913'                      |
| 1110331 | ktx              | 192.168.1.104:5010  | go.cn | Query       | 18     | Locked                                          
                      | select goodsnum from jd_group where gid=2471                                                     |
| 1110332 | ktx              | 192.168.1.105:19824 | go.cn | Query       | 17     | Locked                                          
                      | SELECT * FROM jd_order WHERE orderid='803899'                                                    |
| 1110334 | ktx              | 192.168.1.107:5070  | go.cn | Query       | 18     | Locked                                          
                      | SELECT Delivery,endtime,starttime,browsable FROM jd_group  WHERE gid='2465'                      |
| 1110337 | ktx              | 192.168.1.104:5014  | go.cn | Query       | 20     | Locked                                          
                      | SELECT sql_buffer_result a.*,b.* FROM jd_group a LEFT JOIN jd_supplier b ON a.supplierid=b.s |
| 1110336 | ktx              | 192.168.1.105:19826 | go.cn | Query       | 20     | Locked                                          
                      | SELECT sql_buffer_result a.*,b.* FROM jd_group a LEFT JOIN jd_supplier b ON a.supplierid=b.s |
| 1110339 | ktx              | 192.168.1.107:5074  | go.cn | Query       | 20     | Locked                                          
                      | SELECT sql_buffer_result a.*,b.* FROM jd_group a LEFT JOIN jd_supplier b ON a.supplierid=b.s |
| 1110338 | ktx              | 192.168.1.107:5073  | go.cn | Query       | 20     | Locked                                          
                      | SELECT sql_buffer_result a.*,b.* FROM jd_group a LEFT JOIN jd_supplier b ON a.supplierid=b.s |
| 1110340 | ktx              | 192.168.1.104:5016  | go.cn | Query       | 20     | Locked                                          
                      | SELECT sql_buffer_result a.*,b.* FROM jd_group a LEFT JOIN jd_supplier b ON a.supplierid=b.s |
| 1110341 | ktx              | 192.168.1.104:5018  | go.cn | Query       | 20     | Locked                                          
                      | SELECT sql_buffer_result a.*,b.* FROM jd_group a LEFT JOIN jd_supplier b ON a.supplierid=b.s |
| 1110342 | ktx              | 192.168.1.104:5020  | go.cn | Query       | 20     | Locked                                          
                      | SELECT sql_buffer_result a.*,b.* FROM jd_group a LEFT JOIN jd_supplier b ON a.supplierid=b.s |
|

gnah

我也有这个问题,用的是INNODB

gnah

show processlist:
state                                                                    Info
Waiting for table metadata lock                          Alter table ....

todayhero

回复 2# gnah


    这种问题很常见.解决起来难度也不大!

但是从MYSQL设置和优化方面的改进效果是弱的.

主要还是看程序开发的优化程度.

todayhero

加入memcache或redis

纯静态化+必须的ajax实现.

数据库的读写分离,或是分表.

都可以很好的解决此问题.但也可能牺牲部分功能,这个就要在效率和功能上取舍了.

需要好的技术团队才可以,

todayhero

烂的程序,给运维人员的维护带来很大的麻烦.
不过也是提高运维能力的一个有效办法.

yifangyou

用update LOW_PRIORITY试试，推迟update