如果mysql 連接數(shù)據(jù)設(shè)置不合理可能會(huì)導(dǎo)致很小的流量mysql就提示MySQL: ERROR 1040: Too many connections錯(cuò)誤了，那么要如何才算是合理設(shè)置mysql最大連接數(shù)呢，下面我來(lái)給大家介紹介紹。 MySQL服務(wù)器的連接數(shù)并不是要達(dá)到最大的100%為好，還是要具體問(wèn)題具體

如果mysql 連接數(shù)據(jù)設(shè)置不合理可能會(huì)導(dǎo)致很小的流量mysql就提示MySQL: ERROR 1040: Too many connections錯(cuò)誤了，那么要如何才算是合理設(shè)置mysql最大連接數(shù)呢，下面我來(lái)給大家介紹介紹。

MySQL服務(wù)器的連接數(shù)并不是要達(dá)到最大的100%為好，還是要具體問(wèn)題具體分析，下面就對(duì)MySQL服務(wù)器最大連接數(shù)的合理設(shè)置進(jìn)行了詳盡的分析，供您參考。

我們經(jīng)常會(huì)遇見(jiàn)“MySQL: ERROR 1040: Too many connections”的情況，通常，mysql的最大連接數(shù)默認(rèn)是100, 最大可以達(dá)到16384。

一種是訪問(wèn)量確實(shí)很高，MySQL服務(wù)器抗不住，這個(gè)時(shí)候就要考慮增加從服務(wù)器分散讀壓力，另外一種情況是MySQL配置文件中max_connections值過(guò)小：

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+

這臺(tái)MySQL服務(wù)器最大連接數(shù)是256，然后查詢一下服務(wù)器響應(yīng)的最大連接數(shù)：

mysql> show global status like 'Max_used_connections';

MySQL服務(wù)器過(guò)去的最大連接數(shù)是245，沒(méi)有達(dá)到服務(wù)器連接數(shù)上限256，應(yīng)該沒(méi)有出現(xiàn)1040錯(cuò)誤，比較理想的設(shè)置是：

Max_used_connections / max_connections * 100% ≈ 85%

最大連接數(shù)占上限連接數(shù)的85%左右，如果發(fā)現(xiàn)比例在10%以下，MySQL服務(wù)器連接上線就設(shè)置得過(guò)高了

在Windows下常用的有兩種方式修改最大連接數(shù)。

第一種：命令行修改。

>mysql -uuser -ppassword(命令行登錄MySQL)

mysql>show variables like 'max_connections';(查可以看當(dāng)前的最大連接數(shù))

msyql>set global max_connections=1000;(設(shè)置最大連接數(shù)為1000，可以再次查看是否設(shè)置成功)

mysql>exit(推出)

這種方式有個(gè)問(wèn)題，就是設(shè)置的最大連接數(shù)只在mysql當(dāng)前服務(wù)進(jìn)程有效，一旦mysql重啟，又會(huì)恢復(fù)到初始狀態(tài)。因?yàn)閙ysql啟動(dòng)后的初始化工作是從其配置文件中讀取數(shù)據(jù)的，而這種方式?jīng)]有對(duì)其配置文件做更改。

第二種：修改配置文件。

這 種方式說(shuō)來(lái)很簡(jiǎn)單，只要修改MySQL配置文件my.ini 或 my.cnf的參數(shù)max_connections，將其改為max_connections=1000，然后重啟MySQL即可。但是有一點(diǎn)最難的就是my.ini這個(gè)文件在哪找。通常有兩種可能，一個(gè)是在安裝目錄下（這是比較理想的情況），另一種是在數(shù)據(jù)文件的目錄下，安裝的時(shí)候如果沒(méi)有人為改變目錄的話，一般就在C:/ProgramData/MySQL往下的目錄下。

與連接數(shù)相關(guān)的幾個(gè)參數(shù)：

在修改最大連接數(shù)的時(shí)候會(huì)有這樣一個(gè)疑問(wèn)—這個(gè)值是不是越大越好，或者設(shè)置為多大才合適？這個(gè)參數(shù)的大小要綜合很多因素來(lái)考慮，比如使用的平臺(tái)所支持的線程庫(kù)數(shù)量（windows只能支持到2048）、服務(wù)器的配置（特別是內(nèi)存大小）、每個(gè)連接占用資源（內(nèi)存和負(fù)載）的多少、系統(tǒng)需要的響應(yīng)時(shí)間等。可以在global或session范圍內(nèi)修改這個(gè)參數(shù)。連接數(shù)的增加會(huì)帶來(lái)很多連鎖反應(yīng)，需要在實(shí)際中避免由此引發(fā)的負(fù)面影響。

首先看一下MySQL的狀態(tài)：

mysql> status;
--------------
mysql Ver 14.14 Distrib 5.5.15, for Win32 (x86)

Connection id: 1
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.5.15 MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 1 hour 3 min 27 sec

Threads: 12 Questions: 18 Slow queries: 10 Opens: 33 Flush tables: 5 Open tab
les: 34 Queries per second avg: 6.256
--------------

