Mysql-服務端-擴展配置

Mysql-服務端-擴展配置

擴展配置
max_connections
允許最大連接數，默認100，最大16384。這個根據性能調節，如果3000連接就會導致mysql的資源不夠，那就給3000.因為再給多了，就會導致其它連接的資源被搶占。

建議：
根據需求來看，一般2核4G機器填寫1000，16核64G填寫5000。
測試運行后，查詢當前所有連接數和服務器負載情況。若連接數滿了，但負載還不是很大，可以加大連接數。

查詢配置：statusThreads就是連接數
在線配置：
配置文件參數：max_connections=5000

connect_timeout
建立三次握手的超時時間，可能是客戶端和服務端網絡問題導致的鏈接超時，單位秒。

查詢配置：
在線配置：
配置文件參數：connect_timeout=10

interactive_timeout|wait_timeout
控制連接最大空閑時長的參數。默認28800，也就是8小時，單位秒。

wait_timeout控制非交互，比如java程序的鏈接，interactive_timeout控制交互，比如mysql命令進行的操作。

建議：
通常情況下300秒就足夠了，這樣防止有些鏈接假死，不做操作單還占用鏈接。

查詢：show global variables like '%timeout%';
在線配置：set global wait_timeout=300; | set global interactive_timeout=300;
配置文件：interactive_timeout = 300 | wait_timeout = 300

net_retry_count
如果讀或寫一個通信端口中斷，mysql放棄前嘗試連接的次數。在FreeBSD系統中此值應設置很高，因為FreeBSD內部中斷被發送到所有線程去。

查詢配置：
在線配置：
配置文件參數：net_retry_count = 100

thread_concurrency
這個變量是針對Solaris系統的，設定為內核數的2倍。

如果設置這個變量的話，mysqld就會調用thr_setconcurrency()。這個函數使應用程序給同一時間運行的線程系統提供期望的線程數目。

查詢配置：
在線配置：
配置文件參數：thread_concurrency = 8

thread_cache_size|thread_stack
每一個客戶端連接都會有一個與之對應的連接線程。在MySQL中實現了一個Thread Cache池，將空閑的連接線程存放其中，而不是完成請求后就銷毀。

這樣，當有新的連接請求時，MySQL首先會檢查Thread Cache中是否存在空閑連接線程，如果存在則取出來直接使用，如果沒有空閑連接線程，才創建新的連接線程。3G內存設置64個比較好

每個連接線程被創建時，MySQL給它分配的內存大小。當MySQL創建一個新的連接線程時，需要給它分配一定大小的內存堆棧空間，以便存放客戶端的請求的Query及自身的各種狀態和處理信息。thread_stack控制這個值。16G/32G機器設定512K，太小會有 Thread stack overrun 錯誤。

可以用sql語句show global status like 'Thread%';來查看參數

+—————————-+———-+
| Variable_name | Value |
+—————————-+———-+
| Threads_cached | 1 |
| Threads_connected | 1 |
| Threads_created | 2 |
| Threads_running | 1 |
+—————————-+———-+

Threads_cached，如果太大，證明一直在創建新的線程，可以將thread_cache_size調大。

查詢配置：show variables like 'thread_%';
在線配置：
配置文件參數：thread_cache_size = 64 | thread_stack = 1M

open_files_limit
mysql可以打開的最大文件數，不能超過 ulimt -n 看到的數值

查詢配置：
在線配置：
配置文件參數：open_files_limit = 65535

max_connect_errors
實驗參考

當客戶端連接延遲超過connect_timeout定義的時間時，將會在performance_schema數據庫下host_cache表中進行記錄。

可以用use performance_schema;select * from host_cache\G;來查看SUM_CONNECT_ERRORS字段將會增加。

當超過的次數等于max_connect_errors定義的次數時，將會報錯如下：
ERROR 1129 (HY000): Host ‘10.10.10.101’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

盡量去改善網絡環境，或者將max_connect_errors值調大

查詢配置：show variables like '%max_connect_error%';
在線配置：set global max_connect_errors=3000;
配置文件參數：max_connect_errors = 3000

back_log
在MySQL的鏈接數達到max_connections時，當前無法處理新的請求，將存放到堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過back_log，將不被授予連接資源。

back_log值不能超過TCP/IP連接的偵聽隊列的大小。若超過則無效，查看當前系統的TCP/IP連接的偵聽隊列的大小命令
cat /proc/sys/net/ipv4/tcp_max_syn_backlog

