欧美一区二区三区老妇人-欧美做爰猛烈大尺度电-99久久夜色精品国产亚洲a-亚洲福利视频一区二区

【Mysql】mysql主鍵的缺少導(dǎo)致備庫hang

最近線上頻繁的出現(xiàn)slave延時的情況，經(jīng)排查發(fā)現(xiàn)為用戶在刪除數(shù)據(jù)的時候，由于表主鍵的缺少，同時刪除條件沒有索引，或或者刪除的條件過濾性極差，導(dǎo)致slave出現(xiàn)hang住，嚴重的影響了生產(chǎn)環(huán)境的穩(wěn)定性，也希望通過這篇博客，來加深主鍵在innodb引擎中的重要性，希望用戶在使用RDS，設(shè)計自己的表的時候，一定要為表加上主鍵，主鍵可以認為是innodb存儲引擎的生命，下面我們就來分析一下這個案例（本案例的生產(chǎn)環(huán)境的binlog為row模式，對于myisam存儲引擎也有同樣的問題）：

  1. (1).現(xiàn)象slave:
  1. MySQL> show slave status\G;
  2. *************************** 1. row ***************************
  3. Slave_IO_State: Waiting for master to send event
  4. Master_Host: xxx.xx.xx.xx
  5. Master_User: replicator
  6. Master_Port: 3006
  7. Connect_Retry: 60
  8. Master_Log_File: mysql-bin.000006
  9. Read_Master_Log_Pos: 47465657
  10. Relay_Log_File: slave-relay.100383
  11. Relay_Log_Pos: 251
  12. Relay_Master_Log_File: mysql-bin.000006
  13. Slave_IO_Running: Yes
  14. Slave_SQL_Running: Yes
  15. Replicate_Do_DB:
  16. Replicate_Ignore_DB:
  17. Replicate_Do_Table:
  18. Replicate_Ignore_Table:
  19. Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table:
  21. Last_Errno: 0
  22. Last_Error:
  23. Skip_Counter: 0
  24. Exec_Master_Log_Pos: 18057461
  25. Relay_Log_Space: 29409335
  26. Until_Condition: None
  27. Until_Log_File:
  28. Until_Log_Pos: 0
  29. Master_SSL_Allowed: No
  30. Master_SSL_CA_File:
  31. Master_SSL_CA_Path:
  32. Master_SSL_Cert:
  33. Master_SSL_Cipher:
  34. Master_SSL_Key:
  35. Seconds_Behind_Master: 1339
  36. Master_SSL_Verify_Server_Cert: No
  37. Last_IO_Errno: 0
  38. Last_IO_Error:
  39. Last_SQL_Errno: 0
  40. Last_SQL_Error:
  41. slave的Seconds_Behind_Master一直在增加，slave出現(xiàn)hang住。

(2).解析當(dāng)前slave執(zhí)行到的位置的binlog：
  1. mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000006 –start-position=18057461 >/tmp/2.log
  2. ### UPDATE qianyi.dmpush_message_temp
  3. ### WHERE
  4. ### @1=’fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
  5. ### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
  6. ### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
  7. ### @4=’0′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */

(3)分析：

  1. 模擬場景：
    1.表中無主鍵，全表進行更新：
    master:
    表結(jié)構(gòu)：
    CREATE TABLE `dmpush_message_temp` (
    `clientid` varchar(36) DEFAULT NULL,
    `infoid` bigint(10) DEFAULT NULL,
    `endtime` varchar(14) DEFAULT NULL,
    `stat` varchar(8) DEFAULT NULL
    ) ENGINE=innodb DEFAULT CHARSET=utf8;

    十余年的杜集網(wǎng)站建設(shè)經(jīng)驗，針對設(shè)計、前端、開發(fā)、售后、文案、推廣等六對一服務(wù)，響應(yīng)快，48小時及時工作處理。成都全網(wǎng)營銷推廣的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動調(diào)整杜集建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計，從而大程度地提升瀏覽體驗。創(chuàng)新互聯(lián)建站從事“杜集網(wǎng)站設(shè)計”,“杜集網(wǎng)站推廣”以來，每個客戶項目都認真落實執(zhí)行。

    mysql> update dmpush_message_temp set stat=1 ;
    Query OK, 226651 rows affected (1.69 sec)
    Rows matched: 226651 Changed: 226651 Warnings: 0

    mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000007 >/tmp/test.log

    1. binlog中第一個出現(xiàn)的update事務(wù)日志：
    2. 2281772 ### UPDATE qianyi.dmpush_message_temp
      2281773 ### WHERE
      2281774 ### @1=’fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
      2281775 ### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
      2281776 ### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
      2281777 ### @4=’0′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */
      2281778 ### SET
      2281779 ### @1=’fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
      2281780 ### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
      2281781 ### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
      2281782 ### @4=’1′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */

    4. binlog中最后出現(xiàn)的update事務(wù)日志：
      5313201 ### UPDATE qianyi.dmpush_message_temp
      5313202 ### WHERE
      5313203 ### @1=’fffff4fc-0b72-4ba2-9749-0189658af6d5′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
      5313204 ### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
      5313205 ### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
      5313206 ### @4=’0′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */
      5313207 ### SET
      5313208 ### @1=’fffff4fc-0b72-4ba2-9749-0189658af6d5′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
      5313209 ### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
      5313210 ### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
      5313211 ### @4=’1′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */
      注意這里由于表中沒有主鍵，所以導(dǎo)致了每一個事務(wù)條目的更新都是全表掃描，如果表中很很多的數(shù)據(jù)，則備庫執(zhí)行該更新的事務(wù)條目的時候，就會出現(xiàn)很多的全表掃描更新；
    計算有多少事務(wù)條目：
    root@xxxxxxxxx # cat /tmp/test.log|grep ‘UPDATE qianyi.dmpush_message_temp’ -A 10 |wc -l
    2521492

    mysql> select 2521492/11;—-11為一個update事務(wù)條目占用的行數(shù)
    +————-+
    | 2521492/11 |
    +————-+
    | 229226.5455 |
    +————-+

    mysql> use qianyi
    Database changed
    mysql> select count(*) from dmpush_message_temp;
    +———-+
    | count(*) |
    +———-+
    | 226651 |
    +———-+
    可以看到，binlog的條目數(shù)和該表的數(shù)據(jù)量查不多是一致，也就是在全表更新的時候(在row模式下)更新多少行，就有多少事務(wù)的事務(wù)條目；



