Mysql常用的配置參數的整理

這篇文章主要介紹“常用的配置參數的整理”，在日常操作中，相信很多人在Mysql常用的配置參數的整理問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對大家解答”Mysql常用的配置參數的整理”的疑惑有所幫助！接下來(lái)，請跟著(zhù)小編一起來(lái)學(xué)習吧！

cat /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the

# *** default location during install, and will be replaced if you

# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# socket = .....

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

##################################################

#innodb

user=mysql

innodb_buffer_pool_size=6G

innodb_log_file_size=4G

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit=2

innodb_file_per_table=1

innodb_file_io_threads=4

innodb_flush_method=O_DIRECT

innodb_io_capacity=2000

innodb_io_capacity_max=6000

innodb_lru_scan_depth=2000

innodb_thread_concurrency = 0

innodb_additional_mem_pool_size=16M

innodb_autoinc_lock_mode = 2

##################################################

# Binary log/replication

log-bin

sync_binlog=1

sync_relay_log=1

relay-log-info-repository=TABLE

master-info-repository=TABLE

expire_logs_days=7

binlog_format=ROW

transaction-isolation=READ-COMMITTED

#################################################

#cache

tmp_table_size=512M

character-set-server=utf8

collation-server=utf8_general_ci

skip-external-locking

back_log=1024

key_buffer_size=1024M

thread_stack=256k

read_buffer_size=8M

thread_cache_size=64

query_cache_size=128M

max_heap_table_size=256M

query_cache_type=1

binlog_cache_size = 2M

table_open_cache=128

thread_cache=1024

thread_concurrency=8

wait_timeout=30

join_buffer_size = 1024M

sort_buffer_size = 8M

read_rnd_buffer_size = 8M

#################################################

#connect

max-connect-errors=100000

max-connections=1000

#################################################

explicit_defaults_for_timestamp=true

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

##################################################

# Binary log/replication(這里主要是復制功能，也就是主從,提前配置好，后面講主從配置)

#二進(jìn)制日志

log-bin

#為了在最大程序上保證復制的InnoDB事務(wù)持久性和一致性

sync_binlog=1

sync_relay_log=1

#啟用此兩項,可用于實(shí)現在崩潰時(shí)保證二進(jìn)制及從安全的功能

relay-log-info-repository=TABLE

master-info-repository=TABLE

#設置清除日志時(shí)間

expire_logs_days=7

#行復制

binlog_format=ROW

#mysql數據庫事務(wù)隔離級別有四種(READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SERIALIZABLE)

transaction-isolation=READ-COMMITTED

#cache

#內部?jì)却媾R時(shí)表的最大值

tmp_table_size=512M

character-set-server=utf8

collation-server=utf8_general_ci

#即跳過(guò)外部鎖定

skip-external-locking

#MySQL能暫存的連接數量(根據實(shí)際設置)

back_log=1024

#指定索引緩沖區的大小,只對MyISAM表起作用,這里寫(xiě)上也沒(méi)有關(guān)系

key_buffer_size=1024M

#這條指令限定用于每個(gè)數據庫線(xiàn)程的棧大小

thread_stack=256k

#當一個(gè)查詢(xún)不斷地掃描某一個(gè)表,MySQL會(huì )為它分配一段內存緩沖區

read_buffer_size=8M

#線(xiàn)程緩存

thread_cache_size=64

#查詢(xún)緩存大小

query_cache_size=128M

#內部?jì)却媾R時(shí)表的最大值,每個(gè)線(xiàn)程都要分配

max_heap_table_size=256M

#將查詢(xún)結果放入查詢(xún)緩存中

query_cache_type=1

#代表在事務(wù)過(guò)程中容納二進(jìn)制日志SQL語(yǔ)句的緩存大小

binlog_cache_size = 2M

#同樣是緩存表大小

table_open_cache=128

#緩存線(xiàn)程

thread_cache=1024

#推薦設置為服務(wù)器 CPU核數的2倍

thread_concurrency=8

wait_timeout=30

#表和表聯(lián)接的緩沖區的大小

join_buffer_size = 1024M

#是一個(gè)connection級參數,在每個(gè)connection第一次需要使用這個(gè)buffer的時(shí)候,一次性分配設置的內存

sort_buffer_size=8M

