Mysql參數優化的詳細步驟

本篇文章給大家主要講的是關于Mysql參數優化的詳細步驟的內容，感興趣的話就一起來看看這篇文章吧，相信看完Mysql參數優化的詳細步驟對大家多少有點參考價值吧。

連接優化

back_log=500 （默認50）

MySql的連接達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過back_log，將不被授予連接資源。將會報：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進程時.back_log值不能超過TCP/IP連接的偵聽隊列的大小。若超過則無效，查看當前系統的TCP/IP連接的偵聽隊列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系統為1024。對于Linux系統推薦設置為小于512的整數。

wait_timeout=1800 （默認8小時，單位秒）

MySQL客戶端的數據庫連接閑置最大時間值；有大量sleep長時間占用內存和連接數，則需要修改此項。

max_connections=3000 （默認151）

MySql的最大連接數，如果云服務器的并發連接請求量比較大，建議調高此值，連接數越多占用內存越多

max_connect_errors = 10000000

盡量設大，防止錯誤鏈接數過多導致無法寫入數據

CPU優化

 thread_concurrency=16 （默認8）

正確設置可以充分利用多核CPU，thread_concurrency應設為CPU核數的2倍

網絡優化

skip-name-resolve （默認被注釋）

禁止MySQL對外部連接進行DNS解析，使用這一選項可以避免MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程主機連接授權都要使用IP地址方式，否則MySQL將無法正常處理連接請求！

IO優化

 key_buffer_size=400M （MyISAM）

用于索引塊的緩沖區大小，對MyISAM影響較大的參數。key_reads / key_read_requests在0.1之下比較好

key_cache_miss_rate = Key_reads / Key_read_requests * 100%
show status like 'key_read%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 28535 |
| Key_reads         | 269   |
+-------------------+-------+
以上key_cache_miss_rate大于0.1%，則需要增大參數。key_cache_miss_rate在0.1%以下都很好(每1000個請求有一個直接讀硬盤)，如果key_cache_miss_rate在0.01%以下的話，key_buffer_size分配的過多，可以適當減少。
mysql> show global status like 'key_blocks_u%';
+---------------------------+
| Variable_name     | Value |
+---------------------------+
| Key_blocks_unused | 0     |
| Key_blocks_used   | 413543|
+---------------------------+
Key_blocks_unused表示未使用的緩存簇(blocks)數，Key_blocks_used表示曾經用到的最大的blocks數，比如這臺服務器，所有的緩存都用到了，要么增加key_buffer_size，要么就是過渡索引了，把緩存占滿了。比較理想的設置：
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

innodb_buffer_pool_size = 2048M  （默認128M，InnoDB）

用于緩存數據塊和索引鍵。對InnoDB表性能影響最大的一個參數，增加這個參數的大小，可以有效的減少 InnoDB 類型的表的磁盤 I/O 。

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| Innodb_buffer_pool_pages_data    | 129037 |
| Innodb_buffer_pool_pages_dirty   | 362    |
| Innodb_buffer_pool_pages_flushed | 9998   |
| Innodb_buffer_pool_pages_free    | 0      |  !!!!!!!!
| Innodb_buffer_pool_pages_misc    | 2035   |
| Innodb_buffer_pool_pages_total   | 131072 |
+----------------------------------+--------+
6 rows in set (0.00 sec)

 Innodb_buffer_pool_pages_free 為 0，則說明 buffer pool 已經被用光，需要增大innodb_buffer_pool_size
 建議使用 所有內存的 75% （在剩余內存能夠保證系統和其它服務正常運行的情況下）

innodb_additional_mem_pool_size=100M （默認8M）

設置了InnoDB存儲引擎用來存放數據字典信息以及一些內部數據結構的內存空間大小，所以當我們一個MySQL Instance中的數據庫對象非常多的時候，是需要適當調整該參數的大小以確保所有數據都能存放在內存中提高訪問效率的。如果設置太小日志會出現warning

2G內存推薦20M，32G內存推薦100M

