自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

MySQL死鎖與日志二三事

作者：xrzs
數(shù)據(jù)庫 MySQL
最近線上 MySQL 接連發(fā)生了幾起數(shù)據(jù)異常，都是在凌晨爆發(fā)，由于業(yè)務(wù)場(chǎng)景屬于典型的數(shù)據(jù)倉庫型應(yīng)用，白天壓力較小無法復(fù)現(xiàn)。甚至有些異常還比較詭異，最后 root cause 分析頗費(fèi)周折。那實(shí)際業(yè)務(wù)當(dāng)中咱們?nèi)绾文芸焖俚亩ㄎ痪€上 MySQL 問題，修復(fù)異常呢?下文我會(huì)根據(jù)兩個(gè)實(shí)際 case，分享下相關(guān)的經(jīng)驗(yàn)與方法。

MySQL死鎖與日志二三事

最近線上 MySQL 接連發(fā)生了幾起數(shù)據(jù)異常，都是在凌晨爆發(fā)，由于業(yè)務(wù)場(chǎng)景屬于典型的數(shù)據(jù)倉庫型應(yīng)用，白天壓力較小無法復(fù)現(xiàn)。甚至有些異常還比較詭異，*** root cause 分析頗費(fèi)周折。那實(shí)際業(yè)務(wù)當(dāng)中咱們?nèi)绾文芸焖俚亩ㄎ痪€上 MySQL 問題，修復(fù)異常呢?下文我會(huì)根據(jù)兩個(gè)實(shí)際 case，分享下相關(guān)的經(jīng)驗(yàn)與方法。

1、Case1：部分?jǐn)?shù)據(jù)更新失敗

某天渠道同學(xué)反饋某報(bào)表極個(gè)別渠道數(shù)據(jù)為 0，大部分渠道數(shù)據(jù)正常。這個(gè)數(shù)據(jù)是由一個(gè)統(tǒng)計(jì)程序每天凌晨例行更新的，按理來說，要么全部正常，要么全部失敗，那會(huì)是什么原因?qū)?**個(gè)別數(shù)據(jù)異常呢?

首先我們能想到的自然是根據(jù)統(tǒng)計(jì)任務(wù)日志來看了，但是看了統(tǒng)計(jì)程序打印的日志沒有發(fā)現(xiàn)諸如 SQL update 失敗的異常描述，那當(dāng)時(shí)的數(shù)據(jù)庫究竟發(fā)生了什么呢?在查看 MySQL-server 日志之前，習(xí)慣性的看了下數(shù)據(jù)庫狀態(tài)：

  1. SHOW ENGINE INNODB STATUS\G 