建議：
推薦設置為350

查詢：show variables like 'back_log';
在線配置：
配置文件：back_log= 350

max_allowed_packet
mysql根據配置文件會限制server接收的數據包大小。
有時候大的插入和更新會被max_allowed_packet 參數限制掉，導致失敗。

建議：
大部分情況下4M就足夠了，如果還是不夠慢慢加。

查詢：show VARIABLES like '%max_allowed_packet%';
在線配置：set global max_allowed_packet = 410241024*
配置文件：max_allowed_packet = 4M

ft_min_word_len
開啟全文索引，默認關閉。根據需求開啟，如果沒使用全文索引，就不要開啟。

查詢：
在線配置：
配置文件：ft_min_word_len = 1

auto_increment_increment|auto_increment_offset
這兩個參數一般用在主主同步中，用來錯開自增值, 防止鍵值沖突

查詢：show variables like 'auto_inc%';
在線配置：
配置文件：auto_increment_increment = 1 | auto_increment_offset = 1

log_bin_trust_function_creators
如果開啟了主從復制，要設置為0，禁止用戶創建函數，觸發器。因為存儲函數有可能導致主從的數據不一致。

如果只開啟Binlog，沒主從，則設置為1。

查詢：
在線配置：
配置文件：log_bin_trust_function_creators = 1

read_buffer_size
MySQL讀入緩沖區大小。對表進行順序掃描的請求將分配一個讀入緩沖區，MySQL會為它分配一段內存緩沖區。read_buffer_size變量控制這一緩沖區的大小。

如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及內存緩沖區大小提高其性能

建議：
8G機器可以設置此參數為1M

查詢：
在線配置：
配置文件：read_buffer_sizes = 4M

performance_schema
5.5版本以后默認打開，用于收集性能參數，在實例中也會有對應名稱的一個庫。

查詢：
在線配置：
配置文件：performance_schema = 1

skip-locking|skip-external-locking
避免MySQL的外部鎖定，減少出錯幾率增強穩定性。
5以前版本skip-locking，新版本skip-external-locking

當外部鎖定（external-locking）起作用時，每個進程若要訪問數據表，則必須等待之前的進程完成操作并解除鎖定。由于服務器訪問數據表時經常需要等待解鎖，因此在單服務器環境下external locking會讓MySQL性能下降。

查詢：
在線配置：
配置文件：skip-locking | skip-external-locking

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

查詢：
在線配置：
配置文件：skip-name-resolve

table_cache
它的作用就是緩存表文件描述符，降低打開關閉表的頻率

mysql只有一個全局鎖來控制打開和關閉表，也就是說無論有多少個線程在并行執行，只有一個線程可以打開或關閉表，這也就會出現很多死鎖，別的線程等待那個全局鎖

相應地增加了cpu的消耗，延長了其他鏈接線程執行sql的時間，降低系統性能。所以在保證table_cache夠用的情況下，盡量保持table_cache足夠小

查詢：
在線配置：
配置文件：table_cache = 128K

init_connect
init_connect是用戶登錄到數據庫上之后，默認執行里面的內容，類似Linux系統的/etc/profile。在用戶操作前，可以先進行設定字符集，或者初始化一些東西。

但內容里面語法有問題，會導致用戶從mysql退出。init_connect 對具有super 權限的用戶是無效的。

查詢：
在線配置： set global init_connect=set autocommit=0; set names gbk;'
配置文件：init_connect='set autocommit=0; set names gbk;'

explicit_defaults_for_timestamp
參考實驗

明確時間戳默認null方式。如果高于5.5.6版本，創建如下

create table mytime (
id int,
atime timestamp not null,
ctime timestamp not null
);
出現如下錯誤，將變量改為true即可
ERROR 1067 (42000): Invalid default value for ‘ctime’

=false時，按照如下規則”初始化”:
未明確聲明為NULL屬性的TIMESTAMP列被分配為NOT NULL屬性。 （其他數據類型的列，如果未顯式聲明為NOT NULL，則允許NULL值。）將此列設置為NULL將其設置為當前時間戳。

表中的第一個TIMESTAMP列（如果未聲明為NULL屬性或顯式DEFAULT或ON UPDATE子句）將自動分配DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP屬性。

