linux 下怎么優(yōu)化mysql占用內(nèi)存？

修改mysql配置文件，優(yōu)化緩存大小和連接數(shù)連接方式，優(yōu)化sql語句 ,記得mysql好像是有工具可以查看最占用資源的sql語句，找到他，優(yōu)化他。

10年積累的成都做網(wǎng)站、網(wǎng)站制作經(jīng)驗，可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認識你，你也不認識我。但先網(wǎng)站制作后付款的網(wǎng)站建設(shè)流程，更有興化免費網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

安裝好mysql后，配制文件應(yīng)該在/usr/local/mysql/share/mysql目錄中，配制文件有幾個，有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的網(wǎng)站和不同配制的服務(wù)器環(huán)境，當然需要有不同的配制文件了。

一般的情況下，my-medium.cnf這個配制文件就能滿足我們的大多需要；一般我們會把配置文件拷貝到/etc/my.cnf 只需要修改這個配置文件就可以了，使用mysqladmin variables extended-status _u root _p 可以看到目前的參數(shù)，有3個配置參數(shù)是最重要的，即key_buffer_size,query_cache_size,table_cache。

key_buffer_size只對MyISAM表起作用，

key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。一般我們設(shè)為16M,實際上稍微大一點的站點　這個數(shù)字是遠遠不夠的，通過檢查狀態(tài)值Key_read_requests和Key_reads,可以知道key_buffer_size設(shè)置是否合理。比例 key_reads / key_read_requests應(yīng)該盡可能的低，至少是1:100，1:1000更好（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。 或者如果你裝了phpmyadmin 可以通過服務(wù)器運行狀態(tài)看到,筆者推薦用phpmyadmin管理mysql，以下的狀態(tài)值都是本人通過phpmyadmin獲得的實例分析:

這個服務(wù)器已經(jīng)運行了20天

key_buffer_size _ 128M

key_read_requests _ 650759289

key_reads - 79112

比例接近1:8000 健康狀況非常好

mysql 性能優(yōu)化參數(shù)配置

mysql -u root -p 回車輸入密碼進入mysql

show processlist;

查看連接數(shù)，可以發(fā)現(xiàn)有很多連接處于sleep狀態(tài)，這些其實是暫時沒有用的，所以可以kill掉

show variables like "max_connections";

查看最大連接數(shù)，應(yīng)該是與上面查詢到的連接數(shù)相同，才會出現(xiàn)too many connections的情況

set GLOBAL max_connections=1000;

修改最大連接數(shù)，但是這不是一勞永逸的方法，應(yīng)該要讓它自動殺死那些sleep的進程。

show global variables like 'wait_timeout';

這個數(shù)值指的是mysql在關(guān)閉一個非交互的連接之前要等待的秒數(shù)，默認是28800s

set global wait_timeout=300;

修改這個數(shù)值，這里可以隨意，最好控制在幾分鐘內(nèi)

set global interactive_timeout=500;

修改這個數(shù)值，表示mysql在關(guān)閉一個連接之前要等待的秒數(shù)，至此可以讓mysql自動關(guān)閉那些沒用的連接，但要注意的是，正在使用的連接到了時間也會被關(guān)閉，因此這個時間值要合適

SHOW VARIABLES LIKE '%table_open_cache%';

查看

show global status like 'Open%tables';

MySQL數(shù)據(jù)庫優(yōu)化（七）

為了能最小化磁盤I/O MyISAM 存儲引擎采用了很多數(shù)據(jù)庫系統(tǒng)使用的一種策略 它采用一種機制將最經(jīng)常訪問的表保存在內(nèi)存區(qū)塊中

對索引區(qū)塊來說 它維護著一個叫索引緩存(索引緩沖)的結(jié)構(gòu)體 這個結(jié)構(gòu)體中放著許多那些最常使用的索引區(qū)塊的緩沖區(qū)塊 對數(shù)據(jù)區(qū)塊來說 MySQL沒有使用特定的緩存 它依靠操作系統(tǒng)的本地文件系統(tǒng)緩存

本章首先描述了 MyISAM 索引緩存的基本操作 然后討論在MySQL 中所做的改進 它提高了索引緩存性能 同時能更好地控制緩存操作

線程之間不再是串行地訪問索引緩存 多個線程可以并行地訪問索引緩存 可以設(shè)置多個索引緩存 同時也能指定數(shù)據(jù)表索引到特定的緩存中

