Mysql資料庫作業系統及配置參數最佳化

標籤：

• 資料庫結構最佳化

  表的水平分割
  常用的水平分割方法為：
  1.對 customer_id進行 hash運算，如果要拆分成5個表 則使用mod(customer_id,5)取出0-4個值
  2.針對不同的 hashID 把資料存到不同的表中。
  挑戰:
  1.跨分區表進行資料查詢
  2.統計及後台報表操作

• 作業系統配置最佳化

  資料庫是基於作業系統的，目前大多數MySQL都是安裝在Linux系統之上，所以對於作業系統的一些參數配置也會影響到MySQL的效能，下面就列出一些常到的系統配置。
  網給方面的配置， 要修改/etc/sysctl.conf檔案
  增加tcp支援的隊列數
  net.ipv4.tcp_max_syn_backlog = 65535
  減少中斷連線時 ，資源回收
  net.ipv4.tcp_max_tw_buckets = 8000
  net.ipv4.tcp_tw_reuse = 1
  net.ipv4.tcp_tw_recycle = 1
  net.ipv4.tcp_fin_timeout = 10

• 開啟檔案的限制
  可以便用ulimit -a目錄的芻各位限制，可以修改/etc/security/limits.conf檔案 增加以下內容以修改開啟檔案數量的限制
  *soft nofile 65535
  *hard nofile 65535
  除此之外最好在MySQL伺服器上關閉iptables,selinux 等防火牆軟體。

• MySQL設定檔

  Linux系統中MySQl設定檔一般位於/etc/my.cnf
  MySQL設定檔一常用參數說明
  innodb_buffer_pool_size
  非常重要的一個參數 ，用於配置Innodb的緩衝池,如果資料庫中只有Innodb表，
  則推薦配置量為總記憶體的75%（這個前提是這個伺服器只用做Mysql資料庫伺服器）.
  SELECT ENGINE,
  ROUND(SUM(data_length + index_length)/1024/1024,1) AS ‘Total MB",FROM INFORMATION_SCHEMA.TABLES WHERE table_schema not in
  ("information_schema", "performance schema")
  GROUP BY ENGINE;
  Innodb_buffer_pool_size>=Total MB
  innodb_buffer_pool_instances
  MySQL5.5中新增參數，可以控制緩衝池的個數，預設情況下只有一個緩衝池。
  innodb_log_buffer_size
  innodb log緩衝的大小，由於日誌最長每秒鐘就會重新整理所以一般不用太大。
  innodb_flush_log_at_trx_commit
  關鍵參數，對innodb的IO影響很大。預設值為1，可以取0,1,2三個值，一般建議設為2，但如果資料安全性要求比較高則使用預設值1.
  innodb_read_io_threads
  innodb_write_io_threads
  以上兩個參數決定了Innodb讀寫的IO進程數，預設為4.
  innodb_file_per_table
  關鍵參數控制Innodb每一個表使用獨立的資料表空間，預設是OFF，也就是所有表都會建立在共用資料表空間裡，建議為ON.
  innodb_stats_on_metadata
  決定了MySQL在什麼情況下會重新整理innodb表的統計資訊。
  max_connections
  很多開發人員都會遇見”MySQL: ERROR 1040: Too many connections”的異常情況，造成這種情況的一種原因是訪問量過高，MySQL伺服器抗不住，這個時候就要考慮增加從伺服器分散讀壓力；另一種原因就是MySQL設定檔中max_connections值過小。
  首先，我們來查看mysql的最大串連數：
  mysql> show variables like ‘%max_connections%‘;
  +-----------------+-------+
  | Variable_name | Value |
  +-----------------+-------+
  | max_connections | 151 |
  +-----------------+-------+
  1 row in set (0.00 sec)

其次，查看伺服器響應的最大串連數:
mysql> show global status like ‘Max_used_connections‘;
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 2 |
+----------------------+-------+
1 row in set (0.00 sec)

可以看到伺服器響應的最大串連數為2，遠遠低於mysql伺服器允許的最大串連數值。
對於mysql伺服器最大串連數值的設定範圍比較理想的是：伺服器響應的最大串連數值占伺服器上限串連數值的比例值在10%以上，如果在10%以下，說明mysql伺服器最大串連上限值設定過高。
Max_used_connections / max_connections 100% = 2/151 100% ≈ 1%

我們可以看到佔比遠低於10%（因為這是本地測試伺服器，結果值沒有太大的參考意義，大家可以根據實際情況設定串連數的上限值）。
再來看一下自己 linode VPS 現在（時間：2013-11-13 23：40：11）的結果值：
mysql> show variables like ‘%max_connections%‘;
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.19 sec)
mysql> show global status like ‘Max_used_connections‘;
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 44 |
+----------------------+-------+
1 row in set (0.17 sec)

這裡的最大串連數占上限串連數的30％左右。
上面我們知道怎麼查看mysql伺服器的最大串連數值，並且知道了如何判斷該值是否合理，下面我們就來介紹一下如何設定這個最大串連數值。
方法1：
mysql> set GLOBAL max_connections=256;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘%max_connections%‘;
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+
1 row in set (0.00 sec)

方法2：
修改mysql設定檔my.cnf，在[mysqld]段中添加或修改max_connections值：
max_connections=128
重啟mysql服務即可。

參考：
mysql最佳化串連數防止訪問量過高的方法
效能最佳化之MySQL最佳化

來自為知筆記(Wiz)

Mysql資料庫作業系統及配置參數最佳化