mysql參數(shù)設(shè)置

采用set GLOBAL命令。如：set GLOBAL event_scheduler=1;

專注于為中小企業(yè)提供網(wǎng)站設(shè)計制作、做網(wǎng)站服務(wù),電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)方山免費做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了近千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實現(xiàn)規(guī)模擴充和轉(zhuǎn)變。

在Mysql的配置文件中對參數(shù)值進行修改，之后重啟數(shù)據(jù)庫服務(wù)即可。

如何設(shè)置合理的mysql的參數(shù)

非root用戶運行MySQL，當MySQL配置比較高時，MySQL運行中生效的參數(shù)值與配置的值不一樣,所以具體分析一下MySQL是怎么調(diào)整這些參數(shù)值的。?這篇文章的目的是為了說明在系統(tǒng)資源不夠的情況下，MySQL 是怎么調(diào)整者三個參數(shù)的。說明此文涉及到三個參數(shù)open_files_limit、?max_connections、?table_open_cache。與這三個參數(shù)相關(guān)的系統(tǒng)資源是打開文件數(shù)限制，即文件描述符（fd）限制。系統(tǒng)參數(shù)與文件描述符的關(guān)系?-?max_connection??fd?: 每一個MySQL connection ? ? ?都需要一個文件描述符；-?table_open_cache??fd?打開一張表至少需要一個 ? ? ?文件描述符，如打開MyISAM需要兩個fd?；- 系統(tǒng)最大打開文件數(shù)可以通過?ulimit -n查看。MySQL調(diào)整參數(shù)的方式

根據(jù)配置（三個參數(shù)的配置值或默認值）計算?request_open_files（需要的文件描述符）；

2.獲取有效的系統(tǒng)的限制值effective_open_files；? 3.根據(jù)effective_open_files調(diào)整request_open_files；? 4.根據(jù)調(diào)整后的request_open_files,計算實際生效的參數(shù)值（show variables?可查看參數(shù)值）。計算request_open_filesrequest_open_files有三個計算公式：1. ??? ?// 最大連接數(shù)+同時打開的表的最大數(shù)量+其他（各種日志等等）2. ? ? limit_1= max_connections+table_cache_size * 2 + 10;3.? ?4.???? ?//假設(shè)平均每個連接打開的表的數(shù)量（2-4）5. ? ? ?//源碼中是這么寫的：6. ? ? ?//We are trying to allocate no less than?7. ? ???// max_connections*5 file handles8. ? ? ?limit_2= max_connections * 5;9.? ?10. ? ?//mysql 默認的默認是500011. ? ?limit_3= open_files_limit ? open_files_limit : 5000;12. ?13. ? ? 所以open_files_limit期待的最低14. ? ? request_open_files= max(limit_1,limit_2,limit_3);計算effective_open_files:MySQL 的思路：

在有限值的的范圍內(nèi)MySQL?盡量將effective_open_files的值設(shè)大。

修正request_open_files

requested_open_files= min(effective_open_files,?request_open_files)

重新計算參數(shù)值

修正open_files_limit

open_files_limit?=?effective_open_files

修正max_connections

max_connections?根據(jù)?request_open_files?來做修正。1.? limit = requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2;

如果配置的max_connections值大于limit，則將max_connections?的值修正為limit

其他情況下?max_connections?保留配置值

修正table_cache_size

table_cache_size?會根據(jù)?request_open_files?來做修正1. ??// mysql table_cache_size 最小值，4002. ? limit1 = TABLE_OPEN_CACHE_MIN3. ??// 根據(jù) requested_open_files 計算4. ? limit2 = (requested_open_files - 10 - max_connections) / 25. ? limit = max(limit1,limt2);

如果配置的table_cache_size?值大于limit，則將?table_cache_size?的值修正為limit

其他情況下table_cache_size?保留配置值

舉例

以下用例在非 root 用戶下運行

參數(shù)設(shè)置：

//mysql

max_connections = 500

table_open_cache = 999

//ulimit -n

1500

生效的值：

open_files_limit = 1500? ?max_connections = min[(1500 - 10 - 800),500] = 500

table_open_cache = ( 1500 - 10 - 500) / 2 =495

MySQL內(nèi)存相關(guān)參數(shù)

相關(guān)查看命令

sql show global variables like 'innodb_buffer_pool_size';

sql show global status like 'Innodb_buffer_pool_pages_data';

sql show global status like 'Innodb_page_size';

