乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當(dāng)前位置： 維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

Mysql必讀Mysql IO 內(nèi)存方面的優(yōu)化

作者：VEPHP   時(shí)間 2017-09-24

《Mysql必讀Mysql IO 內(nèi)存方面的優(yōu)化》要點(diǎn)：
本文介紹了Mysql必讀Mysql IO 內(nèi)存方面的優(yōu)化，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

這里使用的是mysql Ver 14.14 Distrib 5.6.19, for Linux (i686) using EditLine wrapper
MYSQL應(yīng)用

一、mysql目錄文件
MYSQL應(yīng)用

ibdata1:系統(tǒng)表空間 包含數(shù)據(jù)字典、回滾日志/undolog等
MYSQL應(yīng)用

(insert buffer segment/double write segment/rollback segment/index segment/dictionary segment/undo segment)
MYSQL應(yīng)用

ib_logfile0/ib_logfile1：事務(wù)日志/redolog
MYSQL應(yīng)用

mysql-relay-bin：中繼日志
MYSQL應(yīng)用

binarylog：二進(jìn)制日志
MYSQL應(yīng)用

general_log.log：常規(guī)日志
MYSQL應(yīng)用

mysql_error.log：錯(cuò)誤日志
MYSQL應(yīng)用

slow_query.log：慢日志
MYSQL應(yīng)用

.ibd：用戶表空間-數(shù)據(jù)文件(insert buffer bitmap page/leaf page segment/none leaf page segment)
MYSQL應(yīng)用

Innodb buffer pool(內(nèi)存)：undo page /insert buffer page/adaptive hash index/index page/lock info/data dictionary
MYSQL應(yīng)用

二、mysql線程
MYSQL應(yīng)用

FILE IOMYSQL應(yīng)用

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
393 OS file reads, 5 OS file writes, 5 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/sMYSQL應(yīng)用

innodb后臺(tái)所有線程
MYSQL應(yīng)用

| thread/sql/main | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/io_handler_thread | BACKGROUND | YES |
| thread/innodb/srv_master_thread | BACKGROUND | YES |
| thread/innodb/srv_purge_thread | BACKGROUND | YES |
| thread/innodb/srv_monitor_thread | BACKGROUND | YES |
| thread/innodb/srv_error_monitor_thread | BACKGROUND | YES |
| thread/innodb/srv_lock_timeout_thread | BACKGROUND | YES |
| thread/innodb/page_cleaner_thread | BACKGROUND | YES |
| thread/sql/signal_handler | BACKGROUND | YES |
| thread/sql/slave_sql | BACKGROUND | YES |
| thread/sql/slave_io | BACKGROUND | YES |　MYSQL應(yīng)用

IO線程分別是insert buffer thread、log thread、read thread、write thread.
MYSQL應(yīng)用

在MySQL 5.6.10之后,默認(rèn)線程處理模型使用執(zhí)行每個(gè)客戶端連接一個(gè)線程語句.隨著越來越多的客戶端連接到服務(wù)器和執(zhí)行語句,整體性能降低.線程池插件的提供旨在減少開銷,提高性能的其他線程的處理模式.該插件實(shí)現(xiàn)了通過有效地管理語句執(zhí)行線程的大量客戶端連接的提高服務(wù)器性能的線程池.
MYSQL應(yīng)用

InnoDB Plugin版本開始增加了默認(rèn)IO thread的數(shù)量,默認(rèn)的read thread和write thread分別增大到了4個(gè),并且不再使用innodb_file_io_threads參數(shù),而是分別使用innodb_read_io_threads和innodb_write_io_threads參數(shù).
MYSQL應(yīng)用

線程池解決每個(gè)連接模型解決單線程的幾個(gè)問題MYSQL應(yīng)用

Too many thread stacks make CPU caches almost useless in highly parallel execution workloads. The thread pool promotes thread stack reuse to minimize the CPU cache footprint.MYSQL應(yīng)用

