如何增大MYSQL數(shù)據(jù)庫連接數(shù)

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

站在用戶的角度思考問題，與客戶深入溝通，找到納雍網(wǎng)站設(shè)計(jì)與納雍網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗(yàn)好的作品，建站類型包括：成都網(wǎng)站制作、做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、域名與空間、網(wǎng)站空間、企業(yè)郵箱。業(yè)務(wù)覆蓋納雍地區(qū)。

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

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

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

mysql show variables like 'max_connections';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| max_connections | 256 |

+-----------------+-------+

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

mysql show global status like 'Max_used_connections';

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

Max_used_connections / max_connections * 100% ≈ 85%

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

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

第一種：命令行修改。

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

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

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

mysqlexit(推出)

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

第二種：修改配置文件。

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

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

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

網(wǎng)站訪問量大 怎樣優(yōu)化mysql數(shù)據(jù)庫

I 硬件配置優(yōu)化

CPU選擇：多核的CPU，主頻高的CPU

內(nèi)存：更大的內(nèi)存

磁盤選擇：更快的轉(zhuǎn)速、RAID、陣列卡，

網(wǎng)絡(luò)環(huán)境選擇：盡量部署在局域網(wǎng)、SCI、光纜、千兆網(wǎng)、雙網(wǎng)線提供冗余、0.0.0.0多端口綁定監(jiān)聽

II 操作系統(tǒng)級優(yōu)化

使用64位的操作系統(tǒng)，更好的使用大內(nèi)存。

設(shè)置noatime,nodiratime

[zhangxy@dowload_server1 ~]$ cat /etc/fstab

LABEL=/ / ext3 defaults,noatime,nodiratime 1 1

/dev/sda5 /data xfs defaults,noatime,nodiratime 1 2

優(yōu)化內(nèi)核參數(shù)

net.ipv4.tcp_keepalive_time=7200

net.ipv4.tcp_max_syn_backlog=1024

net.ipv4.tcp_syncookies=1

net.ipv4.tcp_tw_reuse = 1

net.ipv4.tcp_tw_recycle = 1

net.ipv4.neigh.default.gc_thresh3 = 2048

net.ipv4.neigh.default.gc_thresh2 = 1024

net.ipv4.neigh.default.gc_thresh1 = 256

net.ipv4.conf.default.rp_filter = 1

net.ipv4.conf.default.forwarding = 1

net.ipv4.conf.default.proxy_arp = 0

net.ipv4.tcp_syncookies = 1

net.core.netdev_max_backlog = 2048

net.core.dev_weight = 64

net.ipv4.tcp_rmem = 4096 87380 16777216

net.ipv4.tcp_wmem = 4096 65536 16777216

net.ipv4.tcp_rfc1337 = 1

net.ipv4.tcp_sack = 0

net.ipv4.tcp_fin_timeout = 20

net.ipv4.tcp_keepalive_probes = 5

net.ipv4.tcp_max_orphans = 32768

net.core.optmem_max = 20480

net.core.rmem_default = 16777216

net.core.rmem_max = 16777216

net.core.wmem_default = 16777216

net.core.wmem_max = 16777216

net.core.somaxconn = 500

net.ipv4.tcp_orphan_retries = 1

net.ipv4.tcp_max_tw_buckets = 18000

net.ipv4.ip_forward = 0

net.ipv4.conf.default.proxy_arp = 0

net.ipv4.conf.all.rp_filter = 1

kernel.sysrq = 1

net.ipv4.conf.default.send_redirects = 1

net.ipv4.conf.all.send_redirects = 0

net.ipv4.ip_local_port_range = 5000 65000

kernel.shmmax = 167108864

vm.swappiness=0

加大文件描述符限制

Vim /etc/security/limits.conf

加上

* soft nofile 65535

* hard nofile 65535

文件系統(tǒng)選擇 xfs

/dev/sda5 /data xfs defaults,noatime,nodiratime 1 2