索引緩存機制對 ISAM 表同樣適用 不過 這種有效性正在減弱 自從MySQL 開始 MyISAM 表類型引進之后 ISAM 就不再建議使用了 MySQL 更是延續(xù)了這個趨勢 ISAM 類型默認被禁用了

可以通過系統(tǒng)變量 key_buffer_size 來控制索引緩存區(qū)塊的大小 如果這個值大小為 那么就不使用緩存 當這個值小得于不足以分配區(qū)塊緩沖的最小數(shù)量( )時 也不會使用緩存

當索引緩存無法操作時 索引文件就只通過操作系統(tǒng)提供的本地文件系統(tǒng)緩沖來訪問(換言之 表索引區(qū)塊采用的訪問策略和數(shù)據(jù)區(qū)塊的一致)

一個索引區(qū)塊在 MyISAM 索引文件中是一個連續(xù)訪問的單元 通常這個索引區(qū)塊的大小和B樹索引節(jié)點大小一樣(索引在磁盤中是以B樹結(jié)構(gòu)來表示的 這個樹的底部時葉子節(jié)點 葉子節(jié)點之上則是非葉子節(jié)點)

在索引緩存結(jié)構(gòu)中所有的區(qū)塊大小都是一樣的 這個值可能等于 大于 或小于表的索引區(qū)塊大小 通常這兩個值是不一樣的

當必須訪問來自任何表的索引區(qū)塊時 服務(wù)器首先檢查在索引緩存中是否有可用的緩沖區(qū)塊 如果有 服務(wù)器就訪問緩存中的數(shù)據(jù) 而非磁盤 就是說 它直接存取緩存 而不是存取磁盤 否則 服務(wù)器選擇一個(多個)包含其它不同表索引區(qū)塊的緩存緩沖區(qū)塊 將它的內(nèi)容替換成請求表的索引區(qū)塊的拷貝 一旦新的索引區(qū)塊在緩存中了 索引數(shù)據(jù)就可以存取了

當發(fā)生被選中要替換的區(qū)塊內(nèi)容修改了的情況時 這個區(qū)塊就被認為 臟 了 那么 在替換之前 它的內(nèi)容就必須先刷新到它指向的標索引

通常服務(wù)器遵循LRU(最近最少使用)策略 當要選擇替換的區(qū)塊時 它選擇最近最少使用的索引區(qū)塊 為了想要讓選擇變得更容易 索引緩存模塊會維護一個包含所有使用區(qū)塊特別的隊列(LRU鏈) 當一個區(qū)塊被訪問了 就把它放到隊列的最后位置 當區(qū)塊要被替換時 在隊列開始位置的區(qū)塊就是最近最少使用的 它就是第一候選刪除對象

共享訪問索引緩存

在MySQL 以前 訪問索引緩存是串行的 兩個線程不能并行地訪問索引緩存緩沖 服務(wù)器處理一個訪問索引區(qū)塊的請求只能等它之前的請求處理完 結(jié)果 新的請求所需的索引區(qū)塊就不在任何索引緩存環(huán)沖區(qū)塊中 因為其他線程把包含這個索引區(qū)塊的緩沖給更新了

從MySQL 開始 服務(wù)器支持共享方式訪問索引緩存

沒有正在被更新的緩沖可以被多個線程訪問

緩沖正被更新時 需要使用這個緩沖的線程只能等到更新完成之后

多個線程可以初始化需要替換緩存區(qū)塊的請求 只要它們不干擾別的線程(也就是 它們請求不同的索引區(qū)塊 因此不同的緩存區(qū)塊被替換)

共享方式訪問索引緩存令服務(wù)器明顯改善了吞吐量

多重索引緩存

共享訪問索引緩存改善了性能 卻不能完全消除線程間的沖突 它們?nèi)匀粻帗尶刂乒芾泶嫒∷饕彺婢彌_的結(jié)構(gòu) 為了更進一步減少索引緩存存取沖突 MySQL 提供了多重索引緩存特性 這能將不同的表索引指定到不同的索引緩存

當有多個索引緩存 服務(wù)器在處理指定的 MyISAM 表查詢時必須知道該使用哪個 默認地 所有的 MyISAM 表索引都緩存在默認的索引緩存中 想要指定到特定的緩存中 可以使用 CACHE INDEX 語句

如下語句所示 指定表的索 t t 和 t 引緩存到名為 hot_cache 的緩存中