第一個之后的TIMESTAMP列（如果未聲明為NULL屬性或顯式DEFAULT子句）將自動分配DEFAULT’0000-00-00 00:00:00’（“零”時間戳）。 對于不指定此列的顯式值的插入行，該列將分配“0000-00-00 00:00:00”，并且不會發生警告。

=true時，按照如下規則”初始化”:
未明確聲明為NOT NULL的TIMESTAMP列允許NULL值。 將此列設置為NULL將其設置為NULL，而不是當前時間戳。

沒有TIMESTAMP列自動分配DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP屬性。 必須明確指定這些屬性。

聲明為NOT NULL且沒有顯式DEFAULT子句的TIMESTAMP列被視為沒有默認值。 對于不為此列指定顯式值的插入行，結果取決于SQL模式。 如果啟用了嚴格的SQL模式，則會發生錯誤。 如果未啟用嚴格的SQL模式，則會為列分配隱式默認值“0000-00-00 00:00:00”，并發出警告。 這類似于MySQL如何處理其他時間類型，如DATETIME。

查詢：
在線配置：
配置文件：explicit_defaults_for_timestamp=false

transaction-isolation
修改事務隔離級別

可選參數有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE。默認REPEATABLE-READ

查詢：
在線配置：set global transaction isolation level read uncommitted;
配置文件：transaction-isolation = REPEATABLE-READ

key_buffer_size
指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。

用show global status like 'key_read%';命令獲得的key_reads/key_read_requests，比例至少是1:100，1:1000更好。如果比例太小，可以調大key_buffer_size值。

key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁盤表是MyISAM表，也要使用該值。

對于1G內存的機器，如果不使用MyISAM表，推薦值是16M（8-64M）
對于64內存的機器，推薦256M。

查詢：SHOW VARIABLES LIKE '%key_buffer_size%';
在線配置：
配置文件：key_buffer_size = 16M

table_open_cache
指定表高速緩存的大小。每當MySQL訪問一個表時，如果在表緩沖區中還有空間，該表就被打開并放入其中，這樣可以更快地訪問表內容。

可以用sql語句SHOW variables LIKE '%table_open_cache%';獲得table_open_cache參數，這是緩存的表。

用SHOW GLOBAL STATUS LIKE 'Open%tables';獲得open_tables參數。這是打開的表。

如果open_tables等于table_open_cache，并且opened_tables在不斷增長，那么你就需要增加table_open_cache的值了。因為mysql正在將緩存的表釋放以容納新的表。

建議：
Open_tables / Opened_tables >= 0.85
Open_tables / table_open_cache <= 0.95

64G內存8千到2萬，2G內存512

查詢：SHOW variables LIKE '%table_open_cache%';
在線配置：
配置文件：table_open_cache = 8000

sort_buffer_size
系統中對數據進行排序的時候用到的Buffer。是針對單個線程的，所以當多個線程同時進行排序的時候，系統中就會出現多個sort buffer。默認256K

我們一般可以通過增大sort buffer的大小來提高order by或者group by的處理性能。Sort_Buffer_Size 并不是越大越好，由于是connection級的參數，過大的設置+高并發會耗盡系統內存資源。

查詢：show variables like '%sort_buffer_size%';
在線配置：
配置文件：sort_buffer_size = 8M

join_buffer_size
當使用join命令時，為了減少參與join的“被驅動表”的讀取次數以提高性能，需要使用到join buffer來協助完成join操作

當join buffer 太小，MySQL不會將該buffer存入磁盤文件而是先將join buffer中的結果與需求join的表進行操作，然后清空join buffer中的數據，繼續將剩余的結果集寫入次buffer中，如此往復，這勢必會造成被驅動表需要被多次讀取，成倍增加IO訪問，降低效率

查詢：
在線配置：
配置文件：join_buffer_size = 8M

read_rnd_buffer_size
這個變量用于讀取MyISAM表,對于任何存儲引擎用于Multi-Range Read optimization.

當讀取行從一個MyISAM 表按排序順序跟著一個key-sorting operation,記錄從這個buffer讀取,避免磁盤尋址See Section 8.2.1.15, “ORDER BY Optimization”.

如果你有很多order by 查詢語句，增長這值能夠提升性能。這個是一個buffer 分配給每個客戶端,因此你不能設置全局變量為一個大的值。相反,只改變session 變量對那些客戶端需要運行大的查詢。

查詢：
在線配置：SET GLOBAL read_rnd_buffer_size = 810241024;
配置文件：read_rnd_buffer_size = 8M