恰好看到了凌晨這個(gè) update 發(fā)生了死鎖：

  1. ------------------------ 
  2. LATEST DETECTED DEADLOCK 
  3. ------------------------ 
  4. 2017-07-17 04:09:01 0x7f6de03c8700 
  5. *** (1) TRANSACTION: 
  6. TRANSACTION 215208479, ACTIVE 0 sec fetching rows 
  7. mysql tables in use 3, locked 3 
  8. LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s) 
  9. MySQL thread id 27844824, OS thread handle 140092183037696, query id 412503674 10.126.95.84 zeye Searching rows for update 
  10. update t_channel_final_datas set nr_register=133,nr_add_goods=29,nr_order_normal=11,nr_pay_normal=8,nr_order_special=0,nr_pay_special=0,n_add_user_num=16 where count_date='2017-07-16' and channel_id='16' and channel_type='10' and terminal='26' 
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 
  12. RECORD LOCKS space id 464 page no 5459 n bits 392 index index_countdate_type_terminal of table `db_zz_flow`.`t_channel_final_datas` trx id 215208479 lock_mode X locks rec but not gap waiting 
  13. Record lock, heap no 304 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 
  14.  0: len 10; hex 323031372d30372d3136; asc 2017-07-16;; 
  15.  1: len 1; hex 30; asc 0;; 
  16.  2: len 4; hex 80000010; asc     ;; 
  17.  3: len 4; hex 8009055e; asc    ^;; 
  18.  
  19. *** (2) TRANSACTION: 
  20. TRANSACTION 215208474, ACTIVE 0 sec fetching rows 
  21. mysql tables in use 3, locked 3 
  22. 6 lock struct(s), heap size 1136, 7 row lock(s) 
  23. MySQL thread id 27844825, OS thread handle 140109890225920, query id 412503669 10.135.6.41 zeye Searching rows for update 
  24. update t_channel_final_datas set nr_register=24,nr_add_goods=32,nr_order_normal=0,nr_pay_normal=0,nr_order_special=0,nr_pay_special=0,n_add_user_num=11 where count_date='2017-07-16' and channel_id='114' and channel_type='10' and terminal='116' 
  25. *** (2) HOLDS THE LOCK(S): 
  26. RECORD LOCKS space id 464 page no 5459 n bits 392 index index_countdate_type_terminal of table `db_zz_flow`.`t_channel_final_datas` trx id 215208474 lock_mode X locks rec but not gap 
  27. Record lock, heap no 304 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 
  28.  0: len 10; hex 323031372d30372d3136; asc 2017-07-16;; 
  29.  1: len 1; hex 30; asc 0;; 
  30.  2: len 4; hex 80000010; asc     ;; 
  31.  3: len 4; hex 8009055e; asc    ^;; 
  32.  
  33. ... 
  34.  
  35. *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
  36. RECORD LOCKS space id 464 page no 4743 n bits 264 index PRIMARY of table `db_zz_flow`.`t_channel_final_datas` trx id 215208474 lock_mode X locks rec but not gap waiting 
  37. Record lock, heap no 168 PHYSICAL RECORD: n_fields 32; compact format; info bits 0 
  38.  0: len 4; hex 80090569; asc    i;; 
  39.  1: len 6; hex 00000cd3b9d0; asc       ;; 
  40. ... 
  41.  
  42. *** WE ROLL BACK TRANSACTION (1)  

篇幅所限，上下文我這里省略了很多，從這段日志里可以看到，TRANSACTION 1 和 TRANSACTION 2 分別持有一定數(shù)量的行鎖，然后又等待對(duì)方的鎖，*** MySQL 檢測(cè)到 deadlock ，然后選擇回滾了 TRANSACTION 1：Innodb目前處理死鎖的方法是將持有最少行級(jí)排他鎖的事務(wù)進(jìn)行回滾。

那這里就有 3 個(gè)問題了：

(1)innodb 行鎖不是只鎖一行?

因?yàn)檫@張表是 innodb 引擎的，InnoDB 支持行鎖和表鎖。而InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，這一點(diǎn)MySQL與Oracle不同，后者是通過在數(shù)據(jù)塊中對(duì)相應(yīng)數(shù)據(jù)行加鎖來實(shí)現(xiàn)的。InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級(jí)鎖，否則，InnoDB將使用表鎖，會(huì)把所有掃描過的行都鎖定!在實(shí)際應(yīng)用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能。由于MySQL的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會(huì)出現(xiàn)鎖沖突的。當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖;另外間隙鎖也會(huì)鎖多行，InnoDB除了通過范圍條件加鎖時(shí)使用間隙鎖外，如果使用相等條件請(qǐng)求給一個(gè)不存在的記錄加鎖，InnoDB也會(huì)使用間隙鎖!

話都說到這了，那就看下咱們業(yè)務(wù)表的索引情況：

  1. show INDEX from `db_zz_flow`.`t_channel_final_datas`; 
  2. +-----------------------+--------------+-------------------------------+----------------+-------------+----------+--------+--------------+-----------+-----------------+ 
  3. | Table                 |   Non_unique | Key_name                      |   Seq_in_index | Column_namt |   Packed | Null   | Index_type   | Comment   | Index_comment   | 
  4. |-----------------------+--------------+-------------------------------+----------------+-------------+----------+--------+--------------+-----------+-----------------| 
  5. | t_channel_final_datas |            0 | PRIMARY                       |              1 | id        > |   <null> |        | BTREE        |           |                 | 
  6. | t_channel_final_datas |            1 | index_countdate_type_terminal |              1 | count_date> |   <null> | YES    | BTREE        |           |                 | 
  7. | t_channel_final_datas |            1 | index_countdate_type_terminal |              2 | channel_ty> |   <null> | YES    | BTREE        |           |                 | 
  8. | t_channel_final_datas |            1 | index_countdate_type_terminal |              3 | terminal  > |   <null> | YES    | BTREE        |           |                 | 
  9. | t_channel_final_datas |            1 | index_countdate_channelid     |              1 | count_date> |   <null> | YES    | BTREE        |           |                 | 
  10. | t_channel_final_datas |            1 | index_countdate_channelid     |              2 | channel_id> |   <null> | YES    | BTREE        |           |                 | 
  11. +-----------------------+--------------+-------------------------------+----------------+-------------+----------+--------+--------------+-----------+-----------------+ 

