网站上的一个应用突然读取不到数据,服务器配置:16G内存,16核,进入数据库服务器A(从服务器,配置:16G内存,16核)查看负载很高:uptime有8左右(相对于平时的1左右)我就查看其线程:mysql> show processlist;
+--------+------------------+--------------------+-------------+-------------+-------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------------------+--------------------+-------------+-------------+-------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 39 | rep | 172.31.1.136:4446 | NULL | Binlog Dump | 84304 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 40 | rep | 172.31.1.133:9862 | NULL | Binlog Dump | 84304 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 41 | rep | 172.31.1.126:35952 | NULL | Binlog Dump | 84304 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 393400 | system user | | NULL | Connect | 952 | Waiting for master to send event | NULL |
| 393428 | system user | | twioo_group | Connect | 312 | update | INSERT INTO twioo_group_user( id, user_id, group_id, member_type, join_time, expiratio |还有其他的都是些select,因为这是读服务器,这个线程里面的update状态持续了300多s,这个sql只是一条insert语句:
twioo_group_user的表结构是6个字段,4个int类型,2个time类型.就1个自增主键,并且表就1W多条数据,不可能执行一条要费这么久的时间;刚开始以为有锁,但是查看show innodb status 的状态也没什么异常:------------
TRANSACTIONS
------------
Trx id counter 826110A
Purge done for trx's n:o < 825FD1D undo n:o < 0
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 30957, OS thread id 1100056896
MySQL thread id 394968, query id 8744808 localhost root
show innodb status
---TRANSACTION 8261102, not started, process no 30957, OS thread id 1098565952
MySQL thread id 394833, query id 8744776 172.31.1.105 query_user
---TRANSACTION 8260BC7, not started, process no 30957, OS thread id 1244395840
MySQL thread id 394601, query id 8743459 172.31.1.106 twioo_blog_user
---TRANSACTION 8260934, not started, process no 30957, OS thread id 1238006080
MySQL thread id 393959, query id 8733131 172.31.1.103 hs_Q
---TRANSACTION 82610E1, not started, process no 30957, OS thread id 1236941120
MySQL thread id 393740, query id 8744679 172.31.1.103 query_user
---TRANSACTION 82610DC, not started, process no 30957, OS thread id 1237207360
MySQL thread id 393739, query id 8744649 172.31.1.103 query_user
---TRANSACTION 8260E4C, not started, process no 30957, OS thread id 1239603520
MySQL thread id 393555, query id 8740286 172.31.1.105 query_user
---TRANSACTION 8261107, not started, process no 30957, OS thread id 1096218944
MySQL thread id 393550, query id 8744794 172.31.1.105 query_user
---TRANSACTION 8261109, ACTIVE 1 sec, process no 30957, OS thread id 1238804800 fetching rows, thread declared inside InnoDB 221
mysql tables in use 2, locked 1
41 lock struct(s), heap size 6752, 13421 row lock(s)
MySQL thread id 393563, query id 8744807 172.31.1.106 twioo_group_user Sending data
select id , create_user_id , group_name , group_domain , group_type, group_, group_info , group_face , join_type, join_charge_type, join_charge_amount, content_privacy, comment_total, comment_okay, content_num , paper_charge_type, paper_charge_amount, delay_time, user_num , status , is_elite , create_time from twioo_group where id in (select group_id from twioo_group_user whe
Trx read view will not see trx with id >= 826110A, sees < 825FD23
---TRANSACTION 8261103, ACTIVE 1 sec, process no 30957, OS thread id 1101596992 fetching rows, thread declared inside InnoDB 434
mysql tables in use 2, locked 1
41 lock struct(s), heap size 6752, 13421 row lock(s)
MySQL thread id 393565, query id 8744779 172.31.1.106 twioo_group_user Sending data
select id , create_user_id , group_name , group_domain , group_type, group_, group_info , group_face , join_type, join_charge_type, join_charge_amount, content_privacy, comment_total, comment_okay, content_num , paper_charge_type, paper_charge_amount, delay_time, user_num , status , is_elite , create_time from twioo_group where id in (select group_id from twioo_group_user whe
Trx read view will not see trx with id >= 8261104, sees < 825FD23
---TRANSACTION 8261100, ACTIVE 1 sec, process no 30957, OS thread id 1240934720 fetching rows, thread declared inside InnoDB 244
mysql tables in use 2, locked 1
41 lock struct(s), heap size 6752, 13421 row lock(s)
MySQL thread id 393561, query id 8744765 172.31.1.106 twioo_group_user Sending data
select id , create_user_id , group_name , group_domain , group_type, group_, group_info , group_face , join_type, join_charge_type, join_charge_amount, content_privacy, comment_total, comment_okay, content_num , paper_charge_type, paper_charge_amount, delay_time, user_num , status , is_elite , create_time from twioo_group where id in (select group_id from twioo_group_user whe
Trx read view will not see trx with id >= 8261101, sees < 825FD23
---TRANSACTION 82610FF, ACTIVE 1 sec, process no 30957, OS thread id 1241999680 fetching rows, thread declared inside InnoDB 33
mysql tables in use 2, locked 1
41 lock struct(s), heap size 6752, 13421 row lock(s)
MySQL thread id 393562, query id 8744762 172.31.1.106 twioo_group_user Sending data
select id , create_user_id , group_name , group_domain , group_type, group_, group_info , group_face , join_type, join_charge_type, join_charge_amount, content_privacy, comment_total, comment_okay, content_num , paper_charge_type, paper_charge_amount, delay_time, user_num , status , is_elite , create_time from twioo_group where id in (select group_id from twioo_group_user whe
Trx read view will not see trx with id >= 8261100, sees < 825FD23
---TRANSACTION 825FD23, ACTIVE 168 sec, process no 30957, OS thread id 1240402240 inserting
mysql tables in use 1, locked 1
LOCK WAIT 234 lock struct(s), heap size 30704, 233 row lock(s)
MySQL thread id 393428, query id 8715835 update
INSERT INTO twioo_group_user( id, user_id, group_id, member_type, join_time, expirationdate ) VALUES ( null, 10114, 64, 10, null, '2011-09-17 11:35:32' )
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3220998 n bits 352 index `PRIMARY` of table `twioo_group`.`twioo_group_user` trx id 825FD23 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;------------------然后把这个应用连到另外一台配置较差的从服务器B上,数据就马上可以在网站上查看到,
想了很久,不知道是什么原因导致在A上查看不到数据.
请各位指点下,到底在哪里出了问题
+--------+------------------+--------------------+-------------+-------------+-------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------------------+--------------------+-------------+-------------+-------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 39 | rep | 172.31.1.136:4446 | NULL | Binlog Dump | 84304 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 40 | rep | 172.31.1.133:9862 | NULL | Binlog Dump | 84304 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 41 | rep | 172.31.1.126:35952 | NULL | Binlog Dump | 84304 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 393400 | system user | | NULL | Connect | 952 | Waiting for master to send event | NULL |
| 393428 | system user | | twioo_group | Connect | 312 | update | INSERT INTO twioo_group_user( id, user_id, group_id, member_type, join_time, expiratio |还有其他的都是些select,因为这是读服务器,这个线程里面的update状态持续了300多s,这个sql只是一条insert语句:
twioo_group_user的表结构是6个字段,4个int类型,2个time类型.就1个自增主键,并且表就1W多条数据,不可能执行一条要费这么久的时间;刚开始以为有锁,但是查看show innodb status 的状态也没什么异常:------------
TRANSACTIONS
------------
Trx id counter 826110A
Purge done for trx's n:o < 825FD1D undo n:o < 0
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 30957, OS thread id 1100056896
MySQL thread id 394968, query id 8744808 localhost root
show innodb status
---TRANSACTION 8261102, not started, process no 30957, OS thread id 1098565952
MySQL thread id 394833, query id 8744776 172.31.1.105 query_user
---TRANSACTION 8260BC7, not started, process no 30957, OS thread id 1244395840
MySQL thread id 394601, query id 8743459 172.31.1.106 twioo_blog_user
---TRANSACTION 8260934, not started, process no 30957, OS thread id 1238006080
MySQL thread id 393959, query id 8733131 172.31.1.103 hs_Q
---TRANSACTION 82610E1, not started, process no 30957, OS thread id 1236941120
MySQL thread id 393740, query id 8744679 172.31.1.103 query_user
---TRANSACTION 82610DC, not started, process no 30957, OS thread id 1237207360
MySQL thread id 393739, query id 8744649 172.31.1.103 query_user
---TRANSACTION 8260E4C, not started, process no 30957, OS thread id 1239603520
MySQL thread id 393555, query id 8740286 172.31.1.105 query_user
---TRANSACTION 8261107, not started, process no 30957, OS thread id 1096218944
MySQL thread id 393550, query id 8744794 172.31.1.105 query_user
---TRANSACTION 8261109, ACTIVE 1 sec, process no 30957, OS thread id 1238804800 fetching rows, thread declared inside InnoDB 221
mysql tables in use 2, locked 1
41 lock struct(s), heap size 6752, 13421 row lock(s)
MySQL thread id 393563, query id 8744807 172.31.1.106 twioo_group_user Sending data
select id , create_user_id , group_name , group_domain , group_type, group_, group_info , group_face , join_type, join_charge_type, join_charge_amount, content_privacy, comment_total, comment_okay, content_num , paper_charge_type, paper_charge_amount, delay_time, user_num , status , is_elite , create_time from twioo_group where id in (select group_id from twioo_group_user whe
Trx read view will not see trx with id >= 826110A, sees < 825FD23
---TRANSACTION 8261103, ACTIVE 1 sec, process no 30957, OS thread id 1101596992 fetching rows, thread declared inside InnoDB 434
mysql tables in use 2, locked 1
41 lock struct(s), heap size 6752, 13421 row lock(s)
MySQL thread id 393565, query id 8744779 172.31.1.106 twioo_group_user Sending data
select id , create_user_id , group_name , group_domain , group_type, group_, group_info , group_face , join_type, join_charge_type, join_charge_amount, content_privacy, comment_total, comment_okay, content_num , paper_charge_type, paper_charge_amount, delay_time, user_num , status , is_elite , create_time from twioo_group where id in (select group_id from twioo_group_user whe
Trx read view will not see trx with id >= 8261104, sees < 825FD23
---TRANSACTION 8261100, ACTIVE 1 sec, process no 30957, OS thread id 1240934720 fetching rows, thread declared inside InnoDB 244
mysql tables in use 2, locked 1
41 lock struct(s), heap size 6752, 13421 row lock(s)
MySQL thread id 393561, query id 8744765 172.31.1.106 twioo_group_user Sending data
select id , create_user_id , group_name , group_domain , group_type, group_, group_info , group_face , join_type, join_charge_type, join_charge_amount, content_privacy, comment_total, comment_okay, content_num , paper_charge_type, paper_charge_amount, delay_time, user_num , status , is_elite , create_time from twioo_group where id in (select group_id from twioo_group_user whe
Trx read view will not see trx with id >= 8261101, sees < 825FD23
---TRANSACTION 82610FF, ACTIVE 1 sec, process no 30957, OS thread id 1241999680 fetching rows, thread declared inside InnoDB 33
mysql tables in use 2, locked 1
41 lock struct(s), heap size 6752, 13421 row lock(s)
MySQL thread id 393562, query id 8744762 172.31.1.106 twioo_group_user Sending data
select id , create_user_id , group_name , group_domain , group_type, group_, group_info , group_face , join_type, join_charge_type, join_charge_amount, content_privacy, comment_total, comment_okay, content_num , paper_charge_type, paper_charge_amount, delay_time, user_num , status , is_elite , create_time from twioo_group where id in (select group_id from twioo_group_user whe
Trx read view will not see trx with id >= 8261100, sees < 825FD23
---TRANSACTION 825FD23, ACTIVE 168 sec, process no 30957, OS thread id 1240402240 inserting
mysql tables in use 1, locked 1
LOCK WAIT 234 lock struct(s), heap size 30704, 233 row lock(s)
MySQL thread id 393428, query id 8715835 update
INSERT INTO twioo_group_user( id, user_id, group_id, member_type, join_time, expirationdate ) VALUES ( null, 10114, 64, 10, null, '2011-09-17 11:35:32' )
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3220998 n bits 352 index `PRIMARY` of table `twioo_group`.`twioo_group_user` trx id 825FD23 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;------------------然后把这个应用连到另外一台配置较差的从服务器B上,数据就马上可以在网站上查看到,
想了很久,不知道是什么原因导致在A上查看不到数据.
请各位指点下,到底在哪里出了问题
解决方案 »
- 如果将多个查询结果保存到同一个字段里？
- 紧急求助；mysql语句问题
- COUNT(DISTINCT(`id`)) 如何优化？
- MYSQL添加用户,怎么不行?
- 有没有不是翻译的Mysql教程？
- MYSQL函数无法运行,找不出错误.
- 远程操作过LINUX服务器下的 mysql数据库
- CSDN所有的人都人看看，帮小弟一把吧::mysql数据库误清除的解救办法？？？？要分多少都没问题
- 急！！！！！请帮忙~~~~内部网络的检索服务系统,通过internet也可以访问?
- mysql_query返回错误代码1,不知何故?
- Mysql 触发器能否触发一个exe文件或者是bat文件呢？
- Oracle表转换成 MySQL表
slave上都是串行执行的，不会存在锁等待，那么INSERT应该很快执行完，为什么会很长时间还没完呢？？求解
update状态是slave的sql线程,是没有异常,但是这个状态下执行的sql语句只是一条insert,肯定不会慢的,
慢查询确实有,但是没有高到10s以上的,大部分都是在3s以下,不过查询一直持续,这时候insert都一直在执行.没有执行完.表是innodb表,没有表锁. 这时进入应用的web页面,报504错误.
如果Exec_Master_Log_Pos这个不变 ，才能说明show processlist里面的insert一直没有插入进去
insert 确实没有同步过去,因为我在从上查询不到主上的insert记录,还有就是要是insert进去了的话,那个线程的time字段的信息应该会又从0开始递加,但事实上却是一直递加,没有释放.