III Mysql設(shè)計(jì)優(yōu)化

III.1存儲引擎的選擇

Myisam：數(shù)據(jù)庫并發(fā)不大，讀多寫少,而且都能很好的用到索引，sql語句比較簡單的應(yīng)用，TB數(shù)據(jù)倉庫

Innodb：并發(fā)訪問大，寫操作比較多，有外鍵、事務(wù)等需求的應(yīng)用，系統(tǒng)內(nèi)存較大。

III.2命名規(guī)則

多數(shù)開發(fā)語言命名規(guī)則：比如MyAdress

多數(shù)開源思想命名規(guī)則：my_address

避免隨便命名

III.3字段類型選擇

字段類型的選擇的一般原則：

根據(jù)需求選擇合適的字段類型，在滿足需求的情況下字段類型盡可能小。

只分配滿足需求的最小字符數(shù)，不要太慷慨。

原因：更小的字段類型更小的字符數(shù)占用更少的內(nèi)存，占用更少的磁盤空間，占用更少的磁盤IO，以及占用更少的帶寬。

III.3.1 整型：

見如下圖:

類型

字節(jié)

最小值

最大值

(帶符號的/無符號的)

(帶符號的/無符號的)

TINYINT

1

-128

127

255

SMALLINT

2

-32768

32767

65535

MEDIUMINT

3

-8388608

8388607

16777215

INT

4

-2147483648

2147483647

4294967295

BIGINT

8

-9223372036854775808

9223372036854775807

18446744073709551615

根據(jù)滿足需求的最小整數(shù)為選擇原則，能用INT的就不要用BIGINT。

用無符號INT存儲IP，而非CHAR(15)。

III.3.2 浮點(diǎn)型：

類型

字節(jié)

精度類型

使用場景

FLOAT(M,D)

4

單精度

精度要求不高，數(shù)值比較小

DOUBLE(M,D)（REAL）

8

雙精度

精度要求不高，數(shù)值比較大

DECIMAL(M,D)(NUMERIC）

M+2

自定義精度

精度要求很高的場景

III.3.3 時間類型

類型

取值范圍

存儲空間

零值表示法

DATE

1000-01-01~9999-12-31

3字節(jié)

0000-00-00

TIME

-838:59:59~838:59:59

3字節(jié)

00:00:00

DATETIME

1000-01-01 00:00:00~9999-12-31 23:59:59

8字節(jié)

0000-00-00 00:00:00

TIMESTAMP

19700101000000~2037年的某個時刻

4字節(jié)

00000000000000

YEAR

YEAR(4):1901~2155 YEAR(2)：1970~2069

1字節(jié)

0000

III.3.4 字符類型

類型

最大長度

占用存儲空間

CHAR[(M)]

M字節(jié)

M字節(jié)

VARCHAR[(M)]

M字節(jié)

M+1字節(jié)

TINYBLOD，TINYTEXT

2^8-1字節(jié)

L+1字節(jié)

BLOB，TEXT

2^16-1字節(jié)

L+2

MEDIUMBLOB，MEDIUMTEXT

2^24-1字節(jié)

L+3

LONGBLOB，LONGTEXT

2^32-1字節(jié)

L+4

ENUM('value1','value2',...)

65535個成員

1或2字節(jié)

SET('value1','value2',...)

64個成員

1,2,3,4或8字節(jié)

注：L表示可變長度的意思

對于varchar和char的選擇要根據(jù)引擎和具體情況的不同來選擇，主要依據(jù)如下原則:

1. 如果列數(shù)據(jù)項(xiàng)的大小一致或者相差不大，則使用char。

2. 如果列數(shù)據(jù)項(xiàng)的大小差異相當(dāng)大，則使用varchar。

3. 對于MyISAM表，盡量使用Char，對于那些經(jīng)常需要修改而容易形成碎片的myisam和isam數(shù)據(jù)表就更是如此，它的缺點(diǎn)就是占用磁盤空間。

4. 對于InnoDB表，因?yàn)樗臄?shù)據(jù)行內(nèi)部存儲格式對固定長度的數(shù)據(jù)行和可變長度的數(shù)據(jù)行不加區(qū)分（所有數(shù)據(jù)行共用一個表頭部分，這個標(biāo)頭部分存放著指向各有關(guān)數(shù)據(jù)列的指針），所以使用char類型不見得會比使用varchar類型好。事實(shí)上，因?yàn)閏har類型通常要比varchar類型占用更多的空 間，所以從減少空間占用量和減少磁盤i/o的角度，使用varchar類型反而更有利。

