MySQL5.6基本優(yōu)化配置

更新時間：2014年06月11日 09:13:19 投稿：junjie

這篇文章主要介紹了MySQL5.6基本優(yōu)化配置,詳細分解了MySQL5.6需要優(yōu)化的配置項,最終給出了一個優(yōu)化案例,需要的朋友可以參考下

下面開始優(yōu)化下my.conf文件(這里的優(yōu)化只是在mysql本身的優(yōu)化，之前安裝的時候也要有優(yōu)化)

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(這里主要是復制功能，也就是主從,提前配置好，后面講主從配置)
#二進制日志
log-bin
#為了在最大程序上保證復制的InnoDB事務持久性和一致性
sync_binlog=1
sync_relay_log=1
#啟用此兩項,可用于實現(xiàn)在崩潰時保證二進制及從服務器安全的功能
relay-log-info-repository=TABLE
master-info-repository=TABLE
#設(shè)置清除日志時間
expire_logs_days=7
#行復制
binlog_format=ROW
#mysql數(shù)據(jù)庫事務隔離級別有四種(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
#代表在事務過程中容納二進制日志SQL語句的緩存大小
binlog_cache_size = 2M
#同樣是緩存表大小
table_open_cache=128
#緩存線程
thread_cache=1024
#推薦設(shè)置為服務器 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服務器能夠工作在不同的模式下,并能針對不同的客戶端以不同的方式應用這些模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

下面列出了對性能優(yōu)化影響較大的主要變量，主要分為連接請求的變量和緩沖區(qū)變量。
1.   連接請求的變量：
1)     max_connections
MySQL的最大連接數(shù)，增加該值增加mysqld 要求的文件描述符的數(shù)量。如果服務器的并發(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'響應的連接數(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è)置過低或者超過服務器負載上限了，低于10%則設(shè)置過大。
2)     back_log
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ù)。
3)     interactive_timeout
一個交互連接在被服務器在關(guān)閉前等待行動的秒數(shù)。一個交互的客戶被定義為對mysql_real_connect()使用CLIENT_INTERACTIVE 選項的客戶。
默認數(shù)值是28800，可調(diào)優(yōu)為7200。
2.   緩沖區(qū)變量
全局緩沖：
4)     key_buffer_size
key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態(tài)值 Key_read_requests和Key_reads，可以知道key_buffer_size設(shè)置是否合理。比例key_reads / key_read_requests應該盡可能的低，至少是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)。
5)     query_cache_size
使用查詢緩沖，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的值不大，則表明你的查詢重復率很低，這種情況下使用查詢緩沖反而會影響效率，那么可以考慮不用查詢緩沖。此外，在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%
示例服務器查詢緩存碎片率＝20.46％，查詢緩存利用率＝62.26％，查詢緩存命中率＝1.94％，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。
每個連接的緩沖
6)    record_buffer_size
每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)。如果你做很多順序掃描，你可能想要增加該值。
默認數(shù)值是131072(128K)，可改為16773120 (16M)
7)     read_rnd_buffer_size
隨機讀緩沖區(qū)大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區(qū)。進行排序查詢時，MySQL會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當調(diào)高該值。但MySQL會為每個客戶連接發(fā)放該緩沖空間，所以應盡量適當設(shè)置該值，以避免內(nèi)存開銷過大。
一般可設(shè)置為16M
8)     sort_buffer_size
每個需要進行排序的線程分配該大小的一個緩沖區(qū)。增加這值加速ORDER BY或GROUP BY操作。
默認數(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為每個線程獨占，也就是說，如果有100個線程連接，則占用為16M*100
10) table_cache
表高速緩存的大小。每當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左右的服務器該參數(shù)可設(shè)置為256M或384M。
11) max_heap_table_size
用戶可以創(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表。
12) tmp_table_size
通過設(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服務創(chuàng)建的臨時文件文件數(shù)，比較理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服務器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，應該相當好了
默認為16M，可調(diào)到64-256最佳，線程獨占，太大可能內(nèi)存不夠I/O堵塞
13) thread_cache_size
可以復用的保存在中的線程的數(shù)量。如果有，新的線程從緩存中取得，當斷開連接的時候如果有空間，客戶的線置在緩存中。如果有很多新的線程，為了提高性能可以這個變量值。
通過比較 Connections和Threads_created狀態(tài)的變量，可以看到這個變量的作用。
默認值為110，可調(diào)優(yōu)為80。
14) thread_concurrency
推薦設(shè)置為服務器 CPU核數(shù)的2倍，例如雙核的CPU, 那么thread_concurrency的應該為4；2個雙核的cpu, thread_concurrency的值應為8。默認為8
15) wait_timeout
指定一個請求的最大連接時間，對于4GB左右內(nèi)存的服務器可以設(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ù)庫服務器，最大可以把該值設(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，表示當事務提交時，不做日志寫入操作，而是每秒鐘將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤一次；1，則在每秒鐘或是每次事物的提交都會引起日志文件寫入、flush磁盤的操作，確保了事務的 ACID；設(shè)置為2，每次事務提交引起寫入日志文件的動作，但每秒鐘完成一次flush磁盤操作。
實際測試發(fā)現(xiàn)，該值對插入數(shù)據(jù)的速度影響非常大，設(shè)置為2時插入10000條記錄只需要2秒，設(shè)置為0時只需要1秒，而設(shè)置為1時則需要229秒。因此，MySQL手冊也建議盡量將插入操作合并成一個事務，這樣可以大幅提高速度。
根據(jù)MySQL手冊，在允許丟失最近部分事務的危險的前提下，可以把該值設(shè)為0或2。

innodb_log_buffer_size
log緩存大小，一般為1-8M，默認為1M，對于較大的事務，可以增大緩存大小。
可設(shè)置為4M或8M。

innodb_additional_mem_pool_size
該參數(shù)指定InnoDB用來存儲數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小。缺省值是1M。通常不用太大，只要夠用就行，應該與表結(jié)構(gòu)的復雜度有關(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)友的補充

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打補丁：http://www.percona.com/docs/wiki/release:start，記得找對應的版本。
打開慢查詢?nèi)罩究赡軙ο到y(tǒng)性能有一點點影響，如果你的MySQL是主－從結(jié)構(gòu)，可以考慮打開其中一臺從服務器的慢查詢?nèi)罩?，這樣既可以監(jiān)控慢查詢，對系統(tǒng)性能影響又小。
二、連接數(shù)
經(jīng)常會遇見”MySQL: ERROR 1040: Too manyconnections”的情況，一種是訪問量確實很高，MySQL服務器抗不住，這個時候就要考慮增加從服務器分散讀壓力，另外一種情況是MySQL配置文件中max_connections值過?。?/p>

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

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服務器還提供了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ù)，比如這臺服務器，所有的緩存都用到了，要么增加key_buffer_size，要么就是過渡索引了，把緩存占滿了。比較理想的設(shè)置：

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
四、臨時表

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
比如上面的服務器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 1.20%，應該相當好了。我們再看一下MySQL服務器對臨時表的配置：

