資料庫優化 - 實例優化
- 2019 年 10 月 25 日
- 筆記
從網上去搜資料庫優化基本都是從SQL層次進行優化的，很少有提及到資料庫本身的實例優化。就算有也都是基於某個特定資料庫的實例優化，本文涵蓋目前市面上所有主流資料庫的實例優化（Oralce、MySQL、POSTGRES、達夢）,按照文章的配置能夠將你資料庫性能用到80%或以上。
資料庫優化方法論
這部分為理論知識，不感興趣的同學可以直接跳到後面參數配置部分。
資料庫優化目標
目標
根據角色的不同，資料庫優化分為以下幾個目標：
- 業務角度（關鍵用戶）：
減少用戶頁面響應時間
- 資料庫角度（開發）：
減少資料庫SQL響應時間
- 資料庫伺服器角度（運維）：
充分使用資料庫伺服器物理資源
減少資料庫伺服器CPU使用率
減少資料庫伺服器IO使用率
減少資料庫伺服器記憶體使用率
指標
- SQL平均響應時間變短
- 優化前：資料庫平均響應時間500ms
- 優化目標：資料庫平均響應時間200ms
- 資料庫伺服器CPU佔用率變少
- 優化前：資料庫高峰期CPU使用率70%
- 優化目標：資料庫高峰期CPU使用率50%
- 資料庫伺服器IO使用率變低
- 優化前：資料庫IO WAIT為30%
- 優化目標：資料庫IO WAIT低於10%
資料庫優化誤區
在進行資料庫優化的時候可能會有以下幾個誤區：
- 優化之前一定要深入了解資料庫內部原理
優化是有「套路」的，照著這些「套路」你也可以很好的完成資料庫優化 - 不斷調整資料庫參數就可以最終實現優化
有時候設計不合理怎麼調整參數都不行 - 不斷調整作業系統參數就可以最終實現優化
同上 - 資料庫性能由應用、資料庫架構決定，與應用開發關係不大
恰恰相反，應用開發的關係很大 - 必須要做讀寫分離，必須要弄分庫分表
數據量級只有達到一定的比例才有必要做讀寫分離，分表分庫，否則徒增複雜度。一般來說Oracle的單表量級可以達到1億，MySQL到1000萬~2000萬
資料庫優化流程
完整的資料庫優化流程如下：
首先需要儘可能的了解優化問題，收集問題期間系統資訊並做好存檔。根據當前系統問題表現制定優化目標並與客戶溝通目標達成一致；通過一系列工具分析系統問題，制定優化方案，方案評審完成後由各負責人員進行實施。若達到優化目標則編寫優化報告，否則需要重新制定優化方案。
資料庫實例優化
資料庫實例優化遵循三句口訣：日誌不能小、快取足夠大、連接要夠用。
資料庫事務提交後需要將事務對數據頁的修改刷（ fsync）到磁碟上，才能保證數據的持久性。這個刷盤，是一個隨機寫，性能較低，如果每次事務提交都要刷盤，會極大影響資料庫的性能。資料庫在架構設計中都會採用如下兩個優化手法：
- 先將事務寫到日誌文件RedoLog（WAL），將隨機寫優化成順序寫
- 加一層快取結構Buffer，將每次寫優化成順序寫
所以日誌跟快取對資料庫實例尤其重要。而連接如果不夠用，資料庫會直接拋出異常，系統無法訪問。
資料庫參數優化
主流資料庫架構都有如下的共同點：
- 數據快取
- SQL解析區
- 排序記憶體
- REDO及UNDO
- 鎖、LATCH、MUTEX
- 監聽及連接
- 文件讀寫性能
接下來我們根據不同的資料庫調整參數以使資料庫達到最佳性能。
ORACLE
| 參數分類 | 參數名 | 參數值 | 備註 |
|---|---|---|---|
| 數據快取 | SGA_TAGET、MEMORY_TARGET | 物理記憶體70-80% | 越大越好 |
| 數據快取 | DB_CACHE_SIZE | 物理記憶體70-80% | 越大越好 |
| SQL解析 | SHARED_POOL_SIZE | 4-16G | 不建議設置過大 |
| 監聽及連接 | PROCESSES、SESSIONS、OPEN_CURSORS | 根據業務需求設置 | 一般為業務預估連接數的120% |
| 其他 | SESSION_CACHED_CURSORS | 大於200 | 軟軟解析 |
MYSQL（INNODB）
| 參數分類 | 參數名 | 參數值 | 備註 |
|---|---|---|---|
| 數據快取 | INNODB_BUFFER_POOL_SIZE | 物理記憶體50-80% | 一般來說越大性能越好 |
| 日誌相關 | Innodb_log_buffer_size | 16-32M | 根據運行情況調整 |
| 日誌相關 | sync_binlog | 1、100、0 | 1安全性最好 |
| 監聽及連接 | max_connections | 根據業務情況調整 | 可以預留一部分值 |
| 文件讀寫性能 | innodb_flush_log_at_trx_commit | 2 | 安全和性能的折中考慮 |
| 其他 | wait_timeout，interactive_timeout | 28800 | 避免應用連接定時中斷 |
POSTGRES
| 參數分類 | 參數名 | 參數值 | 備註 |
|---|---|---|---|
| 數據快取 | SHARED_BUFFERS | 物理記憶體10-25% | |
| 數據快取 | CACHE_BUFFER_SIZE | 物理記憶體50-60% | |
| 日誌相關 | wal_buffer | 8-64M | 不建議設置過大過小 |
| 監聽及連接 | max_connections | 根據業務情況調整 | 一般為業務預估連接數的120% |
| 其他 | maintenance_work_mem | 512M或更大 | |
| 其他 | work_mem | 8-16M | 原始配置1M過小 |
| 其他 | checkpoint_segments | 32或者更大 |
達夢資料庫
| 參數分類 | 參數名 | 參數值 | 備註 |
|---|---|---|---|
| 數據快取 | MEMROY_TARGET、MEMROY_POOL | 物理記憶體90% | |
| 數據快取 | BUFFER | 物理記憶體60% | 數據快取 |
| 數據快取 | MAX_BUFFER | 物理記憶體70% | 最大數據快取 |
| 監聽及連接 | max_sessions | 根據業務需求設置 | 一般為業務預估連接數的120% |
總結
資料庫的優化手法太多太多，有換磁碟陣列升級硬體，有改寫SQL腳本添加索引，還有資料庫參數調整優化性能，甚至還可以調整資料庫架構。本文從資料庫本身參數進行調優，大家根據上面幾張表中的參數進行調整基本能達到資料庫最佳性能的80%。
請關注個人公眾號:JAVA日知錄
