這篇文章主要介紹“MySQL常用的配置參數(shù)的整理”，在日常操作中，相信很多人在Mysql常用的配置參數(shù)的整理問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Mysql常用的配置參數(shù)的整理”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
成都創(chuàng)新互聯(lián)是專業(yè)的原陽網(wǎng)站建設(shè)公司，原陽接單;提供成都網(wǎng)站設(shè)計、成都網(wǎng)站制作,網(wǎng)頁設(shè)計,網(wǎng)站設(shè)計,建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進行原陽網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團隊,希望更多企業(yè)前來合作!
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ù)制功能，也就是主從,提前配置好，后面講主從配置)
#二進制日志
log-bin
#為了在最大程序上保證復(fù)制的InnoDB事務(wù)持久性和一致性
sync_binlog=1
sync_relay_log=1
#啟用此兩項,可用于實現(xiàn)在崩潰時保證二進制及從服務(wù)器安全的功能
relay-log-info-repository=TABLE
master-info-repository=TABLE
#設(shè)置清除日志時間
expire_logs_days=7
#行復(fù)制
binlog_format=ROW
#mysql數(shù)據(jù)庫事務(wù)隔離級別有四種(READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SERIALIZABLE)
transaction-isolation=READ-COMMITTED
#cache
#內(nèi)部內(nèi)存臨時表的最大值
tmp_table_size=512M
character-set-server=utf8
collation-server=utf8_general_ci
#即跳過外部鎖定
skip-external-locking
#MySQL能暫存的連接數(shù)量(根據(jù)實際設(shè)置)
back_log=1024
#指定索引緩沖區(qū)的大小,只對MyISAM表起作用,這里寫上也沒有關(guān)系
key_buffer_size=1024M
#這條指令限定用于每個數(shù)據(jù)庫線程的棧大小
thread_stack=256k
#當一個查詢不斷地掃描某一個表,MySQL會為它分配一段內(nèi)存緩沖區(qū)
read_buffer_size=8M
#線程緩存
thread_cache_size=64
#查詢緩存大小
query_cache_size=128M
#內(nèi)部內(nèi)存臨時表的最大值,每個線程都要分配
max_heap_table_size=256M
#將查詢結(jié)果放入查詢緩存中
query_cache_type=1
#代表在事務(wù)過程中容納二進制日志SQL語句的緩存大小
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
#是一個connection級參數(shù),在每個connection第一次需要使用這個buffer的時候,一次性分配設(shè)置的內(nèi)存
sort_buffer_size=8M
#隨機讀取數(shù)據(jù)緩沖區(qū)使用內(nèi)存
read_rnd_buffer_size = 8M
#connect
#是一個MySQL中與安全有關(guān)的計數(shù)器值,它負責阻止過多嘗試失敗的客戶端以防止暴力破解密碼
max-connect-errors=100000
#連接數(shù)
max-connections=1000
#開啟查詢緩存
explicit_defaults_for_timestamp=true
#mysql服務(wù)器能夠工作在不同的模式下,并能針對不同的客戶端以不同的方式應(yīng)用這些模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
下面列出了對性能優(yōu)化影響較大的主要變量，主要分為連接請求的變量和緩沖區(qū)變量。
1. 連接請求的變量：
MySQL的最大連接數(shù)，增加該值增加mysqld 要求的文件描述符的數(shù)量。如果服務(wù)器的并發(fā)連接請求量比較大，建議調(diào)高此值，以增加并行連接數(shù)量，當然這建立在機器能支撐的情況下，因為如果連接數(shù)越多， 介于MySQL會為每個連接提供連接緩沖區(qū)，就會開銷越多的內(nèi)存，所以要適當調(diào)整該值，不能盲目提高設(shè)值。
數(shù)值過小會經(jīng)常出現(xiàn)ERROR 1040: Too many connections錯誤，可以過’conn%’通配符查看當前狀態(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è)置過低或者超過服務(wù)器負載上限了，低于10%則設(shè)置過大。
MySQL能暫存的連接數(shù)量。當主要MySQL線程在一個很短時間內(nèi)得到非常多的連接請求，這就起作用。如果MySQL的連接數(shù)據(jù)達到 max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log，如果等待連接的數(shù)量超過 back_log，將不被授予連接資源。
back_log值指出在MySQL暫時停止回答新請求之前的短時間內(nèi)有多少個請求可以被存在堆棧中。只有如果期望在一個短時間內(nèi)有很多連接，你需要增加它，換句話說，這值對到來的TCP/IP連接的偵聽隊列的大小。
當觀察你主機進程列表（mysql> show full processlist），發(fā)現(xiàn)大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進程時，就要加大back_log 的值了。
默認數(shù)值是50，可調(diào)優(yōu)為128，對系統(tǒng)設(shè)置范圍為小于512的整數(shù)。
一個交互連接在被服務(wù)器在關(guān)閉前等待行動的秒數(shù)。一個交互的客戶被定義為對mysql_real_connect()使用CLIENT_INTERACTIVE 選項的客戶。
默認數(shù)值是28800，可調(diào)優(yōu)為7200。
2. 緩沖區(qū)變量
全局緩沖：
key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態(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只對MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時磁盤表是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個索引讀取請求，有6798830個請求在內(nèi)存中沒有找到直接從硬盤讀取索引，計算索引未命中緩存的概率：
key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%，設(shè)置在1/1000左右較好
默認配置數(shù)值是8388600(8M)，主機有4GB內(nèi)存，可以調(diào)優(yōu)值為268435456(256MB)。
使用查詢緩沖，MySQL將查詢結(jié)果存放在緩沖區(qū)中，今后對于同樣的SELECT語句（區(qū)分大小寫），將直接從緩沖區(qū)中讀取結(jié)果。
通過檢查狀態(tài)值Qcache_*，可以知道query_cache_size設(shè)置是否合理（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況，如果Qcache_hits的值也 非常大，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大?。蝗绻鸔cache_hits的值不大，則表明你的查詢重復(fù)率很低，這種情況下使用查詢緩沖反 而會影響效率，那么可以考慮不用查詢緩沖。此外，在SELECT語句中加入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級的變量。
query_cache_limit指定單個查詢能夠使用的緩沖區(qū)大小，缺省為1M。
query_cache_min_res_unit是在4.1版本以后引入的，它指定分配緩沖區(qū)空間的最小單位，缺省為4K。檢查狀態(tài)值 Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多，這就表明查詢結(jié)果都比較小，此時需要減小 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%
如果查詢緩存碎片率超過20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數(shù)據(jù)量的話。
查詢緩存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查詢緩存利用率在25%以下的話說明query_cache_size設(shè)置的過大，可適當減??；查詢緩存利用率在80％以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。
查詢緩存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例服務(wù)器查詢緩存碎片率＝20.46％，查詢緩存利用率＝62.26％，查詢緩存命中率＝1.94％，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。
每個連接的緩沖
每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)。如果你做很多順序掃描，你可能想要增加該值。
默認數(shù)值是131072(128K)，可改為16773120 (16M)
隨機讀緩沖區(qū)大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區(qū)。進行排序查詢時，MySQL會首先掃描一遍該緩沖，以避 免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當調(diào)高該值。但MySQL會為每個客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當設(shè)置該值，以避免內(nèi)存開 銷過大。
一般可設(shè)置為16M
每個需要進行排序的線程分配該大小的一個緩沖區(qū)。增加這值加速ORDER BY或GROUP BY操作。
默認數(shù)值是2097144(2M)，可改為16777208 (16M)。
聯(lián)合查詢操作所能使用的緩沖區(qū)大小
record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size為每個線程獨占，也就是說，如果有100個線程連接，則占用為16M*100
表高速緩存的大小。每當MySQL訪問一個表時，如果在表緩沖區(qū)中還有空間，該表就被打開并放入其中，這樣可以更快地訪問表內(nèi)容。通過檢查峰值時間的狀態(tài)值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如 果你發(fā)現(xiàn)open_tables等于table_cache，并且opened_tables在不斷增長，那么你就需要增加table_cache的值了 （上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設(shè)置成很大的值。如果設(shè)置得太高，可能會造成文件描述符不足，從而造成性能 不穩(wěn)定或者連接失敗。
1G內(nèi)存機器，推薦值是128－256。內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為256M或384M。
用戶可以創(chuàng)建的內(nèi)存表(memory table)的大小。這個值用來計算內(nèi)存表的最大行數(shù)值。這個變量支持動態(tài)改變，即set @max_heap_table_size=#
這個變量和tmp_table_size一起限制了內(nèi)部內(nèi)存表的大小。如果某個內(nèi)部heap（堆積）表大小超過tmp_table_size，MySQL可以根據(jù)需要自動將內(nèi)存中的heap表改為基于硬盤的MyISAM表。
通過設(shè)置tmp_table_size選項來增加一張臨時表的大小，例如做高級GROUP BY操作生成的臨時表。如果調(diào)高該值，MySQL同時將增加heap表的大小，可達到提高聯(lián)接查詢速度的效果，建議盡量優(yōu)化查詢，要確保查詢過程中生成的臨時表在內(nèi)存中，避免臨時表過大導致生成基于硬盤的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)建臨時表，Created_tmp_tables增加，如果臨時表大小超過tmp_table_size，則是在磁盤上創(chuàng)建臨時 表，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%，應(yīng)該相當好了
默認為16M，可調(diào)到64-256最佳，線程獨占，太大可能內(nèi)存不夠I/O堵塞
可以復(fù)用的保存在中的線程的數(shù)量。如果有，新的線程從緩存中取得，當斷開連接的時候如果有空間，客戶的線置在緩存中。如果有很多新的線程，為了提高性能可以這個變量值。
通過比較 Connections和Threads_created狀態(tài)的變量，可以看到這個變量的作用。
默認值為110，可調(diào)優(yōu)為80。
推薦設(shè)置為服務(wù)器 CPU核數(shù)的2倍，例如雙核的CPU, 那么thread_concurrency的應(yīng)該為4；2個雙核的cpu, thread_concurrency的值應(yīng)為8。默認為8
指定一個請求的最大連接時間，對于4GB左右內(nèi)存的服務(wù)器可以設(shè)置為5-10。
3. 配置InnoDB的幾個變量
innodb_buffer_pool_size
對于InnoDB表來說，innodb_buffer_pool_size的作用就相當于key_buffer_size對于MyISAM表的作用一樣。InnoDB使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)和索引。對于單獨的MySQL數(shù)據(jù)庫服務(wù)器，最大可以把該值設(shè)置成物理內(nèi)存的80%。
根據(jù)MySQL手冊，對于2G內(nèi)存的機器，推薦值是1G（50%）。
innodb_flush_log_at_trx_commit
主要控制了innodb將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤的時間點，取值分別為0、1、2三個。0，表示當事務(wù)提交時，不做日志寫入操作，而是每秒鐘將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤一次；1，則在每秒鐘或是每次事物的提交都會引起日志文件寫入、flush磁盤的操作，確保了事務(wù)的 ACID；設(shè)置為2，每次事務(wù)提交引起寫入日志文件的動作，但每秒鐘完成一次flush磁盤操作。
實際測試發(fā)現(xiàn)，該值對插入數(shù)據(jù)的速度影響非常大，設(shè)置為2時插入10000條記錄只需要2秒，設(shè)置為0時只需要1秒，而設(shè)置為1時則需要229秒。因此，MySQL手冊也建議盡量將插入操作合并成一個事務(wù)，這樣可以大幅提高速度。
根據(jù)MySQL手冊，在允許丟失最近部分事務(wù)的危險的前提下，可以把該值設(shè)為0或2。
innodb_log_buffer_size
log緩存大小，一般為1-8M，默認為1M，對于較大的事務(wù)，可以增大緩存大小。
可設(shè)置為4M或8M。
innodb_additional_mem_pool_size
該參數(shù)指定InnoDB用來存儲數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小。缺省值是1M。通常不用太大，只要夠用就行，應(yīng)該與表結(jié)構(gòu)的復(fù)雜度有關(guān)系。如果不夠用，MySQL會在錯誤日志中寫入一條警告信息。
根據(jù)MySQL手冊，對于2G內(nèi)存的機器，推薦值是20M，可適當增加。
innodb_thread_concurrency=8
推薦設(shè)置為 2*(NumCPUs+NumDisks)，默認一般為8
MySQL 5.6相比于前代GA版本性能提升顯著，但默認緩存設(shè)置對于小型站點并不合理。通過修改my.ini文件中的performance_schema_max_table_instances參數(shù)，能夠有效降低內(nèi)存占用。
以下是5.6默認的設(shè)置
performance_schema_max_table_instances 12500
table_definition_cache 1400
table_open_cache 2000
可以調(diào)成，或者在小點都可以。
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 檢測的表對象的最大數(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)信息
這個很重要啊，之前mount個單獨的文件，數(shù)據(jù)庫一直不成功，原來是這個在作怪啊。
chcon -R -t mysqld_db_t /home/myusqldata
網(wǎng)上的太多了，都不知道哪個是原創(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 |
+---------------------+-------+
配置中打開了記錄慢查詢，執(zhí)行時間超過2秒的即為慢查詢，系統(tǒng)顯示有4148個慢查詢，你可以分析慢查詢?nèi)罩?，找出有問題的SQL語句，慢查詢時間不宜設(shè)置過長，否則意義不大，最好在5秒以內(nèi)，如果你需要微秒級別的慢查詢，可以考慮給MySQL打補?。篽ttp://www.percona.com/docs/wiki/release:start，記得找對應(yīng)的版本。
打開慢查詢?nèi)罩究赡軙ο到y(tǒng)性能有一點點影響，如果你的MySQL是主－從結(jié)構(gòu)，可以考慮打開其中一臺從服務(wù)器的慢查詢?nèi)罩?，這樣既可以監(jiān)控慢查詢，對系統(tǒng)性能影響又小。
二、連接數(shù)
經(jīng)常會遇見”MySQL: ERROR 1040: Too manyconnections”的情況，一種是訪問量確實很高，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';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 245 |
+----------------------+-------+
MySQL服務(wù)器過去的最大連接數(shù)是245，沒有達到服務(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ù)上限設(shè)置的過高了。
三、Key_buffer_size
key_buffer_size是對MyISAM表性能影響最大的一個參數(shù)，下面一臺以MyISAM為主要存儲引擎服務(wù)器的配置：
mysql> show variables like 'key_buffer_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| key_buffer_size | 536870912 |
+-----------------+------------+
分配了512MB內(nèi)存給key_buffer_size，我們再看一下key_buffer_size的使用情況：
mysql> show global status like 'key_read%';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+------------------------+-------------+
一共有27813678764個索引讀取請求，有6798830個請求在內(nèi)存中沒有找到直接從硬盤讀取索引，計算索引未命中緩存的概率：
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%
比 如上面的數(shù)據(jù)，key_cache_miss_rate為0.0244%，4000個索引讀取請求才有一個直接讀硬盤，已經(jīng)很BT 了，key_cache_miss_rate在0.1%以下都很好（每1000個請求有一個直接讀硬盤），如果key_cache_miss_rate在 0.01%以下的話，key_buffer_size分配的過多，可以適當減少。
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ù)，比如這臺服務(wù)器，所有的緩存都用到了，要么 增加key_buffer_size，要么就是過渡索引了，把緩存占滿了。比較理想的設(shè)置：
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
四、臨時表
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)建臨時表，Created_tmp_tables增加，如果是在磁盤上創(chuàng)建臨時表，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%，應(yīng)該相當好了。我們再看一下MySQL服務(wù)器對臨時表的配置：
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以下的臨時表才能全部放內(nèi)存，超過的就會用到硬盤臨時表。
五、Open Table情況
mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 919 |
| Opened_tables | 1951 |
+---------------+-------+
Open_tables 表示打開表的數(shù)量，Opened_tables表示打開過的表數(shù)量，如果Opened_tables數(shù)量過大，說明配置中 table_cache(5.1.3之后這個值叫做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%
六、進程使用情況mysql> show global status like 'Thread%';如 果我們在MySQL服務(wù)器配置文件中設(shè)置了thread_cache_size，當客戶端斷開之后，服務(wù)器處理此客戶的線程將會緩存起來以響應(yīng)下一個客戶 而不是銷毀（前提是緩存數(shù)未達上限）。Threads_created表示創(chuàng)建過的線程數(shù)，如果發(fā)現(xiàn)Threads_created值過大的話，表明 MySQL服務(wù)器一直在創(chuàng)建線程，這也是比較耗資源，可以適當增加配置文件中thread_cache_size值，查詢服務(wù)器 thread_cache_size配置：
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+-------------------+-------+mysql> show variables like 'thread_cache_size';示例中的服務(wù)器還是挺健康的。
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+
七、查詢緩存(query cache)mysql> show global status like 'qcache%';MySQL查詢緩存變量解釋：
+-------------------------+-----------+
| 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 |
+-------------------------+-----------+Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個數(shù)。數(shù)目大說明可能有碎片。FLUSH QUERY CACHE會對緩存中的碎片進行整理，從而得到一個空閑塊。我們再查詢一下服務(wù)器關(guān)于query_cache的配置：
Qcache_free_memory：緩存中的空閑內(nèi)存。
Qcache_hits：每次查詢在緩存中命中時就增大
Qcache_inserts：每次插入一個查詢時就增大。命中次數(shù)除以插入次數(shù)就是不中比率。
Qcache_lowmem_prunes： 緩存出現(xiàn)內(nèi)存不足并且必須要進行清理以便為更多查詢提供空間的次數(shù)。這個數(shù)字最好長時間來看；如果這個數(shù)字在不斷增長，就表示可能碎片非常嚴重，或者內(nèi)存 很少。（上面的 free_blocks和free_memory可以告訴您屬于哪種情況）
Qcache_not_cached：不適合進行緩存的查詢的數(shù)量，通常是由于這些查詢不是 SELECT 語句或者用了now()之類的函數(shù)。
Qcache_queries_in_cache：當前緩存的查詢（和響應(yīng)）的數(shù)量。
Qcache_total_blocks：緩存中塊的數(shù)量。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：超過此大小的查詢將不緩存query_cache_min_res_unit的配置是一柄”雙刃劍”，默認是4KB，設(shè)置值大對大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費。
query_cache_min_res_unit：緩存塊的最小大小
query_cache_size：查詢緩存大小
query_cache_type：緩存類型，決定緩存什么樣的查詢，示例中表示不緩存 select sql_no_cache 查詢
query_cache_wlock_invalidate：當有其他客戶端正在對MyISAM表進行寫操作時，如果查詢在query cache中，是否返回cache結(jié)果還是等寫操作完成再讀表獲取結(jié)果。查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%如果查詢緩存碎片率超過20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數(shù)據(jù)量的話。查詢緩存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%查詢緩存利用率在25%以下的話說明query_cache_size設(shè)置的過大，可適當減??；查詢緩存利用率在80％以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。查詢緩存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%示例服務(wù)器 查詢緩存碎片率 ＝ 20.46％，查詢緩存利用率 ＝ 62.26％，查詢緩存命中率 ＝ 1.94％，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。
八、排序使用情況mysql> show global status like 'sort%';Sort_merge_passes 包括兩步。MySQL 首先會嘗試在內(nèi)存中做排序，使用的內(nèi)存大小由系統(tǒng)變量Sort_buffer_size 決定，如果它的大小不夠把所有的記錄都讀到內(nèi)存中，MySQL 就會把每次在內(nèi)存中排序的結(jié)果存到臨時文件中，等MySQL 找到所有記錄之后，再把臨時文件中的記錄做一次排序。這再次排序就會增加 Sort_merge_passes。實際上，MySQL會用另一個臨時文件來存再次排序的結(jié)果，所以通常會看到 Sort_merge_passes增加的數(shù)值是建臨時文件數(shù)的兩倍。因為用到了臨時文件，所以速度可能會比較慢，增加 Sort_buffer_size 會減少Sort_merge_passes 和 創(chuàng)建臨時文件的次數(shù)。但盲目的增加 Sort_buffer_size 并不一定能提高速度，
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| Sort_merge_passes | 29 |
| Sort_range | 37432840 |
| Sort_rows | 9178691532 |
| Sort_scan | 1860569 |
+-------------------+------------+
另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對排序的操作也有一點的好處，
九、文件打開數(shù)(open_files)mysql> show global status like 'open_files';比較合適的設(shè)置：Open_files / open_files_limit * 100% <= 75％
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 1410 |
+---------------+-------+
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 4590 |
+------------------+-------+
十、表鎖情況mysql> show global status like 'table_locks%';Table_locks_immediate 表示立即釋放表鎖數(shù)，Table_locks_waited表示需要等待的表鎖數(shù)，如果Table_locks_immediate / Table_locks_waited >5000，最好采用InnoDB引擎，因為InnoDB是行鎖而MyISAM是表鎖，對于高并發(fā)寫入的應(yīng)用InnoDB效果會好些。示例中的服務(wù) 器Table_locks_immediate / Table_locks_waited ＝ 235，MyISAM就足夠了。
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| Table_locks_immediate | 490206328 |
| Table_locks_waited | 2084912 |
+-----------------------+-----------+
十一、表掃描情況mysql> show global status like 'handler_read%';各字段解釋參見,調(diào)出服務(wù)器完成的查詢請求次數(shù)：
+-----------------------+-------------+
| 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 |
+-----------------------+-------------+mysql> show global status like 'com_select';計算表掃描率：
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Com_select | 222693559 |
+---------------+-----------+表掃描率 ＝ Handler_read_rnd_next / Com_select如果表掃描率超過4000，說明進行了太多表掃描，很有可能索引沒有建好，增加read_buffer_size值會有一些好處，但最好不要超過8MB。
到此，關(guān)于“Mysql常用的配置參數(shù)的整理”的學習就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關(guān)知識，請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
當前名稱：Mysql常用的配置參數(shù)的整理
網(wǎng)站URL：http://weahome.cn/article/jpoeds.html
重慶分公司
重慶分公司
