TOP

【轉】8G內存下MySQL的優化詳細方案
2019-08-20 16:32:48   字體:【 】  瀏覽:656次   評論:0

【轉】8G內存下MySQL的優化詳細方案

對于任何一個數據庫管理系統來說,內存的分配使用絕對可以算的上是其核心之一了,所以很多希望更為深入了解某數據庫管理系統的人,都會希望一窺究竟,我也不例外。

這里給出方案

按照下面的設置試試看:

key_buffer_size= 3840M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 256M
thread_cache_size = 32
query_cache_size = 256M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

其中key_buffer_size 上限是 4G,不能再多了。

但是實際的為了使MySql的性能最優化,內存的分配是需要進行調試的。建議你參考一下文章進行設置: 

 

從內存的使用方式MySQL 數據庫的內存使用主要分為以下兩類:線程獨享內存 和 全局共享內存

 

線程獨享內存

在 MySQL 中,線程獨享內存主要用于各客戶端連接線程存儲各種操作的獨享數據,如線程棧信息,分組排序操作,數據讀寫緩沖,結果集暫存等等,而且大多數可以通過相關參數來控制內存的使用量。

線程棧信息使用內存(thread_stack):主要用來存放每一個線程自身的標識信息,如線程id,線程運行時基本信息等等,我們可以通過 thread_stack 參數來設置為每一個線程棧分配多大的內存。

排序使用內存(sort_buffer_size):MySQL用此內存區域進行排序操作(filesort),完成客戶端的排序請求。當我們設置的排序區緩存大小無法滿足排序實際所需內存的時候,MySQL會將數據寫入磁盤文件來完成排序。由于磁盤和內存的讀寫性能完全不在一個數量級,所以sort_buffer_size參數對排序操作的性能影響絕對不可小視。

Join操作使用內存(join_buffer_size):應用程序經常會出現一些兩表(或多表)Join的操作需求,MySQL在完成某些 Join 需求的時候(all/indexjoin),為了減少參與Join的“被驅動表”的讀取次數以提高性能,需要使用到 Join Buffer 來協助完成 Join操作。當 Join Buffer 太小,MySQL 不會將該 Buffer 存入磁盤文件,而是先將Join Buffer中的結果集與需要 Join的表進行 Join 操作,然后清空 Join Buffer 中的數據,繼續將剩余的結果集寫入此 Buffer中,如此往復。這勢必會造成被驅動表需要被多次讀取,成倍增加 IO 訪問,降低效率。

順序讀取數據緩沖區使用內存(read_buffer_size):這部分內存主要用于當需要順序讀取數據的時候,如無發使用索引的情況下的全表掃描,全索引掃描等。在這種時候,MySQL按照數據的存儲順序依次讀取數據塊,每次讀取的數據快首先會暫存在read_buffer_size中,當 buffer空間被寫滿或者全部數據讀取結束后,再將buffer中的數據返回給上層調用者,以提高效率。

隨機讀取數據緩沖區使用內存(read_rnd_buffer_size):和順序讀取相對應,當MySQL進行非順序讀取(隨機讀取)數據塊的時候,會利用這個緩沖區暫存讀取的數據。如根據索引信息讀取表數據,根據排序后的結果集與表進行Join等等。總的來說,就是當數據塊的讀取需要滿足一定的順序的情況下,MySQL 就需要產生隨機讀取,進而使用到 read_rnd_buffer_size參數所設置的內存緩沖區。

連接信息及返回客戶端前結果集暫存使用內存(net_buffer_size):這部分用來存放客戶端連接線程的連接信息和返回客戶端的結果集。當 MySQL 開始產生可以返回的結果集,會在通過網絡返回給客戶端請求線程之前,會先暫存在通過net_buffer_size所設置的緩沖區中,等滿足一定大小的時候才開始向客戶端發送,以提高網絡傳輸效率。不過,net_buffer_size參數所設置的僅僅只是該緩存區的初始化大小,MySQL 會根據實際需要自行申請更多的內存以滿足需求,但最大不會超過max_allowed_packet 參數大小。