#隨機讀取數據緩沖區使用內存

read_rnd_buffer_size = 8M

#connect

#是一個(gè)MySQL中與安全有關(guān)的計數器值,它負責阻止過(guò)多嘗試失敗的客戶(hù)端以防止暴力破解密碼

max-connect-errors=100000

#連接數

max-connections=1000

#開(kāi)啟查詢(xún)緩存

explicit_defaults_for_timestamp=true

#mysql服務(wù)器能夠工作在不同的模式下,并能針對不同的客戶(hù)端以不同的方式應用這些模式

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

下面列出了對性能優(yōu)化影響較大的主要變量，主要分為連接請求的變量和緩沖區變量。

1.   連接請求的變量：

1)     max_connections

MySQL的最大連接數，增加該值增加mysqld 要求的文件描述符的數量。如果服務(wù)器的并發(fā)連接請求量比較大，建議調高此值，以增加并行連接數量，當然這建立在機器能支撐的情況下，因為如果連接數越多， 介于MySQL會(huì )為每個(gè)連接提供連接緩沖區，就會(huì )開(kāi)銷(xiāo)越多的內存，所以要適當調整該值，不能盲目提高設值。

數值過(guò)小會(huì )經(jīng)常出現ERROR 1040: Too many connections錯誤，可以過(guò)’conn%’通配符查看當前狀態(tài)的連接數量，以定奪該值的大小。

show variables like ‘max_connections’ 最大連接數

show  status like ‘max_used_connections’響應的連接數

如下：

mysql> show variables like ‘max_connections‘;

+———————–+——-+

| Variable_name　| Value |

+———————–+——-+

| max_connections | 256　　|

+———————–+——-+

mysql> show status like ‘max%connections‘;

+———————–+——-+

| Variable_name　      | Value |

+—————————-+——-+

| max_used_connections | 256|

+—————————-+——-+

max_used_connections / max_connections * 100% （理想值≈ 85%） 

如果max_used_connections跟max_connections相同 那么就是max_connections設置過(guò)低或者超過(guò)服務(wù)器負載上限了，低于10%則設置過(guò)大。

2)     back_log

MySQL能暫存的連接數量。當主要MySQL線(xiàn)程在一個(gè)很短時(shí)間內得到非常多的連接請求，這就起作用。如果MySQL的連接數據達到 max_connections時(shí)，新來(lái)的請求將會(huì )被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過(guò) back_log，將不被授予連接資源。

back_log值指出在MySQL暫時(shí)停止回答新請求之前的短時(shí)間內有多少個(gè)請求可以被存在堆棧中。只有如果期望在一個(gè)短時(shí)間內有很多連接，你需要增加它，換句話(huà)說(shuō)，這值對到來(lái)的TCP/IP連接的偵聽(tīng)隊列的大小。

當觀(guān)察你主機進(jìn)程列表（mysql> show full processlist），發(fā)現大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進(jìn)程時(shí)，就要加大back_log 的值了。

默認數值是50，可調優(yōu)為128，對系統設置范圍為小于512的整數。 

3)     interactive_timeout

一個(gè)交互連接在被服務(wù)器在關(guān)閉前等待行動(dòng)的秒數。一個(gè)交互的客戶(hù)被定義為對mysql_real_connect()使用CLIENT_INTERACTIVE 選項的客戶(hù)。 

默認數值是28800，可調優(yōu)為7200。 

2.   緩沖區變量

全局緩沖：

4)     key_buffer_size

key_buffer_size指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。通過(guò)檢查狀態(tài)值 Key_read_requests和Key_reads，可以知道key_buffer_size設置是否合理。比例key_reads / key_read_requests應該盡可能的低，至少是1:100，1:1000更好（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。

key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時(shí)磁盤(pán)表是MyISAM表，也要使用該值?？梢允褂脵z查狀態(tài)值created_tmp_disk_tables得知詳情。

舉例如下：

mysql> show variables like ‘key_buffer_size‘;

+——————-+————+

| Variable_name | Value      |

+———————+————+

| key_buffer_size | 536870912 |

+———— ———-+————+

key_buffer_size為512MB，我們再看一下key_buffer_size的使用情況：

mysql> show global status like ‘key_read%‘;

+————————+————-+

| Variable_name　  | Value    |

+————————+————-+