4.優(yōu)化

  1. 為dmpush_message_temp表添加主鍵：
    mysql> alter table dmpush_message_temp add column id int not null auto_increment,add PRIMARY Key(id);
    Query OK, 226651 rows affected (1.09 sec)
    Records: 226651 Duplicates: 0 Warnings: 0

    mysql> update dmpush_message_temp set stat=0 ;
    Query OK, 226651 rows affected (1.69 sec)
    Rows matched: 226651 Changed: 226651 Warnings: 0

    解析binlog中的事務(wù)條目：
    root@xxxxxxxxx # mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000008 >/tmp/test1.log

    ### UPDATE qianyi.dmpush_message_temp
    ### WHERE
    ### @1=’fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
    ### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
    ### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
    ### @4=’1′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */
    ### @5=1 /* INT meta=0 nullable=0 is_null=0 */
    ### UPDATE qianyi.dmpush_message_temp
    ### WHERE
    ### @1=’fb5bdc4f-da8a-4f03-aa5e-27677d7c8ac3′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
    ### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
    ### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
    ### @4=’1′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */
    ### @5=2 /* INT meta=0 nullable=0 is_null=0 */
    可以看到這里的事務(wù)條目中由于已經(jīng)有了主鍵，也就是@5（第一個事務(wù)條目更新和第二個事務(wù)條目更新的@5是遞增的，即主鍵）,這樣事務(wù)日志就會根據(jù)主鍵來更新，備庫執(zhí)行則不會卡??；




