Mysql常用的配置參數(shù)的整理

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

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(這里主要是復(fù)制功能，也就是主從,提前配置好，后面講主從配置)

#二進(jìn)制日志

log-bin

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

sync_binlog=1

sync_relay_log=1

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

relay-log-info-repository=TABLE

master-info-repository=TABLE

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

expire_logs_days=7

#行復(fù)制

binlog_format=ROW

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

transaction-isolation=READ-COMMITTED

#cache

#內(nèi)部?jī)?nèi)存臨時(shí)表的最大值

tmp_table_size=512M

character-set-server=utf8

collation-server=utf8_general_ci

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

skip-external-locking

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

back_log=1024

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

key_buffer_size=1024M

#這條指令限定用于每個(gè)數(shù)據(jù)庫(kù)線程的棧大小

thread_stack=256k

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

read_buffer_size=8M

#線程緩存

thread_cache_size=64

#查詢緩存大小

query_cache_size=128M

#內(nèi)部?jī)?nèi)存臨時(shí)表的最大值,每個(gè)線程都要分配

max_heap_table_size=256M

#將查詢結(jié)果放入查詢緩存中

query_cache_type=1

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

binlog_cache_size = 2M

#同樣是緩存表大小

table_open_cache=128

#緩存線程

thread_cache=1024

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

thread_concurrency=8

wait_timeout=30

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

join_buffer_size = 1024M

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

sort_buffer_size=8M

#隨機(jī)讀取數(shù)據(jù)緩沖區(qū)使用內(nèi)存

read_rnd_buffer_size = 8M

#connect

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

max-connect-errors=100000

#連接數(shù)

max-connections=1000

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

explicit_defaults_for_timestamp=true

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

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

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

1.   連接請(qǐng)求的變量：

1)     max_connections

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

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

show variables like ‘max_connections’ 最大連接數(shù)

show  status like ‘max_used_connections’響應(yīng)的連接數(shù)

如下：

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設(shè)置過(guò)低或者超過(guò)服務(wù)器負(fù)載上限了，低于10%則設(shè)置過(guò)大。

2)     back_log

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

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

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

默認(rèn)數(shù)值是50，可調(diào)優(yōu)為128，對(duì)系統(tǒng)設(shè)置范圍為小于512的整數(shù)。 

3)     interactive_timeout

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

默認(rèn)數(shù)值是28800，可調(diào)優(yōu)為7200。 

2.   緩沖區(qū)變量

全局緩沖：

4)     key_buffer_size

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

key_buffer_size只對(duì)MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時(shí)磁盤表是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，我們?cè)倏匆幌耴ey_buffer_size的使用情況：

mysql> show global status like ‘key_read%‘;

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

| Variable_name　  | Value    |

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

| Key_read_requests| 27813678764 |

| Key_reads　　　|  6798830      |

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

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

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

默認(rèn)配置數(shù)值是8388600(8M)，主機(jī)有4GB內(nèi)存，可以調(diào)優(yōu)值為268435456(256MB)。

5)     query_cache_size

使用查詢緩沖，MySQL將查詢結(jié)果存放在緩沖區(qū)中，今后對(duì)于同樣的SELECT語(yǔ)句（區(qū)分大小寫），將直接從緩沖區(qū)中讀取結(jié)果。

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

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

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

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

query_cache_min_res_unit是在4.1版本以后引入的，它指定分配緩沖區(qū)空間的最小單位，缺省為4K。檢查狀態(tài)值 Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多，這就表明查詢結(jié)果都比較小，此時(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　  |

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

查詢緩存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%

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

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

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

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

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

每個(gè)連接的緩沖

6)    record_buffer_size

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

默認(rèn)數(shù)值是131072(128K)，可改為16773120 (16M)

7)     read_rnd_buffer_size

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

一般可設(shè)置為16M 

8)     sort_buffer_size

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

默認(rèn)數(shù)值是2097144(2M)，可改為16777208 (16M)。

9)     join_buffer_size

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

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

10)  table_cache

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

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

11)  max_heap_table_size

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

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

12)  tmp_table_size

通過(guò)設(shè)置tmp_table_size選項(xiàng)來(lái)增加一張臨時(shí)表的大小，例如做高級(jí)GROUP BY操作生成的臨時(shí)表。如果調(diào)高該值，MySQL同時(shí)將增加heap表的大小，可達(dá)到提高聯(lián)接查詢速度的效果，建議盡量?jī)?yōu)化查詢，要確保查詢過(guò)程中生成的臨時(shí)表在內(nèi)存中，避免臨時(shí)表過(guò)大導(dǎo)致生成基于硬盤的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，則是在磁盤上創(chuàng)建臨時(shí) 表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務(wù)創(chuàng)建的臨時(shí)文件文件數(shù)，比較理想的配 置是：

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

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

13)  thread_cache_size

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

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

默認(rèn)值為110，可調(diào)優(yōu)為80。 