| Key_read_requests| 27813678764 |

| Key_reads　　　|  6798830      |

+————————+————-+

一共有27813678764個(gè)索引讀取請求，有6798830個(gè)請求在內存中沒(méi)有找到直接從硬盤(pán)讀取索引，計算索引未命中緩存的概率：

key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%，設置在1/1000左右較好

默認配置數值是8388600(8M)，主機有4GB內存，可以調優(yōu)值為268435456(256MB)。

5)     query_cache_size

使用查詢(xún)緩沖，MySQL將查詢(xún)結果存放在緩沖區中，今后對于同樣的SELECT語(yǔ)句（區分大小寫(xiě)），將直接從緩沖區中讀取結果。

通過(guò)檢查狀態(tài)值Qcache_*，可以知道query_cache_size設置是否合理（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現緩沖不夠的情況，如果Qcache_hits的值也 非常大，則表明查詢(xún)緩沖使用非常頻繁，此時(shí)需要增加緩沖大??；如果Qcache_hits的值不大，則表明你的查詢(xún)重復率很低，這種情況下使用查詢(xún)緩沖反 而會(huì )影響效率，那么可以考慮不用查詢(xún)緩沖。此外，在SELECT語(yǔ)句中加入SQL_NO_CACHE可以明確表示不使用查詢(xún)緩沖。

與查詢(xún)緩沖有關(guān)的參數還有query_cache_type、query_cache_limit、query_cache_min_res_unit。

query_cache_type指定是否使用查詢(xún)緩沖，可以設置為0、1、2，該變量是SESSION級的變量。

query_cache_limit指定單個(gè)查詢(xún)能夠使用的緩沖區大小，缺省為1M。

query_cache_min_res_unit是在4.1版本以后引入的，它指定分配緩沖區空間的最小單位，缺省為4K。檢查狀態(tài)值 Qcache_free_blocks，如果該值非常大，則表明緩沖區中碎片很多，這就表明查詢(xún)結果都比較小，此時(shí)需要減小 query_cache_min_res_unit。

舉例如下：

mysql> show global status like ‘qcache%‘;

+——————————-+—————–+

| Variable_name                  | Value　       |

+——————————-+—————–+

| Qcache_free_blocks　       | 22756　      |

| Qcache_free_memory　    | 76764704    |

| Qcache_hits　　　　　      | 213028692 |

| Qcache_inserts　　　　     | 208894227   |

| Qcache_lowmem_prunes   | 4010916      |

| Qcache_not_cached　| 13385031    |

| Qcache_queries_in_cache | 43560　|

| Qcache_total_blocks          | 111212　     |

+——————————-+—————–+

mysql> show variables like ‘query_cache%‘;

+————————————–+————–+

| Variable_name　　　　　       | Value　     |

+————————————–+———–+

| query_cache_limit　　　　　    | 2097152     |

| query_cache_min_res_unit　     | 4096　　  |

| query_cache_size　　　　　    | 203423744 |

| query_cache_type　　　　　   | ON　          |

| query_cache_wlock_invalidate | OFF　  |

+————————————–+—————+

查詢(xún)緩存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%

如果查詢(xún)緩存碎片率超過(guò)20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢(xún)都是小數據量的話(huà)。

查詢(xún)緩存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查詢(xún)緩存利用率在25%以下的話(huà)說(shuō)明query_cache_size設置的過(guò)大，可適當減??；查詢(xún)緩存利用率在80％以上而且Qcache_lowmem_prunes > 50的話(huà)說(shuō)明query_cache_size可能有點(diǎn)小，要不就是碎片太多。

查詢(xún)緩存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

示例服務(wù)器查詢(xún)緩存碎片率＝20.46％，查詢(xún)緩存利用率＝62.26％，查詢(xún)緩存命中率＝1.94％，命中率很差，可能寫(xiě)操作比較頻繁吧，而且可能有些碎片。

每個(gè)連接的緩沖

6)    record_buffer_size

每個(gè)進(jìn)行一個(gè)順序掃描的線(xiàn)程為其掃描的每張表分配這個(gè)大小的一個(gè)緩沖區。如果你做很多順序掃描，你可能想要增加該值。

默認數值是131072(128K)，可改為16773120 (16M)

7)     read_rnd_buffer_size