+---------------+-------+
比較合適的值為：

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

六、進程使用情況
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2     |
| Threads_created | 570 |
| Threads_running | 1     |
+-------------------+-------+
如果我們在MySQL服務器配置文件中設(shè)置了thread_cache_size，當客戶端斷開之后，服務器處理此客戶的線程將會緩存起來以響應下一個客戶而不是銷毀（前提是緩存數(shù)未達上限）。Threads_created表示創(chuàng)建過的線程數(shù)，如果發(fā)現(xiàn)Threads_created值過大的話，表明 MySQL服務器一直在創(chuàng)建線程，這也是比較耗資源，可以適當增加配置文件中thread_cache_size值，查詢服務器 thread_cache_size配置：
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+
示例中的服務器還是挺健康的。
七、查詢緩存(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)存塊的個數(shù)。數(shù)目大說明可能有碎片。FLUSH 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：當前緩存的查詢（和響應）的數(shù)量。
Qcache_total_blocks：緩存中塊的數(shù)量。
我們再查詢一下服務器關(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：超過此大小的查詢將不緩存
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é)果。
query_cache_min_res_unit的配置是一柄”雙刃劍”，默認是4KB，設(shè)置值大對大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費。
查詢緩存碎片率 = 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%
示例服務器查詢緩存碎片率＝ 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 首先會嘗試在內(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 并不一定能提高速度，
另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對排序的操作也有一點的好處，
九、文件打開數(shù)(open_files)
mysql> show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 1410   |
+---------------+-------+

隨著大量默認選項的改進, MySQL 5.6比以前版本需要調(diào)優(yōu)的選項大為減少. 在本文中我將講述需要優(yōu)化的配置項.

InnoDB設(shè)置

1.innodb_buffer_pool_size —— 默認值為 128M. 這是最主要的優(yōu)化選項,因為它指定 InnoDB 使用多少內(nèi)存來加載數(shù)據(jù)和索引(data+indexes). 針對專用MySQL服務器,建議指定為物理內(nèi)存的 50-80%這個范圍. 例如,擁有64GB物理內(nèi)存的機器,緩存池應該設(shè)置為50GB左右.
如果將該值設(shè)置得更大可能會存在風險,比如沒有足夠的空閑內(nèi)存留給操作系統(tǒng)和依賴文件系統(tǒng)緩存的某些MySQL子系統(tǒng)(subsystem),包括二進制日志(binary logs),InnoDB事務日志(transaction logs)等.

2.innodb_log_file_size —— 默認值為 48M. 有很高寫入吞吐量的系統(tǒng)需要增加該值以允許后臺檢查點活動在更長的時間周期內(nèi)平滑寫入,得以改進性能. 將此值設(shè)置為4G以下是很安全的. 過去的實踐表明,日志文件太大的缺點是增加了崩潰時所需的修復時間,但這在5.5和5.6中已得到重大改進.

3.innodb_flush_method —— 默認值為 fdatasync. 如果使用硬件RAID磁盤控制器, 可能需要設(shè)置為 O_DIRECT. 這在讀取InnoDB緩沖池時可防止“雙緩沖(double buffering)”效應,否則會在文件系統(tǒng)緩存與InnoDB緩存間形成2個副本(copy).
如果不使用硬件RAID控制器,或者使用SAN存儲時, O_DIRECT 可能會導致性能下降.MySQL用戶手冊和 Bug #54306 詳細地說明了這一點.

4.innodb_flush_neighbors —— 默認值為 1. 在SSD存儲上應設(shè)置為0(禁用) ,因為使用順序IO沒有任何性能收益. 在使用RAID的某些硬件上也應該禁用此設(shè)置,因為邏輯上連續(xù)的塊在物理磁盤上并不能保證也是連續(xù)的.

5.innodb_io_capacity and innodb_io_capacity_max —— 這些設(shè)置會影響InnoDB每秒在后臺執(zhí)行多少操作. 如果你深度了解硬件性能(如每秒可以執(zhí)行多少次IO操作),則使用這些功能是很可取的,而不是讓它閑著.

有一個很好的類比示例: 假如某次航班一張票也沒有賣出去 —— 那么讓稍后航班的一些人乘坐該次航班,有可能是很好的策略,以防后面遇到惡劣的天氣. 即有機會就將后臺操作順便處理了,以減少同稍后可能的實時操作產(chǎn)生競爭.

有一個很簡單的計算: 如果每個磁盤每秒讀寫(IOPS)可以達到 200次, 則擁有10個磁盤的 RAID10 磁盤陣列IOPS理論上 =(10/2)* 200 = 1000. 我說它“很簡單”,是因為RAID控制器通常能夠提供額外的合并,并有效提高IOPS能力. 對于SSD磁盤,IOPS可以輕松達到好幾千.

將這兩個值設(shè)置得太大可能會存在某些風險,你肯定不希望后臺操作妨礙了前臺任務IO操作的性能. 過去的經(jīng)驗表明,將這兩個值設(shè)置的太高,InnoDB持有的內(nèi)部鎖會導致性能降低(按我了解到的信息,在MySQL5.6中這得到了很大的改進).

innodb_lru_scan_depth - 默認值為 1024. 這是mysql 5.6中引入的一個新選項. Mark Callaghan 提供了一些配置建議. 簡單來說,如果增大了 innodb_io_capacity 值, 應該同時增加 innodb_lru_scan_depth.

復制(Replication)

假如服務器要支持主從復制,或按時間點恢復,在這種情況下,我們需要:

1.log-bin —— 啟用二進制日志. 默認情況下二進制日志不是事故安全的(not crash safe),但如同我以前的文章所說, 我建議大多數(shù)用戶應該以穩(wěn)定性為目標. 在這種情況下,你還需要啟用: sync_binlog=1, sync_relay_log=1, relay-log-info-repository=TABLE and master-info-repository=TABLE.

2.expire-logs-days —— 默認舊日志會一直保留. 我推薦設(shè)置為 1-10 天. 保存更長的時間并沒有太多用處,因為從備份中恢復會快得多.

3.server-id —— 在一個主從復制體系(replication topology )中的所有服務器都必須設(shè)置唯一的 server-id.

4.binlog_format=ROW —— 修改為基于行的復制. 我最近寫的另一篇基于行的復制 ,里面敘述了我真的很喜歡它的原因,因為它可以通過減少資源鎖定提高性能. 此外還需要啟用兩個附加設(shè)置: transaction-isolation=READ-COMMITTED and innodb_autoinc_lock_mode = 2.

其他配置(Misc)

1.timezone=GMT 將時區(qū)設(shè)置為格林尼治時間. 越來越多的系統(tǒng)管理員建議將所有服務器都設(shè)置為格林尼治時間(GMT). 我個人非常喜歡這點,因為現(xiàn)在幾乎所有的業(yè)務都是全球化的. 設(shè)置為你本地的時區(qū)似乎是有點武斷的.

2.character-set-server=utf8mb4 and collation-server=utf8mb4_general_ci 如之前的文章所講述的 ,utf8 編碼對新應用來說是更好的默認選項. 您還可以設(shè)置 skip-character-set-client-handshake 以忽略應用程序想要設(shè)置的其他字符集(character-set).

3.sql-mode —— MySQL默認對不規(guī)范的數(shù)據(jù)很寬容,并且會靜默地截斷數(shù)據(jù). 在我之前的一篇文章中, 我提到新應用程序最好設(shè)置為：

復制代碼代碼如下:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,

 NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,

 NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,

 NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY.

4.skip-name-resolve —— 禁用反向域名解析. DNS解析在某些系統(tǒng)上可能有點慢/不穩(wěn)定,所以如果不需要基于主機名的授權(quán),我建議避免這種解析.

5.max_connect_errors —— Todd Farmer 寫道 :“[這個功能]提供了沒有實際意義的暴力訪問攻擊保護”. 事實上當設(shè)置skip-name-resolve 時, max_connect_errors 甚至不起作用(見上一段所述).

防火墻是更合適的解決方案,通常我將3306端口屏蔽,不管是公網(wǎng)的還是內(nèi)網(wǎng)的端口,只有特定的應用程序可以訪問和連接到MySQL.
我通常會設(shè)置 max_connect_errors=100000, 這樣我可以避免任何“雙重配置”,保證它不會礙事.

6.max-connections ——默認值是151. 我看到很多用戶將他設(shè)置得比較大,大多在 300 ~ 1000之間.
通常不可避免地這個值會被設(shè)置得更大,但讓我有點緊張的是, 16核的機器在IO阻塞的情況下也只有大約 2x~10x 的連接執(zhí)行能力.
你可能希望,許多打開的連接都是空閑并休眠的. 但如果他們都處于活躍狀態(tài)的話,可能會創(chuàng)建大量新的線程(thread-thrash).
如果條件允許,可以為應用程序配置優(yōu)化數(shù)據(jù)庫連接池(connection-pools)來解決這個問題,而不是打開并保持大量連接;
當然那些不使用連接池(non-pooled ), 迅速打開,執(zhí)行任務后又盡可能快地關(guān)閉連接的應用也是可行的.
從5.5開始的另一種解決方案(在MySQL社區(qū)版和企業(yè)版之間有一些差異) 是使用線程池插件.

總結(jié)(Conclusion)

假設(shè)MySQL服務器的配置為:
1.64GB物理內(nèi)存
2.硬件RAID控制器(假設(shè)每秒IO可達 2000 IOPS)
3.需要主從復制(Replication)
4.新的應用(eg. 非遺留系統(tǒng))
5.有防火墻保護
6.不需要基于域名(hostnames,主機名)的授權(quán)
7.全球化應用,并不想固定在某一時區(qū).
8.想要程序可靠穩(wěn)定(durable).

則配置可能如下所示:

復制代碼代碼如下:

# InnoDB settings
innodb_buffer_pool_size=50G
innodb_log_file_size=2G
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000

# Binary log/replication
log-bin
sync_binlog=1
sync_relay_log=1
relay-log-info-repository=TABLE
master-info-repository=TABLE
expire_logs_days=10
binlog_format=ROW
transaction-isolation=READ-COMMITTED
innodb_autoinc_lock_mode = 2

# Other
timezone=GMT
character-set-server=utf8
collation-server=utf8_general_ci
sql-mode="STRICT_TRANS_TABLES,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_AUTO_VALUE_ON_ZERO,
NO_ENGINE_SUBSTITUTION,
NO_ZERO_DATE,
NO_ZERO_IN_DATE,
ONLY_FULL_GROUP_BY"
skip-name_resolve
max-connect-errors=100000
max-connections=500

# Unique to this machine
server-id=123

到最后了，腳本之家小編為大家分享一個配置測試不錯的適合高配高負載的服務器

[client]
port        = 3306
socket        = /tmp/mysql.sock
default-character-set = utf8
#設(shè)置客戶端的字符編碼
[mysqld]
# generic configuration options
port        = 3306
socket        = /tmp/mysql.sock
basedir="d:/database/mysql/"
datadir="d:/database/mysql/data/"
tmpdir = "d:/database/mysql/tmp/"
#*** char set ***
character-set-server = utf8
#設(shè)置服務器端的字符編碼

#下面三個參數(shù)12500,1400,2000
performance_schema_max_table_instances = 12500
table_definition_cache = 1400
table_open_cache = 2000

#*** network ***
back_log = 1024
#skip-networking #默認沒有開啟
max_connections = 10000
#max_connect_errors = 3000
table_open_cache = 4096
#external-locking #默認沒有開啟
max_allowed_packet = 256M
max_heap_table_size = 128M

#*** timeout ***
interactive_timeout=1000
wait_timeout=1000

# *** global cache ***
read_buffer_size = 8M
read_rnd_buffer_size = 64M
sort_buffer_size = 16M
join_buffer_size = 1024M

# *** thread ***
thread_cache_size = 64
# thread_concurrency = 8
thread_stack = 512K

# *** query cache ***
query_cache_size = 128M
query_cache_limit = 8M

# *** index ***
ft_min_word_len = 8

#memlock #默認沒有開啟
default-storage-engine=MYISAM
innodb=OFF
default-tmp-storage-engine=MYISAM
transaction_isolation = REPEATABLE-READ

# *** tmp table ***
tmp_table_size = 1024M

# *** bin log ***
#log-bin=mysql-bin
binlog_cache_size = 4M
binlog_format=mixed
#log_slave_updates #默認沒有開啟
#log #默認沒有開啟，此處是查詢?nèi)罩荆_啟會影響服務器性能
log_warnings #開啟警告日志

# *** slow query log ***
slow_query_log
long_query_time = 10
# *** Replication related settings
#server-id = 1
#server-id = 2
#master-host = <hostname>
#master-user = <username>
#master-password = <password>
#master-port = <port>
#read_only
#*** MyISAM Specific options
#myisam_recover
key_buffer_size = 2048M
bulk_insert_buffer_size = 128M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover_options=force,backup

# *** INNODB Specific options ***
#skip-innodb #默認沒有開啟
loose-innodb-trx=0
loose-innodb-locks=0
loose-innodb-lock-waits=0
loose-innodb-cmp=0
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0
loose-innodb-cmpmem=0
loose-innodb-cmpmem-reset=0
loose-innodb-buffer-page=0
loose-innodb-buffer-page-lru=0
loose-innodb-buffer-pool-stats=0
loose-innodb-metrics=0
loose-innodb-ft-default-stopword=0
#loose-innodb-ft-inserted=0
loose-innodb-ft-deleted=0
loose-innodb-ft-being-deleted=0
loose-innodb-ft-config=0
loose-innodb-ft-index-cache=0
loose-innodb-ft-index-table=0
loose-innodb-sys-tables=0
loose-innodb-sys-tablestats=0
loose-innodb-sys-indexes=0
loose-innodb-sys-columns=0
loose-innodb-sys-fields=0
loose-innodb-sys-foreign=0
loose-innodb-sys-foreign-cols=0

[mysqldump]
quick
max_allowed_packet = 256M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 10240
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES