Linux叢集和自動化維1.5.2 利用tuning-primer腳本來調優MySQL資料庫

<b>1.5.2　利用tuning-primer腳本來調優mysql資料庫</b>

mysql線上上穩定運作一段時間後，就可以調用mysql調優腳本tuning-primer.sh來檢查參數的設定是否合理，該腳本的下載下傳位址為：

http://www.day32.com/mysql/tuning-primer.sh。

該腳本使用“show status like…”和“show variables like…”指令獲得mysql的相關變量和運作狀态。然後根據推薦的調優參數對目前的mysql資料庫進行測試。最後根據不同顔色的辨別來提醒使用者需要注意的各個參數設定。

目前版本會處理如下這些推薦的參數：

slow query log（慢查詢日志）

max connections（最大連接配接數）

worker threads（工作線程）

key buffer（key緩沖）

query cache（查詢緩存）

sort buffer（排序緩存）

joins（連接配接）

temp tables（臨時表）

table（open &amp; definition）cache（表緩存）

table locking（表鎖定）

table scans（read_buffer）（表掃描，讀緩沖）

innodb status（innodb狀态）

筆者之前所在公司的主營業務是cpa電子廣告平台，公司規模比較小，是以沒有配備專業的mysql dba，線上的mysql資料庫（四核cpu）伺服器問題比較多，用tuning-primer.sh腳本掃描後發現有如下問題：

mysql資料庫有時連接配接非常慢，嚴重時會被拖死。

通過show full processlist指令可以發現大量的“unauthenticated user”連接配接，資料庫肯定每次都要響應，是以速度越來越慢，解決方法其實很簡單：在mysql.cnf裡添加skip-name-resolve，即不啟用dns反向解析。

發生這種情況的原因其實也很簡單，mysql的認證明際上是user＋host的形式（也就是說user可以相同），是以mysql在處理新連接配接時會試着去解析用戶端連接配接的ip，啟用參數skip-name-resolve後mysql授權的時候就隻能使用純ip的形式了。

資料庫在繁忙期間負載很大，長期達到了13，遠遠超過了系統平均負載4，這個肯定是不正常的。

通過腳本掃描，發現沒有建立thread_cache_size，是以加上了thread_cache_size＝256，然後重新開機資料庫，資料庫的平均負載一下子降到了5～6。

發現資料庫裡有張new_cheat_id表，讀取很頻繁，而且長期處于sending data狀态。

懷疑是磁盤i/o壓力過大所緻，是以操作如下：

explain select

count(new_cheat_id)  from new_cheat where

account_id = '14348612' and offer_id = '689'\g;

顯示結果如下所示：

***************************

1. row ***************************

           id: 1

  select_type: simple

        table: new_cheat

         type: all

possible_keys:

null

          key: null

      key_len: null

          ref: null

         rows: 2529529

        extra: using where

1 row in set

(0.00 sec)

上面出現的這種問題很嚴重，new_cheat沒有建好索引，導緻每次都要全表掃描2 529 529行記錄，嚴重消耗了伺服器的i/o資源，是以立即建好索引，并用show

index指令檢視了表索引：

show index from

new_cheat;

指令顯示結果如下所示：

+-----------+------------+------------+--------------+--------------+-

| table     | non_unique | key_name   | seq_in_index | column_name  | collation | cardinality | sub_part | packed

| null | index_type | comment |

| new_cheat

|          0 | primary    |           

1 | new_cheat_id | a         |     2577704 |     null | null   |     

| btree      |         |

|          1 | ip         |            1 | ip           | a         |    

1288852 |     null | null   |     

|          1 | account_id |            1 | account_id   | a        

|     1288852 |     null | null   |     

3 rows in set

(0.01 sec)

再來檢視explain結果：

         type: ref

account_id

          key: account_id

      key_len: 4

          ref: const

         rows: 6

大家可以發現，加好了索引後，此sql通過account_id索引直接讀取了6條記錄（請對比關注rows這行）就獲得了查詢結果，系統負載由5～6直接降到了3.07～3.66了，這個負載還是能在可接受範圍之内的。

mysql的explain指令可用于sql語句的查詢執行計劃（qep）。這條指令的輸出結果能夠讓我們了解mysql 優化器是如何執行sql 語句的。這條指令并沒有提供任何調整建議，但它提供的重要資訊能夠幫助你做出調優決策。

最後要說明一點的是，對于網站來說，mysql單機優化對整體性能提升的作用畢竟有限，尤其是在mysql單機寫入方面，如果在工作中遇到了那種對mysql即時寫入和讀取速度要求很高的場景，建議大家可以多關注分布式的sql解決方案，例如hadoop的hbase和aws的redshift等分布式sql系統。