5. 表中只要存在一個varchar類型的字段，那么所有的char字段都會自動變成varchar類型，因此建議定長和變長的數(shù)據(jù)分開。

III.4編碼選擇

單字節(jié) latin1

多字節(jié) utf8(漢字占3個字節(jié)，英文字母占用一個字節(jié))

如果含有中文字符的話最好都統(tǒng)一采用utf8類型，避免亂碼的情況發(fā)生。

III.5主鍵選擇原則

注：這里說的主鍵設(shè)計(jì)主要是針對INNODB引擎

1. 能唯一的表示行。

2. 顯式的定義一個數(shù)值類型自增字段的主鍵，這個字段可以僅用于做主鍵，不做其他用途。

3. MySQL主鍵應(yīng)該是單列的，以便提高連接和篩選操作的效率。

4. 主鍵字段類型盡可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT。

5. 盡量保證不對主鍵字段進(jìn)行更新修改，防止主鍵字段發(fā)生變化，引發(fā)數(shù)據(jù)存儲碎片，降低IO性能。

6. MySQL主鍵不應(yīng)包含動態(tài)變化的數(shù)據(jù)，如時間戳、創(chuàng)建時間列、修改時間列等。

7. MySQL主鍵應(yīng)當(dāng)有計(jì)算機(jī)自動生成。

8. 主鍵字段放在數(shù)據(jù)表的第一順序。

推薦采用數(shù)值類型做主鍵并采用auto_increment屬性讓其自動增長。

III.6其他需要注意的地方

NULL OR NOT NULL

盡可能設(shè)置每個字段為NOT NULL，除非有特殊的需求，原因如下：

1. 使用含有NULL列做索引的話會占用更多的磁盤空間，因?yàn)樗饕齆ULL列需要而外的空間來保存。

2. 進(jìn)行比較的時候，程序會更復(fù)雜。

3. 含有NULL的列比較特殊，SQL難優(yōu)化，如果是一個組合索引，那么這個NULL 類型的字段會極大影響整個索引的效率。

索引

索引的缺點(diǎn)：極大地加速了查詢，減少掃描和鎖定的數(shù)據(jù)行數(shù)。

索引的缺點(diǎn)：占用磁盤空間，減慢了數(shù)據(jù)更新速度，增加了磁盤IO。

添加索引有如下原則：

1. 選擇唯一性索引。

2. 為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引。

3. 為常作為查詢條件的字段建立索引。

4. 限制索引的數(shù)據(jù)，索引不是越多越好。

5. 盡量使用數(shù)據(jù)量少的索引，對于大字段可以考慮前綴索引。

6. 刪除不再使用或者很少使用的索引。

7. 結(jié)合核心SQL優(yōu)先考慮覆蓋索引。

8. 忌用字符串做主鍵。

反范式設(shè)計(jì)

適當(dāng)?shù)氖褂萌哂嗟姆捶妒皆O(shè)計(jì)，以空間換時間有的時候會很高效。

IV Mysql軟件優(yōu)化

開啟mysql復(fù)制，實(shí)現(xiàn)讀寫分離、負(fù)載均衡，將讀的負(fù)載分?jǐn)偟蕉鄠€從服務(wù)器上，提高服務(wù)器的處理能力。

使用推薦的GA版本，提升性能

