mysql 參數(shù)調優(yōu)(2)之設置重做日志文件的大小 innodb_log_file_size

我們知道redo log包括 buffer和log file的部分，這里的innodb_log_file_size是配置log file的大小的。

創(chuàng)新互聯(lián)長期為近1000家客戶提供的網(wǎng)站建設服務，團隊從業(yè)經(jīng)驗10年，關注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為江州企業(yè)提供專業(yè)的網(wǎng)站設計制作、成都網(wǎng)站建設，江州網(wǎng)站改版等技術服務。擁有10多年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。

innodb_log_file_size這個選項是設置 redo 日志（重做日志）的大小。這個值的默認為5M，是遠遠不夠的，在安裝完mysql時需要盡快的修改這個值。如果對 Innodb 數(shù)據(jù)表有大量的寫入操作，那么選擇合適的 innodb_log_file_size 值對提升MySQL性能很重要。然而設置太大了，就會增加恢復的時間，因此在MySQL崩潰或者突然斷電等情況會令MySQL服務器花很長時間來恢復。

由于事務日志相當于一個寫緩沖，而小日志文件會很快的被寫滿，這時候就需要頻繁地刷新到硬盤，速度就慢了。如果產(chǎn)生大量的寫操作，MySQL可能就不能足夠快地刷新數(shù)據(jù)，那么寫性能將會降低。

大的日志文件，另一方面，在刷新操作發(fā)生之前給你足夠的空間來使用。反過來允許InnoDB填充更多的頁面。對于崩潰恢復 – 大的重做日志意味著在服務器啟動前更多的數(shù)據(jù)需要讀取，更多的更改需要重做，這就是為什么崩潰恢復慢了。

如果不配的后果：默認是5M，這是肯定不夠的。

最后，讓我們來談談如何找出重做日志的正確大小。

幸運的是，你不需要費力算出正確的大小，這里有一個經(jīng)驗法則：在服務器繁忙期間，檢查重做日志的總大小是否夠寫入1-2小時。你如何知道InnoDB寫入多少，使用下面方法可以統(tǒng)計60秒內地增量數(shù)據(jù)大小：

mysql show engine innodb status\G select sleep(60); show engine innodb status\G

Log sequence number 4631632062

...

Log sequence number 4803805448

mysql select (4803805448-4631632062) 60/1024/1024;

+--------------------------------------+

| (4803805448-4631632062) 60/1024/1024 |

+--------------------------------------+

| 9851.84017181 |

+--------------------------------------+

1 row in set (0.00 sec)

在這個60s的采樣情況下，InnoDB每小時寫入9.8GB數(shù)據(jù)。所以如果innodb_log_files_in_group沒有更改(默認是2，是InnoDB重復日志的最小數(shù)字)，然后設置innodb_log_file_size為10G，那么你實際上兩個日志文件加起來有20GB，夠你寫兩小時數(shù)據(jù)了。

更改innodb_log_file_size的難易程度和能設置多大取決于你現(xiàn)在使用的MySQL版本。特別地，如果你使用的是5.6之前的版本，你不能僅僅的更改變量，期望服務器會自動重啟。

好了，下面是步驟：

1、在my.cnf更改innodb_log_file_size

2、停止mysql服務器

3、刪除舊的日志,通過執(zhí)行命令rm -f /var/lib/mysql/ib_logfile*

4、啟動mysql服務器 – 應該需要比之前長點的時間，因為需要創(chuàng)建新的事務日志。最后，需要注意的是，有些mysql版本(比如5.6.2)限制了重做日志大小為4GB。所以在你設置innodb_log_file_size為2G或者更多時，請先檢查一下MySQL的版本這方面的限制。

mysql調優(yōu)技巧 增加線程緩存大小

增加線程緩存大小

連接管理器線程處理服務器監(jiān)聽的網(wǎng)絡接口上的客戶端連接請求。連接管理器線程將每個客戶端連接與專用于它的線程關聯(lián)，該線程負責處理該連接的身份驗證和所有請求處理。因此，線程和當前連接的客戶端之間是一對一的比例。確保線程緩存足夠大以容納所有傳入請求是非常重要的。

MySQL提供了許多與連接線程相關的服務器變量：

線程緩存大小由thread_cache_size系統(tǒng)變量決定。默認值為0（無緩存），這將導致為每個新連接設置一個線程，并在連接終止時需要處理該線程。如果希望服務器每秒接收數(shù)百個連接請求，那么應該將thread_cache_size設置的足夠高，以便大多數(shù)新連接可以使用緩存線程。可以在服務器啟動或運行時設置max_connections的值。

還應該監(jiān)視緩存中的線程數(shù)（Threads_cached）以及創(chuàng)建了多少個線程，因為無法從緩存中獲取線程（Threads_created）。關于后者，如果Threads_created繼續(xù)以每分鐘多于幾個線程的增加，請考慮增加thread_cache_size的值。

使用MySQL show status命令顯示MySQL的變量和狀態(tài)信息。這里有幾個例子：

Monyog線程緩存監(jiān)測

Monyog提供了一個監(jiān)控線程緩存的屏幕，名為“線程”。與MySQL線程相關的服務器變量映射到以下Monyog指標：

Monyog線程屏幕還包括“線程緩存命中率”指標。這是一個提示線程緩存命中率的指標。如果值較低，則應該考慮增加線程緩存。在狀態(tài)欄以百分比形式顯示該值；它的值越接近100％越好。

如果這些指標的值等于或超過指定值，則可以將每一個指標配置為發(fā)出警告和/或嚴重警報

mysql怎么優(yōu)化，都要怎么做

mysql優(yōu)化是一個大方向，大的是要分布式、讀寫分離，小的是對sql語句進行優(yōu)化。不過大多問的也是對sql語句優(yōu)化，網(wǎng)上很多資料，我就大體說說。

1、explain+索引。

在你要查詢的語句前加explain，看下有沒有用到索引，如果出現(xiàn)type為all的，則說明有必要添加下索引。（附多表查詢速度比較：表關聯(lián)existsin）慢查詢優(yōu)化是一大塊。

2、預統(tǒng)計。

很經(jīng)常需要對歷史的數(shù)據(jù)進行過濾統(tǒng)計。比如移動需要統(tǒng)計上個月電話小時數(shù)超過N小時的人，那么如果直接取原始數(shù)據(jù)，那將很慢，此時如果每天晚上凌晨都對數(shù)據(jù)進行預統(tǒng)計，統(tǒng)計每個人每天電話時數(shù)，那再來過濾就很快。

3、分表分區(qū)。

分表分區(qū)也是為了提高搜索速度。例如，公交車的gps行駛記錄，gps每隔15s報一次，一輛車一天運行12小時，一天就要插入4*60*12條記錄，N輛車就要再乘，其數(shù)量極大，所以經(jīng)常按月分表，分表里再按上報時間做日分區(qū)，這樣就達到很大的優(yōu)化，想查詢某段時間，mysql很快就可以定位到。

4、表結構。

表結構很重要，經(jīng)常需要多表關聯(lián)查詢一些字段，有時可以冗余下放到同一張表。

mysql優(yōu)化很有意思，多去查閱些資料，多去嘗試，對你有好處的。

mysql 服務器CPU占用過高，如何調優(yōu)，求助

通過以前對mysql的操作經(jīng)驗，先將mysql的配置問題排除了，查看msyql是否運行正常，通過查看mysql data目錄里面的*.err文件(將擴展名改為.txt)記事本查看即可。如果過大不建議用記事本了，容易死掉，可以用editplus等工具。

簡單的分為下面幾個步驟來解決這個問題：

1、mysql運行正常，也有可能是同步設置問題導致