隨機讀緩沖區大小。當按任意順序讀取行時(shí)(例如，按照排序順序)，將分配一個(gè)隨機讀緩存區。進(jìn)行排序查詢(xún)時(shí)，MySQL會(huì )首先掃描一遍該緩沖，以避 免磁盤(pán)搜索，提高查詢(xún)速度，如果需要排序大量數據，可適當調高該值。但MySQL會(huì )為每個(gè)客戶(hù)連接發(fā)放該緩沖空間，所以應盡量適當設置該值，以避免內存開(kāi) 銷(xiāo)過(guò)大。

一般可設置為16M 

8)     sort_buffer_size

每個(gè)需要進(jìn)行排序的線(xiàn)程分配該大小的一個(gè)緩沖區。增加這值加速ORDER BY或GROUP BY操作。

默認數值是2097144(2M)，可改為16777208 (16M)。

9)     join_buffer_size

聯(lián)合查詢(xún)操作所能使用的緩沖區大小

record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size為每個(gè)線(xiàn)程獨占，也就是說(shuō)，如果有100個(gè)線(xiàn)程連接，則占用為16M*100

10)  table_cache

表高速緩存的大小。每當MySQL訪(fǎng)問(wèn)一個(gè)表時(shí)，如果在表緩沖區中還有空間，該表就被打開(kāi)并放入其中，這樣可以更快地訪(fǎng)問(wèn)表內容。通過(guò)檢查峰值時(shí)間的狀態(tài)值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如 果你發(fā)現open_tables等于table_cache，并且opened_tables在不斷增長(cháng)，那么你就需要增加table_cache的值了 （上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設置成很大的值。如果設置得太高，可能會(huì )造成文件描述符不足，從而造成性能 不穩定或者連接失敗。

1G內存機器，推薦值是128－256。內存在4GB左右的服務(wù)器該參數可設置為256M或384M。

11)  max_heap_table_size

用戶(hù)可以創(chuàng )建的內存表(memory table)的大小。這個(gè)值用來(lái)計算內存表的最大行數值。這個(gè)變量支持動(dòng)態(tài)改變，即set @max_heap_table_size=#

這個(gè)變量和tmp_table_size一起限制了內部?jì)却姹淼拇笮?。如果某個(gè)內部heap（堆積）表大小超過(guò)tmp_table_size，MySQL可以根據需要自動(dòng)將內存中的heap表改為基于硬盤(pán)的MyISAM表。

12)  tmp_table_size

通過(guò)設置tmp_table_size選項來(lái)增加一張臨時(shí)表的大小，例如做高級GROUP BY操作生成的臨時(shí)表。如果調高該值，MySQL同時(shí)將增加heap表的大小，可達到提高聯(lián)接查詢(xún)速度的效果，建議盡量?jì)?yōu)化查詢(xún)，要確保查詢(xún)過(guò)程中生成的臨時(shí)表在內存中，避免臨時(shí)表過(guò)大導致生成基于硬盤(pán)的MyISAM表。

mysql> show global status like ‘created_tmp%‘;

+——————————–+———+

| Variable_name　　           | Value　|

+———————————-+———+

| Created_tmp_disk_tables | 21197  |

| Created_tmp_files　　　| 58　　|

| Created_tmp_tables　　| 1771587 |

+——————————–+———–+

每次創(chuàng )建臨時(shí)表，Created_tmp_tables增加，如果臨時(shí)表大小超過(guò)tmp_table_size，則是在磁盤(pán)上創(chuàng )建臨時(shí) 表，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%，應該相當好了

默認為16M，可調到64-256最佳，線(xiàn)程獨占，太大可能內存不夠I/O堵塞

13)  thread_cache_size

可以復用的保存在中的線(xiàn)程的數量。如果有，新的線(xiàn)程從緩存中取得，當斷開(kāi)連接的時(shí)候如果有空間，客戶(hù)的線(xiàn)置在緩存中。如果有很多新的線(xiàn)程，為了提高性能可以這個(gè)變量值。

通過(guò)比較 Connections和Threads_created狀態(tài)的變量，可以看到這個(gè)變量的作用。

默認值為110，可調優(yōu)為80。 

14)  thread_concurrency

推薦設置為服務(wù)器 CPU核數的2倍，例如雙核的CPU, 那么thread_concurrency的應該為4；2個(gè)雙核的cpu, thread_concurrency的值應為8。默認為8