批量插入暫存使用內存(bulk_insert_buffer_size):當我們使用如 insert …values(…),(…),(…)… 的方式進行批量插入的時候,MySQL會先將提交的數據放如一個緩存空間中,當該緩存空間被寫滿或者提交完所有數據之后,MySQL才會一次性將該緩存空間中的數據寫入數據庫并清空緩存。此外,當我們進行 LOAD DATA INFILE 操作來將文本文件中的數據 Load進數據庫的時候,同樣會使用到此緩沖區。

臨時表使用內存(tmp_table_size):當我們進行一些特殊操作如需要使用臨時表才能完成的Order By,Group By 等等,MySQL 可能需要使用到臨時表。當我們的臨時表較小(小于 tmp_table_size參數所設置的大小)的時候,MySQL 會將臨時表創建成內存臨時表,只有當 tmp_table_size所設置的大小無法裝下整個臨時表的時候,MySQL 才會將該表創建成 MyISAM 存儲引擎的表存放在磁盤上。不過,當另一個系統參數max_heap_table_size 的大小還小于 tmp_table_size 的時候,MySQL 將使用max_heap_table_size 參數所設置大小作為最大的內存臨時表大小,而忽略 tmp_table_size 所設置的值。而且tmp_table_size 參數從 MySQL 5.1.2 才開始有,之前一直使用 max_heap_table_size。

上面所列舉的 MySQL 線程獨享內存僅僅只是所有線程獨享內存中的部分,并不是全部,選擇的原則是可能對 MySQL 的性能產生較大的影響,且可以通過系統參數進行調節。

由于以上內存都是線程獨享,極端情況下的內存總體使用量將是所有連接線程的總倍數。所以各位朋友在設置過程中一定要謹慎,切不可為了提升性能就盲目的增大各參數值,避免因為內存不夠而產生 Out Of Memory 異常或者是嚴重的 Swap 交換反而降低整體性能。

 

全局共享內存

全局共享內主要是 MySQLInstance(mysqld進程)以及底層存儲引擎用來暫存各種全局運算及可共享的暫存信息,如存儲查詢緩存的 QueryCache,緩存連接線程的 Thread Cache,緩存表文件句柄信息的 Table Cache,緩存二進制日志的 BinLogBuffer, 緩存 MyISAM 存儲引擎索引鍵的 Key Buffer以及存儲 InnoDB 數據和索引的 InnoDB BufferPool 等等。下面針對 MySQL 主要的共享內存進行一個簡單的分析。

查詢緩存(Query Cache):查詢緩存是 MySQL 比較獨特的一個緩存區域,用來緩存特定Query 的結果集(Result Set)信息,且共享給所有客戶端。通過對 Query 語句進行特定的 Hash 計算之后與結果集對應存放在Query Cache 中,以提高完全相同的 Query 語句的相應速度。當我們打開 MySQL 的 Query Cache 之后,MySQL接收到每一個 SELECT 類型的 Query 之后都會首先通過固定的 Hash 算法得到該 Query 的 Hash 值,然后到 QueryCache 中查找是否有對應的 Query Cache。如果有,則直接將 Cache的結果集返回給客戶端。如果沒有,再進行后續操作,得到對應的結果集之后將該結果集緩存到 Query Cache中,再返回給客戶端。當任何一個表的數據發生任何變化之后,與該表相關的所有 Query Cache 全部會失效,所以 Query Cache對變更比較頻繁的表并不是非常適用,但對那些變更較少的表是非常合適的,可以極大程度的提高查詢效率,如那些靜態資源表,配置表等等。為了盡可能高效的利用 Query Cache,MySQL 針對 Query Cache 設計了多個 query_cache_type 值和兩個 QueryHint:SQL_CACHE 和 SQL_NO_CACHE。當 query_cache_type 設置為0(或者 OFF)的時候不使用Query Cache,當設置為1(或者 ON)的時候,當且僅當 Query 中使用了 SQL_NO_CACHE 的時候 MySQL 會忽略Query Cache,當 query_cache_type 設置為2(或者DEMAND)的時候,當且僅當Query 中使用了SQL_CACHE 提示之后,MySQL 才會針對該 Query 使用 Query Cache。可以通過 query_cache_size來設置可以使用的最大內存空間。