mysql?CACHE?INDEX?t ?t ?t ?IN?hot_cache; + + + + + |?Table?|?Op?|?Msg_type?|?Msg_text?| + + + + + |?test t ?|?assign_to_keycache?|?status?|?OK?| |?test t ?|?assign_to_keycache?|?status?|?OK?| |?test t ?|?assign_to_keycache?|?status?|?OK?| + + + + +

注意 如果服務(wù)器編譯支持存 ISAM 儲引擎了 那么 ISAM 表也使用索引緩存機制 不過 ISAM 表索引只能使用默認的索引緩存而不能自定義

CACHE INDEX 語句中用到的索引緩存是根據(jù)用 SET GLOBAL 語句的參數(shù)設(shè)定的值或者服務(wù)器啟動參數(shù)指定的值創(chuàng)建的 如下 mysql SET GLOBAL keycache key_buffer_size= * ;想要刪除索引緩存 只需設(shè)置它的大小為 mysql SET GLOBAL keycache key_buffer_size= ;索引緩存變量是一個結(jié)構(gòu)體變量 由名字和組件構(gòu)成 例如 keycache key_buffer_size keycache 就是緩存名 key_buffer_size 是緩存組件 默認地 表索引在服務(wù)器啟動時指定到主(默認的)索引緩存中 當一個索引緩存被刪掉后 指定到這個緩存的所有索引都被重新指向到了默認索引緩存中去 對一個繁忙的系統(tǒng)來說 我們建議以下三條策略來使用索引緩存 熱緩存占用 %的總緩存空間 用于繁重搜索但很少更新的表 冷緩存占用 %的總緩存空間 用于中等強度更新的表 如臨時表 冷緩存占用 %的總緩存空間 作為默認的緩存 用于所有其他表 使用三個緩存的一個原因是好處在于 存取一個緩存結(jié)構(gòu)時不會阻止對其他緩存的訪問 訪問一個表索引的查詢不會跟指定到其他緩存的查詢競爭 性能提高還表現(xiàn)在以下幾點原因 熱緩存只用于檢索記錄 因此它的內(nèi)容總是不需要變化 所以 無論什么時候一個索引區(qū)塊需要從磁盤中引入 被選中要替換的緩存區(qū)塊的內(nèi)容總是要先被刷新 索引被指向熱緩存中后 如果沒有需要掃描全部索引的查詢 那么對應(yīng)到B樹中非葉子節(jié)點的索引區(qū)塊極可能還保留在緩存中 在臨時表里必須頻繁執(zhí)行一個更新操作是相當快的 如果要被更新的節(jié)點已經(jīng)在緩存中了 它無需先從磁盤中讀取出來 當臨時表的索引大小和冷緩存大小一樣時 那么在需要更新一個節(jié)點時它已經(jīng)在緩存中存在的幾率是相當高的

中點插入策略

默認地 MySQL 的索引緩存管理系統(tǒng)采用LRU策略來選擇要被清除的緩存區(qū)塊 不過它也支持更完善的方法 叫做 中點插入策略

使用中點插入策略時 LRU鏈就被分割成兩半 一個熱子鏈 一個溫子鏈 兩半分割的點不是固定的 不過緩存管理系統(tǒng)會注意不讓溫子鏈部分 太短 總是至少包括全部緩存區(qū)塊的 key_cache_division_limit 比率 key_cache_division_limit 是緩存結(jié)構(gòu)體變量的組件部分 因此它是每個緩存都可以設(shè)置這個參數(shù)值

當一個索引區(qū)塊從表中讀入緩存時 它首先放在溫子鏈的末尾 當達到一定的點擊率(訪問這個區(qū)塊)后 它就提升到熱子鏈中去 目前 要提升一個區(qū)塊的點擊率( )對每個區(qū)塊來說都是一樣的 將來 我們會讓點擊率依靠B樹中對應(yīng)的索引區(qū)塊節(jié)點的級別 包含非葉子節(jié)點的索引區(qū)塊所要求的提升點擊率就低一點 包含葉子節(jié)點的B索引樹的區(qū)塊的值就高點

提升起來的區(qū)塊首先放在熱子鏈的末尾 這個區(qū)塊在熱子鏈內(nèi)一直循環(huán) 如果這個區(qū)塊在該子鏈開頭位置停留時間足夠長了 它就會被降級回溫子鏈 這個時間是由索引緩存結(jié)構(gòu)體變量的組件 key_cache_age_threshold 值來決定的