可以看到這張表的索引極不合理：有3個(gè)索引，但是 update 卻沒有完全的用上索引，導(dǎo)致 update 沒有精確的用上索引，需要鎖定多行范圍數(shù)據(jù)，從而引發(fā)死鎖。

知道原理后，咱們?cè)倬臉?gòu)建一個(gè)四字段的組合索引即可讓 update 精準(zhǔn)的走 innodb 索引，實(shí)際上，我們更新索引后，這個(gè)死鎖問題即得到了解決。

注：innodb不僅會(huì)打印出事務(wù)和事務(wù)持有和等待的鎖，而且還有記錄本身，不幸的是，它可能超過innodb為輸出結(jié)果預(yù)留的長度(只能打印1M的內(nèi)容且只能保留最近一次的死鎖信息)，如果你無法看到完整的輸出，此時(shí)可以在任意庫下創(chuàng)建innodb_monitor或innodb_lock_monitor表，這樣innodb status信息會(huì)完整且每15s一次被記錄到錯(cuò)誤日志中。如：create table innodb_monitor(a int)engine=innodb;，不需要記錄到錯(cuò)誤日志中時(shí)就刪掉這個(gè)表即可。

(2)回滾的話，為什么只有部分 update 語句失敗，而不是整個(gè)事務(wù)里的所有 update 都失敗?

這是因?yàn)樵蹅兊?innodb 默認(rèn)是自動(dòng)提交的：

  1. show variables like 'autocommit'; 
  2. +-----------------+---------+ 
  3. | Variable_name   | Value   | 
  4. |-----------------+---------| 
  5. | autocommit      | ON      | 
  6. +-----------------+---------+  

在多個(gè) update 或 insert 語句情況下，每執(zhí)行完一條 SQL，innodb 就立即 commit 一次以持久化變更，同時(shí)釋放鎖，這也正是本例中死鎖回滾事務(wù)后只有極個(gè)別語句失敗的原因。

需要注意的是，通常還有另外一種情況也可能導(dǎo)致部分語句回滾，需要格外留意。在 innodb 里有個(gè)參數(shù)叫：innodb_rollback_on_timeout

  1. show VARIABLES LIKE 'innodb_rollback_on_timeout' 
  2. +----------------------------+---------+ 
  3. | Variable_name              | Value   | 
  4. |----------------------------+---------| 
  5. | innodb_rollback_on_timeout | OFF     | 
  6. +----------------------------+---------+  

官方手冊(cè)里這樣描述：

In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.

解釋：這個(gè)參數(shù)關(guān)閉或不存在的話遇到超時(shí)只回滾事務(wù)***一個(gè)Query，打開的話事務(wù)遇到超時(shí)就回滾整個(gè)事務(wù)。

(3)怎樣降低 innodb 死鎖幾率?

死鎖在行鎖及事務(wù)場(chǎng)景下很難完全消除，但可以通過表設(shè)計(jì)和SQL調(diào)整等措施減少鎖沖突和死鎖，包括：

  • 盡量使用較低的隔離級(jí)別，比如如果發(fā)生了間隙鎖，你可以把會(huì)話或者事務(wù)的事務(wù)隔離級(jí)別更改為 RC(read committed)級(jí)別來避免，但此時(shí)需要把 binlog_format 設(shè)置成 row 或者 mixed 格式
  • 精心設(shè)計(jì)索引，并盡量使用索引訪問數(shù)據(jù)，使加鎖更精確，從而減少鎖沖突的機(jī)會(huì);
  • 選擇合理的事務(wù)大小，小事務(wù)發(fā)生鎖沖突的幾率也更小;
  • 給記錄集顯示加鎖時(shí)，***一次性請(qǐng)求足夠級(jí)別的鎖。比如要修改數(shù)據(jù)的話，***直接申請(qǐng)排他鎖，而不是先申請(qǐng)共享鎖，修改時(shí)再請(qǐng)求排他鎖，這樣容易產(chǎn)生死鎖;
  • 不同的程序訪問一組表時(shí)，應(yīng)盡量約定以相同的順序訪問各表，對(duì)一個(gè)表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機(jī)會(huì);
  • 盡量用相等條件訪問數(shù)據(jù)，這樣可以避免間隙鎖對(duì)并發(fā)插入的影響;
  • 不要申請(qǐng)超過實(shí)際需要的鎖級(jí)別;除非必須，查詢時(shí)不要顯示加鎖;
  • 對(duì)于一些特定的事務(wù)，可以使用表鎖來提高處理速度或減少死鎖的可能。

2、Case2：詭異的 Lock wait timeout

連續(xù)幾天凌晨6點(diǎn)和早上8點(diǎn) 都分別有一個(gè)任務(wù)失敗，load data local infile 的時(shí)候報(bào) Lock wait timeout exceeded try restarting transaction innodb 的 Java SQL 異常，和平臺(tái)的同學(xué)溝通得知，這是我們自己的業(yè)務(wù)數(shù)據(jù)庫的 Lock 時(shí)間太短或者鎖沖突的問題。但是回頭一想不應(yīng)該啊?這不一直好好的嗎?而且基本都是單表單任務(wù)，不存在多人沖突。

甭管誰的問題，那咱們還是先看自己的數(shù)據(jù)庫有沒有問題：

  1. show variables like 'innodb_lock_wait_timeout'; 
  2. +--------------------------+---------+ 
  3. | Variable_name            |   Value | 
  4. |--------------------------+---------| 
  5. | innodb_lock_wait_timeout |      50 | 
  6. +--------------------------+---------+  

默認(rèn) lock 超時(shí)時(shí)間 50s，這個(gè)時(shí)間真心不短了，估計(jì)調(diào)了也沒用，事實(shí)上確實(shí)死馬當(dāng)活馬醫(yī)的試了下沒用。。。

而且這次 SHOW ENGINE INNODB STATUS\G 也沒出現(xiàn)任何死鎖信息，然后又將目光轉(zhuǎn)向 MySQL-server 日志，希望能從日志里看一看那個(gè)時(shí)刻前后數(shù)據(jù)究竟在做什么操作。這里先簡(jiǎn)單的介紹下MySQL日志文件系統(tǒng)的組成：

(a) error 日志：記錄啟動(dòng)、運(yùn)行或停止 mysqld 時(shí)出現(xiàn)的問題，默認(rèn)開啟。

(b) general 日志：通用查詢?nèi)罩?，記錄所有語句和指令，開啟數(shù)據(jù)庫會(huì)有 5% 左右性能損失。

(c) binlog 日志：二進(jìn)制格式，記錄所有更改數(shù)據(jù)的語句，主要用于 slave 復(fù)制和數(shù)據(jù)恢復(fù)。

(d) slow 日志：記錄所有執(zhí)行時(shí)間超過 long_query_time 秒的查詢或不使用索引的查詢，默認(rèn)關(guān)閉。

(e) Innodb日志：innodb redo log、undo log，用于恢復(fù)數(shù)據(jù)和撤銷操作。

從上面的介紹可以看到，目前這個(gè)問題的日志可能在 d 和 b 中，看了下 d 中沒有，那就只能開啟 b 了，但 b 對(duì)數(shù)據(jù)庫的性能有一定損耗，由于是全量日志，量非常巨大，所以開啟一定要謹(jǐn)慎：

  1. -- general_log 日志默認(rèn)關(guān)閉，開啟會(huì)影響數(shù)據(jù)庫 5% 左右性能： 
  2. show variables like 'general%'; 
  3. +------------------+---------------------------------+ 
  4. | Variable_name    | Value                           | 
  5. |------------------+---------------------------------| 
  6. | general_log      | OFF                             | 
  7. | general_log_file | /opt/data/mysql/tjtx-103-26.log | 
  8. +------------------+---------------------------------+ 
  9.  
  10. -- 全局 session 級(jí)別開啟： 
  11. set global general_log=1 
  12.  
  13. -- 如果需要對(duì)當(dāng)前 session 生效需要： 
  14. set general_log=1 
  15.  
  16. -- set 指令設(shè)置的動(dòng)態(tài)參數(shù)在 MySQL 重啟后失效，如果需要***生效需要在 /etc/my.cnf 中配置靜態(tài)變量/參數(shù)。 
  17. -- 如果不知道 my.cnf 位置，可以根據(jù) mysql -? | grep ".cnf" 查詢 
  18.                       order of preference, my.cnf, $MYSQL_TCP_PORT, 
  19. /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf  