連接線程緩存(Thread Cache):連接線程是 MySQL為了提高創建連接線程的效率,將部分空閑的連接線程保持在一個緩存區以備新進連接請求的時候使用,這尤其對那些使用短連接的應用程序來說可以極大的提高創建連接的效率。當我們通過 thread_cache_size設置了連接線程緩存池可以緩存的連接線程的大小之后,可以通過(Connections - Threads_created) /Connections * 100% 計算出連接線程緩存的命中率。注意,這里設置的是可以緩存的連接線程的數目,而不是內存空間的大小。

表緩存(Table Cache):表緩存區主要用來緩存表文件的文件句柄信息,在MySQL5.1.3之前的版本通過 table_cache 參數設置,但從MySQL5.1.3開始改為 table_open_cache來設置其大小。當我們的客戶端程序提交 Query 給 MySQL 的時候,MySQL 需要對 Query所涉及到的每一個表都取得一個表文件句柄信息,如果沒有 Table Cache,那么 MySQL就不得不頻繁的進行打開關閉文件操作,無疑會對系統性能產生一定的影響,Table Cache 正是為了解決這一問題而產生的。在有了 TableCache 之后,MySQL 每次需要獲取某個表文件的句柄信息的時候,首先會到 Table Cache中查找是否存在空閑狀態的表文件句柄。如果有,則取出直接使用,沒有的話就只能進行打開文件操作獲得文件句柄信息。在使用完之后,MySQL會將該文件句柄信息再放回 Table Cache池中,以供其他線程使用。注意,這里設置的是可以緩存的表文件句柄信息的數目,而不是內存空間的大小。

表定義信息緩存(Table definition Cache):表定義信息緩存是從MySQL5.1.3 版本才開始引入的一個新的緩存區,用來存放表定義信息。當我們的 MySQL中使用了較多的表的時候,此緩存無疑會提高對表定義信息的訪問效率。MySQL 提供了 table_definition_cache參數給我們設置可以緩存的表的數量。在 MySQL5.1.25 之前的版本中,默認值為128,從 MySQL5.1.25版本開始,則將默認值調整為 256 了,最大設置值為524288。注意,這里設置的是可以緩存的表定義信息的數目,而不是內存空間的大小。

二進制日志緩沖區(Binlog Buffer):二進制日志緩沖區主要用來緩存由于各種數據變更操做所產生的Binary Log 信息。為了提高系統的性能,MySQL 并不是每次都是將二進制日志直接寫入 Log File,而是先將信息寫入Binlog Buffer 中,當滿足某些特定的條件(如 sync_binlog參數設置)之后再一次寫入 Log File 中。我們可以通過binlog_cache_size 來設置其可以使用的內存大小,同時通過 max_binlog_cache_size限制其最大大小(當單個事務過大的時候 MySQL 會申請更多的內存)。當所需內存大于 max_binlog_cache_size參數設置的時候,MySQL 會報錯:“Multi-statement transaction required more than‘max_binlog_cache_size’ bytes of storage”。

MyISAM索引緩存(Key Buffer):MyISAM 索引緩存將 MyISAM 表的索引信息緩存在內存中,以提高其訪問性能。這個緩存可以說是影響 MyISAM 存儲引擎性能的最重要因素之一了,通過 key_buffere_size 設置可以使用的最大內存空間。

InnoDB 日志緩沖區(InnoDB Log Buffer):這是 InnoDB存儲引擎的事務日志所使用的緩沖區。類似于 Binlog Buffer,InnoDB 在寫事務日志的時候,為了提高性能,也是先將信息寫入Innofb Log Buffer 中,當滿足 innodb_flush_log_trx_commit參數所設置的相應條件(或者日志緩沖區寫滿)之后,才會將日志寫到文件(或者同步到磁盤)中。可以通過 innodb_log_buffer_size參數設置其可以使用的最大內存空間。
注:innodb_flush_log_trx_commit 參數對 InnoDB Log 的寫入性能有非常關鍵的影響。該參數可以設置為0,1,2,解釋如下:


0:log buffer中的數據將以每秒一次的頻率寫入到log file中,且同時會進行文件系統到磁盤的同步操作,但是每個事務的commit并不會觸發任何log buffer 到log file的刷新或者文件系統到磁盤的刷新操作;
1:在每次事務提交的時候將log buffer 中的數據都會寫入到log file,同時也會觸發文件系統到磁盤的同步;
2:事務提交會觸發log buffer 到log file的刷新,但并不會觸發磁盤文件系統到磁盤的同步。此外,每秒會有一次文件系統到磁盤同步操作。
此外,MySQL文檔中還提到,這幾種設置中的每秒同步一次的機制,可能并不會完全確保非常準確的每秒就一定會發生同步,還取決于進程調度的問題。實際上,InnoDB 能否真正滿足此參數所設置值代表的意義正常 Recovery 還是受到了不同 OS下文件系統以及磁盤本身的限制,可能有些時候在并沒有真正完成磁盤同步的情況下也會告訴 mysqld 已經完成了磁盤同步。

InnoDB 數據和索引緩存(InnoDB Buffer Pool):InnoDB BufferPool 對 InnoDB 存儲引擎的作用類似于 Key Buffer Cache 對 MyISAM 存儲引擎的影響,主要的不同在于InnoDB Buffer Pool 不僅僅緩存索引數據,還會緩存表的數據,而且完全按照數據文件中的數據快結構信息來緩存,這一點和Oracle SGA 中的 database buffer cache 非常類似。所以,InnoDB Buffer Pool 對 InnoDB存儲引擎的性能影響之大就可想而知了。可以通過 (Innodb_buffer_pool_read_requests -Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%計算得到 InnoDB Buffer Pool 的命中率。

InnoDB 字典信息緩存(InnoDB Additional Memory Pool):InnoDB字典信息緩存主要用來存放 InnoDB 存儲引擎的字典信息以及一些 internal 的共享數據結構信息。所以其大小也與系統中所使用的InnoDB 存儲引擎表的數量有較大關系。不過,如果我們通過 innodb_additional_mem_pool_size參數所設置的內存大小不夠,InnoDB 會自動申請更多的內存,并在 MySQL 的 Error Log 中記錄警告信息。

這里所列舉的各種共享內存,是我個人認為對 MySQL 性能有較大影響的集中主要的共享內存。實際上,除了這些共享內存之外,MySQL 還存在很多其他的共享內存信息,如當同時請求連接過多的時候用來存放連接請求信息的back_log隊列等。

Tags:內存 MySQL 優化 詳細 方案 責任編輯:羅賓
】【打印繁體】【投稿】【收藏】 【推薦】【舉報】【評論】 【關閉】 【返回頂部
上一篇遠程讀取數據庫的寫法。 下一篇windows mysql 優化配置參數。

  •  摘要:公司網站訪問量越來越大,MySQL自然成為瓶頸,因此最近我一直在研究 MySQL 的優化,第一步自然想到的是 MySQL 系統參數的優化,作為一個訪問量很大的網站(日20萬人次以上)的數據庫。在Apache, PHP, MySQL的體系架構中,MySQL對于性能的影響最大,也是關鍵的核心部分。對于Discuz!論壇程序也是如此,MySQL的設置是否合理優化,直接影響到..

  • 問題事件名稱:APPCRASH應用程序名:httpd.exe應用程序版本:2.4.23.0應用程序時間戳:577703ab故障模塊名稱:ntdll.dll重新注冊Window動態庫就可以了。運行中輸入cmd,在命令提示符下輸入:for %1 in (%windir%\system32\*.dll) do regsvr32.exe /s %1注意,是在命令提示符下,不是在“運行框”中!!環境變量在path中加入如下路徑,就好了D:\wamp\php..

  • 到php.ini配置文件里面找到 allow_url_fopen = On 把Off設置為On即可 語法:file_get_contents(path,include_path,context,start,max_length) file_get_contents() 函數把整個文件讀入一個字符串中。 和 file() 一樣,不同的是 file_get_contents() 把文件讀入一個字符串。 file_get_contents() 函數是用于將文件的內容讀入到一個字符串中的首選方法。如果操作系統支持,還會使用..

  • 網站常見安全問題記錄(持續更新)Apache說明 初衷:本文檔用于記錄所遇到的網站安全問題,并分類匯總,方便后期遇到類似問題,能夠快速找到解決方案,提高效率,讓程序員有更多的時間去把妹,LOL...記錄規范:標題必須清晰明了,方便用戶快速查找,拒絕標題黨;問題放到正確的分類中;記錄問題的時候先闡述問題,再列出解決方法,盡量做到有圖有真相;如果有對應的資料,可以附..

  • $_SERVER 是PHP預定義的超全局變量。所謂“超全局變量”,即在腳本全部作用域中都可以使用,$_SERVER保存關于報頭、路徑和腳本位置的信息。工作中經常忘記,在此整理記錄下,加深印象。測試是在Windows下進行的,環境為Apache/2.4.23 (Win32)+PHP/5.6.27-nts,訪問域名為http://www.example.com/index....,文件目錄在E:/WWW/example/。主要內容詳解$_SERVER["SCRIPT_N..

  • 系統環境:Windows Server 2008 R2 + Sql Server 2008 R2 問題描述:Windows Server 2008 R2系統內存占用率過大,而在任務管理器中各進程內存占用總和都遠不到此占用率。相關現象:1. 內存占用率90%以上2. 任務管理器中所有進程內存和較低,遠不到90%,有二十多G的內存偏差 分析過程: 首先懷疑SQL&..

  • ALTER TABLE zysjyj DROP aid;ALTER TABLE zysjyj ADD aid int(10) NOT NULL FIRST;ALTER TABLE zysjyj AUTO_INCREMENT=10000;ALTER TABLE zysjyj MODIFY COLUMN aid int(10) NOT NULL AUTO_INCREMENT,ADD PRIMARY KEY(aid);

  • 前言最近在進行apache性能優化設置。在修改apache配置)文件之前需要備份原有的配置文件夾conf,這是網站架設的好習慣。以下的apache配置調優均是在red had的環境下進行的。httpd 相關查看命令了解查看當前安裝模塊mpm(多路處理器)[root@localhost ~]# httpd -l查看httpd進程數(即各個mpm模式下Apache能夠處理的并發請求數)[root@localhost ~]# p..

  • 說明在我的五旬節期間,我注意到您可以通過java script,如果您的站點易受XSS影響,并且您正在使用Apache HTTP服務器。這是可能的,因為apachehttp服務器發送一個“http400壞消息”請求“如果HTTP頭值太長(超過服務器限制)。在本例中,apache還包括頭名稱和頭值在400錯誤頁上。所以通過XSS可以設置一組大的cookies,激發XHR請求并讀取響應,還包含僅HttpCookies據我所知,..

-->

發布者

羅賓 羅賓
等級:普通會員
積分:0 個
性別:保密
luobing@qq.com
47 身份還未認證 郵箱還未認證 手機還未認證 給它發送短消息 搜尋它在mysql發布的全部文章 51

最新文章

網站服務地區

運城市網站建設
紅河哈尼族彝族自治州網站建設
宣城市網站建設
唐山市網站建設
通遼市網站建設
巴彥淖爾市網站建設
高雄縣網站建設
隴南市網站建設
林芝地區網站建設
南通市網站建設
大港區網站建設
鄂州市網站建設
怒江傈僳族自治州網站建設
葫蘆島市網站建設
揭陽市網站建設
南區網站建設
瓊海市網站建設
仙桃市網站建設
元朗區網站建設
瀘州市網站建設
萬寧市網站建設
徐匯區網站建設

本月熱門

相關文章

120期波叔一波中特图