RDBMS 資料庫案例設計 (二) - 最佳化設計技巧

PHP 也有 Day 番外篇,最佳化關於 MySQL 的 Database 參數調校 …

開場以電子商務網站為例做討論


ToC

  1. Workload
  2. MySQLTuner 工具
  3. Tuning the query cache
  4. Connection Pool
  5. Thread Pool
  6. Buffer Pool
  7. 其他的 MySQL server-system-variables 設定經驗

圖片出自 Ant_ModernWeb 恰如其分的MySQL設計技巧 p.35

Workload

從 Workload 出發來思考,業務的

  • 資料完整性
    • 對於遺失,損壞的忍受度為何
  • 效能維度
    • 取決機器資源. e.g. CPU, Storage, Memory, Bandwidth

然而評估效能取捨時,通常考慮 latencythroughput 兩項指標

找尋 workload 的 Capacity:

隨著 Throughput, latency 兩者因素變動下的 Capacity 關係折線圖

在 connection 變高的時候

  • throughput 會降低
  • latency 會升高

找到兩者的交叉點。另外依照業務需求

  • e.g. 高頻交易為 0.3 ~ 0.5 sec 就要處理完交易
  • e.g. 傳輸的時候是否要進行壓縮,壓縮會耗損 CPU 資源
  • e.g. Bandwidth 是買不到的,這是瓶頸之處 > sometime 會用 CPU 效能換 Bandwidth

MySQLTuner 工具

https://github.com/major/MySQLTuner-perl

  • 數據蒐集之後告訴你可以怎麼調整
  • 但他不會告訴你是為了 throughputlatency,還是需要手動檢核

Tuning the query cache

  • 佔記憶體空間,又有查詢成本
  • Cache 最大的問題是 更新資料策略
  • Cache 資料的 讀跟寫 的比率為多少?
  • 對 Cache 最好的情境是 100% READ
    • 但要 100% READ 就用 Redis 就好啦!
    • So, query cache will no longer be supported in MySQL 8.0

Connection Pool

Connection pool demo flow
  • like PHP swool extension
  • 傾向 Application 的執行面
  • 用來控制 Connection 數量不要太高,過高一定影響 Server 效能
  • 建立連線的時候不用再走 3-steps Acks 跟 身份驗證層

Thread Pool

MySQL with thread pool enabled
  • 在資料庫本身層級的 Pool
  • MySQL 的 Enterprise Edition 才有 Thread Pool
  • MariaDB, Percona 有支援 Community Server ThreadPool
    • 如果要使用 Thread Pool 要記得設定 pool size

Buffer Pool

MySQL Buffer Pool demo flow
  • 把資料跟 index 都從 Disk 抓出來放在 Memory(Buffer Pool) 中
  • 通常參數不要設定到 100%,系統層級還有其他部分要操作 Memory
    • maybe: 50% ~ 70%
  • 設定太高 MySQL 會 crash 表示記憶體不夠用

其他的 MySQL server-system-variables 設定經驗

see more: RDBMS 資料庫案例設計 (一) - Schema 設計技巧