MYSQL数据库的优化（二）
電商MySQL數(shù)據(jù)庫配置文件
這是一份電子商務(wù)網(wǎng)站MySQL數(shù)據(jù)庫調(diào)整后所運(yùn)行的配置文件/etc/my.cnf(服務(wù)器為DELL R710、16GB內(nèi)存、RAID10)，大家可以根據(jù)實(shí)際的MySQL數(shù)據(jù)庫硬件情況進(jìn)行調(diào)整配置文件如下：
[client]
port = 3306
socket = /data/3306/mysql.sock
default-character-set = utf8
[mysqld]
user = mysql
port = 3306
character-set-server = utf8
socket = /data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
log-error=/data/3306/mysql_err.log
pid-file=/data/3306/mysql.pid
log_slave_updates = 1
log-bin = /data/3306/mysql-bin
binlog_format = mixed
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1G
expire_logs_days = 90
binlog-ignore – db = mysql
binlog-ignore – db = information_schema
key_buffer_size = 384M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
join_buffer_size = 2M
thread_cache_size = 8
query_cache_size = 32M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
thread_concurrency = 32
table_cache = 614
table_open_cache = 512
open_files_limit = 10240
back_log = 600
max_connections = 5000
max_connect_errors = 6000
external-locking = FALSE
max_allowed_packet =16M
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 256M
max_heap_table_size = 512M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
long_query_time = 2
slow_query_log
slow_query_log_file = /data/3306/slow.log
skip-name-resolv
skip-locking
skip-networking
server-id = 1
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:256M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no – auto – rehash
MySQL上線后根據(jù)status狀態(tài)進(jìn)行優(yōu)化
MySQL數(shù)據(jù)庫上線后，可以等其穩(wěn)定運(yùn)行一段時(shí)間后再根據(jù)服務(wù)器的status狀態(tài)進(jìn)行適當(dāng)優(yōu)化，我們可以用如下命令列出MySQL服務(wù)器運(yùn)行的各種狀態(tài)值：
mysql > show global status;
我個(gè)人比較喜歡的用法是 show status like ‘查詢%’;
1.慢查詢
有時(shí)我們?yōu)榱硕ㄎ幌到y(tǒng)中效率比較低下的Query語法，需要打開慢查詢?nèi)罩?#xff0c;也就是Slow Que-ry log。打開慢查詢?nèi)罩镜南嚓P(guā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 | 2128 |
+———————+——-+
打開慢查詢?nèi)罩究赡軙ο到y(tǒng)性能有一點(diǎn)點(diǎn)影響，如果你的MySQL是主從結(jié)構(gòu)，可以考慮打開其中一臺從服務(wù)器的慢查詢?nèi)罩?#xff0c;這樣既可以監(jiān)控慢查詢，對系統(tǒng)性能影響也會很小。另外，可以用MySQL自帶的命令mysqldumpslow進(jìn)行查詢。比如：下面的命令可以查出訪問次數(shù)最多的20個(gè)SQL語句：
mysqldumpslow -s c -t 20 host-slow.log
2.連接數(shù)
我們?nèi)绻?jīng)常遇見MySQL：ERROR1040：Too manyconnections的情況，一種情況是訪問量確實(shí)很高，MySQL服務(wù)器扛不住了，這個(gè)時(shí)候就要考慮增加從服務(wù)器分散讀壓力。另外一種情況是MySQL配置文件中max_connections的值過小。來看一個(gè)例子。
mysql>
show variables like ‘max_connections’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 800 |
+—————–+——-+
這臺服務(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，沒有達(dá)到服務(wù)器連接數(shù)的上線800，不會出現(xiàn)1040錯(cuò)誤。
Max_used_connections /max_connections * 100% = 85%
最大連接數(shù)占上限連接數(shù)的85%左右,如果發(fā)現(xiàn)比例在10%以下，則說明MySQL服務(wù)器連接數(shù)的上限設(shè)置得過高了。
3.key_buffer_size
key_buffer_size是設(shè)置MyISAM表索引緩存空間的大小，此參數(shù)對MyISAM表性能影響最大。下面是一臺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 | 27813678766 |
| Key_reads | 6798830 |
+——————-+————–+
一共有27813678766個(gè)索引讀取請求，有6798830個(gè)請求在內(nèi)存中沒有找到，直接從硬盤讀取索引。
key_cache_miss_rate = key_reads / key_read_requests * 100%
比如上面的數(shù)據(jù)，key_cache_miss_rate為0.0244%，4000%個(gè)索引讀取請求才有一個(gè)直接讀硬盤，效果已經(jīng)很好了，key_cache_miss_rate在0.1%以下都很好，如果key_cache_miss_rate在0.01%以下的話，則說明key_buffer_size分配得過多，可以適當(dāng)減少。
4.臨時(shí)表
當(dāng)執(zhí)行語句時(shí)，關(guān)于已經(jīng)被創(chuàng)建了隱含臨時(shí)表的數(shù)量，我們可以用如下命令查詢其具體情況：
mysql> show global status like ‘created_tmp%’;
+————————-+———-+
| Variable_name | Value |
+————————-+———-+
| Created_tmp_disk_tables | 21119 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 17715532 |
+————————-+———-+
每次創(chuàng)建臨時(shí)表時(shí)，Created_tmp_table都會增加，如果磁盤上創(chuàng)建臨時(shí)表，Created_tmp_disk_tables也會增加。Created_tmp_files表示MySQL服務(wù)創(chuàng)建的臨時(shí)文件數(shù)，比較理想的配置是：
Created_tmp_disk_tables / Created_tmp_files * 100% <= 25%
比如上面的服務(wù)器Created_tmp_disk_tables / Created_tmp_files * 100% =1.20%，就相當(dāng)不錯(cuò)。我們在看一下MySQL服務(wù)器對臨時(shí)表的配置：
mysql> show variables where Variable_name in (‘tmp_table_size’,’max_heap_table_size’);
+———————+———+
| Variable_name | Value |
+———————+———+
| max_heap_table_size | 2097152 |
| tmp_table_size | 2097152 |
+———————+———+
5.打開表的情況
Open_tables表示打開表的數(shù)量，Opened_tables表示打開過的表數(shù)量，我們可以用如下命令查看其具體情況：
mysql> show global status like ‘open%tables%’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 351 |
| Opened_tables | 1455 |
如果Opened_tables數(shù)量過大，說明配置中table_open_cache的值可能太小。我們查詢下服務(wù)器table_open_cache;
mysql> show variables like ‘table_open_cache’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| table_open_cache | 2048 |
+——————+——-+
比較合適的值為：
open_tables / opened_tables* 100% > = 85%
open_tables / table_open_cache* 100% < = 95%
6.進(jìn)程使用情況
如果我們在MySQL服務(wù)器的配置文件中設(shè)置了thread_cache_size，當(dāng)客戶端斷開時(shí)，服務(wù)器處理此客戶請求的線程將會緩存起來以響應(yīng)一下客戶而不是銷毀(前提是緩存數(shù)未達(dá)上線)Thread_created表示創(chuàng)建過的線程數(shù)，我們可以用如下命令查看：
mysql> show global status like ‘thread%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 40 |
| Threads_connected | 1 |
| Threads_created | 330 |
| Threads_running | 1 |
+——————-+——-+
如果發(fā)現(xiàn)Threads_created的值過大的話，表明MySQL服務(wù)器一直在創(chuàng)建線程，這也是比較耗費(fèi)資源的，可以適當(dāng)增大配置文件中thread_cache_size的值。查詢服務(wù)器thread_cache_size配置如下：
mysql> show variables like ‘thread_cache_size’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 100 |
+——————-+——-+
示例中的MySQL服務(wù)器還是挺健康的。
7.查詢緩存(query cache)
它主要涉及兩個(gè)參數(shù)，query_cache_size是設(shè)置MySQL的Query Cache大小，query_cache_type是設(shè)置使用查詢緩存的類型，我們可以用如下命令查看其具體情況：
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查詢緩存變量的相關(guān)解釋如下：
Qcache_free_blocks：
緩存中相領(lǐng)內(nèi)存快的個(gè)數(shù)。數(shù)目大說明可能有碎片。flush query cache會對緩存中的碎片進(jìn)行整理，從而得到一個(gè)空間塊。
Qcache_free_memory：緩存中的空閑空間。
Qcache_hits：多少次命中。通過這個(gè)參數(shù)可以查看到Query Cache的基本效果。
Qcache_inserts：插入次數(shù)，沒插入一次查詢時(shí)就增加1。命中次數(shù)除以插入次數(shù)就是命中比率。
Qcache_lowmem_prunes：多少條Query因?yàn)閮?nèi)存不足而被清楚出Query Cache。通過Qcache_lowmem_prunes和Query_free_memory相互結(jié)合，能夠更清楚地了解到系統(tǒng)中Query Cache的內(nèi)存大小是否真的足夠，是否非常頻繁地出現(xiàn)因?yàn)閮?nèi)存不足而有Query被換出的情況。
Qcache_not_cached：不適合進(jìn)行緩存的查詢數(shù)量，通常是由于這些查詢不是select語句或用了now()之類的函數(shù)。
Qcache_queries_in_cache：當(dāng)前緩存的查詢和響應(yīng)數(shù)量。
Qcache_total_blocks：緩存中塊的數(shù)量。
我們在查詢一下服務(wù)器上關(guān)于query_cache的配置命令：
mysql> show variables like ‘query_cache%’;
+——————————+———+
| Variable_name | Value |
+——————————+———+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 2048 |
| query_cache_size | 2097152 |
| 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_invalidat：表示當(dāng)有其他客戶端正在對MyISAM表進(jìn)行寫操作，讀請求是要等WRITE LOCK釋放資源后再查詢還是允許直接從Query Cache中讀取結(jié)果，默認(rèn)為OFF（可以直接從Query Cache中取得結(jié)果。）
query_cache_min_res_unit的配置是一柄雙刃劍，默認(rèn)是4KB，設(shè)置值大對大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費(fèi)。
查詢緩存碎片率 = Qcache_free_blocks /Qcache_total_blocks * 100%
如果查詢碎片率超過20%，可以用 flush query cache 整理緩存碎片，或者試試減少query_cache_min_res_unit，如果你查詢都是小數(shù)據(jù)庫的話。
查詢緩存利用率 = (Qcache_free_size – Qcache_free_memory)/query_cache_size * 100%
查詢緩存利用率在25%一下的話說明query_cache_size設(shè)置得過大，可適當(dāng)減少;查詢緩存利用率在80%以上而且Qcache_lowmem_prunes > 50的話則說明query_cache_size可能有點(diǎn)小，不然就是碎片太多。
查詢命中率
= (Qcache_hits – Qcache_insert)/Qcache)hits * 100%
示例服務(wù)器中的查詢緩存碎片率等于20%左右，查詢緩存利用率在50%，查詢命中率在2%，說明命中率很差，可能寫操作比較頻繁，而且可能有些碎片。
8.排序使用情況
它表示系統(tǒng)中對數(shù)據(jù)進(jìn)行排序時(shí)所用的Buffer，我們可以用如下命令查看：
mysql> show global status like ‘sort%’;
+——————-+———-+
| Variable_name | Value |
+——————-+———-+
| Sort_merge_passes | 10 |
| Sort_range | 37431240 |
| Sort_rows | 6738691532 |
| Sort_scan | 1823485 |
+——————-+———-+
Sort_merge_passes包括如下步驟：MySQL首先會嘗試在內(nèi)存中做排序，使用的內(nèi)存大小由系統(tǒng)變量sort_buffer_size來決定，如果它不夠大則把所有的記錄都讀在內(nèi)存中，而MySQL則會把每次在內(nèi)存中排序的結(jié)果存到臨時(shí)文件中，等MySQL找到所有記錄之后，再把臨時(shí)文件中的記錄做一次排序。這次再排序就會增加sort_merge_passes。實(shí)際上，MySQL會用另外一個(gè)臨時(shí)文件來存儲再次排序的結(jié)果，所以我們通常會看到sort_merge_passes增加的數(shù)值是建臨時(shí)文件數(shù)的兩倍。因?yàn)橛玫搅伺R時(shí)文件，所以速度可能會比較慢，增大sort_buffer_size會減少sort_merge_passes和創(chuàng)建臨時(shí)文件的次數(shù)，但盲目地增大sort_buffer_size并不一定能提高速度。
9.文件打開數(shù)(open_files)
我們現(xiàn)在處理MySQL故障時(shí)，發(fā)現(xiàn)當(dāng)Open_files大于open_files_limit值時(shí)，MySQL數(shù)據(jù)庫就會發(fā)生卡住的現(xiàn)象，導(dǎo)致Nginx服務(wù)器打不開相應(yīng)頁面。這個(gè)問題大家在工作中應(yīng)注意，我們可以用如下命令查看其具體情況：
show global status like ‘open_files’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_files | 1481 |
+—————+——-+
mysql> show global status like ‘open_files_limit’;
+——————+——-+
| Variable_name | Value |
+——————+——–+
| Open_files_limit | 4509 |
+——————+——–+
比較合適的設(shè)置是：Open_files / Open_files_limit * 100% < = 75%
10.InnoDB_buffer_pool_cache合理設(shè)置
InnoDB存儲引擎的緩存機(jī)制和MyISAM的最大區(qū)別就在于，InnoDB不僅僅緩存索引，同時(shí)還會緩存實(shí)際的數(shù)據(jù)。此參數(shù)用來設(shè)置InnoDB最主要的Buffer的大小，也就是緩存用戶表及索引數(shù)據(jù)的最主要緩存空間，對InnoDB整體性能影響也最大。
無論是MySQL官方手冊還是網(wǎng)絡(luò)上許多人分享的InnoDB優(yōu)化建議，都是簡單地建議將此值設(shè)置為整個(gè)系統(tǒng)物理內(nèi)存的50%~80%。這種做法其實(shí)不妥，我們應(yīng)根據(jù)實(shí)際的運(yùn)行場景來正確設(shè)置此項(xiàng)參數(shù)。
MySQL優(yōu)化小思想
很多時(shí)候我們會發(fā)現(xiàn)，通過參數(shù)設(shè)置進(jìn)行性能優(yōu)化所帶來的性能提升，并不如許多人想象的那樣會產(chǎn)生質(zhì)的飛躍，除非是之前的設(shè)置存在嚴(yán)重不合理的情況。我們不能將性能調(diào)優(yōu)完全依托與通過DBA在數(shù)據(jù)庫上線后進(jìn)行參數(shù)調(diào)整，而應(yīng)該在系統(tǒng)設(shè)計(jì)和開發(fā)階段就盡可能減少性能問題。(重點(diǎn)在于前期架構(gòu)合理的設(shè)計(jì)及開發(fā)的程序合理)
MySQL數(shù)據(jù)庫的可擴(kuò)展架構(gòu)方案
如果憑借MySQL的優(yōu)化任無法頂住壓力，這個(gè)時(shí)候我們就必須考慮MySQL的可擴(kuò)展性架構(gòu)了(有人稱為MySQL集群)它有以下明顯的優(yōu)勢：
成本低，很容易通過價(jià)格低廉Pc server搭建出一個(gè)處理能力非常強(qiáng)大的計(jì)算機(jī)集群。
不太容易遇到瓶頸，因?yàn)楹苋菀淄ㄟ^添加主機(jī)來增加處理能力。
單節(jié)點(diǎn)故障對系統(tǒng)的整體影響較小。
目前可行的方案如下：
（1）MySQL Cluter
其特點(diǎn)為可用性非常高，性能非常好。每份數(shù)據(jù)至少可在不同主機(jī)上存一份副本，且冗余數(shù)據(jù)拷貝實(shí)時(shí)同步。但它的維護(hù)非常復(fù)雜，存在部分Bug，目前還不適合比較核心的線上系統(tǒng)，所以暫時(shí)不推薦。
（2）DRBD磁盤網(wǎng)絡(luò)鏡像方案
其特點(diǎn)為軟件功能強(qiáng)大，數(shù)據(jù)可在底層快設(shè)備級別跨物理主機(jī)鏡像，且可根據(jù)性能可靠性要求配置不同級別的同步。I/O操作會保持順序，可滿足數(shù)據(jù)庫對數(shù)據(jù)一致性的苛刻要求。但非分布式文件系統(tǒng)環(huán)境無法支持鏡像數(shù)據(jù)同時(shí)可見，性能和可靠性兩者互相矛盾，無法適用于性能和可靠性要求都比較苛刻的環(huán)境，維護(hù)成本高于MySQL Replication。另外，DRBD是官方推薦的可用于MySQL的搞可用方案之一，大家可根據(jù)實(shí)際環(huán)境來考慮是否部署。
（3）MySQL Replication
在工作中，此種MySQL搞可用、高擴(kuò)展性架構(gòu)也是用得最多的，我也推薦此方案，一主多從、雙主多從是生產(chǎn)環(huán)境常見的高可用架構(gòu)方案。
高可用架構(gòu)：MySQL一主多從、MySQL雙主多從、MySQL讀寫分離、MySQL分布式集群、DRBD+Heartbeat+MySQL、等各種集群架構(gòu)
總結(jié)
以上是生活随笔為你收集整理的MYSQL数据库的优化（二）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 生产环境常见的HTTP状态码列表
- 下一篇： 二进制包安装MySQL数据库