解決：
  1. 問題的原因已經(jīng)找到了，由于表中沒有主鍵，ROW模式下，每刪一條數(shù)據(jù)都會做全表掃，也就是說一條delete，如果刪了10條，會做10次全表掃。。。。所以slave一直卡??；
  2. 1.slave:停止slave；
  3. mysql> stop slave;
  4. Ctrl-C — sending “KILL QUERY 59028” to server …
  5. Ctrl-C — query aborted.
  6. Ctrl-C — sending “KILL 59028” to server …
  7. Ctrl-C — query aborted.
  8. Ctrl-C —
  9. Aborted

  10. 2.這個時候，發(fā)現(xiàn)slave已經(jīng)卡住，無法進行任何操作，這個時候只有強行kill掉mysql進程
    root@xxxxxxxx.com # ps -ef|grep 3006
    root 4456 1 0 Oct11 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe –defaults-file=/etc/my3006.cnf
    mysql 6828 4456 0 Oct11 ? 00:39:27 /usr/sbin/mysqld –defaults-file=/etc/my3006.cnf –basedir=/ –datadir=/home/mysql/data3006/dbs3006 –user=mysql –log-error=/home/mysql/data3006/mysql/master-error.log –open-files-limit=8192 –pid-file=/home/mysql/data3006/dbs3006/xxxxxxxx.com.pid –socket=/home/mysql/data3006/tmp/mysql.sock –port=3006

    kill -9 4456 6828

    由于我們的slave復(fù)制是在mysqld啟動的時候自動啟動，所以這里我們需要將其關(guān)閉：
    vi /etc/my3006.cnf中加入：skip-slave-start，在用mysqld_safe啟動；


    12. 3.由于主庫的binlog已經(jīng)傳入備庫，這個時候，slave執(zhí)行沒有主鍵更新的事務(wù)日志就會hang住，這個時候可以采取一種巧妙的方法來避過，就是將備庫中的這張表進行數(shù)據(jù)清空，slave在執(zhí)行realy log的時候，就會報1032錯誤，我們寫一個腳本進行skip掉這些錯誤，當(dāng)備庫追趕上主庫后，我們在把主庫的表通過mysqldump，或者insert select 還原到備庫，這樣就可以讓slave正常的運行起來，然后在通知客戶進行主鍵的改造；

    a。slave上執(zhí)行以下命令：
    slave:清空備庫上有問題的表
    set sql_log_bin=off;
    truncate table qianyi.dmpush_message_temp;
    start slave;

    跳過該表上的錯誤：
    sh /tmp/skip.sh 3006 dmpush_message_temp

    b.等備庫追上主庫后，執(zhí)行以下命令：
    master：

    lock tables qianyi.dmpush_message_temp read;

    create table a2 like qianyi.dmpush_message_temp ;

    lock tables a2 write, qianyi.dmpush_message_temp read;

    insert into a2 select * from qianyi.dmpush_message_temp ;

    slave:

    set sql_log_bin=off;

    drop table qianyi.dmpush_message_temp;

    create table qianyi.dmpush_message_temp like a2;

    insert into qianyi.dmpush_message_temp select * from a2;

    c.最后讓應(yīng)用改造，添加上主鍵：
    mysql> alter table dmpush_message_temp add column id int not null auto_increment,add PRIMARY Key(id);

    3.當(dāng)slave卡住的時候，可以通過解析binlog來看看，slave到底卡住在那里，是那個事務(wù)，下面是一個簡單的方法，來看當(dāng)前salve打開的表：
    mysql> show open tables;
    +———-+———————+——–+————-+
    | Database | Table | In_use | Name_locked |
    +———-+———————+——–+————-+
    | qianyi | dmpush_message_temp | 1 | 0 |
    | qianyi | test | 0 | 0 |
    | qianyi | anson | 0 | 0 |
    | mysql | db | 0 | 0 |
    | mysql | slow_log | 0 | 0 |
    | mysql | event | 0 | 0 |
    +———-+———————+——–+————-+
    可以看到這里dmpush_message_temp一直處于打開狀態(tài)，這里就可以直接定位問題的根源了；

    總結(jié)：主鍵對于innodb來說，是非常重要的，每張表的設(shè)計的時候，都應(yīng)該把主鍵默認的加上，不管你需不需要他，而且主鍵的設(shè)計最好選擇自增型的主鍵，這里也可以略提一下自增主鍵的好處：
    a.自增型主鍵以利于插入性能的提高；
    b.自增型主鍵設(shè)計(int,bigint)可以降低二級索引的空間，提升二級索引的內(nèi)存命中率；
    c.自增型的主鍵可以減小page的碎片，提升空間和內(nèi)存的使用。



與這篇文章很像：http://wubx.net/slave-delay/

文章題目：【Mysql】mysql主鍵的缺少導(dǎo)致備庫hang
文章位置：http://chinadenli.net/article34/ppcspe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)建站、網(wǎng)頁設(shè)計公司、網(wǎng)站維護、用戶體驗、網(wǎng)站內(nèi)鏈、App設(shè)計

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

成都網(wǎng)站建設(shè)
亚洲国产欧美久久精品| 亚洲天堂精品在线视频| 午夜福利激情性生活免费视频| 国产亚洲成av人在线观看| 国产亚洲二区精品美女久久| 黑色丝袜脚足国产一区二区| 在线观看视频成人午夜| 粉嫩国产一区二区三区在线| 精品一区二区三区人妻视频| 亚洲av熟女一区二区三区蜜桃| 日韩免费国产91在线| 老熟妇2久久国内精品| 午夜成年人黄片免费观看| 午夜福利激情性生活免费视频| 伊人色综合久久伊人婷婷| 日韩高清一区二区三区四区| 日本男人女人干逼视频| 日本人妻丰满熟妇久久| 久久香蕉综合网精品视频| 国产不卡在线免费观看视频| 中文字幕无线码一区欧美| 国产美女网红精品演绎| 91亚洲熟女少妇在线观看| 日韩日韩欧美国产精品| 亚洲午夜精品视频观看| 国产不卡免费高清视频| 亚洲国产性生活高潮免费视频 | 东京不热免费观看日本| 欧美一区二区三区高潮菊竹| 国产传媒中文字幕东京热| 98精品永久免费视频| 日本午夜免费啪视频在线| 欧美一区二区三区性视频| 日本一二三区不卡免费| 婷婷色香五月综合激激情| 欧美野外在线刺激在线观看| 国产成人亚洲精品青草天美| 日本人妻精品有码字幕| 国产亚洲精品岁国产微拍精品| 久久偷拍视频免费观看| 果冻传媒在线观看免费高清|