有的參數(shù)對應(yīng)不同引擎，比如對于innodb引擎的，都是innodb_打頭。

例如：

innodb_buffer_pool_size = 81920M

join_buffer_size = 1024M

innodb_sort_buffer_size =1024M

sort_buffer_size = 2048M

read_rnd_buffer_size = 2048M

innodb_log_buffer_size = 128M

innodb_log_file_size =2048M

innodb_log_files_in_group=10

bulk_insert_buffer_size=4096M

myisam_sort_buffer_size = 512M

myisam_max_sort_file_size = 10G

thread_cache_size = 300

ft_min_word_len = 1 #for chinese full text search

query_cache_size = 512M

query_cache_limit = 4M

query_cache_type = 0

query_cache_min_res_unit = 2k

thread_stack = 512K

tmp_table_size = 3G

max_heap_table_size = 3G

long_query_time = 3

log-slave-updates

max_binlog_cache_size = 8M

調(diào)優(yōu)參考計算方法：

val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%

val 95% 則考慮增大 innodb_buffer_pool_size， 建議使用物理內(nèi)存的75%

val 95% 則考慮減小 innodb_buffer_pool_size， 建議設(shè)置為：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

設(shè)置命令：set global innodb_buffer_pool_size = 2097152; //緩沖池字節(jié)大小，單位kb，如果不設(shè)置，默認為128M

設(shè)置要根據(jù)自己的實際情況來設(shè)置，如果設(shè)置的值不在合理的范圍內(nèi)，并不是設(shè)置越大越好，可能設(shè)置的數(shù)值太大體現(xiàn)不出優(yōu)化效果，反而造成系統(tǒng)的swap空間被占用，導致操作系統(tǒng)變慢，降低sql查詢性能。

修改配置文件的調(diào)整方法，修改my.cnf配置：

innodb_buffer_pool_size = 2147483648??#設(shè)置2G

innodb_buffer_pool_size = 2G??#設(shè)置2G

innodb_buffer_pool_size = 500M??#設(shè)置500M

MySQL5.7及以后版本，改參數(shù)時動態(tài)的，修改后，無需重啟MySQL，但是低版本，靜態(tài)的，修改后，需要重啟MySQL。

MySQL中的配置參數(shù)interactive_timeout和wait_timeout設(shè)置

1)interactive_timeout:

參數(shù)含義：服務(wù)器關(guān)閉交互式連接前等待活動的秒數(shù)。交互式客戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的客戶端。

參數(shù)默認值：28800秒（8小時）

(2)wait_timeout:

參數(shù)含義：服務(wù)器關(guān)閉非交互連接之前等待活動的秒數(shù)。

在線程啟動時，根據(jù)全局wait_timeout值或全局interactive_timeout值初始化會話wait_timeout值，取決于客戶端類型(由mysql_real_connect()的連接選項CLIENT_INTERACTIVE定義)。

參數(shù)默認值：28800秒（8小時）

MySQL服務(wù)器所支持的最大連接數(shù)是有上限的，因為每個連接的建立都會消耗內(nèi)存，因此我們希望客戶端在連接到My SQL Server 處理完相應(yīng)的操作后，應(yīng)該斷開連接并釋放占用的內(nèi)存。如果你的MySQL Server有大量的閑置連接，他們不僅會白白消耗內(nèi)存，而且如果連接一直在累加而不斷開，最終肯定會達到MySQL Server的連接上限數(shù)，這會報'too many connections'的錯誤。對于wait_timeout的值設(shè)定，應(yīng)該根據(jù)系統(tǒng)的運行情況來判斷。在系統(tǒng)運行一段時間后，可以通過show processlist命令查看當前系統(tǒng)的連接狀態(tài)，如果發(fā)現(xiàn)有大量的sleep狀態(tài)的連接進程，則說明該參數(shù)設(shè)置的過大，可以進行適當?shù)恼{(diào)整小些。

問題：

如果在配置文件my.cnf中只設(shè)置參數(shù)wait_timeout=100,則重啟服務(wù)器后進入，執(zhí)行:

Mysql show variables like “%timeout%”;

會發(fā)現(xiàn)參數(shù)設(shè)置并未生效，仍然為28800（即默認的8個小時）。

查詢資料后，要同時設(shè)置interactive_timeout和wait_timeout才會生效。

【mysqld】

wait_timeout=100

interactive_timeout=100

重啟MySQL Server進入后，查看設(shè)置已經(jīng)生效。