【mysql】關於IO/記憶體方面的一些優化

来源:http://www.cnblogs.com/chenpingzhao/archive/2016/01/10/5119161.html
-Advertisement-
Play Games

這裡使用的是mysql Ver 14.14 Distrib 5.6.19, for Linux (i686) using EditLine wrapper一、mysql目錄文件ibdata1:系統表空間 包含數據字典、回滾日誌/undolog等（insert buffer segment/doubl...


這裡使用的是mysql  Ver 14.14 Distrib 5.6.19, for Linux (i686) using  EditLine wrapper

一、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

二、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.

三、mysql訪問文件流程

Transaction 來自網路

 

 

三、影響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不採用連接池效果比較明顯。

 

參考文章

http://dev.cmcm.com/archives/107/comment-page-1

http://www.zhdba.com/mysqlops/2012/05/24/mysql-io/

http://blog.itpub.net/22664653/viewspace-1063134/

http://liyangliang.me/posts/2014/03/innodb_flush_log_at_trx_commit-and-sync_binlog/

http://www.cnblogs.com/snifferhu/p/4736479.html

http://www.cnblogs.com/xuanzhi201111/p/4040681.html

http://mysqllover.com/?p=636

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 電腦連接海信電視 HDMI
    註意：我們家的電視是海信的，所以不能代表所有的電視哦~~~ 家裡電視有線電視已經過期很長時間了，早就想把電腦連接到電視上用電視做顯示器的心了，今天來興趣了，就弄了一下！！！ 用電腦連接電視需要先解決兩個問題： 1、電視有介面能讓你連，例如HDMI等 備好...
  • 全局變數的說明和列子
    一、全局變數 變數 含義@@ERROR最後一SQL錯誤的錯誤號@@IDENTITY最後一次插入的標識值@@LANGUAGE當前使用的語言名稱@@MAX_CONNETIONS可以創建的、同時連接的最大數目@@ROWCOUNT受上一個SQl語句影響的行數（增加語句）@@SERV...
  • 將資料庫備份到AZURE blob storage
    1創建一個Storage Account1)點擊Browse->Storage accounts2) 填寫Storage account，請記住這個名字，之後創建credential需要用到。3)點擊Create。 一般等待一段時間就好了2 創建container1)All resources->B...
  • YARN DistributedShell源碼分析與修改
    YARN DistributedShell源碼分析與修改 轉載請註明出處： "http://www.cnblogs.com/BYRans/" <br/ "1 概述" "2 YARN DistributedShell不能滿足當前需求" "2.1 功能需求" "2.2 YARN Distr...
  • SQLite使用(二)&&數據類型
    1.概述 我們熟知的資料庫引擎大部分採用靜態數據類型，即列定義的類型定義了值的存儲，並且值要嚴格滿足列的定義，同一列所有值的存儲方式都相同，比如定義了一個列類型為整型 int，不能在該列上輸入'abc'。SQLite的數據類型則採用了動態類型，列定義不能決定值的存儲，值的存儲由值本身決定，因此在.....
  • 資料庫的實現----(建庫,建表,建約束)
    首先大家要知道資料庫是由哪三個部分組成的.解析:01.資料庫文件:*.mdf02.次要數據文件:*.ndf03.日誌文件:*.ldf每個資料庫至少要包含兩個文件:一個數據文件和一個日誌文件.數據文件中包含了資料庫的數據和對象,如表,視圖和索引等;日誌文件中包含了用於恢複數據庫所需的信息.創建資料庫時...
  • SQL Server中的事務日誌管理(8/9)：優化日誌吞吐量
    當一切正常時，沒有必要特別留意什麼是事務日誌，它是如何工作的。你只要確保每個資料庫都有正確的備份。當出現問題時，事務日誌的理解對於採取修正操作是重要的，尤其在需要緊急恢複數據庫到指定點時。這系列文章會告訴你每個DBA應該知道的具體細節。對於日誌文件的最大日誌吞吐量，我們從存儲架構思路的簡單回顧開始，...
  • 這輩子寫過的比較有意思的幾個sql
    遞歸withmyRecursionas(select*fromrecursionwhereid=1unionallselectr.*frommyRecursionm,recursionrwherem.id=r.pid)select*frommyRecursionPs:unionall不去重求並集很多...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類