Open tables:34,即當(dāng)前數(shù)據(jù)庫(kù)打開(kāi)表的數(shù)量是34個(gè)，注意這個(gè)34并不是實(shí)際的34個(gè)表，因?yàn)镸ySQL是多線程的系統(tǒng)，幾個(gè)不同的并發(fā)連接可能打開(kāi)同一個(gè)表，這就需要為不同的連接session分配獨(dú)立的內(nèi)存空間來(lái)存儲(chǔ)這些信息以避免沖突。因此連接數(shù)的增加會(huì)導(dǎo)致MySQL需要的文件描述符數(shù)目的增加。另外對(duì)于MyISAM表，還會(huì)建立一個(gè)共享的索引文件描述符。

在MySQL數(shù)據(jù)庫(kù)層面，有幾個(gè)系統(tǒng)參數(shù)決定了可同時(shí)打開(kāi)的表的數(shù)量和要使用的文件描述符，那就是table_open_cache、max_tmp_tables和open_files_limit。

mysql> show variables like 'table_open%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 256 |
+------------------+-------+
1 row in set (0.00 sec)

table_open_cache:256，這就是說(shuō)所有的MySQL線程一共能同時(shí)打開(kāi)256個(gè)表，我們可以搜集系統(tǒng)的打開(kāi)表的數(shù)量的歷史記錄和這個(gè)參數(shù)來(lái)對(duì)比，決定是否要增加這個(gè)參數(shù)的大小。查看當(dāng)前的打開(kāi)表的數(shù)目(Open tables)可用上邊提到過(guò)的status命令，另外可以直接查詢這個(gè)系統(tǒng)變量的值：

mysql> show status like 'open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 3 |
+---------------+-------+
1 row in set (0.00 sec)

Open_tables就是當(dāng)前打開(kāi)表的數(shù)目，通過(guò)flush tables命令可以關(guān)閉當(dāng)前打開(kāi)的表。 這個(gè)值如果過(guò)大，并且如果沒(méi)有經(jīng)常的執(zhí)行flush tables命令，可以考慮增加table_open_cache參數(shù)的大小。

接下來(lái)看max_tmp_tables:
mysql> show variables like 'max_tmp%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| max_tmp_tables | 32 |
+----------------+-------+
1 row in set (0.00 sec)
max_tmp_tables:32即單個(gè)客戶端連接能打開(kāi)的臨時(shí)表數(shù)目。查看當(dāng)前已打開(kāi)的臨時(shí)表的信息：
mysql> show global status like '%tmp%table%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 11 |
+-------------------------+-------+
2 rows in set (0.00 sec)
根據(jù)這兩個(gè)值可以判斷臨時(shí)表的創(chuàng)建位置，一般選取BLOB和TEXT列、Group by 和 Distinct語(yǔ)句的數(shù)據(jù)量超過(guò)512 bytes，或者union的時(shí)候select某列的數(shù)據(jù)超過(guò)512 bytes的時(shí)候，就直接在磁盤(pán)上創(chuàng)建臨時(shí)表了，另外內(nèi)存中的臨時(shí)表變大的時(shí)候，也可能被MySQL自動(dòng)轉(zhuǎn)移到磁盤(pán)上（由tmp_table_size和max_heap_table_size參數(shù)決定）。

增加table_open_cache或max_tmp_tables 參數(shù)的大小后，從操作系統(tǒng)的角度看，mysqld進(jìn)程需要使用的文件描述符的個(gè)數(shù)就要相應(yīng)的增加，這個(gè)是由open_files_limit參數(shù)控制的。
mysql> show variables like 'open_files%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 2670 |
+------------------+-------+
1 row in set (0.00 sec)
但是這個(gè)參數(shù)是OS限制的，所以我們?cè)O(shè)定的值并不一定總是生效。如果OS限制MySQL不能修改這個(gè)值，那么置為0。如果是專(zhuān)用的MySQL服務(wù)器上，這個(gè)值一般要設(shè)置的盡量大，就是設(shè)為沒(méi)有報(bào)Too many open files錯(cuò)誤的最大值，這樣就能一勞永逸了。當(dāng)操作系統(tǒng)無(wú)法分配足夠的文件描述符的時(shí)候，mysqld進(jìn)程會(huì)在錯(cuò)誤日志里記錄警告信息。
相應(yīng)的，有兩個(gè)狀態(tài)變量記錄了當(dāng)前和歷史的文件打開(kāi)信息：
mysql> show global status like '%open%file%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 0 |
| Opened_files | 76 |
+---------------+-------+
2 rows in set (0.00 sec)
MySQL為每個(gè)連接分配線程來(lái)處理，可以通過(guò)threads_connected參數(shù)查看當(dāng)前分配的線程數(shù)量：
mysql> show status like '%thread%';
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Delayed_insert_threads | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
+------------------------------------------+-------+
8 rows in set (0.00 sec)
比較threads_connected參數(shù)和前面提到的max_connections參數(shù)，也可以作為目前的系統(tǒng)負(fù)載的參照，決定是否需要修改連接數(shù)。

查看每個(gè)線程的詳細(xì)信息：mysql>show processlist;對(duì)影響系統(tǒng)運(yùn)行的線程：kill connection|query threadid的命令殺死。

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com