关于MYSQL的瓶颈问题 我现在在数据库中发现一个瓶颈,就是写数据非常延迟,请问该去调整哪里的参数?谢谢。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 插入语句我没有,你是怀疑和SQL语句的优化有关?? 还想请问一下,Innodb_row_lock_time 4855875Innodb_row_lock_current_waits 55Innodb_row_lock_time_avg 909Innodb_row_lock_time_max 5190Innodb_row_lock_waits 5337这样的lock是不是不正常,太高了。 我也是觉得有点高,而且我使用show innodb status查看,有比较多的锁,请问这有什么好点方法去解决么》谢谢。 不好意思,再麻烦你看一下这个,看看下面这些给了我什么信息,现在查出来有很多类似这样的信息,谢谢。------------------------LATEST FOREIGN KEY ERROR------------------------081104 5:01:20 Transaction:TRANSACTION 0 158994809, ACTIVE 0 sec, process no 13146, OS thread id 1163372864 inserting, thread declared inside InnoDB 500mysql tables in use 1, locked 19 lock struct(s), heap size 1216, undo log entries 1MySQL thread id 16, query id 85786202 172.31.0.82 lsg updateINSERT INTO c_battle_player(c_battle_id, c_player_id, c_faction_id, c_registration_time) VALUES ( NAME_CONST('v_battle_id',1805199), NAME_CONST('p_player_id',3920181526268087009), NAME_CONST('v_faction_id',2), unix_timestamp())Foreign key constraint fails for table `mmp_1_76_endwar_xbox360_prod/c_battle_player`:, CONSTRAINT `fk_battleplayer_battleid` FOREIGN KEY (`c_battle_id`) REFERENCES `c_battle` (`c_battle_id`) ON DELETE CASCADE ON UPDATE CASCADETrying to add in child table, in index `c_battle_id` tuple:DATA TUPLE: 2 fields; 0: len 8; hex 00000000001b8b8f; asc ;; 1: len 8; hex 3667485400420ae1; asc 6gHT B ;;But in parent table `mmp_1_76_endwar_xbox360_prod/c_battle`, in index `PRIMARY`,the closest match we can find is record:PHYSICAL RECORD: n_fields 29; compact format; info bits 0 0: len 8; hex 0000000000148d2d; asc -;; 1: len 6; hex 0000097a0ffb; asc z ;; 2: len 7; hex 000000003f2345; asc ?#E;; 3: len 2; hex 000d; asc ;; 4: len 2; hex 0014; asc ;; 5: len 2; hex 0001; asc ;; 6: len 2; hex 000a; asc ;; 7: len 8; hex 0000000012128c85; asc ;; 8: len 1; hex 81; asc ;; 9: len 8; hex 00000000490fd6bb; asc I ;; 10: len 8; hex 00000000490fd6f7; asc I ;; 11: len 1; hex 01; asc ;; 12: len 1; hex cd; asc ;; 13: len 1; hex 01; asc ;; 14: len 1; hex 80; asc ;; 15: len 1; hex 01; asc ;; 16: len 1; hex 01; asc ;; 17: len 1; hex 80; asc ;; 18: len 4; hex 00000000; asc ;; 19: len 4; hex 00000000; asc ;; 20: len 4; hex 00000000; asc ;; 21: len 4; hex 00000000; asc ;; 22: len 4; hex 00000000; asc ;; 23: len 4; hex 00000000; asc ;; 24: len 4; hex 00000000; asc ;; 25: len 4; hex 00000000; asc ;; 26: len 4; hex 00000000; asc ;; 27: len 1; hex 01; asc ;; 28: len 1; hex 02; asc ;;------------TRANSACTIONS------------Trx id counter 0 160277685Purge done for trx's n:o < 0 160273768 undo n:o < 0 7History list length 624Total number of lock structs in row lock hash table 1877... truncated...'p_camouflage_pattern_id',77), c_team_wins_count = 0, c_team_losses_count = 0, c_solo_wins_count = 0, c_solo_losses_count = 0, c_total_game_time = 0, c_deaths_count = 0, c_killed_unit_count = 0, c_routed_unit_count = 0, c_evacuated_unit_count = 0, c_incapa---TRANSACTION 0 160276906, not started, process no 13146, OS thread id 1170561344mysql tables in use 1, locked 1MySQL thread id 43, query id 216415270 172.31.0.83 lsg Table lockUPDATE c_battalion SET c_battalion_name_id = NAME_CONST('p_battalion_name_id',1), c_camouflage_pattern_id = NAME_CONST('p_camouflage_pattern_id',77), c_team_wins_count = 0, c_team_losses_count = 0, c_solo_wins_count = 0, c_solo_losses_count = 0, c_total_game_time = 0, c_deaths_count = 0, c_killed_unit_count = 0, c_routed_unit_count = 0, c_evacuated_unit_count = 0, c_incapa---TRANSACTION 0 160276071, not started, process no 13146, OS thread id 1170028864mysql tables in use 1, locked 1MySQL thread id 41, query id 216350819 172.31.0.81 lsg Table lockUPDATE c_battalion SET c_battalion_name_id = NAME_CONST('p_battalion_name_id',1), c_camouflage_pattern_id = NAME_CONST('p_camouflage_pattern_id',77), c_team_wins_count = 0, c_team_losses_count = 0, c_solo_wins_count = 0, c_solo_losses_count = 0, c_total_game_time = 0, c_deaths_count = 0, c_killed_unit_count = 0, c_routed_unit_count = 0, c_evacuated_unit_count = 0, c_incapa---TRANSACTION 0 160277109, not started, process no 13146, OS thread id 1169496384mysql tables in use 1, locked 1MySQL thread id 39, query id 216458378 172.31.0.83 lsg Table lockUPDATE c_battalion SET c_battalion_name_id = NAME_CONST('p_battalion_name_id',1), c_camouflage_pattern_id = NAME_CONST('p_camouflage_pattern_id',77), c_team_wins_count = 0, c_team_losses_count = 0, c_solo_wins_count = 0, c_solo_losses_count = 0, c_total_game_time = 0, c_deaths_count = 0, c_killed_unit_count = 0, c_routed_unit_count = 0, c_evacuated_unit_count = 0, c_incapa请问这个是不是需要优化SQL语句呢? 如何判断表的id字段是否为主键 ? sql语句 ? 用MySQL做集群时的一个异常 求通过链接服务器调用MYSQL上过程 一个用于先select出id,再根据id进行update的语句,出错了 ERROR 126 Incorrect key file for table 请教一个查询语句 在RedHat8.0 MySql如何授权? jdbc 批量更新 求助 Mysql二进制备份文件还原缓慢 关于b树索引的原理问题 关于mysql索引的问题 如何查询用户下表的个数 谢谢高手指点
Innodb_row_lock_time 4855875
Innodb_row_lock_current_waits 55
Innodb_row_lock_time_avg 909
Innodb_row_lock_time_max 5190
Innodb_row_lock_waits 5337
这样的lock是不是不正常,太高了。
------------------------
LATEST FOREIGN KEY ERROR
------------------------
081104 5:01:20 Transaction:
TRANSACTION 0 158994809, ACTIVE 0 sec, process no 13146, OS thread id 1163372864 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 16, query id 85786202 172.31.0.82 lsg update
INSERT INTO c_battle_player(c_battle_id,
c_player_id,
c_faction_id,
c_registration_time)
VALUES ( NAME_CONST('v_battle_id',1805199),
NAME_CONST('p_player_id',3920181526268087009),
NAME_CONST('v_faction_id',2),
unix_timestamp())
Foreign key constraint fails for table `mmp_1_76_endwar_xbox360_prod/c_battle_player`:
,
CONSTRAINT `fk_battleplayer_battleid` FOREIGN KEY (`c_battle_id`) REFERENCES `c_battle` (`c_battle_id`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `c_battle_id` tuple:
DATA TUPLE: 2 fields;
0: len 8; hex 00000000001b8b8f; asc ;; 1: len 8; hex 3667485400420ae1; asc 6gHT B ;;But in parent table `mmp_1_76_endwar_xbox360_prod/c_battle`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 29; compact format; info bits 0
0: len 8; hex 0000000000148d2d; asc -;; 1: len 6; hex 0000097a0ffb; asc z ;; 2: len 7; hex 000000003f2345; asc ?#E;; 3: len 2; hex 000d; asc ;; 4: len 2; hex 0014; asc ;; 5: len 2; hex 0001; asc ;; 6: len 2; hex 000a; asc ;; 7: len 8; hex 0000000012128c85; asc ;; 8: len 1; hex 81; asc ;; 9: len 8; hex 00000000490fd6bb; asc I ;; 10: len 8; hex 00000000490fd6f7; asc I ;; 11: len 1; hex 01; asc ;; 12: len 1; hex cd; asc ;; 13: len 1; hex 01; asc ;; 14: len 1; hex 80; asc ;; 15: len 1; hex 01; asc ;; 16: len 1; hex 01; asc ;; 17: len 1; hex 80; asc ;; 18: len 4; hex 00000000; asc ;; 19: len 4; hex 00000000; asc ;; 20: len 4; hex 00000000; asc ;; 21: len 4; hex 00000000; asc ;; 22: len 4; hex 00000000; asc ;; 23: len 4; hex 00000000; asc ;; 24: len 4; hex 00000000; asc ;; 25: len 4; hex 00000000; asc ;; 26: len 4; hex 00000000; asc ;; 27: len 1; hex 01; asc ;; 28: len 1; hex 02; asc ;;------------
TRANSACTIONS
------------
Trx id counter 0 160277685
Purge done for trx's n:o < 0 160273768 undo n:o < 0 7
History list length 624
Total number of lock structs in row lock hash table 1877
... truncated...
'p_camouflage_pattern_id',77),
c_team_wins_count = 0,
c_team_losses_count = 0,
c_solo_wins_count = 0,
c_solo_losses_count = 0,
c_total_game_time = 0,
c_deaths_count = 0,
c_killed_unit_count = 0,
c_routed_unit_count = 0,
c_evacuated_unit_count = 0,
c_incapa
---TRANSACTION 0 160276906, not started, process no 13146, OS thread id 1170561344
mysql tables in use 1, locked 1
MySQL thread id 43, query id 216415270 172.31.0.83 lsg Table lock
UPDATE c_battalion
SET c_battalion_name_id = NAME_CONST('p_battalion_name_id',1),
c_camouflage_pattern_id = NAME_CONST('p_camouflage_pattern_id',77),
c_team_wins_count = 0,
c_team_losses_count = 0,
c_solo_wins_count = 0,
c_solo_losses_count = 0,
c_total_game_time = 0,
c_deaths_count = 0,
c_killed_unit_count = 0,
c_routed_unit_count = 0,
c_evacuated_unit_count = 0,
c_incapa
---TRANSACTION 0 160276071, not started, process no 13146, OS thread id 1170028864
mysql tables in use 1, locked 1
MySQL thread id 41, query id 216350819 172.31.0.81 lsg Table lock
UPDATE c_battalion
SET c_battalion_name_id = NAME_CONST('p_battalion_name_id',1),
c_camouflage_pattern_id = NAME_CONST('p_camouflage_pattern_id',77),
c_team_wins_count = 0,
c_team_losses_count = 0,
c_solo_wins_count = 0,
c_solo_losses_count = 0,
c_total_game_time = 0,
c_deaths_count = 0,
c_killed_unit_count = 0,
c_routed_unit_count = 0,
c_evacuated_unit_count = 0,
c_incapa
---TRANSACTION 0 160277109, not started, process no 13146, OS thread id 1169496384
mysql tables in use 1, locked 1
MySQL thread id 39, query id 216458378 172.31.0.83 lsg Table lock
UPDATE c_battalion
SET c_battalion_name_id = NAME_CONST('p_battalion_name_id',1),
c_camouflage_pattern_id = NAME_CONST('p_camouflage_pattern_id',77),
c_team_wins_count = 0,
c_team_losses_count = 0,
c_solo_wins_count = 0,
c_solo_losses_count = 0,
c_total_game_time = 0,
c_deaths_count = 0,
c_killed_unit_count = 0,
c_routed_unit_count = 0,
c_evacuated_unit_count = 0,
c_incapa
请问这个是不是需要优化SQL语句呢?