mysql日常營運與參數調優

標籤：

日常營運DBA營運工作日常

• 導資料，資料修改，表結構變更
• 加許可權，問題處理

其它

• 資料庫選型部署，設計，監控，備份，最佳化等

日常營運工作：

• 導資料及注意事項
• 資料修改及注意事項
• 表結構變更及注意事項
• 加許可權及注意事項
• 問題處理，如資料庫響應慢 

導資料及注意事項

1. 資料最終形式（csv,sql文本，還是直接匯入某庫中）
2. 導資料方法（mysqldump，select into outfile，）
3. 注意事項
   1. 匯出為csv格式需要file許可權，並且只能資料庫本地導
   2. 避免鎖庫鎖表（mysqldump使用--single-transaction選項不鎖表）
   3. 避免對業務造成影響，盡量在鏡像庫做

 資料修改及注意事項

1. 修改前切記做好備份
2. 開事務做，修改過完檢查好了再提交
3. 避免一次修改大量資料，可以分批修改
4. 避免業務高峰期做

表結構變更注意事項

1. 在低峰期做
2. 表結構變更是否會有鎖？（5.6包含online ddl 功能）
3. 使用pt-online-schema-change完成，表結構變更
   1. 可以避免主從延時
   2. 可以避免負載過高，可以限速

percona維護了mysql dba 必看的部落格 加許可權及注意事項

1. 只給符合需求的最低許可權
2. 避免授權時修改密碼
3. 避免給應用帳號super許可權

 問題處理（資料庫慢？）

1. 資料庫慢在哪裡？
   1. 是查詢慢還是寫入慢
   2. 是秒層級慢，還是毫秒層級慢
2. show processlist 查看mysql串連資訊
3. 查看系統狀態（iostat,top,vmstat）

 小結

1. 日常工作比較簡單，但是任何一個操作都可能影響線上服務
2. 結合不同環境，不同要求選擇最合適的方法處理
3. 日常工作應該求穩不求快，保障線上穩定是DBA的最大責任

 不求最快，但求最穩； 例子 1.修改t1表中id<5的資料

1)備份select * from t1 where id<5 into outfile ‘/tmp/t1id5.txt‘;2)執行修改begin;update t1 set b=100,c=100 where id<5;select * from t1 where id<5;rollback;/commit;

 

2.表結構變更

1)5.5版本：alter table t55 add c1 int;delete from t55 where id<100;(卡住一會後才執行)2)5.6版本：use db1;alter table t55 add c1 int;delete from t55 where id<100;(執行順暢)alter table t1 modify c1 varchar(90);delete from t55 where id<100;(卡住一會後才執行)3)pt-online-schema-change工具./pt-online-schema-change --user=root --password=123456 --host=localhost --socket=/mysqldata/node4/mysqld.sock D=db55,t=t55 --alter "add c2 int" --print --dry-run

 

3.加許可權

grant select,insert,delete on *.* to [email protected]‘localhost‘ identified by ‘163‘;#mysql -unetease -p163 --socet=/mysqldata/node3/mysqld.sock --port=4001(登陸成功)grant select,insert,delete on *.* to [email protected]‘localhost‘ identified by ‘123‘;#mysql -unetease -p163 --socet=/mysqldata/node3/mysqld.sock --port=4001(登陸失敗)

 

4.導資料

use db1;select count(*) from t1;(先看一下資料量)1)mysqldump匯出#mysqldump -uroot -p123456 --single-transaction --socket=/mysqldata/node3/mysqld.sock db1 t1 > /tmp/t1.sqlgrant select on *.* to [email protected]‘localhost‘ identified by ‘163‘;#mysqldump -unetease -p163 --socket=/mysqldata/node3/mysqld.sock db1 t1 > /tmp/t2.sql(報錯，沒有鎖表許可權)#mysqldump -unetease -p163 --single-transaction --socket=/mysqldata/node3/mysqld.sock db1 t1 > /tmp/t2.sql(成功)#mysqldump -uroot -p123456 --single-transaction --socket=/mysqldata/node3/mysqld.sock db1 t1 -T /tmpuse db1;2)以file許可權into outfile匯出資料select * from t1 into outfile ‘/tmp/t1_2.txt‘;select t1.c,t3,b from t1.id=t3.id into outfile ‘/tmp/t13.txt‘;

 

5.資料庫慢問題../tcpstat --port 4001 -t 1 -n 0（tcpstat,查看每一個tcp串連的回應時間，percona公司出品）   參數調優 為什麼要調整參數

