NEIL's_學而時習之x不學無術~私藏IT技術經驗分享部落

關於部落格
NEIL. Cheng....寫作只是為了日後工作之備忘操作參考

本部落格文章撰寫前進邁向第五年(since 2009 ~至今)仍繼續推進分享IT學習!!About Windows/Linux Server,Virtualization


分享網誌: 由於,本人預先完成LAB後,才補上撰寫文章與截圖,若發現文圖對照說明有誤..本人將盡快校正,也請不吝指教! Thanks!

  • 153344

    累積人氣

  • 44

    今日人氣

    0

    訂閱人氣

[動手調優資料庫] Tune MySQL 慣用效能調優參數紀實 by Neil

 
1.建立"索引"測試分析      #大量索引配置很佔磁碟空間!!

   chunju 資料庫底下 有張資料表結構如下: no (主鍵) name,address,tel

  測試全局索引: select * from  chunju1

  Type = ALL  全文索引 最差查詢狀況

  共 const、eq_reg、ref、range、indexhe、ALL 幾種 #const 整個查詢過程中這個表最多只會有一條匹配的行(最佳) 只需讀取一次



  explain select no,name from chun1 where no<=2;

  possible keys = Primary,no  , key= Primary   此次查詢有使用到Primary key 

  Extra :   最差狀態:
 

 Using filesort :
速度很慢的外部排序。
 
Using temporary:MySQL必須建立一個暫時的資料表(Table)來儲存結果


 Use filesort  案例: 

 explain select no,name from chun1 where no <=2 order by name;


替"name" 欄位加入索引並使用order by 做測試

explain select no,name from chun1 order by name;  

Extra:   Using index   使用索引排序


測試"組合索引"  test(name,no) 搜尋測試

explain  select  name,no from chun1 where name='GimG' and no <=3 order by name;

Extra:  Using where, Using index


   Result Output

 Explain

指令可協助DBA頗析SQL語法執行時效能問題瓶頸與修正,適當組合索引使用比全文索引(ALL)減低

檢索關鍵資料所花費時間


[慢查詢Slow Query啟用]:  

紀錄執行時間高過N秒至文字紀錄檔,並搭配mysqlsla 工具進行慢查詢分析

 vi /etc/my.cnf.d/server.cnf

long_query_time=2                              #執行超過兩秒查詢紀錄
log-slow-queries=/data/slow.log      #記錄到slow.log 檔案裡
 

[安裝mysqlsla 工具]


wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz

 tar zxvf mysqla-2.03.tar.gz

 cd mysqlsla-2.03

perl Makefile.PL 

make && make install;

mysqlsla -lt slow /data/slow.log    

#DBA即可分析獲得慢查詢統計數據,檢視>2秒不良查詢SQL結構進一步做修正改善


[慣用MySQL參數調校]: 請視資源使用率適當調配.我使用版本Mariadb 5.5.34

 vi /etc/my.cnf.d/server.cnf

back_log=256                    # 超過max_connections 暫緩請求儲存空間 Default: 50 調大256

wait_timeout=1800           #Default: 8 hour(28800) ,變更為30MIN(1800秒) ,降低閒置連線占用
                                                資源需搭配interactive_timeou 一起使用有效

interactive_timeout=1800
#服務器關閉交互式連接前等待活動的秒數配置與wait_timeout一致




max_connections=3000    
#MySQL 實際最大連線數Default :1024 ,本Lab調大3000連線數

max_user_connections=1024
 #mysql服務器的最大連接數 Default:0,現定義為1000


thread_concurrency=8        
#多核心CPU調度配置,調整總核心數2倍值( 本Lab 2x2=4core x2 =8)

innodb_buffer_pool_size=2048M #  
#有效緩存Innodb數據塊與索引鍵,減少I/0 耗損 Default:128MB

innodb_additional_mem_pool_size=40M

# 存儲的數據目錄信息和其它內部數據結構的內存池大小,Defaut: 8M = 此次調整為40M

 
innodb_log_buffer_size=20M               #日誌使用緩衝區大小: Default 8mb 增加為20M

innodb_flush_log_at_trx_commit=2  

#
0 可得最佳效能(資料遺失性最大) 折衷選擇配置:2 將低資料遺失性可能,每隔一秒更新一次,
   同時更新資料 與索引(先寫入到磁碟檔案內)

innodb_flush_method=O_DIRECT  
#檔案系統略過檔案系統緩衝區(強化I/O)效能,改採用本身緩衝區

query_cache_size=32M  

 #select將查詢結果暫存到內存Cache,減少同質查詢頻繁 , Default:32M(不考慮使用: 當數據瘋狂
   寫入.更新情境下時不建議使用Query_Cache)

query_cache_type=1        
#1:啟用Query Cache , 預設Default: 0 關

tmp_table_size=32M      
 #臨時表緩衝大小16M , 是需求調高到32M

thread_cache_size=64    
#線程緩存 > 3GB RAM內存建議值 = 64

 
檢視Innodb_buffer_pool 緩存命中率:

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests    * 100

(17584 - 1365) / 17584   * 100  =  92% ( 命中率: 9成命中率:佳)  

# Query Cache 暫不納入考慮: 認為不適合大量寫入更新情境下配置該值


Final: 展示壓力測試成果

調校完成後最後使用MySQL: mysqlslap 工具做併發數200,400 併發數及查詢2000次壓力測試

mysqlslap -a   --concurrency=200,400 --number-of-queries 2000 --iterations=8   --engine=myisam,innodb       --debug-info -uroot -p 

併發數增加到1000 , 2000 , mysqlslap 還有更多細部測試參數可使用,本篇在此介紹於此


另推薦一個觀測Mariadb 相關效能統計數據可供DBA 執行效能調優參考[mysqlreport]

 mysqlreport --user=root --password=xxxxx    uptime 時間期望每五日一個周期來觀測



SQL 各語法執行數據統計: 

  INSERT      183.86k    29.9/s           46.47         60.83
  SELECT      118.40k    19.2/s           29.92         39.17
  REPLACE           0       0/s            0.00          0.00
  DELETE            0       0/s            0.00          0.00
  UPDATE            0       0/s            0.00          0.00


  方才提到max_connections 與 temp (臨時表) 使用狀況統計數據做調優參考


  Threads 對應方才提到 thread_cache_size


InnoDB Buffer Pool 對應到方才提到 InnoDB Buffer Pool size 相關係數

從下方來看我們配置2GB 內存,實際上目前只用了1% 相當充裕
本篇實作/設計Lab時參考文獻:

MySQL性能优化之参数配置

巨型網站大師親自指導: 建立極速Web網站的秘密/郭欣 佳魁
 
 
相簿設定
標籤設定
相簿狀態