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数据库