• 不同伺服器之間的配置，效能不一樣
• 不同業務情境對資料的需求不一樣
• mysql的預設參數只是個參考值，並不適合所有的應用情境

 最佳化之前我們需要知道什麼

• 伺服器相關的配置
• 業務相關的情況
• mysql相關的配置

 伺服器相關的配置

• 硬體情況
• 作業系統版本
• CPU,網卡省電模式
• 伺服器numa設定---記憶體分區，cpu對應記憶體；
• RAID卡緩衝

 磁碟調度策略--write back

• 資料寫入cache即返回，資料非同步從cache刷入儲存介質

 磁碟調度策略--write through

• 資料同時寫入cache和儲存介質才返回寫入成功

      write back 效能高於 write through 而write through  的安全性更高。  RAID RAID --廉價的存放裝置陣列

 

  RAID0

• 簡單就是將多塊盤當做一塊盤來使用；容量是多盤的和，效能也是多盤之和；
• 問題，就是當其中一塊盤損壞後，無法保證其資料的安全性；

 RAID1

• 指兩塊盤做相互的鏡像--達到高可用
• 問題，只能使用兩塊盤來做，儲存空間 有限制

 RAID5

• 至少使用三塊盤，總儲存空間只有兩塊；因為它需要儲存校正資料區塊；
• 高可用的實現，是通過校正資料區塊，來恢複資料；
• 局限，只能壞一塊盤，才能通過另外兩塊盤的 儲存校正資料區塊，進行資料恢複，如果壞了兩塊盤則不能進行資料恢複

 RAID10

• 先對兩塊盤做RAID1，再做RAID0
• RAID1保證資料安全性，RAID0保證資料擴充性；
• 局限，做RAID1的兩塊盤同時壞了，則也不能保證資料安全性；

 RAID如何保證資料安全

• BBU(Backup  Battery Unit)
• • 保證在電池有電的情況下，即使伺服器發生掉電或者宕機，也能夠將緩衝中的資料寫入到磁碟，從而保證資料的安全

  注意事項 mysql有哪些注意事項

• mysql的部署安裝
• mysql的監控
• mysql參數調優

 部署mysql的要求

• 推薦的mysql版本：>=mysql5.5
• 推薦的mysql儲存引擎：innodb

  系統調優的依據：監控

• 即時監控mysql的SLOW log
• 即時監控資料服務器的負載情況
• 即時監控mysql內部狀態值

 網易內部監控的參數：

• binlog檔案大小（MB）
• BufferPool命中率（%）
• cpu利用率（%）
• 磁碟讀操作延時（ms/op）
• 磁碟讀取位元組數（KB/s）
• 磁碟讀取次數（次/秒）
• 佔用磁碟儲存空間（MB）
• 磁碟寫入操作延時（ms/op）
• 磁碟寫入位元組數（KB/S）
• 磁碟寫入次數（次/秒）
• 磁碟IO利用率（%）
• 佔用記憶體量（%）
• 記憶體使用量率（%）
• 一般事務提交操作（次/秒）
• 刪除操作（次/秒）
• 插入操作（次/秒）
• 查詢操作（次/秒）
• 更新操作（次/秒）
• 二階段事務提交操作（次/秒）

 通常關注哪些mysql  status

• com_select/update/delete/insert
• •  看資料庫的請求是否變多
• Bytes_received/Bytes_sent
• •  看 mysql總的輸送量
• Buffer Pool Hit Rate
• •  innodb記憶體的命中率決定了效能
• Threads_connected/Threads_created/Threads_running
• •   前兩個多的話， 可以判斷 應用是否使用串連池，或者串連池使用是否合理
  •   活躍串連很多，說明資料庫很忙，可能是被人惡意攻擊；

  為什麼要調整mysql的參數：

• 需要根據業務區動態調整這個通用的mysql資料庫，使其變成專用資料庫
• 有些參數，很可能是老版本做的，可能是為了限流和保護用的，但是隨著機器的效能提高這些參數，顯然是不合適的。

  讀取最佳化

• 合理利用索引對mysql查詢效能至關重用
•  適當的調整mysql參數也能提升查詢效能

 innodb_buffer_pool_size：緩衝池大小，innodb自己維護一塊記憶體地區完成新老資料的替換 innodb_thread_concurrency:innodb內部並發控制參數，設定為0代表不做控制如果並發請求較多，餐宿設定較小，後進來的請求將會排隊  寫最佳化

• 表結構設計上使用自增欄位作為表的主鍵
• 只對合適的欄位加索引，索引太多影響寫入效能
• 監控伺服器磁碟IO情況，如果寫延遲較大則需要擴容
• 選擇正確的mysql版本，合理設定參數

 哪些參數有助於提高寫入效能