With too many threads executing in parallel, context switching overhead is high. This also presents a challenging task to the operating system scheduler. The thread pool controls the number of active threads to keep the parallelism within the MySQL server at a level that it can handle and that is appropriate for the server host on which MySQL is executing.MYSQL應(yīng)用

Too many transactions executing in parallel increases resource contention. In InnoDB, this increases the time spent holding central mutexes. The thread pool controls when transactions start to ensure that not too many execute in parallel.MYSQL應(yīng)用

三、mysql訪問文件流程
MYSQL應(yīng)用

Transaction 來自網(wǎng)絡(luò)
MYSQL應(yīng)用

Mysql必讀Mysql IO 內(nèi)存方面的優(yōu)化MYSQL應(yīng)用

三、影響IO/內(nèi)存的一些參數(shù)
MYSQL應(yīng)用

1、innodb_flush_log_at_trx_commit 設(shè)置為2
MYSQL應(yīng)用

這參數(shù)是指 事務(wù)log(ib_logfile0、ib_logfile1)以怎樣的方式寫入到log buffer
MYSQL應(yīng)用

=0 mysql crash 就丟失了,性能最好
MYSQL應(yīng)用

buffer pool -> log buffer 每秒 wirte os cache & flush磁盤
MYSQL應(yīng)用

=1 不會(huì)丟失,效率低
MYSQL應(yīng)用

buffer pool -> log buffer 每次 write os cache & flush磁盤
MYSQL應(yīng)用

=2 即使mysql崩潰也不會(huì)丟數(shù)據(jù)
MYSQL應(yīng)用

buffer pool -> os cache 每秒flush 磁盤
MYSQL應(yīng)用

注意：由于進(jìn)程調(diào)度策略問題,這個(gè)“每秒執(zhí)行一次 flush(刷到磁盤)操作”并不是保證100%的“每秒
MYSQL應(yīng)用

2、sync_binlog
MYSQL應(yīng)用

二進(jìn)制日志(binary log)同步到磁盤的頻率.binary log 每寫入sync_binlog 次后,刷寫到磁盤.
MYSQL應(yīng)用

如果 autocommit 開啟,每個(gè)語句都寫一次 binary log,否則每次事務(wù)寫一次.
MYSQL應(yīng)用

默認(rèn)值是 0,不主動(dòng)同步,而依賴操作系統(tǒng)本身不定期把文件內(nèi)容 flush 到磁盤
MYSQL應(yīng)用

設(shè)為 1 最安全,在每個(gè)語句或事務(wù)后同步一次 binary log,即使在崩潰時(shí)也最多丟失一個(gè)語句或事務(wù)的日志,但因此也最慢.
MYSQL應(yīng)用

大多數(shù)情況下,對(duì)數(shù)據(jù)的一致性并沒有很嚴(yán)格的要求,所以并不會(huì)把 sync_binlog 配置成 1,為了追求高并發(fā),提升性能,可以設(shè)置為 100 或直接用 0
MYSQL應(yīng)用

3、write/read thread
MYSQL應(yīng)用

異步IO線程數(shù)
MYSQL應(yīng)用

innodb_write_io_threads=16
innodb_read_io_threads=16
MYSQL應(yīng)用

(該參數(shù)需要在配置文件中添加,重啟mysql實(shí)例起效)臟頁寫的線程數(shù),加大該參數(shù)可以提升寫入性能
MYSQL應(yīng)用

4、innodb_max_dirty_pages_pctMYSQL應(yīng)用

最大臟頁百分?jǐn)?shù),當(dāng)系統(tǒng)中臟頁所占百分比超過這個(gè)值,INNODB就會(huì)進(jìn)行寫操作以把頁中的已更新數(shù)據(jù)寫入到磁盤文件中.默認(rèn)75,一般現(xiàn)在流行的SSD硬盤很難達(dá)到這個(gè)比例.可依據(jù)實(shí)際情況在75-80之間調(diào)節(jié)
MYSQL應(yīng)用

5、innodb_io_capacity=5000
MYSQL應(yīng)用

從緩沖區(qū)刷新臟頁時(shí),一次刷新臟頁的數(shù)量.根據(jù)磁盤IOPS的能力一般建議設(shè)置如下：
MYSQL應(yīng)用

SAS 200
SSD 5000
PCI-E 10000-50000
MYSQL應(yīng)用

6、innodb_flush_method=O_DIRECT(該參數(shù)需要重啟mysql實(shí)例起效)
MYSQL應(yīng)用

控制innodb 數(shù)據(jù)文件和redo log的打開、刷寫模式.有三個(gè)值：fdatasync(默認(rèn)),O_DSYNC,O_DIRECT.
fdatasync模式：寫數(shù)據(jù)時(shí),write這一步并不需要真正寫到磁盤才算完成(可能寫入到操作系統(tǒng)buffer中就會(huì)返回完成),真正完成是flush操作,buffer交給操作系統(tǒng)去flush,并且文件的元數(shù)據(jù)信息也都需要更新到磁盤.
O_DSYNC模式：寫日志操作是在write這步完成,而數(shù)據(jù)文件的寫入是在flush這步通過fsync完成.
MYSQL應(yīng)用

O_DIRECT模式：數(shù)據(jù)文件的寫入操作是直接從mysql innodb buffer到磁盤的,并不用通過操作系統(tǒng)的緩沖,而真正的完成也是在flush這步,日志還是要經(jīng)過OS緩沖.MYSQL應(yīng)用

Mysql必讀Mysql IO 內(nèi)存方面的優(yōu)化MYSQL應(yīng)用

通過圖可以看出O_DIRECT相比fdatasync的優(yōu)點(diǎn)是避免了雙緩沖,本身innodb buffer pool就是一個(gè)緩沖區(qū),不需要再寫入到系統(tǒng)的buffer,但是有個(gè)缺點(diǎn)是由于是直接寫入到磁盤,所以相比fdatasync的順序讀寫的效率要低些.
MYSQL應(yīng)用

在大量隨機(jī)寫的環(huán)境中O_DIRECT要比fdatasync效率更高些,順序?qū)懚嗟脑?還是默認(rèn)的fdatasync更高效.
MYSQL應(yīng)用

7、innodb_adaptive_flushing 設(shè)置為 ON (使刷新臟頁更智能)
MYSQL應(yīng)用

影響每秒刷新臟頁的數(shù)目
MYSQL應(yīng)用

規(guī)則由原來的“大于innodb_max_dirty_pages_pct時(shí)刷新100個(gè)臟頁到磁盤”變?yōu)?“通過buf_flush_get_desired_flush_reate函數(shù)判斷重做日志產(chǎn)生速度確定需要刷新臟頁的最合適數(shù)目”,即使臟頁比例小于 innodb_max_dirty_pages_pct時(shí)也會(huì)刷新一定量的臟頁.
MYSQL應(yīng)用

8、innodb_adaptive_flushing_method 設(shè)置為 keep_averageMYSQL應(yīng)用

影響checkpoint,更平均的計(jì)算調(diào)整刷臟頁的速度,進(jìn)行必要的flush.(該變量為mysql衍生版本Percona Server下的一個(gè)變量,原生mysql不存在)
MYSQL應(yīng)用

9、innodb_stats_on_metadata=OFFMYSQL應(yīng)用

關(guān)掉一些訪問information_schema庫(kù)下表而產(chǎn)生的索引統(tǒng)計(jì).
MYSQL應(yīng)用

當(dāng)重啟mysql實(shí)例后,mysql會(huì)隨機(jī)的io取數(shù)據(jù)遍歷所有的表來取樣來統(tǒng)計(jì)數(shù)據(jù),這個(gè)實(shí)際使用中用的不多,建議關(guān)閉.
MYSQL應(yīng)用

10、innodb_change_buffering=allMYSQL應(yīng)用