2、如果mysql運行正常，那就是php的一些sql語句導致問題發(fā)現(xiàn)，用root用戶進入mysql管理

mysql -u root -p

輸入密碼

mysql:show processlist 語句，查找負荷最重的 SQL 語句，優(yōu)化該SQL，比如適當建立某字段的索引。

通過這個命令我看到原來是有人惡意刷搜索，因為dedecms搜索后面調用搜索最高的詞，導致很多人用工具刷這個，而且是定時有間隔的，所以將這個php程序改名跳轉都方法解決了。

當然如果你的確實是sql語句用了大量的group by等語句，union聯(lián)合查詢等肯定會將mysql的占用率提高。所以就需要優(yōu)化sql語句，網(wǎng)站盡量生成靜態(tài)的，一般4W ip的靜態(tài)網(wǎng)站，mysql占用率幾乎為0的。所以這對于程序員的經(jīng)驗是個考慮。盡量提高mysql性能 (MySQL 性能優(yōu)化的最佳20多條經(jīng)驗分享)

下面是豆芽收集的文章，大家都可以參考下

MYSQL CPU 占用 100% 的現(xiàn)象描述

早上幫朋友一臺服務器解決了 Mysql cpu 占用 100% 的問題。稍整理了一下，將經(jīng)驗記錄在這篇文章里

朋友主機(Windows 2003 + IIS + PHP + MYSQL )近來 MySQL 服務進程 (mysqld-nt.exe) CPU 占用率總為 100% 高居不下。此主機有10個左右的 database, 分別給十個網(wǎng)站調用。據(jù)朋友測試，導致 mysqld-nt.exe cpu 占用奇高的是網(wǎng)站A，一旦在 IIS 中將此網(wǎng)站停止服務，CPU 占用就降下來了。一啟用，則馬上上升。

MYSQL CPU 占用 100% 的解決過程

今天早上仔細檢查了一下。目前此網(wǎng)站的七日平均日 IP 為2000，PageView 為 3萬左右。網(wǎng)站A 用的 database 目前有39個表，記錄數(shù) 60.1萬條，占空間 45MB。按這個數(shù)據(jù)，MySQL 不可能占用這么高的資源。

于是在服務器上運行命令，將 mysql 當前的環(huán)境變量輸出到文件 output.txt：

d:\web\mysql mysqld.exe --help output.txt

發(fā)現(xiàn) tmp_table_size 的值是默認的 32M，于是修改 My.ini, 將 tmp_table_size 賦值到 200M:

d:\web\mysql notepad c:\windows\my.ini

[mysqld]

tmp_table_size=200M

然后重啟 MySQL 服務。CPU 占用有輕微下降，以前的CPU 占用波形圖是 100% 一根直線，現(xiàn)在則在 97%~100%之間起伏。這表明調整 tmp_table_size 參數(shù)對 MYSQL 性能提升有改善作用。但問題還沒有完全解決。

于是進入 mysql 的 shell 命令行，調用 show processlist, 查看當前 mysql 使用頻繁的 sql 語句：

mysql show processlist;

反復調用此命令，發(fā)現(xiàn)網(wǎng)站 A 的兩個 SQL 語句經(jīng)常在 process list 中出現(xiàn)，其語法如下：

SELECT t1.pid, t2.userid, t3.count, t1.date

FROM _mydata AS t1

LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid

LEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pid

ORDER BY t1.pid

LIMIT 0,15

調用 show columns 檢查這三個表的結構 :

mysql show columns from _myuser;

mysql show columns from _mydata;

mysql show columns from _mydata_body;

終于發(fā)現(xiàn)了問題所在：_mydata 表，只根據(jù) pid 建立了一個 primary key，但并沒有為 userid 建立索引。而在這個 SQL 語句的第一個 LEFT JOIN ON 子句中：

LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid

_mydata 的 userid 被參與了條件比較運算。于是我為給 _mydata 表根據(jù)字段 userid 建立了一個索引：

mysql ALTER TABLE `_mydata` ADD INDEX ( `userid` )

建立此索引之后，CPU 馬上降到了 80% 左右。看到找到了問題所在，于是檢查另一個反復出現(xiàn)在 show processlist 中的 sql 語句：

SELECT COUNT(*)

FROM _mydata AS t1, _mydata_key AS t2

WHERE t1.pid=t2.pid and t2.keywords = '孔雀'

經(jīng)檢查 _mydata_key 表的結構，發(fā)現(xiàn)它只為 pid 建了了 primary key, 沒有為 keywords 建立 index。_mydata_key 目前有 33 萬條記錄，在沒有索引的情況下對33萬條記錄進行文本檢索匹配，不耗費大量的 cpu 時間才怪?？磥砭褪轻槍@個表的檢索出問題了。于是同樣為 _mydata_key 表根據(jù)字段 keywords 加上索引:

mysql ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )

建立此索引之后，CPU立刻降了下來，在 50%~70%之間震蕩。

再次調用 show prosslist，網(wǎng)站A 的sql 調用就很少出現(xiàn)在結果列表中了。但發(fā)現(xiàn)此主機運行了幾個 Discuz 的論壇程序， Discuz 論壇的好幾個表也存在著這個問題。于是順手一并解決，cpu占用再次降下來了。(2007.07.09 附注：關于 discuz 論壇的具體優(yōu)化過程，我后來另寫了一篇文章，詳見：千萬級記錄的 Discuz! 論壇導致 MySQL CPU 100% 的 優(yōu)化筆記 )

解決 MYSQL CPU 占用 100% 的經(jīng)驗總結

增加 tmp_table_size 值。mysql 的配置文件中，tmp_table_size 的默認大小是 32M。如果一張臨時表超出該大小，MySQL產(chǎn)生一個 The table tbl_name is full 形式的錯誤，如果你做很多高級 GROUP BY 查詢，增加 tmp_table_size 值。

對 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的條件判斷中用到的字段,應該根據(jù)其建立索引 INDEX。索引被用來快速找出在一個列上用一特定值的行。沒有索引，MySQL不得不首先以第一條記錄開始并然后讀完整個表直到它找出相關的行。表越大，花費時間越多。如果表對于查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到數(shù)據(jù)文件的中間，沒有必要考慮所有數(shù)據(jù)。如果一個表有1000行，這比順序讀取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹中存儲。

根據(jù) mysql 的開發(fā)文檔:

索引 index 用于：

快速找出匹配一個WHERE子句的行

當執(zhí)行聯(lián)結(JOIN)時，從其他表檢索行。

對特定的索引列找出MAX()或MIN()值

如果排序或分組在一個可用鍵的最左面前綴上進行(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有鍵值部分跟隨DESC，鍵以倒序被讀取。

在一些情況中，一個查詢能被優(yōu)化來檢索值，不用咨詢數(shù)據(jù)文件。如果對某些表的所有使用的列是數(shù)字型的并且構成某些鍵的最左面前綴，為了更快，值可以從索引樹被檢索出來。

假定你發(fā)出下列SELECT語句：

mysql SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2；

如果一個多列索引存在于col1和col2上，適當?shù)男锌梢灾苯颖蝗〕?。如果分開的單行列索引存在于col1和col2上，優(yōu)化器試圖通過決定哪個索引將找到更少的行并來找出更具限制性的索引并且使用該索引取行。

網(wǎng)站名稱：中科院mysql怎么調優(yōu) 中國科學院數(shù)據(jù)庫
鏈接URL：http://jinyejixie.com/article24/dosgjje.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供標簽優(yōu)化、網(wǎng)站收錄、Google、面包屑導航、網(wǎng)站營銷、外貿(mào)建站

廣告

聲明：本網(wǎng)站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)