我這里只是每天在出問題的前后半小時(shí)開啟下全量日志，結(jié)果沒有發(fā)現(xiàn)任何 MySQL-client 請(qǐng)求到我們的業(yè)務(wù)數(shù)據(jù)庫!該日志格式如下，記錄了所有的連接與命令：

  1. /usr/sbin/mysqld, Version: 5.7.12-log (MySQL Community Server (GPL)). started with: 
  2. Tcp port: 3306  Unix socket: /opt/data/mysql/mysql.sock 
  3. Time                 Id Command    Argument 
  4. 2017-07-20T21:45:01.880828Z28556028 Quit 
  5. 2017-07-20T21:45:02.708621Z28401469 Query       SELECT 1 
  6. 2017-07-20T21:45:02.736734Z28556029 Connect     ooxx@127.0.0.1 on db_zz_system using TCP/IP 
  7. 2017-07-20T21:45:02.737242Z28556029 Query       /* mysql-connector-java-5.1.6 ( Revision: ${svn.Revision} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect' 
  8. 2017-07-20T21:45:02.738868Z28556029 Query       SHOW COLLATION 
  9. 2017-07-20T21:45:02.739941Z28556029 Query       SET character_set_results = NULL 
  10. 2017-07-20T21:45:02.740275Z28556029 Query       SET autocommit=1 

那問題基本確定了，客戶端請(qǐng)求都沒到我們這邊就拋出了上述的異常，和平臺(tái)方再三溝通確認(rèn)下，***平臺(tái)方查證是因?yàn)樵趫?zhí)行插入前他們需要先從 SQL task 表取出 SQL 和更新 task 狀態(tài)，結(jié)果這張表由于在整點(diǎn)存在大量 insert 和 update 并發(fā)，導(dǎo)致部分 SQL 等待 lock 超時(shí)了。。。

[[198411]] 

3、MySQL 日志分析腳本

由于凌晨是數(shù)據(jù)倉庫的業(yè)務(wù)高峰，很多問題都是在這個(gè)時(shí)候爆發(fā)，一些詭異的問題往往是過了這個(gè)村就沒這個(gè)店了，白天無法復(fù)現(xiàn)。如何能捕獲我們關(guān)心的日志，便于快速的定位問題，這個(gè)是重中之重，這里我寫了個(gè)小腳本，crontab 部署，可以選擇時(shí)間范圍開啟，每分鐘采樣一次日志，需要說明的是 general log 沒事別輕易開啟，否則對(duì)數(shù)據(jù)庫性能損耗較大。

  1. # crontab 部署方式： 
  2. # */1 0-10 * * * cd /opt/ooxx/script && bash mysql_perf.sh >> logs/mysql_perf.log.`date -I` 2>&1 
  3.  
  4. date -Iseconds 
  5.  
  6. echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> top -bn1|head' 
  7. top -bn1|head   
  8. echo 
  9.  
  10. echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SHOW ENGINE INNODB STATUS\G ' 
  11. mysql -uroot -pooxx -h127.0.0.1 -e 'SHOW ENGINE INNODB STATUS\G' 
  12. echo 
  13.  
  14. echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show open tables where in_use>0;' 
  15. mysql -uroot -pooxx -h127.0.0.1 -e 'show open tables where in_use>0;' 
  16. echo 
  17.  
  18. echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show full processlist;' 
  19. mysql -uroot -pooxx -h127.0.0.1 -e 'show full processlist;' 
  20. echo 
  21.  
  22. echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; ' 
  23. mysql -uroot -pooxx -h127.0.0.1 -e 'SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;'  
  24. echo 
  25.  
  26. echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SELECT * FROM `information_schema`.`innodb_locks`;' 
  27. mysql -uroot -pooxx -h127.0.0.1 -e 'SELECT * FROM `information_schema`.`innodb_locks`;' 
  28. echo 
  29.  
  30. echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show status like '%lock%';" 
  31. mysql -uroot -pooxx -h127.0.0.1 -e "show status like '%lock%';" 
  32. echo 
  33.  
  34. echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show global status like "table_locks%";' 
  35. mysql -uroot -pooxx -h127.0.0.1 -e 'show global status like "table_locks%";' 
  36. echo 
  37.  
  38. echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>> too long omit..." 
  39. mysql -uroot -pooxx -h127.0.0.1 -e "select r.trx_isolation_level, r.trx_id waiting_trx_id, r.trx_mysql_thread_id  waiting_trx_thread, r.trx_state  waiting_trx_state, lr.lock_mode waiting_trx_lock_mode, lr.lock_type  waiting_trx_lock_type, lr.lock_table  waiting_trx_lock_table, lr.lock_index  waiting_trx_lock_index, r.trx_query  waiting_trx_query, b.trx_id  blocking_trx_id, b.trx_mysql_thread_id  blocking_trx_thread, b.trx_state  blocking_trx_state, lb.lock_mode blocking_trx_lock_mode, lb.lock_type  blocking_trx_lock_type, lb.lock_table  blocking_trx_lock_table, lb.lock_index  blocking_trx_lock_index, b.trx_query  blocking_query from  information_schema.innodb_lock_waits  w  inner  join  information_schema.innodb_trx b  on  b.trx_id=w.blocking_trx_id  inner  join  information_schema.innodb_trx  r on  r.trx_id=w.requesting_trx_id  inner  join   information_schema.innodb_locks  lb on  lb.lock_trx_id=w.blocking_trx_id inner  join   information_schema.innodb_locks  lr on  lr.lock_trx_id=w.requesting_trx_id\G" 
  40. echo 
  41.  
  42. echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>> too long omit..." 
  43. mysql -uroot -pooxx -h127.0.0.1 -e "SELECT r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id,  b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON  b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON  r.trx_id = w.requesting_trx_id\G" 
  44. echo 
  45.  
  46.  
  47. date -Iseconds 
  48. echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>==================================================================================' 
  49. echo 
  50.  
  51. file_name=mysql_perf.log.`date -I` 
  52. if [[ `date +%-H%-M` == 1059 ]] 
  53. then 
  54.     cd /opt/ooxx/script/logs 
  55.     chmod 777 /home/work/ooxx/$file_name 
  56.     find /home/work/ooxx -name 'mysql_perf.log.*' -mtime +7 -delete 
  57.      
  58.     cd /opt/data/mysql 
  59.     cp tjtx-ooxx-slow.log /home/work/ooxx/tjtx-ooxx-slow.log.`date -I` 
  60.     chmod 777 /home/work/ooxx/tjtx-ooxx-slow.log.`date -I` 
  61.     find /home/work/ooxx -name 'tjtx-ooxx-slow.log.*' -mtime +7 -delete 
  62.      
  63.     cp mysqld.log /home/work/ooxx/mysqld.log.`date -I` 
  64.     chmod 777 /home/work/ooxx/mysqld.log.`date -I` 
  65.     find /home/work/ooxx -name 'mysqld.log.*' -mtime +7 -delete 
  66. fi 
  67.  
  68.  
  69. ################ 
  70. # 開啟 general_log 全量明細(xì)日志會(huì)降低數(shù)據(jù)庫 5% 性能 
  71. #if [[ "`date +%H%M`" == "0545" ]] 
  72. #then 
  73. #   echo "`date +%H%M` ------- set global general_log=1;" 
  74. #   mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=1;' 
  75. #elif [[ "`date +%H%M`" == "0630" ]] 
  76. #then 
  77. #   echo "`date +%H%M` ------- set global general_log=0;" 
  78. #   mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=0;' 
  79. #elif [[ "`date +%H%M`" == "0745" ]] 
  80. #then 
  81. #   echo "`date +%H%M` ------- set global general_log=1;" 
  82. #   mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=1;' 
  83. #elif [[ "`date +%H%M`" == "0830" ]] 
  84. #then 
  85. #   echo "`date +%H%M` ------- set global general_log=0;" 
  86. #   mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=0;' 
  87. #elif [[ "`date +%H%M`" == "0001" ]] 
  88. #then 
  89. #   echo "`date +%H%M` ------- set global general_log=1;" 
  90. #   mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=1;' 
  91. #elif [[ "`date +%H%M`" == "0002" ]] 
  92. #then 
  93. #   echo "`date +%H%M` ------- set global general_log=0;" 
  94. #   mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=0;' 
  95. #fi 
  96.  
  97.  
  98.  
  99.  
  100.  
  101. #[[ 10#`date +%H%M` -lt 10#0550 ||  10#`date +%H%M` -gt 10#0830 ]] && echo "`date +%H%M` ------- set global general_log=0;" && mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=0;' 
  102.  
  103.  
  104. # mysql -uroot -pooxx -h127.0.0.1 -e 'show open tables where in_use>0;show full processlist;SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;SELECT * FROM `information_schema`.`innodb_locks`;SHOW ENGINE INNODB STATUS\G' 
  105.  
  106. # --show variables like '%tx_isolation%'; 
  107. # --SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation; 
  108. # --  SET GLOBAL tx_isolation = 'READ-COMMITTED'; 
  109. #  
  110. # --show variables like '%timeout%'; 
  111. # --show variables like 'innodb_lock_wait_timeout'; 
  112. # --  SET GLOBAL innodb_lock_wait_timeout=60 
  113. #  
  114. # --show variables like 'long_query_time'; 
  115. # --  SET global long_query_time=3; 
  116.  
  117.  
  118. # --  show variables like 'innodb_rollback_on_timeout'; 
  119.  
  120. # --  show VARIABLES like '%max_allowed_packet%'; 
  121. # --  set global max_allowed_packet = 100*1024*1024; 
  122.  
  123. # 自動(dòng)提交 
  124. # -- show variables like 'autocommit'; 
  125.  
  126. # 慢查詢 
  127. # -- show variables  like '%slow_query_log%'; 
  128. # set global 只對(duì)當(dāng)前數(shù)據(jù)庫生效，如果MySQL重啟后則會(huì)失效。如果要***生效，就必須修改配置文件my.cnf（其它系統(tǒng)變量也是如此）。 
  129. # set global slow_query_log=1; 
  130. # -- show variables like 'long_query_time%'; 
  131. # set global long_query_time=4; 
  132. # show global variables like 'long_query_time';  
  133. # select sleep(5); 
  134. # -- show variables like 'log_queries_not_using_indexes'; 
  135. # set global log_queries_not_using_indexes=1; 
  136. # -- show variables like 'log_slow_admin_statements'; 
  137. # -- show global status like '%Slow_queries%'; 
  138. # http://www.cnblogs.com/kerrycode/p/5593204.html 
  139.  
  140.  
  141.  
  142. # -- show variables like "%time_zone%"; 
  143. #set global time_zone = '+8:00'; 
  144.  
  145. #開啟general_log日志 
  146. # -- show variables like 'general%'; 
  147. #可以在my.cnf里添加,1開啟（0關(guān)閉）,當(dāng)然了,這樣要重啟才能生效,有點(diǎn)多余了 
  148. #general-log = 1 
  149. #log = /log/mysql_query.log路徑 
  150. #也可以設(shè)置變量那樣更改,1開啟（0關(guān)閉）,即時(shí)生效,不用重啟,***當(dāng)然是這樣的了 
  151. #  set global general_log=1 
  152. #這個(gè)日志對(duì)于操作頻繁的庫,產(chǎn)生的數(shù)據(jù)量會(huì)很快增長,出于對(duì)硬盤的保護(hù),可以設(shè)置其他存放路徑 
  153. #set global general_log_file=/tmp/general_log.log 
  154.  
  155. #mysql記錄客戶端IP：init_connect，有super權(quán)限的用戶是不記錄的， 
  156. # create  table t1 ( cur_user varchar(100), n_user varchar(100),in_time timestamp  default current_timestamp()) ; 
  157. # set global init_connect='insert into test.t1 (cur_user,n_user) values (current_user(),user())';    
  158. # SHOW CREATE TABLE mysql.general_log\G ，開啟general_log日志也行： 
  159. # https://dba.stackexchange.com/questions/33654/mysql-logging-activity-from-specific-user-or-ip               
  160. #SELECT REVERSE(SUBSTRING_INDEX(REVERSE(USER()),'@',1)) as ip; 
  161. #SELECT SUBSTRING(USER(), LOCATE('@', USER())+1) as ip; 
  162. #select SUBSTRING_INDEX(host,':',1) as 'ip' from information_schema.processlist WHERE ID=connection_id(); 