當(dāng)更新/插入的非聚集索引的數(shù)據(jù)所對(duì)應(yīng)的頁不在內(nèi)存中時(shí)(對(duì)非聚集索引的更新操作通常會(huì)帶來隨機(jī)IO),會(huì)將其放到一個(gè)insert buffer中,當(dāng)隨后頁面被讀到內(nèi)存中時(shí),會(huì)將這些變化的記錄merge到頁中.當(dāng)服務(wù)器比較空閑時(shí),后臺(tái)線程也會(huì)做merge操作.
MYSQL應(yīng)用

由于主要用到merge的優(yōu)勢(shì)來降低io,但對(duì)于一些場(chǎng)景并不會(huì)對(duì)固定的數(shù)據(jù)進(jìn)行多次修改,此處則并不需要把更新/插入操作開啟change_buffering,如果開啟只是多余占用了buffer_pool的空間和處理能力.這個(gè)參數(shù)要依據(jù)實(shí)際業(yè)務(wù)環(huán)境來配置.
MYSQL應(yīng)用

11、innodb_old_blocks_time=1000
MYSQL應(yīng)用

使Block在old sublist中停留時(shí)間長(zhǎng)為1s,不會(huì)被轉(zhuǎn)移到new sublist中,避免了Buffer Pool被污染BP可以被認(rèn)為是一條長(zhǎng)鏈表.被分成young 和 old兩個(gè)部分,其中old默認(rèn)占37%的大小(由innodb_old_blocks_pct 配置).靠近頂端的Page表示最近被訪問.靠近尾端的Page表示長(zhǎng)時(shí)間未被訪問.而這兩個(gè)部分的交匯處成為midpoint.每當(dāng)有新的Page需要加載到BP時(shí),該page都會(huì)被插入到midpoint的位置,并聲明為old-page.當(dāng)old部分的page,被訪問到時(shí),該page會(huì)被提升到鏈表的頂端,標(biāo)識(shí)為young.
MYSQL應(yīng)用

由于table scan的操作是先load page,然后立即觸發(fā)一次訪問.所以當(dāng)innodb_old_blocks_time =0 時(shí),會(huì)導(dǎo)致table scan所需要的page不讀的作為young page被添加到鏈表頂端.而一些使用較為不頻繁的page就會(huì)被擠出BP,使得之后的SQL會(huì)產(chǎn)生磁盤IO,從而導(dǎo)致響應(yīng)速度變慢.
MYSQL應(yīng)用

這時(shí)雖然mysqldump訪問的page會(huì)不斷加載在LRU頂端,但是高頻度的熱點(diǎn)數(shù)據(jù)訪問會(huì)以更快的速度把page再次搶占到LRU頂端.從而導(dǎo)致mysqldump加載入的page會(huì)被迅速刷下,并立即被evict(淘汰).因此,time=0或1000對(duì)這種壓力環(huán)境下的訪問不會(huì)造成很大影響,因?yàn)閐ump的數(shù)據(jù)根本搶占不過熱點(diǎn)數(shù)據(jù).不只dump,當(dāng)大數(shù)據(jù)操作的時(shí)候也是如此.
MYSQL應(yīng)用

12、binlog_cache_size
MYSQL應(yīng)用

二進(jìn)制日志緩沖大小：一個(gè)事務(wù),在沒有提交(uncommitted)的時(shí)候,產(chǎn)生的日志,記錄到Cache中；等到事務(wù)提交(committed)需要提交的時(shí)候,則把日志持久化到磁盤.
MYSQL應(yīng)用

設(shè)置太大的話,會(huì)比較消耗內(nèi)存資源(Cache本質(zhì)就是內(nèi)存),更加需要注意的是：binlog_cache是不是全局的,是按SESSION為單位獨(dú)享分配的,也就是說當(dāng)一個(gè)線程開始一個(gè)事務(wù)的時(shí)候,Mysql就會(huì)為這個(gè)SESSION分配一個(gè)binlog_cache
MYSQL應(yīng)用

怎么判斷我們當(dāng)前的binlog_cache_size設(shè)置的沒問題呢?MYSQL應(yīng)用

