Mysql IO 內存方面的優化。本站提示廣大學習愛好者：（Mysql IO 內存方面的優化）文章只能為提供參考，不一定能成為您想要的結果。以下是Mysql IO 內存方面的優化正文
這裡應用的是mysql Ver 14.14 Distrib 5.6.19, for Linux (i686) using EditLine wrapper
1、mysql目次文件
ibdata1:體系表空間 包括數據字典、回滾日記/undolog等
（insert buffer segment/double write segment/rollback segment/index segment/dictionary segment/undo segment）
ib_logfile0/ib_logfile1：事務日記/redolog
mysql-relay-bin：中繼日記
binarylog：二進制日記
general_log.log：慣例日記
mysql_error.log：毛病日記
slow_query.log：慢日記
.ibd：用戶表空間-數據文件（insert buffer bitmap page/leaf page segment/none leaf page segment）
Innodb buffer pool（內存）：undo page /insert buffer page/adaptive hash index/index page/lock info/data dictionary
2、mysql線程
FILE IO
--------
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/s
innodb後台一切線程
| 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 |
IO線程分離是insert buffer thread、log thread、read thread、write thread。
在MySQL 5.6.10以後，默許線程處置模子應用履行每一個客戶端銜接一個線程語句。跟著愈來愈多的客戶端銜接到辦事器和履行語句，全體機能下降。線程池插件的供給旨在削減開支，進步機能的其他線程的處置形式。該插件完成了經由過程有用地治理語句履行線程的年夜量客戶端銜接的進步辦事器機能的線程池。
InnoDB Plugin版本開端增長了默許IO thread的數目，默許的read thread和write thread分離增年夜到了4個，而且不再應用innodb_file_io_threads參數，而是分離應用innodb_read_io_threads和innodb_write_io_threads參數。
線程池處理每一個銜接模子處理單線程的幾個成績
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.
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.
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.
3、mysql拜訪文件流程
Transaction 來自收集
3、影響IO/內存的一些參數
1、innodb_flush_log_at_trx_commit 設置為2
這參數是指 事務log（ib_logfile0、ib_logfile1）以如何的方法寫入到log buffer
=0 mysql crash 就喪失了，機能最好
buffer pool -> log buffer 每秒 wirte os cache & flush磁盤
=1 不會喪失，效力低
buffer pool -> log buffer 每次 write os cache & flush磁盤
=2 即便mysql瓦解也不會丟數據
buffer pool -> os cache 每秒flush 磁盤
留意：因為過程調劑戰略成績,這個“每秒履行一次 flush(刷到磁盤)操作”其實不是包管100%的“每秒
2、sync_binlog
二進制日記（binary log）同步到磁盤的頻率。binary log 每寫入sync_binlog 次後，刷寫到磁盤。
假如 autocommit 開啟，每一個語句都寫一次 binary log，不然每次事務寫一次。
默許值是 0，不自動同步，而依附操作體系自己不按期把文件內容 flush 到磁盤
設為 1 最平安，在每一個語句或事務後同步一次 binary log，即便在瓦解時也最多喪失一個語句或事務的日記，但是以也最慢。
年夜多半情形下，對數據的分歧性並沒有很嚴厲的請求，所以其實不會把 sync_binlog 設置裝備擺設成 1，為了尋求高並發，晉升機能，可以設置為 100 或直接用 0
3、write/read thread
異步IO線程數
innodb_write_io_threads=16
innodb_read_io_threads=16
(該參數須要在設置裝備擺設文件中添加，重啟mysql實例起效)髒頁寫的線程數，加年夜該參數可以晉升寫入機能
4、innodb_max_dirty_pages_pct
最年夜髒頁百分數，當體系中髒頁所占百分比跨越這個值，INNODB就會停止寫操作以把頁中的已更新數據寫入到磁盤文件中。默許75，普通如今風行的SSD硬盤很難到達這個比例。可根據現實情形在75-80之間調理
5、innodb_io_capacity=5000
從緩沖區刷新髒頁時，一次刷新髒頁的數目。依據磁盤IOPS的才能普通建議設置以下：
SAS 200
SSD 5000
PCI-E 10000-50000
6、innodb_flush_method=O_DIRECT(該參數須要重啟mysql實例起效)
掌握innodb 數據文件和redo log的翻開、刷寫形式。有三個值：fdatasync(默許)，O_DSYNC，O_DIRECT。
fdatasync形式：寫數據時，write這一步其實不須要真正寫到磁盤才算完成（能夠寫入到操作體系buffer中就會前往完成），真正完成是flush操作，buffer交給操作體系去flush,而且文件的元數據信息也都須要更新到磁盤。
O_DSYNC形式：寫日記操作是在write這步完成，而數據文件的寫入是在flush這步經由過程fsync完成。
O_DIRECT形式：數據文件的寫入操作是直接從mysql innodb buffer到磁盤的，其實不用經由過程操作體系的緩沖，而真實的完成也是在flush這步,日記照樣要經由OS緩沖。
經由過程圖可以看出O_DIRECT比擬fdatasync的長處是防止了雙緩沖，自己innodb buffer pool就是一個緩沖區，不須要再寫入到體系的buffer，然則有個缺陷是因為是直接寫入到磁盤，所以比擬fdatasync的次序讀寫的效力要低些。
在年夜量隨機寫的情況中O_DIRECT要比fdatasync效力更高些，次序寫多的話，照樣默許的fdatasync更高效。
7、innodb_adaptive_flushing 設置為 ON （使刷新髒頁更智能）
影響每秒刷新髒頁的數量
規矩由本來的“年夜於innodb_max_dirty_pages_pct時刷新100個髒頁到磁盤”變成 “經由過程buf_flush_get_desired_flush_reate函數斷定重做日記發生速度肯定須要刷新髒頁的最適合數量”，即便髒頁比例小於 innodb_max_dirty_pages_pct時也會刷新必定量的髒頁。
8、innodb_adaptive_flushing_method 設置為 keep_average
影響checkpoint，更均勻的盤算調劑刷髒頁的速度，停止需要的flush.（該變量為mysql衍生版本Percona Server下的一個變量，原生mysql不存在）
9、innodb_stats_on_metadata=OFF
關失落一些拜訪information_schema庫下表而發生的索引統計。
當重啟mysql實例後，mysql會隨機的io取數據遍歷一切的表來取樣來統計數據，這個現實應用頂用的不多，建議封閉.
10、innodb_change_buffering=all
當更新/拔出的非集合索引的數據所對應的頁不在內存中時（對非集合索引的更新操作平日會帶來隨機IO），會將其放到一個insert buffer中，當隨後頁面被讀到內存中時，會將這些變更的記載merge到頁中。當辦事器比擬余暇時，後台線程也會做merge操作。
因為重要用到merge的優勢來下降io，但關於一些場景其實不會對固定的數據停止屢次修正，此處則其實不須要把更新/拔出操作開啟change_buffering，假如開啟只是過剩占用了buffer_pool的空間和處置才能。這個參數要根據現實營業情況來設置裝備擺設。
11、innodb_old_blocks_time=1000
使Block在old sublist中逗留時光長為1s，不會被轉移到new sublist中,防止了Buffer Pool被淨化BP可以被以為是一條長鏈表。被分紅young 和 old兩個部門，個中old默許占37%的年夜小（由innodb_old_blocks_pct 設置裝備擺設）。接近頂真個Page表現比來被拜訪。接近尾真個Page表現長時光未被拜訪。而這兩個部門的交匯處成為midpoint。每當有新的Page須要加載到BP時，該page都邑被拔出到midpoint的地位，並聲明為old-page。當old部門的page，被拜訪到時，該page會被晉升到鏈表的頂端，標識為young。
因為table scan的操作是先load page，然後立刻觸發一次拜訪。所以當innodb_old_blocks_time =0 時，會招致table scan所須要的page不讀的作為young page被添加到鏈表頂端。而一些應用較為不頻仍的page就會被擠出BP，使得以後的SQL會發生磁盤IO，從而招致呼應速度變慢。
這時候固然mysqldump拜訪的page會赓續加載在LRU頂端，然則高頻度的熱門數據拜訪會以更快的速度把page再次搶占到LRU頂端。從而招致mysqldump加載入的page會被敏捷刷下，並立刻被evict（镌汰）。是以，time=0或1000對這類壓力情況下的拜訪不會形成很年夜影響，由於dump的數據基本搶占不外熱門數據。不只dump，當年夜數據操作的時刻也是如斯。
12、binlog_cache_size
二進制日記緩沖年夜小：一個事務，在沒有提交（uncommitted）的時刻，發生的日記，記載到Cache中；比及事務提交（committed）須要提交的時刻，則把日記耐久化到磁盤。
設置太年夜的話，會比擬消費內存資本（Cache實質就是內存），加倍須要留意的是：binlog_cache是否是全局的，是按SESSION為單元獨享分派的，也就是說當一個線程開端一個事務的時刻，Mysql就會為這個SESSION分派一個binlog_cache
怎樣斷定我們以後的binlog_cache_size設置的沒成績呢？
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)
運轉情形Binlog_cache_use 表現binlog_cache內存方法被用上了若干次，Binlog_cache_disk_use表現binlog_cache暫時文件方法被用上了若干次
13、innodb_file_per_table
innodb_file_per_table=1
自力表空間
長處：
每一個表的數據和索引都邑存在自已的表空間中
可以完成單表在分歧的數據庫中挪動
空間可以收受接管（除drop table操作）
刪除年夜量數據後可以經由過程:alter table TableName engine=innodb;回縮不消的空間
應用turncate table也會使空間壓縮
關於應用自力表空間的表，不論怎樣刪除，表空間的碎片不會太嚴重的影響機能
缺陷：
單表增長過年夜，如跨越100個G
結論：同享表空間在Insert操作上少有優勢。其它都沒自力表空間表示好。當啟用自力表空間時，請公道調劑一 下：innodb_open_files ，InnoDB Hot Backup（冷備）的表空間cp不會見對許多無用的copy了。並且應用innodb hot backup及表空間的治理敕令可以完成單現挪動。
14、增長當地端口，以應對年夜量銜接
echo ‘1024 65000′ > /proc/sys/net/ipv4/ip_local_port_range
該參數指定端口的分派規模，該端口是向外拜訪的限制。mysql默許監聽的3306端口即便有多個要求鏈接，也不會有影響。然則因為mysql是屬於高內存、高cpu、高io運用，不建議把若干運用於mysql混搭在統一台機械上。即便營業量不年夜，也能夠經由過程下降單台機械的設置裝備擺設，多台機械共存來完成更好。
15、增長隊列的鏈接數
echo ‘1048576' > /proc/sys/net/ipv4/tcp_max_syn_backlog
樹立鏈接的隊列的數越年夜越好，然則從另外一個角度想，現實情況中應當應用銜接池更適合，防止反復樹立鏈接形成的機能消費。應用銜接池，鏈接數會從運用層面更可控些。
16、設置鏈接超不時間
echo '10' > /proc/sys/net/ipv4/tcp_fin_timeout
該參數重要為了下降TIME_WAIT占用的資本時長。特別針對http短鏈接的辦事端或許mysql不采取銜接池後果比擬顯著。