14)  thread_concurrency

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

15)  wait_timeout

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

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

innodb_buffer_pool_size

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

根據(jù)MySQL手冊(cè)，對(duì)于2G內(nèi)存的機(jī)器，推薦值是1G（50%）。

innodb_flush_log_at_trx_commit

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

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

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

innodb_log_buffer_size

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

可設(shè)置為4M或8M。

innodb_additional_mem_pool_size

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

根據(jù)MySQL手冊(cè)，對(duì)于2G內(nèi)存的機(jī)器，推薦值是20M，可適當(dāng)增加。

innodb_thread_concurrency=8

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

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

以下是5.6默認(rèn)的設(shè)置

performance_schema_max_table_instances 12500

table_definition_cache 1400

table_open_cache 2000

可以調(diào)成，或者在小點(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  檢測(cè)的表對(duì)象的最大數(shù)目。

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指的是緩存數(shù)據(jù)文件的描述符(Linux/Unix)相關(guān)信息

這個(gè)很重要啊，之前mount個(gè)單獨(dú)的文件，數(shù)據(jù)庫(kù)一直不成功，原來(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;

一、慢查詢

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)了記錄慢查詢，執(zhí)行時(shí)間超過(guò)2秒的即為慢查詢，系統(tǒng)顯示有4148個(gè)慢查詢，你可以分析慢查詢?nèi)罩?，找出有?wèn)題的SQL語(yǔ)句，慢查詢時(shí)間不宜設(shè)置過(guò)長(zhǎng)，否則意義不大，最好在5秒以內(nèi)，如果你需要微秒級(jí)別的慢查詢，可以考慮給MySQL打補(bǔ)?。篽ttp://www.percona.com/docs/wiki/release:start，記得找對(duì)應(yīng)的版本。
打開(kāi)慢查詢?nèi)罩究赡軙?huì)對(duì)系統(tǒng)性能有一點(diǎn)點(diǎn)影響，如果你的MySQL是主－從結(jié)構(gòu)，可以考慮打開(kāi)其中一臺(tái)從服務(wù)器的慢查詢?nèi)罩荆@樣既可以監(jiān)控慢查詢，對(duì)系統(tǒng)性能影響又小。
二、連接數(shù)
經(jīng)常會(huì)遇見(jiàn)”MySQL: ERROR 1040: Too manyconnections”的情況，一種是訪問(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';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 245 |
+----------------------+-------+

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ù)上限設(shè)置的過(guò)高了。
三、Key_buffer_size
key_buffer_size是對(duì)MyISAM表性能影響最大的一個(gè)參數(shù)，下面一臺(tái)以MyISAM為主要存儲(chǔ)引擎服務(wù)器的配置：

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

分配了512MB內(nèi)存給key_buffer_size，我們?cè)倏匆幌耴ey_buffer_size的使用情況：

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

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

key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%

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

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

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

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增加，如果是在磁盤上創(chuàng)建臨時(shí)表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務(wù)創(chuàng)建的臨時(shí)文件文件數(shù)，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

比如上面的服務(wù)器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 1.20%，應(yīng)該相當(dāng)好了。我們?cè)倏匆幌翸ySQL服務(wù)器對(duì)臨時(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í)表才能全部放內(nèi)存，超過(guò)的就會(huì)用到硬盤臨時(shí)表。
五、Open Table情況

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

Open_tables 表示打開(kāi)表的數(shù)量，Opened_tables表示打開(kāi)過(guò)的表數(shù)量，如果Opened_tables數(shù)量過(guò)大，說(shuō)明配置中 table_cache(5.1.3之后這個(gè)值叫做table_open_cache)值可能太小，我們查詢一下服務(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     |
+-------------------+-------+

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

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

示例中的服務(wù)器還是挺健康的。
七、查詢緩存(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查詢緩存變量解釋：

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

我們?cè)俨樵円幌路?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    |
+------------------------------+-----------+

各字段的解釋：

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

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

查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

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

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

查詢緩存利用率在25%以下的話說(shuō)明query_cache_size設(shè)置的過(guò)大，可適當(dāng)減?。徊樵兙彺胬寐试?0％以上而且Qcache_lowmem_prunes > 50的話說(shuō)明query_cache_size可能有點(diǎn)小，要不就是碎片太多。

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

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

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

比較合適的設(shè)置：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 表示立即釋放表鎖數(shù)，Table_locks_waited表示需要等待的表鎖數(shù)，如果Table_locks_immediate / Table_locks_waited >5000，最好采用InnoDB引擎，因?yàn)镮nnoDB是行鎖而MyISAM是表鎖，對(duì)于高并發(fā)寫入的應(yīng)用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),調(diào)出服務(wù)器完成的查詢請(qǐng)求次數(shù)：

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

計(jì)算表掃描率：

表掃描率 ＝ Handler_read_rnd_next / Com_select

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

到此，關(guān)于“Mysql常用的配置參數(shù)的整理”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！