MY SQL 知識

LINUX配置優化

• 診斷命令
  • more perfomance tool
  • SRE performance
• 硬體優化
  • raid 10
  • ssd
  • Fusion-io
  • 大記憶體
  • 電池後備SCSI磁碟快取
• 系統優化
  • 日誌，資料分盤
  • IO排程演算法 deadline適用於磁碟 noop適用於ssd
  • XFS檔案系統
  • 避免使用swap 分割槽
  • fd限制 ulimit

MySQL引數配置

• 基礎配置
  • 字符集
  • 資料目錄，日誌目錄，臨時目錄
  • sql_mode
• 快取設定
  • 執行緒快取
  • 表快取
  • 排序快取
  • join快取
  • 查詢快取
  • binlog快取
  • ......
• 閾值設定
  • max connection
  • timeout
  • slow query time
  • ......
• innodb 設定
  • instances
  • size
  • io_capacity
  • innodb_flush_method=O_DIRECT 資料直接寫盤，避過OS cache 日誌還是OS flush
  • 雙一設定
  • ......

MySQL開發規範

• DDL
  • 型別使用要精確
  • not null
  • 避免text/blob
  • 避免外來鍵
  • 自增主鍵
  • 避免冗餘索引
  • 索引數量控制
  • 索引欄位過濾度
• DML
  • 避免使用觸發器，儲存過程
  • 儲存使用Mysql，不要有運算邏輯
  • 更新操作禁用limit
  • select * 禁用
  • in 優先 or
  • 同表alter操作合併

BIG SQL

• 索引優化
  • WHERE 條件索引使用率，符合最左原則
  • 聯合索引，注意索引過濾度
  • 表join操作，注意加索引，且join欄位型別必須一致
• SQL優化
  • 子查詢變成連線操作，優先使用獨立子查詢
  • 利用覆蓋索引，分頁操作
  • 拆分SQL，加快取表或者彙總表
  • 外在條件下推，減少中間結果集
  • 重寫SQL，利用過濾性更好的索引

主從延遲

• 避免
  • 避免3B
  • 較少從庫壓力，包括伺服器和資料庫本身的
  • 從庫上有觸發操作，控制更新量
• 改善
  • 升級從庫機器硬體ssd
  • 升級資料庫版本，5.6的庫併發 5.7的binlog執行緒併發
  • 第三方工具，丁奇的transfer外掛
  • inset...select rbr模式，可以先select 快取結果，後insert

排查方法

• 伺服器
  • top
    • 檢視負載
    • 檢視CPU耗時
  • iostat
  • free
  • ps aux | grep pid
    • 檢視具體程式資訊
• MySQL
  • SHOW FULL PROCESSLIST / dbaslowing
    • 資料庫目前執行SQL，連線數
    • 慢查詢實時採集，格式話輸出
  • SHOW ENGINE INNODB STATUS
    • 執行事務資訊
    • 死鎖資訊 / pt-deadlock-logger 週期採集死鎖資訊
    • buffer pool 資訊檢視
  • dbaprocess.sh
    • 檢視連線數排序，顯示連線服務

緊急恢復

• 切換
  • 主從切換
    • 雙主模式，設定自增間隔，不停服
    • read_only 切換，保留靜態老主
• 連線數打滿
  • pt-kill
  • mysqladmin kill

日常指標

• zabbix
  • 歷史狀態查詢
  • 報警監控
  • 組合定製指令碼監控
• 運維指令碼
  • 慢查詢指令碼每日郵件
  • 資料庫周健康度郵件
  • 核心表每日備份
  • 擴容從庫指令碼
  • 搭建級聯指令碼
  • 測試庫同步指令碼
  • 試執行環境對比指令碼
• 備份
  • hotbak，dump
  • nfs掛載磁碟
  • hdfs資料冗餘

自動化運維

• 自動稽核inception
• 自動授權系統

MySQL HA

• 中介軟體

  • dbproxy
    • 讀寫分離
    • 從庫HA，負載均衡
  • mycat
    • 讀寫分離
    • 從庫負載均衡
    • 分庫分表&全域性表
    • HA
    • 不支援分散式事務
    • 業務SQL限制，需要有路由key
    • 效能有所損耗

• MHA

• PXC

常用PT

• pt-config 資料庫替換時，驗證引數一致性
• pt-deadlock-logger 記錄歷史死鎖資訊
• pt-duplicate-key-checker 冗餘索引檢測
• pt-ioprofile IO除錯
• pt-kill 定製kill連線執行緒
• pt-online-schema-change OSC
• pt-query-digest 狀態輸出慢查詢
• pt-table-checksum 主從一致性驗證
• pt-slave-find 主從拓撲結構
• pt-fifo-split 大檔案匯入