15)  wait_timeout

指定一個(gè)請求的最大連接時(shí)間，對于4GB左右內存的服務(wù)器可以設置為5-10。

3.    配置InnoDB的幾個(gè)變量

innodb_buffer_pool_size

對于InnoDB表來(lái)說(shuō)，innodb_buffer_pool_size的作用就相當于key_buffer_size對于MyISAM表的作用一樣。InnoDB使用該參數指定大小的內存來(lái)緩沖數據和索引。對于單獨的MySQL數據庫服務(wù)器，最大可以把該值設置成物理內存的80%。

根據MySQL手冊，對于2G內存的機器，推薦值是1G（50%）。

innodb_flush_log_at_trx_commit

主要控制了innodb將log buffer中的數據寫(xiě)入日志文件并flush磁盤(pán)的時(shí)間點(diǎn)，取值分別為0、1、2三個(gè)。0，表示當事務(wù)提交時(shí)，不做日志寫(xiě)入操作，而是每秒鐘將log buffer中的數據寫(xiě)入日志文件并flush磁盤(pán)一次；1，則在每秒鐘或是每次事物的提交都會(huì )引起日志文件寫(xiě)入、flush磁盤(pán)的操作，確保了事務(wù)的 ACID；設置為2，每次事務(wù)提交引起寫(xiě)入日志文件的動(dòng)作，但每秒鐘完成一次flush磁盤(pán)操作。

實(shí)際測試發(fā)現，該值對插入數據的速度影響非常大，設置為2時(shí)插入10000條記錄只需要2秒，設置為0時(shí)只需要1秒，而設置為1時(shí)則需要229秒。因此，MySQL手冊也建議盡量將插入操作合并成一個(gè)事務(wù)，這樣可以大幅提高速度。

根據MySQL手冊，在允許丟失最近部分事務(wù)的危險的前提下，可以把該值設為0或2。

innodb_log_buffer_size

log緩存大小，一般為1-8M，默認為1M，對于較大的事務(wù)，可以增大緩存大小。

可設置為4M或8M。

innodb_additional_mem_pool_size

該參數指定InnoDB用來(lái)存儲數據字典和其他內部數據結構的內存池大小。缺省值是1M。通常不用太大，只要夠用就行，應該與表結構的復雜度有關(guān)系。如果不夠用，MySQL會(huì )在錯誤日志中寫(xiě)入一條警告信息。

根據MySQL手冊，對于2G內存的機器，推薦值是20M，可適當增加。

innodb_thread_concurrency=8

推薦設置為 2*(NumCPUs+NumDisks)，默認一般為8

MySQL 5.6相比于前代GA版本性能提升顯著(zhù)，但默認緩存設置對于小型站點(diǎn)并不合理。通過(guò)修改my.ini文件中的performance_schema_max_table_instances參數，能夠有效降低內存占用。

以下是5.6默認的設置

performance_schema_max_table_instances 12500

table_definition_cache 1400

table_open_cache 2000

可以調成，或者在小點(diǎn)都可以。

performance_schema_max_table_instances=600

table_definition_cache=400

table_open_cache=256

performance_schema_max_table_instances

The maximum number of instrumented table objects  檢測的表對象的最大數目。

table_definition_cache
The number of table definitions (from .frm files) that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. The minimum and default values are both 400. 
緩存frm文件

table_open_cache
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. 

table_open_cache指的是緩存數據文件的描述符(Linux/Unix)相關(guān)信息

這個(gè)很重要啊，之前mount個(gè)單獨的文件，數據庫一直不成功，原來(lái)是這個(gè)在作怪啊。

chcon -R -t mysqld_db_t /home/myusqldata

網(wǎng)上的太多了，都不知道哪個(gè)是原創(chuàng )了：我找的是http://hi.baidu.com/houligen/item/7b4883c3ad1299360931c6fe

mysql> show variables;

一、慢查詢(xún)

mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | ON |
| slow_launch_time | 2     |
+------------------+-------+

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name    | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 4148 |
+---------------------+-------+