myisam_sort_buffer_size
當對MyISAM表執行repair table或創建索引時，用以緩存排序索引

設置太小時可能會遇到” myisam_sort_buffer_size is too small”

查詢：
在線配置：
配置文件：myisam_sort_buffer_size = 64M

query_cache_size|query_cache_type
參考

MySQL查詢緩存保存查詢返回的完整結果。當查詢命中該緩存，會立刻返回結果，跳過了解析，優化和執行階段。

query_cache_size用于設置查詢緩存的內存大小。如果寫多讀少的高并發情況下，就會頻繁變更緩存。

查詢緩存會跟蹤查詢中涉及的每個表，如果這寫表發生變化，那么和這個表相關的所有緩存都將失效。

query_cache_type決定是否緩存查詢結果。這個變量有三個取值：0,1,2，0時表示關閉，1時表示打開，2表示只要select 中明確指定SQL_CACHE才緩存

查詢：show variables like ‘thread_cache_size’;
在線配置：
配置文件：query_cache_size = 64M | query_cache_type = 0

innodb-file-per-table
MySQL InnoDB引擎 默認會將所有的數據庫InnoDB引擎的表數據存儲在一個共享空間中：ibdata1，當增刪數據庫的時候，ibdata1文件不會自動收縮，單個數據庫的備份也將成為問題。通常只能將數據使用mysqldump 導出，然后再導入解決這個問題。

如果啟用了innodb_file_per_talbe參數，需要注意的是每張表的表空間內存放的只是數據、索引和插入緩沖Bitmap頁，其他數據如：回滾信息、插入緩沖索引頁、系統事物信息、二次寫緩沖（Double write buffer）等還是放在原來的共享表空間內。同時說明了一個問題：即使啟用了innodb_file_per_table參數共享表空間還是會不斷的增加其大小的。

獨立表空間優缺點：
優點：
1：每個表的數據、索引存放在自己單獨的表空間中。
2：空間可以回收（drop/truncate table 方式操作表空間不能自動回收）
3：對于獨立的表空間、碎片影響的性能要低于共享表空間
缺點：
單表增加比共享表空間方式更大

結論：
共享表空間在Insert操作上有一些優勢，但在其它都沒獨立表空間表現好。
當啟用獨立表空間時，請合理調整一下 innodb_open_files 參數。

tmp_table_size|max_heap_table_size
它規定了內部內存臨時表的最大值，每個線程都要分配。（實際起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果內存臨時表超出了限制，MySQL就會自動地把它轉化為基于磁盤的MyISAM表，存儲在指定的tmpdir目錄下。

優化查詢語句的時候，要避免使用臨時表，如果實在避免不了的話，要保證這些臨時表是存在內存中的。

如果需要的話并且你有很多group by語句，并且你有很多內存，增大tmp_table_size(和max_heap_table_size)的值。這個變量不適用與用戶創建的內存表(memory table).

建議盡量優化查詢，要確保查詢過程中生成的臨時表在內存中，避免臨時表過大導致生成基于硬盤的MyISAM表。

使用sql命令show global status like 'created_tmp%';來獲得信息

+————————————-+———-+
| Variable_name | Value |
+————————————-+———-+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 12 |
+————————————-+———-+

每次創建臨時表，Created_tmp_tables增加，如果臨時表大小超過tmp_table_size，則是在磁盤上創建臨時表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務創建的臨時文件文件數，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables 100% <= 25%比如上面的服務器Created_tmp_disk_tables / Created_tmp_tables 100% ＝1.20%，應該相當好了

默認為16M，可調到64-256最佳，線程獨占，太大可能內存不夠I/O堵塞。如果動態頁面要調大點，100M以上，如果網站大部分都是靜態內容，一般64M足夠。

max_heap_table_size控制用戶可以創建多大的內存表，防止創建一個特別多大的內存表而耗盡資源。

查詢：
在線配置：
配置文件：tmp_table_size = 256M | max_heap_table_size = 64M

bulk_insert_buffer_size
和key_buffer_size一樣，這個參數同樣也僅作用于使用 MyISAM存儲引擎，用來緩存批量插入數據的時候臨時緩存寫入數據。當我們使用如下幾種數據寫入語句的時候，會使用這個內存區域來緩存批量結構的數據以幫助批量寫入數據文件

查詢：
在線配置：
配置文件：bulk_insert_buffer_size = 4M