Optimizing mysql server

2014年因為工作需求,恰巧有機會調整到兩個台灣百大網站級的伺服器(infra structure),環境主要是 php 網站可執行的條件,有 laravel 開發的,也有放 wordpress 的,曾經最高同時在線人數約有5000~6000水準。以下就針對最近調整一台放 wordpress 的機器心得作分享,主要 mysql server 部分。

調整機器 performance loading 可從三個地方著手

  • Web Server
  • DB
  • Memory Usage (是否充分利用 memory cache)

Web Server 部分可參考本站的PHP5-FPM with Nginx 效能調教 (1)10 Tips For Optimizing NGINX And PHP-Fpm For High Traffic Sites。BTW, 上次那篇文章講解時的環境是 AWS Solution,但這次的需求是 linode,為了省錢而沒有轉去 AWS 使用猛猛的 RDS,是自己架設的 mysql......因此研究了如何調整 mysql

#######機器是 CPU Core * 6, 8G RAM
修改 my.cnf (/etc/mysql/my.cnf)

max_connections 1000 # 一般是500~1000
thread_stack 256K # 192K for 32 bit systems and 256K for 64 bit systems. use 'uname -a' to find out
thread_concurrency = 12 # 數量設置為CPU核心數量的兩倍.
thread_cache_size 64 # 按照內存大小來設置, 1G=8, 2G=16, 3G=32, >3G=64
query_cache_limit = 256K # 允許進入查詢緩衝區的最小數據大小,默認值是1MB,可以修改的小一點以滿足更多查詢的需求。但是如果設置的過於小,則會導致很多新的小查詢結果,將原有的查詢結果交換出去,增加系統的不穩。
query_cache_size = 512M # 查詢緩衝,默認是0,所以必須打開以提高mysql性能

確認 mysql 優化是否有效

進去 mysql 後輸入「SHOW STATUS LIKE 'Qcache%';」

查看數據中的 Qcache_queries_in_cache 是否有增加

+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 2698 |
| Qcache_free_memory | 24065728 |
| Qcache_hits | 49367684 |
| Qcache_inserts | 10431164 |
| Qcache_lowmem_prunes | 825016 |
| Qcache_not_cached | 2826264 |
| Qcache_queries_in_cache | 27635 |
| Qcache_total_blocks | 59360 |
+-------------------------+----------+

基本上這些調教都是因為,隨著機器開的越大(因網站流量也大),可用資源就越多,相對我們也會希望 mysql server 使用更多,才會需要去做調整。

Reference