innodb_log_buffer_size=20M （默認8M）

InnoDB存儲引擎的事務日志所使用的緩沖區。類似于Binlog Buffer，InnoDB在寫事務日志的時候，為了提高性能，也是先將信息寫入Innofb Log Buffer中。理想8M，不建議超過32MB，將此緩存段增大可以減少數據庫寫數據文件次數。

innodb_flush_log_trx_commit=0 （默認1）

0  log buffer每秒就會被刷寫日志文件到磁盤，提交事務的時候不做任何操作
1  在每次事務提交的時候將log buffer 中的數據都會寫入到log file，同時也會觸發文件系統到磁盤的同步;

2  事務提交會觸發log buffer到log file的刷新，但并不會觸發磁盤文件系統到磁盤的同步。此外，每秒會有一次文件系統到磁盤同步操作。

設為1當然是最安全的，但性能頁是最差的（相對其他兩個參數而言，但不是不能接受）。如果對數據一致性和完整性要求不高，完全可以設為2,如果只最求性能，例如高并發寫的日志云服務器，設為0來獲得更高性能

innodb_log_file_size = 128M （默認8M）

此配置項作用設定innodb 數據庫引擎UNDO日志的大小；從而減少數據庫checkpoint操作。 

query_cache_size = 128M （默認32M）

緩存MySQL中的ResultSet，也就是一條SQL語句執行的結果集，所以僅僅只能針對select語句

show status like 'Qcache_%';
| Qcache_hits             | 1892463  |
| Qcache_inserts          | 35627    |
命中率98.17%=1892463/(1892463 +35627 )*100

thread_cache_size = 128 （默認8）

,當斷開連接時如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請求，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那么這個線程將被重新創建,

 mysql> show status like 'thread%';
+——————-+——-+
| Variable_name     | Value |
+——————-+——-+
| Threads_cached    | 0     |  <—當前被緩存的空閑線程的數量
| Threads_connected | 1     |  <—正在使用（處于連接狀態）的線程
| Threads_created   | 1498  |  <—服務啟動以來，創建了多少個線程
| Threads_running   | 1     |  <—正在忙的線程（正在查詢數據，傳輸數據等等操作）
+——————-+——-+
查看開機起來數據庫被連接了多少次？
mysql> show status like '%connection%';
+———————-+——-+
| Variable_name        | Value |
+———————-+——-+
| Connections          | 1504  |          –>服務啟動以來，歷史連接數
| Max_used_connections | 2     |
+———————-+——-+
通過連接線程池的命中率來判斷設置值是否合適？命中率超過90%以上,設定合理。
 (Connections -  Threads_created) / Connections * 100 %
 物理內存 1G  —> 8；2G  —> 16； 3G  —> 32； >3G  —> 64

 innodb_autoextend_increment=128 （默認8M）

此配置項作用主要是當tablespace 空間已經滿了后，需要MySQL系統需要自動擴展多少空間，每次tablespace 擴展都會讓各個SQL 處于等待狀態。增加自動擴展Size可以減少tablespace自動擴展次數

表結構優化

innodb_file_per_table = on (5.6之前默認off)

這項設置告知InnoDB是否需要將所有表的數據和索引存放在共享表空間里（innodb_file_per_table = OFF） 或者為每張表的數據單獨放在一個.ibd文件（innodb_file_per_table = ON）。每張表一個文件允許你在drop、truncate或者rebuild表時回收磁盤空間。這對于一些高級特性也是有必要的，比如數據壓縮。但是它不會帶來任何性能收益。你不想讓每張表一個文件的主要場景是：有非常多的表（比如10k+）。

MySQL 5.6中，這個屬性默認值是ON，因此大部分情況下你什么都不需要做。對于之前的版本你必需在加載數據之前將這個屬性設置為ON，因為它只對新創建的表有影響。

以上關于Mysql參數優化的詳細步驟詳細內容，對大家有幫助嗎？如果想要了解更多相關，可以繼續關注我們的行業資訊板塊。