mysql> show status like 'binlog_%'; 
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| Binlog_cache_disk_use | 1425 |
| Binlog_cache_use | 126945718 |
+-----------------------+-----------+
2 rows in set (0.00 sec)
mysql> select @@binlog_cache_size; 
+---------------------+
| @@binlog_cache_size |
+---------------------+
| 1048576 |
+---------------------+
1 row in set (0.00 sec)

運(yùn)行情況Binlog_cache_use 表示binlog_cache內(nèi)存方式被用上了多少次,Binlog_cache_disk_use表示binlog_cache臨時(shí)文件方式被用上了多少次
MYSQL應(yīng)用

13、innodb_file_per_table
MYSQL應(yīng)用

innodb_file_per_table=1

獨(dú)立表空間
MYSQL應(yīng)用

優(yōu)點(diǎn)：MYSQL應(yīng)用

每個(gè)表的數(shù)據(jù)和索引都會(huì)存在自已的表空間中MYSQL應(yīng)用

可以實(shí)現(xiàn)單表在不同的數(shù)據(jù)庫(kù)中移動(dòng)MYSQL應(yīng)用

空間可以回收(除drop table操作)MYSQL應(yīng)用

刪除大量數(shù)據(jù)后可以通過:alter table TableName engine=innodb;回縮不用的空間MYSQL應(yīng)用

使用turncate table也會(huì)使空間收縮MYSQL應(yīng)用

對(duì)于使用獨(dú)立表空間的表,不管怎么刪除,表空間的碎片不會(huì)太嚴(yán)重的影響性能
MYSQL應(yīng)用

缺點(diǎn)：MYSQL應(yīng)用

單表增加過大,如超過100個(gè)G
MYSQL應(yīng)用

結(jié)論：共享表空間在Insert操作上少有優(yōu)勢(shì).其它都沒獨(dú)立表空間表現(xiàn)好.當(dāng)啟用獨(dú)立表空間時(shí),請(qǐng)合理調(diào)整一 下：innodb_open_files ,InnoDB Hot Backup(冷備)的表空間cp不會(huì)面對(duì)很多無用的copy了.而且利用innodb hot backup及表空間的管理命令可以實(shí)現(xiàn)單現(xiàn)移動(dòng).
MYSQL應(yīng)用

14、增加本地端口,以應(yīng)對(duì)大量連接
MYSQL應(yīng)用

echo ‘1024 65000′ > /proc/sys/net/ipv4/ip_local_port_range

該參數(shù)指定端口的分配范圍,該端口是向外訪問的限制.mysql默認(rèn)監(jiān)聽的3306端口即使有多個(gè)請(qǐng)求鏈接,也不會(huì)有影響.但是由于mysql是屬于高內(nèi)存、高cpu、高io應(yīng)用,不建議把多少應(yīng)用于mysql混搭在同一臺(tái)機(jī)器上.即使業(yè)務(wù)量不大,也可以通過降低單臺(tái)機(jī)器的配置,多臺(tái)機(jī)器共存來實(shí)現(xiàn)更好.
MYSQL應(yīng)用

15、增加隊(duì)列的鏈接數(shù)
MYSQL應(yīng)用

echo ‘1048576' > /proc/sys/net/ipv4/tcp_max_syn_backlog

建立鏈接的隊(duì)列的數(shù)越大越好,但是從另一個(gè)角度想,實(shí)際環(huán)境中應(yīng)該使用連接池更合適,避免重復(fù)建立鏈接造成的性能消耗.使用連接池,鏈接數(shù)會(huì)從應(yīng)用層面更可控些.
MYSQL應(yīng)用

16、設(shè)置鏈接超時(shí)時(shí)間
MYSQL應(yīng)用

echo '10' > /proc/sys/net/ipv4/tcp_fin_timeout

該參數(shù)主要為了降低TIME_WAIT占用的資源時(shí)長(zhǎng).尤其針對(duì)http短鏈接的服務(wù)端或者mysql不采用連接池效果比較明顯.
MYSQL應(yīng)用

轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/3630.html


    標(biāo)簽：