Linux 進(jìn)程通過(guò) C 標(biāo)準(zhǔn)庫(kù)中的內(nèi)存分配函數(shù) malloc 向系統(tǒng)申請(qǐng)內(nèi)存，但是到真正與內(nèi)核交互之間，其實(shí)還隔了一層，即內(nèi)存分配管理器(memory allocator)。常見(jiàn)的內(nèi)存分配器包括：ptmalloc(Glibc)、tcmalloc(Google)、jemalloc(FreeBSD)。MySQL 默認(rèn)使用的是 glibc 的 ptmalloc 作為內(nèi)存分配器。
成都創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比盈江網(wǎng)站開(kāi)發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫(kù),直接使用。一站式盈江網(wǎng)站制作公司更省心,省錢(qián),快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋盈江地區(qū)。費(fèi)用合理售后完善，10多年實(shí)體公司更值得信賴。
內(nèi)存分配器采用的是內(nèi)存池的管理方式，處在用戶程序?qū)雍蛢?nèi)核層之間，它響應(yīng)用戶的分配請(qǐng)求，向操作系統(tǒng)申請(qǐng)內(nèi)存，然后將其返回給用戶程序。
為了保持高效的分配，分配器通常會(huì)預(yù)先向操作系統(tǒng)申請(qǐng)一塊內(nèi)存，當(dāng)用戶程序申請(qǐng)和釋放內(nèi)存的時(shí)候，分配器會(huì)將這些內(nèi)存管理起來(lái)，并通過(guò)一些算法策略來(lái)判斷是否將其返回給操作系統(tǒng)。這樣做的最大好處就是可以避免用戶程序頻繁的調(diào)用系統(tǒng)來(lái)進(jìn)行內(nèi)存分配，使用戶程序在內(nèi)存使用上更加高效快捷。
關(guān)于 ptmalloc 的內(nèi)存分配原理，個(gè)人也不是非常了解，這里就不班門(mén)弄斧了，有興趣的同學(xué)可以去看下華庭的《glibc 內(nèi)存管理 ptmalloc 源代碼分析》【文末鏈接】。
關(guān)于如何選擇這三種內(nèi)存分配器，網(wǎng)上資料大多都是推薦摒棄 glibc 原生的 ptmalloc，而改用 jemalloc 或者 tcmalloc 作為默認(rèn)分配器。因?yàn)?ptmalloc 的主要問(wèn)題其實(shí)是內(nèi)存浪費(fèi)、內(nèi)存碎片、以及加鎖導(dǎo)致的性能問(wèn)題，而 jemalloc 與 tcmalloc 對(duì)于內(nèi)存碎片、多線程處理優(yōu)化的更好。
目前 jemalloc 應(yīng)用于 Firefox、FaceBook 等，并且是 MariaDB、Redis、Tengine 默認(rèn)推薦的內(nèi)存分配器，而 tcmalloc 則應(yīng)用于 WebKit、Chrome 等。
MySQL CPU占用過(guò)高原因主要有以下幾種
CPU過(guò)時(shí)（比較舊的CPU）
RAM資源不足（RAM記憶體）
解決辦法如下：
①臨時(shí)解決方案
首先是ctrl+alt+delete快捷鍵打開(kāi)工作管理員
然后找到下方圖一中的mysqld.exe
右擊移至詳細(xì)資料
再來(lái)右擊設(shè)定優(yōu)先順序
按照下方圖二的步驟
根據(jù)占用情況調(diào)整成低于標(biāo)準(zhǔn)或者低
這個(gè)方法只能臨時(shí)解決
②實(shí)際解決方法是更換CPU
總結(jié)：根據(jù)正常的mysql使用，即使大量數(shù)據(jù)往來(lái)也不會(huì)造成CPU占用過(guò)高，目前推論應(yīng)該是CPU比較過(guò)時(shí)的原因，治標(biāo)不治本的臨時(shí)解決方案。
備注：如采取方案②你需要備份你的資料，因?yàn)楦鼡QCPU會(huì)有很大的機(jī)會(huì)需要重新安裝你的作業(yè)系統(tǒng)。
一臺(tái)服務(wù)器解決了 Mysql cpu 占用 100% 的問(wèn)題。稍整理了一下，將經(jīng)驗(yàn)記錄在這篇文章里。
朋友主機(jī)(Windows 2003 + IIS + PHP + MYSQL )近來(lái) MySQL 服務(wù)進(jìn)程 (mysqld-nt.exe) CPU 占用率總為 100% 高居不下。此主機(jī)有10個(gè)左右的 database, 分別給十個(gè)網(wǎng)站調(diào)用。據(jù)朋友測(cè)試，導(dǎo)致 mysqld-nt.exe cpu 占用奇高的是網(wǎng)站A，一旦在 IIS 中將此網(wǎng)站停止服務(wù)，CPU 占用就降下來(lái)了。一啟用，則馬上上升。
MYSQL CPU 占用 100% 的解決過(guò)程
今天早上仔細(xì)檢查了一下。目前此網(wǎng)站的七日平均日 IP 為2000，PageView 為 3萬(wàn)左右。網(wǎng)站A 用的 database 目前有39個(gè)表，記錄數(shù) 60.1萬(wàn)條，占空間 45MB。按這個(gè)數(shù)據(jù)，MySQL 不可能占用這么高的資源。于是在服務(wù)器上運(yùn)行命令，將 mysql 當(dāng)前的環(huán)境變量輸出到文件 output.txt：
d:\web\mysql mysqld.exe --help output.txt發(fā)現(xiàn) tmp_table_size 的值是默認(rèn)的 32M，于是修改 My.ini, 將 tmp_table_size 賦值到 200M:
d:\web\mysql notepad c:\windows\my.ini
[mysqld]
tmp_table_size=200M
然后重啟 MySQL 服務(wù)。CPU 占用有輕微下降，以前的CPU 占用波形圖是 100% 一根直線，現(xiàn)在則在 97%~100%之間起伏。這表明調(diào)整 tmp_table_size 參數(shù)對(duì) MYSQL 性能提升有改善作用。但問(wèn)題還沒(méi)有完全解決。
于是進(jìn)入 mysql 的 shell 命令行，調(diào)用 show processlist, 查看當(dāng)前 mysql 使用頻繁的 sql 語(yǔ)句：
mysql show processlist;
反復(fù)調(diào)用此命令，發(fā)現(xiàn)網(wǎng)站 A 的兩個(gè) SQL 語(yǔ)句經(jīng)常在 process list 中出現(xiàn)，其語(yǔ)法如下：
SELECT t1.pid, t2.userid, t3.count, t1.dateFROM _mydata AS t1
LEFT JOIN _myuser AS t3 ON t1.userid=t3.useridLEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pidORDER BY t1.pid
LIMIT 0,15
調(diào)用 show columns 檢查這三個(gè)表的結(jié)構(gòu) :
mysql show columns from _myuser;
mysql show columns from _mydata;
mysql show columns from _mydata_body;
終于發(fā)現(xiàn)了問(wèn)題所在：_mydata 表，只根據(jù) pid 建立了一個(gè) primary key，但并沒(méi)有為 userid 建立索引。而在這個(gè) SQL 語(yǔ)句的第一個(gè) LEFT JOIN ON 子句中：
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid_mydata 的 userid 被參與了條件比較運(yùn)算。于是我為給 _mydata 表根據(jù)字段 userid 建立了一個(gè)索引：
mysql ALTER TABLE `_mydata` ADD INDEX ( `userid` )建立此索引之后，CPU 馬上降到了 80% 左右?？吹秸业搅藛?wèn)題所在，于是檢查另一個(gè)反復(fù)出現(xiàn)在 show processlist 中的 sql 語(yǔ)句：
SELECT COUNT(*)
FROM _mydata AS t1, _mydata_key AS t2
WHERE t1.pid=t2.pid and t2.keywords = '孔雀'
經(jīng)檢查 _mydata_key 表的結(jié)構(gòu)，發(fā)現(xiàn)它只為 pid 建了了 primary key, 沒(méi)有為 keywords 建立 index。_mydata_key 目前有 33 萬(wàn)條記錄，在沒(méi)有索引的情況下對(duì)33萬(wàn)條記錄進(jìn)行文本檢索匹配，不耗費(fèi)大量的 cpu 時(shí)間才怪。看來(lái)就是針對(duì)這個(gè)表的檢索出問(wèn)題了。于是同樣為 _mydata_key 表根據(jù)字段 keywords 加上索引:
mysql ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )建立此索引之后，CPU立刻降了下來(lái)，在 50%~70%之間震蕩。
再次調(diào)用 show prosslist，網(wǎng)站A 的sql 調(diào)用就很少出現(xiàn)在結(jié)果列表中了。但發(fā)現(xiàn)此主機(jī)運(yùn)行了幾個(gè) Discuz 的論壇程序， Discuz 論壇的好幾個(gè)表也存在著這個(gè)問(wèn)題。于是順手一并解決，cpu占用再次降下來(lái)了。(2007.07.09 附注：關(guān)于 discuz 論壇的具體優(yōu)化過(guò)程，我后來(lái)另寫(xiě)了一篇文章，詳見(jiàn)：千萬(wàn)級(jí)記錄的 Discuz! 論壇導(dǎo)致 MySQL CPU 100% 的 優(yōu)化筆記 )解決 MYSQL CPU 占用 100% 的經(jīng)驗(yàn)總結(jié)
增加 tmp_table_size 值。mysql 的配置文件中，tmp_table_size 的默認(rèn)大小是 32M。如果一張臨時(shí)表超出該大小，MySQL產(chǎn)生一個(gè) The table tbl_name is full 形式的錯(cuò)誤，如果你做很多高級(jí) GROUP BY 查詢，增加 tmp_table_size 值。 這是 mysql 官方關(guān)于此選項(xiàng)的解釋：
tmp_table_size
This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.
對(duì) WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的條件判斷中用到的字段,應(yīng)該根據(jù)其建立索引 INDEX。索引被用來(lái)快速找出在一個(gè)列上用一特定值的行。沒(méi)有索引，MySQL不得不首先以第一條記錄開(kāi)始并然后讀完整個(gè)表直到它找出相關(guān)的行。表越大，花費(fèi)時(shí)間越多。如果表對(duì)于查詢的列有一個(gè)索引，MySQL能快速到達(dá)一個(gè)位置去搜尋到數(shù)據(jù)文件的中間，沒(méi)有必要考慮所有數(shù)據(jù)。如果一個(gè)表有1000行，這比順序讀取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹(shù)中存儲(chǔ)。根據(jù) mysql 的開(kāi)發(fā)文檔:
索引 index 用于：
快速找出匹配一個(gè)WHERE子句的行
當(dāng)執(zhí)行聯(lián)結(jié)(JOIN)時(shí)，從其他表檢索行。
對(duì)特定的索引列找出MAX()或MIN()值
如果排序或分組在一個(gè)可用鍵的最左面前綴上進(jìn)行(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個(gè)表。如果所有鍵值部分跟隨DESC，鍵以倒序被讀取。
在一些情況中，一個(gè)查詢能被優(yōu)化來(lái)檢索值，不用咨詢數(shù)據(jù)文件。如果對(duì)某些表的所有使用的列是數(shù)字型的并且構(gòu)成某些鍵的最左面前綴，為了更快，值可以從索引樹(shù)被檢索出來(lái)。假定你發(fā)出下列SELECT語(yǔ)句：
mysql SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;如果一個(gè)多列索引存在于col1和col2上，適當(dāng)?shù)男锌梢灾苯颖蝗〕?。如果分開(kāi)的單行列索引存在于col1和col2上，優(yōu)化器試圖通過(guò)決定哪個(gè)索引將找到更少的行并來(lái)找出更具限制性的索引并且使用該索引取行。
開(kāi)發(fā)人員做 SQL 數(shù)據(jù)表設(shè)計(jì)的時(shí)候，一定要通盤(pán)考慮清楚。
生產(chǎn)環(huán)境中，MySQL 不經(jīng)意間吃掉全部的內(nèi)容，然后開(kāi)始吃掉 SWAP，性能一降再降，怎么辦？
可以從下面三點(diǎn)查看原因：
MySQL 使用內(nèi)存，有兩個(gè)途徑。
永久占用的內(nèi)容
比如全局緩沖區(qū)（Global Buffer）類別，是在服務(wù)器啟動(dòng)期間從操作系統(tǒng)獲得的，不會(huì)釋放到任何一個(gè)別的進(jìn)程。
動(dòng)態(tài)請(qǐng)求的內(nèi)存
線程緩沖區(qū)由MySQL使用，它是在處理新查詢時(shí)從操作系統(tǒng)請(qǐng)求的內(nèi)存。在執(zhí)行查詢之后，該內(nèi)存被釋放回操作系統(tǒng)。
這意味著 MySQL 的內(nèi)存使用，是 全局緩沖區(qū) 加上 線程緩沖區(qū) 以及 允許的最大連接數(shù) 。
對(duì)于專用數(shù)據(jù)庫(kù)服務(wù)器，該值需要保持在服務(wù)器內(nèi)存的90%以下。在共享服務(wù)器的情況下，它應(yīng)該保持在服務(wù)器內(nèi)存的50%以下。
檢查一下 MySQL 設(shè)置，有助于確定內(nèi)存使用情況，從而為 MySQL 分配合適的值。
一個(gè)近似的公式：
當(dāng)網(wǎng)站受到攻擊時(shí)，有可能在短時(shí)間內(nèi)建立異常高的連接數(shù)量。MySQL 中的 PROCESSLIST 可用于檢測(cè)頂級(jí)用戶并阻止對(duì)濫用連接的訪問(wèn)。
找出查詢需要很長(zhǎng)時(shí)間才能執(zhí)行的語(yǔ)句，因?yàn)檫@些查詢需要進(jìn)一步優(yōu)化服務(wù)器才能更好地執(zhí)行，可以通過(guò)服務(wù)器查詢?nèi)罩具M(jìn)行識(shí)別。由于查詢速度慢，導(dǎo)致磁盤(pán)讀取較多，導(dǎo)致內(nèi)存和CPU使用率較高，影響服務(wù)器性能。
最后，到了加內(nèi)存條的時(shí)候了。雖然在優(yōu)化數(shù)據(jù)庫(kù)設(shè)置之后，服務(wù)器會(huì)不斷地路由到使用交換內(nèi)存，但也必須增加內(nèi)存。俗話說(shuō)：“巧婦難為無(wú)米之炊”，就是這個(gè)意思。
上面說(shuō)的這些方向，大家可以在實(shí)際操作中驗(yàn)證體會(huì)，希望大家在數(shù)據(jù)庫(kù)優(yōu)化的路上，麻溜順暢，砥礪前行。
重慶分公司
重慶分公司