利用分區(qū)新功能進(jìn)行大數(shù)據(jù)的數(shù)據(jù)拆分

V Mysql配置優(yōu)化

注意：全局參數(shù)一經(jīng)設(shè)置，隨服務(wù)器啟動預(yù)占用資源。

key_buffer_size參數(shù)

mysql索引緩沖，如果是采用myisam的話要重點(diǎn)設(shè)置這個參數(shù)，根據(jù)（key_reads/key_read_requests）判斷

innodb_buffer_pool_size參數(shù)

INNODB 數(shù)據(jù)、索引、日志緩沖最重要的引擎參數(shù)，根據(jù)（hit riatos和FILE I/O）判斷

wait_time_out參數(shù)

線程連接的超時時間，盡量不要設(shè)置很大，推薦10s

max_connections參數(shù)

服務(wù)器允許的最大連接數(shù)，盡量不要設(shè)置太大，因?yàn)樵O(shè)置太大的話容易導(dǎo)致內(nèi)存溢出，需要通過如下公式來確定：

SET @k_bytes = 1024;

SET @m_bytes = @k_bytes * 1024;

SET @g_bytes = @m_bytes * 1024;

SELECT

(

@@key_buffer_size + @@query_cache_size + @@tmp_table_size+

@@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size+

@@innodb_log_buffer_size+

@@max_connections *

( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size+

@@join_buffer_size + @@binlog_cache_size + @@thread_stack

) )

/ @g_bytes AS MAX_MEMORY_USED_GB;

thread_concurrency參數(shù)

線程并發(fā)利用數(shù)量，(cpu+disk)*2,根據(jù)(os中顯示的請求隊(duì)列和tickets)判斷

sort_buffer_size參數(shù)

獲得更快的--ORDER BY,GROUP BY,SELECT DISTINCT,UNION DISTINCT

read_rnd_buffer_size參數(shù)

當(dāng)根據(jù)鍵進(jìn)行分類操作時獲得更快的--ORDER BY

join_buffer_size參數(shù)

join連接使用全表掃描連接的緩沖大小，根據(jù)select_full_join判斷

read_buffer_size參數(shù)

全表掃描時為查詢預(yù)留的緩沖大小，根據(jù)select_scan判斷

tmp_table_size參數(shù)

臨時內(nèi)存表的設(shè)置，如果超過設(shè)置就會轉(zhuǎn)化成磁盤表，根據(jù)參數(shù)(created_tmp_disk_tables)判斷

innodb_log_file_size參數(shù)(默認(rèn)5M)

記錄INNODB引擎的redo log文件，設(shè)置較大的值意味著較長的恢復(fù)時間。

? innodb_flush_method參數(shù)(默認(rèn)fdatasync)

Linux系統(tǒng)可以使用O_DIRECT處理數(shù)據(jù)文件，避免OS級別的cache，O_DIRECT模式提高數(shù)據(jù)文件和日志文件的IO提交性能

innodb_flush_log_at_trx_commit(默認(rèn)1)

表示每秒進(jìn)行一次log寫入cache，并flush log到磁盤。

表示在每次事務(wù)提交后執(zhí)行l(wèi)og寫入cache，并flush log到磁盤。

表示在每次事務(wù)提交后，執(zhí)行l(wèi)og數(shù)據(jù)寫入到cache，每秒執(zhí)行一次flush log到磁盤。

VI Mysql語句級優(yōu)化

1. 性能查的讀語句，在innodb中統(tǒng)計(jì)行數(shù),建議另外弄一張統(tǒng)計(jì)表，采用myisam，定期做統(tǒng)計(jì).一般的對統(tǒng)計(jì)的數(shù)據(jù)不會要求太精準(zhǔn)的情況下適用。

2. 盡量不要在數(shù)據(jù)庫中做運(yùn)算。

3. 避免負(fù)向查詢和%前綴模糊查詢。

4. 不在索引列做運(yùn)算或者使用函數(shù)。