配置中打開(kāi)了記錄慢查詢(xún)，執行時(shí)間超過(guò)2秒的即為慢查詢(xún)，系統顯示有4148個(gè)慢查詢(xún)，你可以分析慢查詢(xún)日志，找出有問(wèn)題的SQL語(yǔ)句，慢查詢(xún)時(shí)間不宜設置過(guò)長(cháng)，否則意義不大，最好在5秒以?xún)?，如果你需要微秒級別的慢查詢(xún)，可以考慮給MySQL打補?。篽ttp://www.percona.com/docs/wiki/release:start，記得找對應的版本。
打開(kāi)慢查詢(xún)日志可能會(huì )對系統性能有一點(diǎn)點(diǎn)影響，如果你的MySQL是主－從結構，可以考慮打開(kāi)其中一臺從服務(wù)器的慢查詢(xún)日志，這樣既可以監控慢查詢(xún)，對系統性能影響又小。
二、連接數
經(jīng)常會(huì )遇見(jiàn)”MySQL: ERROR 1040: Too manyconnections”的情況，一種是訪(fǎng)問(wèn)量確實(shí)很高，MySQL服務(wù)器抗不住，這個(gè)時(shí)候就要考慮增加從服務(wù)器分散讀壓力，另外一種情況是MySQL配置文件中max_connections值過(guò)?。?/p>

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

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

mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 245 |
+----------------------+-------+

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

Max_used_connections / max_connections   * 100% ≈ 85%

最大連接數占上限連接數的85％左右，如果發(fā)現比例在10%以下，MySQL服務(wù)器連接數上限設置的過(guò)高了。
三、Key_buffer_size
key_buffer_size是對MyISAM表性能影響最大的一個(gè)參數，下面一臺以MyISAM為主要存儲引擎服務(wù)器的配置：

mysql> show variables like 'key_buffer_size';
+-----------------+------------+
| Variable_name | Value    |
+-----------------+------------+
| key_buffer_size | 536870912 |
+-----------------+------------+

分配了512MB內存給key_buffer_size，我們再看一下key_buffer_size的使用情況：

mysql> show global status like 'key_read%';
+------------------------+-------------+
| Variable_name       | Value    |
+------------------------+-------------+
| Key_read_requests    | 27813678764 |
| Key_reads              | 6798830     |
+------------------------+-------------+

一共有27813678764個(gè)索引讀取請求，有6798830個(gè)請求在內存中沒(méi)有找到直接從硬盤(pán)讀取索引，計算索引未命中緩存的概率：

key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%

比 如上面的數據，key_cache_miss_rate為0.0244%，4000個(gè)索引讀取請求才有一個(gè)直接讀硬盤(pán)，已經(jīng)很BT 了，key_cache_miss_rate在0.1%以下都很好（每1000個(gè)請求有一個(gè)直接讀硬盤(pán)），如果key_cache_miss_rate在 0.01%以下的話(huà)，key_buffer_size分配的過(guò)多，可以適當減少。
MySQL服務(wù)器還提供了key_blocks_*參數：

mysql> show global status like 'key_blocks_u%';
+------------------------+-------------+
| Variable_name       | Value    |
+------------------------+-------------+
| Key_blocks_unused    | 0           |
| Key_blocks_used        | 413543    |
+------------------------+-------------+

Key_blocks_unused 表示未使用的緩存簇(blocks)數，Key_blocks_used表示曾經(jīng)用到的最大的blocks數，比如這臺服務(wù)器，所有的緩存都用到了，要么 增加key_buffer_size，要么就是過(guò)渡索引了，把緩存占滿(mǎn)了。比較理想的設置：

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

四、臨時(shí)表

mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name           | Value |
+-------------------------+---------+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files    | 58    |
| Created_tmp_tables    | 1771587 |
+-------------------------+---------+

每次創(chuàng )建臨時(shí)表，Created_tmp_tables增加，如果是在磁盤(pán)上創(chuàng )建臨時(shí)表，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%，應該相當好了。我們再看一下MySQL服務(wù)器對臨時(shí)表的配置：

mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+-----------+
| Variable_name    | Value     |
+---------------------+-----------+
| max_heap_table_size | 268435456 |
| tmp_table_size    | 536870912 |
+---------------------+-----------+

只有256MB以下的臨時(shí)表才能全部放內存，超過(guò)的就會(huì )用到硬盤(pán)臨時(shí)表。
五、Open Table情況

mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 919 |
| Opened_tables | 1951   |
+---------------+-------+

Open_tables 表示打開(kāi)表的數量，Opened_tables表示打開(kāi)過(guò)的表數量，如果Opened_tables數量過(guò)大，說(shuō)明配置中 table_cache(5.1.3之后這個(gè)值叫做table_open_cache)值可能太小，我們查詢(xún)一下服務(wù)器table_cache值：

mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 2048   |

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

比較合適的值為：

Open_tables / Opened_tables   * 100% >= 85%
Open_tables / table_cache * 100% <= 95%

六、進(jìn)程使用情況

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2     |
| Threads_created | 570 |
| Threads_running | 1     |
+-------------------+-------+

如 果我們在MySQL服務(wù)器配置文件中設置了thread_cache_size，當客戶(hù)端斷開(kāi)之后，服務(wù)器處理此客戶(hù)的線(xiàn)程將會(huì )緩存起來(lái)以響應下一個(gè)客戶(hù) 而不是銷(xiāo)毀（前提是緩存數未達上限）。Threads_created表示創(chuàng )建過(guò)的線(xiàn)程數，如果發(fā)現Threads_created值過(guò)大的話(huà)，表明 MySQL服務(wù)器一直在創(chuàng )建線(xiàn)程，這也是比較耗資源，可以適當增加配置文件中thread_cache_size值，查詢(xún)服務(wù)器 thread_cache_size配置：

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+

示例中的服務(wù)器還是挺健康的。
七、查詢(xún)緩存(query cache)

mysql> show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks    | 22756     |
| Qcache_free_memory    | 76764704   |
| Qcache_hits          | 213028692 |
| Qcache_inserts       | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| Qcache_not_cached    | 13385031   |
| Qcache_queries_in_cache | 43560     |
| Qcache_total_blocks     | 111212 |
+-------------------------+-----------+

MySQL查詢(xún)緩存變量解釋?zhuān)?br/>

Qcache_free_blocks：緩存中相鄰內存塊的個(gè)數。數目大說(shuō)明可能有碎片。FLUSH QUERY CACHE會(huì )對緩存中的碎片進(jìn)行整理，從而得到一個(gè)空閑塊。
Qcache_free_memory：緩存中的空閑內存。
Qcache_hits：每次查詢(xún)在緩存中命中時(shí)就增大
Qcache_inserts：每次插入一個(gè)查詢(xún)時(shí)就增大。命中次數除以插入次數就是不中比率。
Qcache_lowmem_prunes： 緩存出現內存不足并且必須要進(jìn)行清理以便為更多查詢(xún)提供空間的次數。這個(gè)數字最好長(cháng)時(shí)間來(lái)看；如果這個(gè)數字在不斷增長(cháng)，就表示可能碎片非常嚴重，或者內存 很少。（上面的 free_blocks和free_memory可以告訴您屬于哪種情況）
Qcache_not_cached：不適合進(jìn)行緩存的查詢(xún)的數量，通常是由于這些查詢(xún)不是 SELECT 語(yǔ)句或者用了now()之類(lèi)的函數。
Qcache_queries_in_cache：當前緩存的查詢(xún)（和響應）的數量。
Qcache_total_blocks：緩存中塊的數量。

我們再查詢(xún)一下服務(wù)器關(guān)于query_cache的配置：

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name             | Value     |
+------------------------------+-----------+
| query_cache_limit          | 2097152 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size          | 203423744 |
| query_cache_type          | ON        |
| query_cache_wlock_invalidate | OFF    |
+------------------------------+-----------+

各字段的解釋?zhuān)?br/>

query_cache_limit：超過(guò)此大小的查詢(xún)將不緩存
query_cache_min_res_unit：緩存塊的最小大小
query_cache_size：查詢(xún)緩存大小
query_cache_type：緩存類(lèi)型，決定緩存什么樣的查詢(xún)，示例中表示不緩存 select sql_no_cache 查詢(xún)
query_cache_wlock_invalidate：當有其他客戶(hù)端正在對MyISAM表進(jìn)行寫(xiě)操作時(shí)，如果查詢(xún)在query cache中，是否返回cache結果還是等寫(xiě)操作完成再讀表獲取結果。

