Mysql-服務(wù)端-擴展配置

Mysql-服務(wù)端-擴展配置

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

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

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

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

查詢配置：
在線配置：
配置文件參數(shù)：connect_timeout=10

interactive_timeout|wait_timeout
控制連接最大空閑時長的參數(shù)。默認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
如果讀或?qū)懸粋€通信端口中斷，mysql放棄前嘗試連接的次數(shù)。在FreeBSD系統(tǒng)中此值應(yīng)設(shè)置很高，因為FreeBSD內(nèi)部中斷被發(fā)送到所有線程去。

查詢配置：
在線配置：
配置文件參數(shù)：net_retry_count = 100

thread_concurrency
這個變量是針對Solaris系統(tǒng)的，設(shè)定為內(nèi)核數(shù)的2倍。

如果設(shè)置這個變量的話，mysqld就會調(diào)用thr_setconcurrency()。這個函數(shù)使應(yīng)用程序給同一時間運行的線程系統(tǒng)提供期望的線程數(shù)目。

查詢配置：
在線配置：
配置文件參數(shù)：thread_concurrency = 8

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

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

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

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

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

Threads_cached，如果太大，證明一直在創(chuàng)建新的線程，可以將thread_cache_size調(diào)大。

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

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

查詢配置：
在線配置：
配置文件參數(shù)：open_files_limit = 65535

max_connect_errors
實驗參考

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

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

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

盡量去改善網(wǎng)絡(luò)環(huán)境，或者將max_connect_errors值調(diào)大

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

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

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

建議：
推薦設(shè)置為350

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

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

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

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

ft_min_word_len
開啟全文索引，默認關(guān)閉。根據(jù)需求開啟，如果沒使用全文索引，就不要開啟。

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

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

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

log_bin_trust_function_creators
如果開啟了主從復(fù)制，要設(shè)置為0，禁止用戶創(chuàng)建函數(shù)，觸發(fā)器。因為存儲函數(shù)有可能導致主從的數(shù)據(jù)不一致。

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

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

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

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

建議：
8G機器可以設(shè)置此參數(shù)為1M

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

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

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

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

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

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

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

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

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

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

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

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

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

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

查詢：
在線配置： 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版本，創(chuàng)建如下

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

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

表中的第一個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”，并且不會發(fā)生警告。

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

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

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

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

transaction-isolation
修改事務(wù)隔離級別

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

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

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

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

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

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

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

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

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

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

如果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內(nèi)存8千到2萬，2G內(nèi)存512

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

sort_buffer_size
系統(tǒng)中對數(shù)據(jù)進行排序的時候用到的Buffer。是針對單個線程的，所以當多個線程同時進行排序的時候，系統(tǒng)中就會出現(xiàn)多個sort buffer。默認256K

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

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

join_buffer_size
當使用join命令時，為了減少參與join的“被驅(qū)動表”的讀取次數(shù)以提高性能，需要使用到j(luò)oin buffer來協(xié)助完成join操作

當join buffer 太小，MySQL不會將該buffer存入磁盤文件而是先將join buffer中的結(jié)果與需求join的表進行操作，然后清空join buffer中的數(shù)據(jù)，繼續(xù)將剩余的結(jié)果集寫入次buffer中，如此往復(fù)，這勢必會造成被驅(qū)動表需要被多次讀取，成倍增加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 分配給每個客戶端,因此你不能設(shè)置全局變量為一個大的值。相反,只改變session 變量對那些客戶端需要運行大的查詢。

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

myisam_sort_buffer_size
當對MyISAM表執(zhí)行repair table或創(chuàng)建索引時，用以緩存排序索引

設(shè)置太小時可能會遇到” myisam_sort_buffer_size is too small”

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

query_cache_size|query_cache_type
參考

MySQL查詢緩存保存查詢返回的完整結(jié)果。當查詢命中該緩存，會立刻返回結(jié)果，跳過了解析，優(yōu)化和執(zhí)行階段。

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

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

query_cache_type決定是否緩存查詢結(jié)果。這個變量有三個取值：0,1,2，0時表示關(guān)閉，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引擎 默認會將所有的數(shù)據(jù)庫InnoDB引擎的表數(shù)據(jù)存儲在一個共享空間中：ibdata1，當增刪數(shù)據(jù)庫的時候，ibdata1文件不會自動收縮，單個數(shù)據(jù)庫的備份也將成為問題。通常只能將數(shù)據(jù)使用mysqldump 導出，然后再導入解決這個問題。

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

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

結(jié)論：
共享表空間在Insert操作上有一些優(yōu)勢，但在其它都沒獨立表空間表現(xiàn)好。
當啟用獨立表空間時，請合理調(diào)整一下 innodb_open_files 參數(shù)。

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

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

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

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

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

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

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

Created_tmp_disk_tables / Created_tmp_tables 100% <= 25%比如上面的服務(wù)器Created_tmp_disk_tables / Created_tmp_tables 100% ＝1.20%，應(yīng)該相當好了

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

max_heap_table_size控制用戶可以創(chuàng)建多大的內(nèi)存表，防止創(chuàng)建一個特別多大的內(nèi)存表而耗盡資源。

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

bulk_insert_buffer_size
和key_buffer_size一樣，這個參數(shù)同樣也僅作用于使用 MyISAM存儲引擎，用來緩存批量插入數(shù)據(jù)的時候臨時緩存寫入數(shù)據(jù)。當我們使用如下幾種數(shù)據(jù)寫入語句的時候，會使用這個內(nèi)存區(qū)域來緩存批量結(jié)構(gòu)的數(shù)據(jù)以幫助批量寫入數(shù)據(jù)文件

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