Refer：

[1] MySQL批量SQL插入性能優(yōu)化

http://database.51cto.com/art/201309/411050.htm

[2] MySQL 四種事務(wù)隔離級(jí)的說明

http://www.cnblogs.com/zhoujinyi/p/3437475.html

[3] innodb_rollback_on_timeout參數(shù)對(duì)鎖的影響

http://q.fireflyclub.org/?/article/37

[4] 日均萬條數(shù)據(jù)丟失，一個(gè)隱式騷操作導(dǎo)致的奇葩事故!

https://mp.weixin.qq.com/s/OpPV24ILewVc2lRDrV4vvA

[5] mysql之show engine innodb status解讀(轉(zhuǎn))

http://www.cnblogs.com/ajianbeyourself/p/6941905.html

http://www.cnblogs.com/olinux/p/5497176.html

[6] Mysql 鎖機(jī)制筆記

http://www.jianshu.com/p/fa28035656a9

http://www.jianshu.com/p/ec4a343c49f0

http://hudeyong926.iteye.com/blog/1490687

[7] MySQL索引原理及慢查詢優(yōu)化

https://tech.meituan.com/mysql-index.html

[8] [譯文]MySQL發(fā)生死鎖腫么辦?

http://www.10tiao.com/html/672/201612/2653929751/1.html