這個閥值是這么描述的 一個索引緩存包含了 N 個區(qū)塊 熱子鏈開頭的區(qū)塊在低于 N*key_cache_age_threshold/ 次訪問后就被移動到溫子鏈的開頭位置 它又首先成為被刪除的候選對象 因為要被替換的區(qū)塊還是從溫子鏈的開頭位置開始的

中點插入策略就能在緩存中總能保持更有價值的區(qū)塊 如果更喜歡采用LRU策略 只需讓 key_cache_division_limit 的值低于默認值

中點插入策略能幫助改善在執(zhí)行需要有效掃描索引 它會將所有對應(yīng)到B樹中高級別的有價值的節(jié)點推出的查詢時的性能 為了避免這樣 就必須設(shè)定 key_cache_division_limit 遠遠低于 以采用中點插入策略 則在掃描索引操作時那些有價值的頻繁點擊的節(jié)點就會保留在熱子鏈中了

索引預(yù)載入

如果索引緩存中有足夠的區(qū)塊用來保存全部索引 或者至少足夠保存全部非葉子節(jié)點 那么在使用前就載入索引緩存就很有意義了 將索引區(qū)塊以十分有效的方法預(yù)載入索引緩存緩沖 從磁盤中順序地讀取索引區(qū)塊

沒有預(yù)載入 查詢所需的索引區(qū)塊仍然需要被放到緩存中去 雖然索引區(qū)塊要保留在緩存中 因為有足夠的緩沖 它們可以從磁盤中隨機讀取到 而非順序地

想要預(yù)載入緩存 可以使用 LOAD INDEX INTO CACHE 語句 如下語句預(yù)載入了表 t 和 t 的索引節(jié)點(區(qū)塊)

mysql?LOAD?INDEX?INTO?CACHE?t ?t ?IGNORE?LEAVES; + + + + + |?Table?|?Op?|?Msg_type?|?Msg_text?| + + + + + |?test t ?|?preload_keys?|?status?|?OK?| |?test t ?|?preload_keys?|?status?|?OK?| + + + + +

增加修飾語 IGNORE LEAVES 就只預(yù)載入非葉子節(jié)點的索引區(qū)塊 因此 上述語句加載了 t 的全部索引區(qū)塊 但是只加載 t 的非葉子節(jié)點區(qū)塊

如果使用 CACHE INDEX 語句將索引指向一個索引緩存 將索引區(qū)塊預(yù)先放到那個緩存中去 否則 索引區(qū)塊只會加載到默認的緩存中去

索引緩存大小

MySQL 引進了對每個索引緩存的新變量 key_cache_block_size 這個變量可以指定每個索引緩存的區(qū)塊大小 用它就可以來調(diào)整索引文件I/O操作的性能

當讀緩沖的大小和本地操作系統(tǒng)的I/O緩沖大小一樣時 就達到了I/O操作的最高性能了 但是設(shè)置索引節(jié)點的大小和I/O緩沖大小一樣未必能達到最好的總體性能 讀比較大的葉子節(jié)點時 服務(wù)器會讀進來很多不必要的數(shù)據(jù) 這大大阻礙了讀其他葉子節(jié)點

目前 還不能控制數(shù)據(jù)表的索引區(qū)塊大小 這個大小在服務(wù)器創(chuàng)建索引文件 ` MYI 時已經(jīng)設(shè)定好了 它根據(jù)數(shù)據(jù)表的索引大小的定義而定 在很多時候 它設(shè)置成和I/O緩沖大小一樣 在將來 可以改變它的值 并且會全面采用變量 key_cache_block_size

重建索引緩存

索引緩存可以通過修改其參數(shù)值在任何時候重建它 例如

mysql?SET?GLOBAL?cold_cache key_buffer_size= * * ;

如果設(shè)定索引緩存的結(jié)構(gòu)體變量組件變量 key_buffer_size 或 key_cache_block_size 任何一個的值和它當前的值不一樣 服務(wù)器就會清空原來的緩存 在新的變量值基礎(chǔ)上重建緩存 如果緩存中有任何的 臟 索引塊 服務(wù)器會先把它們保存起來然后才重建緩存 重新設(shè)定其他的索引緩存變量并不會重建緩存

lishixinzhi/Article/program/Oracle/201311/16615