query_cache_min_res_unit的配置是一柄”雙刃劍”，默認是4KB，設置值大對大數據查詢(xún)有好處，但如果你的查詢(xún)都是小數據查詢(xún)，就容易造成內存碎片和浪費。

查詢(xún)緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查詢(xún)緩存碎片率超過(guò)20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢(xún)都是小數據量的話(huà)。

查詢(xún)緩存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

查詢(xún)緩存利用率在25%以下的話(huà)說(shuō)明query_cache_size設置的過(guò)大，可適當減??；查詢(xún)緩存利用率在80％以上而且Qcache_lowmem_prunes > 50的話(huà)說(shuō)明query_cache_size可能有點(diǎn)小，要不就是碎片太多。

查詢(xún)緩存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

示例服務(wù)器 查詢(xún)緩存碎片率 ＝ 20.46％，查詢(xún)緩存利用率 ＝ 62.26％，查詢(xún)緩存命中率 ＝ 1.94％，命中率很差，可能寫(xiě)操作比較頻繁吧，而且可能有些碎片。
八、排序使用情況

mysql> show global status like 'sort%';
+-------------------+------------+
| Variable_name     | Value    |
+-------------------+------------+
| Sort_merge_passes | 29       |
| Sort_range        | 37432840 |
| Sort_rows       | 9178691532 |
| Sort_scan       | 1860569 |
+-------------------+------------+

Sort_merge_passes 包括兩步。MySQL 首先會(huì )嘗試在內存中做排序，使用的內存大小由系統變量Sort_buffer_size 決定，如果它的大小不夠把所有的記錄都讀到內存中，MySQL 就會(huì )把每次在內存中排序的結果存到臨時(shí)文件中，等MySQL 找到所有記錄之后，再把臨時(shí)文件中的記錄做一次排序。這再次排序就會(huì )增加 Sort_merge_passes。實(shí)際上，MySQL會(huì )用另一個(gè)臨時(shí)文件來(lái)存再次排序的結果，所以通常會(huì )看到 Sort_merge_passes增加的數值是建臨時(shí)文件數的兩倍。因為用到了臨時(shí)文件，所以速度可能會(huì )比較慢，增加 Sort_buffer_size 會(huì )減少Sort_merge_passes 和 創(chuàng )建臨時(shí)文件的次數。但盲目的增加 Sort_buffer_size 并不一定能提高速度，
另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對排序的操作也有一點(diǎn)的好處，
九、文件打開(kāi)數(open_files)

mysql> show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 1410   |
+---------------+-------+

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 4590   |
+------------------+-------+

比較合適的設置：Open_files / open_files_limit * 100% <= 75％
十、表鎖情況

mysql> show global status like 'table_locks%';
+-----------------------+-----------+
| Variable_name       | Value     |
+-----------------------+-----------+
| Table_locks_immediate | 490206328 |
| Table_locks_waited | 2084912 |
+-----------------------+-----------+

Table_locks_immediate 表示立即釋放表鎖數，Table_locks_waited表示需要等待的表鎖數，如果Table_locks_immediate / Table_locks_waited >5000，最好采用InnoDB引擎，因為InnoDB是行鎖而MyISAM是表鎖，對于高并發(fā)寫(xiě)入的應用InnoDB效果會(huì )好些。示例中的服務(wù) 器Table_locks_immediate / Table_locks_waited ＝ 235，MyISAM就足夠了。
十一、表掃描情況

mysql> show global status like 'handler_read%';
+-----------------------+-------------+
| Variable_name       | Value    |
+-----------------------+-------------+
| Handler_read_first | 5803750     |
| Handler_read_key    | 6049319850   |
| Handler_read_next     | 94440908210 |
| Handler_read_prev     | 34822001724 |
| Handler_read_rnd    | 405482605 |
| Handler_read_rnd_next | 18912877839 |
+-----------------------+-------------+

各字段解釋參見(jiàn),調出服務(wù)器完成的查詢(xún)請求次數：

mysql> show global status like 'com_select';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| Com_select | 222693559 |
+---------------+-----------+

計算表掃描率：

表掃描率 ＝ Handler_read_rnd_next / Com_select

如果表掃描率超過(guò)4000，說(shuō)明進(jìn)行了太多表掃描，很有可能索引沒(méi)有建好，增加read_buffer_size值會(huì )有一些好處，但最好不要超過(guò)8MB。