[9] MySQL的timeout那點(diǎn)事

http://www.penglixun.com/tech/database/mysql_timeout.html

[10] mysql死鎖(deadlock)分析及解決 (索引 間隙鎖 sql順序)

http://www.youyong.top/article/1158d0e3bcf 

責(zé)任編輯：龐桂玉 來源： xrzs的博客
數(shù)據(jù)庫MySQL死鎖與日志
相關(guān)推薦

2017-07-27 17:37:44

MySQL死鎖日志

2017-06-07 16:10:24

Mysql死鎖死鎖日志

2022-03-16 19:04:33

設(shè)計(jì)模式場(chǎng)景

2013-08-07 14:19:30

禁用

2011-01-26 10:52:56

2012-12-18 20:13:00

云存儲(chǔ)初志

2016-12-05 08:46:07

緩存架構(gòu)設(shè)計(jì)

2014-11-20 13:49:15

2022-01-08 21:33:39

反入侵安全風(fēng)險(xiǎn)攻擊

2010-05-28 10:48:52

MySQL數(shù)據(jù)庫

2021-11-03 06:25:58

確定性網(wǎng)絡(luò)網(wǎng)絡(luò)無線網(wǎng)絡(luò)

2020-04-20 10:40:19

紅藍(lán)對(duì)抗網(wǎng)絡(luò)攻擊數(shù)據(jù)泄露

2012-08-15 16:03:25

Ubuntu 12.0服務(wù)器

2013-10-14 11:03:48

管理貝索斯

2021-10-18 10:47:29

EDAEventBridge

2013-07-08 09:26:55

2017-11-29 14:18:09

面試程序員工程師

2009-10-23 14:44:01

2011-12-30 09:33:02

程序員語言

2011-06-01 15:49:00

Android 測(cè)試
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)