• innodb_flush_log_at_trx_commit&&sync_binlog
• • 控制redo log 重新整理
  • 控制二進位日誌的重新整理
• innodb log file size 
• innodb_io_capacity
• innodb insert buffer 

 innodb_flush_log_at_trx_commit：0,1,2n = 0（高效，但不安全--無論伺服器宕機或者mysql宕機都會丟資料）每隔一秒，把交易記錄緩衝區的資料寫到記錄檔中，以及把記錄檔的資料重新整理到磁碟上n = 1 （低效，非常安全--都不會丟資料）每個事務提交時候，把交易記錄從緩衝區寫到記錄檔中，並且，重新整理記錄檔的資料到磁碟上，最佳化使用此模式保證資料安全性n = 2（高效，但不安全--伺服器宕機會丟資料）每個事務提交的時候，把交易記錄資料從緩衝區寫到記錄檔中，每隔一秒，重新整理一次記錄檔，但不一定重新整理到磁碟上，而是取決於作業系統的調度； sync_binlog

• 控制每次寫入binlog，是否都需要進行一次持久化

 如何保證事務安全

• innodb_flush_log_at_trx_commit&&sync_binlog 都設為1
• 事務要和binlog保證一致性---才不會導致主從不一致

 事務提交過程

 

 串列有哪些問題

• SAS盤每秒只能有150--200個Fsync
• 換算到資料每秒只能執行50--60個事務

 社區和官方的改進

 

  redo log 的作用在資料庫 崩潰後的資料恢複； redo log的問題

• 如果寫入頻繁導致redo log裡對應的最老的資料髒頁還沒有重新整理到磁碟，此時資料庫將卡住，強制重新整理髒頁到磁碟
• mysql預設設定檔才10M，非常容易寫滿，產生環境中應該提高redo log 的大小

 innodb_io_capacity

• innodb每次刷多少個髒頁，決定innodb儲存引擎的吞吐能力。
• 在SSD等高效能儲存介質下，應該提高該參數以提高資料庫的效能。

 insert buffer 

• 順序讀寫 VS 隨機讀寫
• 隨機請求效能遠小於順序請求

將儘可能多的隨機請求合并為順序請求才是提高資料庫效能的關鍵insert buffer 對二級索引，的增刪改，的操作緩衝到 insert buffer中，然後將這些隨機請求合并成順序請求； 小結：

• 伺服器配置要合理（核心版本，磁碟調度策略，RAID卡緩衝）
• 完善的監控系統，提前發現問題
• 資料庫版本要跟上，不要太新，也不要太老
• 資料效能最佳化：
• • 查詢最佳化：索引最佳化為主，參數最佳化為輔
  • 寫入最佳化：業務最佳化為主，參數最佳化為輔

總結

 

• 日常營運工作：
  • 導資料，
    • mysqldump，select into outfile,
    • 避免鎖庫鎖表，mysqldump --single-transaction;
  • 資料修改
    • 做好備份，
    • 開事務做，
    • 分批修改，
    • 避免高峰期
  • 表結構變更
    • 低峰做
    • 5.6後包含online ddl，
    • 使用pt-online-schema-change:避免主從延遲，限速；
  • 加許可權
    • 最低許可權，
    • 避免授權時修改密碼
  • 不求最快，但求最穩；
• 參數調優
  • RAID0，RAID1，RAID5，RADI10，
  • RAID如何保證資料安全：
    • BBU,伺服器掉電，使用電池電量將緩衝內容重新整理到磁碟
  • 有助於提高寫效能的參數：
    • innodb_flush_log_at_trx_commit 控制redo log 重新整理
    • sync_binlog　：控制二進位日誌的重新整理
    • innodb log file size:　 重做日誌迴圈寫，如果太小，當新的寫入來的時候，原記錄檔寫完且還沒有持久化到磁碟，這時候就要阻塞寫入；所以，增大交易記錄大小，可能提升寫效能；
    • innodb insert buffer：
      • 插入緩衝，將隨機讀寫，通過這個緩衝，合并成可能的順序讀寫，以提高寫效能。
      • 只對二級且非唯一索引生效；
    • innodb_io_capacity：
      • innodb每次重新整理多少個髒頁，決定innodb儲存引擎的吞吐能力
      • 在SSD，下應該提高該參數以提高資料庫效能；
  • 讀取最佳化：
    • innodb_buffer_pool_size:緩衝池大小
    • innodb_buffer_pool_size：並發控制； 

  

mysql日常營運與參數調優