以 MySQL 為例的資料庫優化
MySQL Optimization
資料庫運行在作業系統之上,運算的處理能力由中央處理器的運算能力決定,資料的快取速度由記憶體的速度及大小決定,而最終資料是存放在磁碟的檔案中。
想進行資料庫優化,首先要了解最能有效支援你的資料庫的作業系統是什麼?而你又是用什麼作業系統在執行?
更甚,你應該要知道你的CPU每秒可以處理多少個指令集,存放在記憶體的快取每秒可以處理多少資料量,而每秒鐘硬碟的I/O可以都取多少資料量。
我們能以系統化的方式看待資料庫效能管理,分為下列四個步驟:
一、找出潛在問題(Identify)
對於影響服務水準(SLA)的資料庫效能衡量指標(KPI),要清楚地建立起來,以便挖掘潛在問題。
透過這些衡量指標,可以顯示系統的健康狀態,並且找出問題在哪裡,對 DBA 而言,他們能指出執行時間過久的 SQL 查詢、緩衝共用區與 I/O 狀態、記憶體耗用量與資料鎖定的衝突點;而對開發人員來說,他們關切的是在耗用過多時間的資料存取作業,分析應用程式的計算時間,以及等待資料庫回應的時間。
建立好指標後,必須有效地追蹤,以便在發生問題時,透過系統發送警告通知。
二、分析問題(Diagnose)
三、解決問題(Solve)
四、預防問題再度發生(Prevent)
持續進行資料庫效能監控及優化(Optimization),才能使企業所投入的 IT資產發揮最大效益,而非不斷編列硬體擴充預算,增加維運人力。
下列會以幾個部分來討論 MySQL 上的優化方式:
- 監看工具
- SQL 調校
- 初始化設定檔
- 緩衝區和高速緩存
- 資料一致性和性能之間的平衡
- 網路系統應用
- 整體考慮(系統 I/O、Memory)
監看工具
監看工具能幫助你了解資料庫的性能及潛在問題。
- MySQL Performance Reports
- MySQL Enterprise Monitor
- MySQL Plugin for Oracle Enterprise Monitor — Cacti
- Zabbix
- Nagios
- sys schema
sys schema 是一組 views、functions 和 procedures,目地在於簡化 Information Schema 和 Performance Schema 的使用。
https://github.com/mysql/mysql-sys
https://dev.mysql.com/doc/refman/5.7/en/sys-schema.html
但是,也要注意過度監看可能會為系統帶來額外的負荷,例如:
SHOW PROCESSLISTSHOW ENGINE INNODB STATUS
在 MySQL 5.6 之後,可用 performance_schema.thread
取代 SHOW PROCESSLIST
。
SQL 調校
SQL 慢查詢
知道如何紀錄系統中較慢的 SQL 語法,並對其做語法的改善。
- 可以使用執行計畫 (
EXPLAIN
) 來檢視查詢 - 慢查詢日誌
- MySQL Enterprise Monitor Query Analyzer
performance_schema.events_statements_summary_by_digest
sys.statements_with_runtimes_in_95th_percentile
SQL 語法與儲存引擎
是否具備全文檢索的查詢功能。
避免在 LIKE
子查詢中比對 *
號開頭的字串
因為,使用 *
,優化器則不會使用索引,而是掃表。如果確實有需求,應該使用全文檢索來提升效能。
索引數量
索引的目的在建立一個資料較小的索引樹,當應用程式在查詢資料時,可以先利用這顆樹取得資料定址,減少磁碟I/O,加速資料的存取。但是,使用者仍然需要避免使用過多的索引。
索引的數目越多,在新增或更新資料時,連帶更新的索引也會越多,導致資料寫入的時間變長,寫入鎖定(Write Lock)的時間也變長。
儘管你在實際場景中的應用程式,其讀取頻率遠大於寫入頻率。因索引太多所造成的寫入鎖定,也會連帶影響到該資料讀取的全部線程,整體效能會降低許多。
所以,適當的索引數目應該依照使用者的使用頻率、資料分佈的大小來決定索引的建立與否。一般來說鑑別度高的欄位才需要建立索引,且不要在重複的欄位建立索引。
分割資料表
MySQL 5.1 之後,我們可以使用 Partition 將同一個表格的資料,分成不同的小檔案儲存,分散在不同磁碟,增加存取速率。
初始化設定檔
設定檔路徑
datadir
: 指定資料預設要放在哪
非InnoDB表的資料和索引
InnoDB file_per_table
的表空間,各種日誌包含通用、系統、和重做日誌
innodb_data_home_dir
: innodb 預設系統表空間 (ibdata1) 的位置innodb_log_group_home_dir
: 指向 InnoDB 重做日誌檔的路徑innodb_undo_directory
: 指向 InnoDB 復原表空間的目錄log_bin
: 兩個功能,開啟二元日誌且設定路徑/檔的前綴log_error
: 確認您知道它放在哪
將設定檔放到不同的磁碟系統可減少衝突,例如將較為繁忙的檔放到比較快的磁碟。
使用 innodb_flush_log_at_trx_commit = 1
在大量交易提交時,可將 InnoDB 的重做日誌放到 SSD 以達到較高的 flush 速率。
避免 InnoDB 表空間成為一個單一大檔案,可在 my.cnf
中設定 innodb_file_per_table
,讓不同的資料表有獨立的空間。其表空間和通用表空間,可在建立時放到 datadir
之外。
開啟額外的監看
- 如果您用 InnoDB,可開啓所有的 INNODB_METRICS 計數器
全域變數 innodb_monitor_enable = ‘ALL’
它的負荷變得很小,所以值得爲瞭解詳情而開啓它
- 確認 Performance Schema 是開啓的
會有一點負荷,但能提供性能優化的有用訊息
考慮在必要時開啓更多的 consumers 和 instruments,在表 setup_consumers
、setup_instruments
The Performance Schema 也可以在開機時動態改變設定
Buffer Size
可參考 percona online tools,需用整個系統的角度來看待。
MyISAM
key_buffer_size=128M # default is 8M
bulk_insert_buffer_size=4194304 # default is 8M
InnoDB
innodb_buffer_pool_size=32M # default 8M
innodb_log_buffer_size=8M # default 1M
innodb_buffer_pool_size
「在一個專用資料庫的伺服器上,您可以將 innodb_buffer_pool_size
設定為主機記憶體的 80%。」
你應該考慮:
- 主機有多少記憶體?
- 刪減操作系統及其他程序所需的容量
- 刪減 MySQL 除了 InnoDB buffer pool 之外的部分
- 選擇以上的值和工作資料組中較小的值
InnoDB redo log
總重做日誌的大小決定於:
innodb_log_file_size
innodb_log_files_in_group
Total size = innodb_log_file_size * innodb_log_files_in_group
不同版本中,最大的總 redo log size 可達:
- MySQL 5.5 以前最大爲 4G
- MySQL 5.6 以後最大爲 512G
- 應該大到不會出現太多的 checkpointing
- 重做越大,關機所需要的時間可能越久
InnoDB redo log 夠大嗎?
- 在一個異步 flush 啓動時,使用率達 75%
非常集中的 I/O 使得其他的工作都停頓
主 InnoDB 可能處於狀態消息: flushing buffer pool pages
- 確認您有足够的空間來應付尖峰時期
例如目標放在最多用重做日志的 60% 或 70%
- 您所採用的監看方案對 redo log 使用狀况的監看能力很重要
緩衝區和高速緩存
- 開啟高速緩存區未必有好處
- 緩衝區和高速緩存也未必是越大越好
Query Cache
大部分的狀況 (以單一 mutex 保證),開啟不會比較好,預設為關閉:
query_cache_type=0
- 寫得越多效益越差
- 越多的資料落在 buffer pool 中,效果越差
- 越複雜的查詢,越大的掃表,效果越好
join_buffer_size
- 在 MySQL 5.5 之前,只用於簡單的索引掃瞄、區間索引掃瞄,和沒用到索引的 joins
沒有理由大於各對應的行之長度
- 在 MySQL 5.6+ 也用於 Batch Key Access (BKA)
用 BKA 的查詢可受惠於較大的 join buffer
- 一開始給最小
- 通常設一個小的全域值 (每個連結的預設值),最好爲 32k 至 256k
- 在當下對話需要時才增加其值
sore_buffer_size
- 和 join_buffer_size 一樣,一開始設個較小的全域值,最好為 32k 至 256k
- 在繁忙的伺服器上留意,使每秒的值儘量小
- 在必要時適度的增加
為何要給一個小的全域值?
- 有些緩存每當它一用到,就會占用所給定的值
- 一個查詢可能用到多個緩存,大的緩存可能會使記憶體不敷使用
- 相對記憶體的使用成本較高
- 在跨線程時,Linux glibc malloc 改變記憶體分派的運算法 (通常是 256k 或 512k).
對記憶體分派較大的運算法,可能比較小分派的演算法要慢40倍
資料的一致性和性能之間的平衡
- innodb_flush_log_at_trx_commit
- sync_binlog
sync_binlog = 0
可能帶來最好的性能,但是 sync_binlog = 1
卻能 帶來最可預測的性能。
網路系統應用
IP
inet_aton() -- 將字串轉換成整數inet_ntoa() -- 將整數轉換成字串
MAC
hex()
整體考慮
操作系統的 I/O
- I/O 排程器
有一些 Linux 預設使用 CFQ 排程器
- NOOP 和 deadline 通常對 MySQL 的負荷較佳
Deadline 是 Oracle Linux 預設的 I/O 排程器
- 查看現行的排程器
$ cat /sys/block/sda/queue/scheduler
- 動態更換排程器
$ echo deadline > /sys/block/sda/queue/scheduler
$ cat /sys/block/sda/queue/scheduler
- 在開機時設定,使用
elevator=deadline
的開機選項。
記憶體分派的程序庫
- Linux 所用的 glibc malloc 可能造成瓶頸
- 最好用 malloc 以外的 library
tcmalloc
jemalloc
- 設定
mysqld_safe
systemd distributions
總結
- 瞭解您的系統及資料
- 瞭解您的需求
- 不成熟的優化是有反效果的
- 一次只調一個,不要做太多、太大的更動
- 不要見樹不見林,要從大處著手
- 同一套設定不要套用到所有的狀况
- 根據測量的結果下决定
- 在變動一個選項前要瞭解它的作用是什麽
- 整體考慮
參考
- MySQL 與 MariaDB 學習手冊 (ISBN 978–086–476–383–2)
- Effective SQL 中文版 (ISBN 978–986–476–435–8)
性能優化
MySQL 性能優化六大心法 | Oracle Community