5. 不要在生產(chǎn)環(huán)境程序中使用select * from 的形式查詢數(shù)據(jù)。只查詢需要使用的列。

6. 查詢盡可能使用limit減少返回的行數(shù)，減少數(shù)據(jù)傳輸時間和帶寬浪費(fèi)。

7. where子句盡可能對查詢列使用函數(shù)，因?yàn)閷Σ樵兞惺褂煤瘮?shù)用不到索引。

8. 避免隱式類型轉(zhuǎn)換，例如字符型一定要用’’，數(shù)字型一定不要使用’’。

9. 所有的SQL關(guān)鍵詞用大寫，養(yǎng)成良好的習(xí)慣，避免SQL語句重復(fù)編譯造成系統(tǒng)資源的浪費(fèi)。

10. 聯(lián)表查詢的時候，記得把小結(jié)果集放在前面，遵循小結(jié)果集驅(qū)動大結(jié)果集的原則。

11. 開啟慢查詢，定期用explain優(yōu)化慢查詢中的SQL語句。

如何在MYsql數(shù)據(jù)庫中設(shè)定網(wǎng)站的最大訪問量？？？

mysql只能設(shè)定連接的最大數(shù),max connections,這還要取決于服務(wù)器性能,不是說連接數(shù)越多越好,要有足夠的內(nèi)存支持。

網(wǎng)站的負(fù)載能力取決于很多因素，運(yùn)行平臺的架構(gòu)，代碼性能，緩存的性能大小等等。

這里不能一一列舉。

如何修改MySQL連接數(shù)超限的方法

mysql的最大連接數(shù)默認(rèn)是100,

這個數(shù)值對于并發(fā)連接很多的數(shù)據(jù)庫應(yīng)用是遠(yuǎn)遠(yuǎn)不夠的，當(dāng)連接請求大于默認(rèn)連接數(shù)后，就會出現(xiàn)無法連接數(shù)據(jù)庫的錯誤，因此我們需要把它適當(dāng)調(diào)大一些。

調(diào)節(jié)方法為：

1.linux服務(wù)器中

：改my.cnf中的值就行了

2.Windows服務(wù)器中（我用的）:

在文件“my.ini”中找到段

[mysqld],在其中添加一行

max_connections=200###

200可以更改為想設(shè)置成的值.

然后重啟"mysql"服務(wù)。

/mysqladmin所在路徑/mysqladmin -uroot -p variables

輸入root數(shù)據(jù)庫賬號的密碼后可看到

| max_connections | 1000 |

其他需注意的：

在編程時，由于用mysql語句調(diào)用數(shù)據(jù)庫時，在每次之執(zhí)行語句前，會做一個臨時的變量用來打開數(shù)據(jù)庫，所以你在使用mysql語句的時候，記得在每次調(diào)用完mysql之后就關(guān)閉mysql臨時變量。

另外對于訪問量大的，可以考慮直接寫到文本中，根據(jù)預(yù)測的訪問量，先定義假若是100個文件文件名依次為1.

txt,2.

txt

100.

txt。

如何設(shè)置mysql數(shù)據(jù)庫訪問次數(shù)

如何設(shè)置mysql數(shù)據(jù)庫訪問次數(shù)

對于查詢占主要的應(yīng)用來說，索引顯得尤為重要。很多時候性能問題很簡單的就是因?yàn)槲覀兺颂砑铀饕斐傻模蛘哒f沒有添加更為有效的索引導(dǎo)致。如果不加索引的話，那么查找任何哪怕只是一條特定的數(shù)據(jù)都會進(jìn)行一次全表掃描，如果一張表的數(shù)據(jù)量很大而符合條件的結(jié)果又很少，那么不加索引會引起致命的性能下降。但是也不是什么情況都非得建索引不可，比如性別可能就只有兩個值，建索引不僅沒什么優(yōu)勢，還會影響到更新速度，這被稱為過度索引。