文章來源地址http://www.zghlxwxcb.cn/news/detail-705501.html

1.?除非遇到異常情況，否則不需要調(diào)整配置

1.1.?不要“調(diào)優(yōu)”服務(wù)器，不要使用比率、公式或“調(diào)優(yōu)腳本”作為設(shè)置配置變量的基礎(chǔ)

1.1.1.?在互聯(lián)網(wǎng)上搜索配置建議并不總是一個好主意，你會在博客、論壇等找到很多糟糕的建議

1.1.2.?很難判斷誰是真正的專家

1.1.3.?不要相信流行的內(nèi)存消耗公式

1.2.?可靠的、有信譽(yù)的MySQL服務(wù)提供商通常比簡單的互聯(lián)網(wǎng)搜索結(jié)果更安全，因為那些需要擁有滿意的客戶的人可能正在做正確的事情

1.2.1.?即使是他們的建議，在沒有經(jīng)過測試和理解的情況下進(jìn)行應(yīng)用也可能是危險的，因為它可能針對的是一種與你不同的情況，而你卻沒有理解

1.3.?MySQL的不同版本會刪除、棄用和更改一些選項，欲了解詳細(xì)信息請查看相關(guān)文檔

1.4.?應(yīng)該始終通過閱讀相關(guān)的官方手冊來檢查任何更改并仔細(xì)測試

1.5.?MySQL有許多可以更改但不應(yīng)該更改的設(shè)置

1.5.1.?MySQL的默認(rèn)設(shè)置是有充分理由的

1.5.2.?修改配置的潛在缺點可能是巨大的

1.5.3.?很多默認(rèn)設(shè)置都是安全的，很多人都會直接使用。這使默認(rèn)設(shè)置成為測試最徹底的設(shè)置。當(dāng)沒必要改變這些設(shè)置而改變它們時，可能會引起意想不到的錯誤

1.5.4.?節(jié)省時間和避免麻煩的好方法是使用默認(rèn)設(shè)置，除非你明確知道不應(yīng)該使用默認(rèn)設(shè)置

1.6.?更好的做法是正確地配置基本設(shè)置（在大多數(shù)情況下，只有少數(shù)設(shè)置是重要的），并將更多的時間花在schema優(yōu)化、索引和查詢設(shè)計上

1.7.?如果問題是由服務(wù)器的某個部分引起的，而該部分的行為可以通過配置選項進(jìn)行糾正，那么可能需要對其進(jìn)行更改

1.8.?應(yīng)該只在發(fā)現(xiàn)它們解決的特定性能問題時，才設(shè)置它們

1.9.?如果需要改進(jìn)配置，應(yīng)該會在查詢響應(yīng)時間中體現(xiàn)出來

1.10.?最好從查詢及其響應(yīng)時間開始分析，而不是從配置選項開始

1.10.1.?節(jié)省很多時間，避免很多問題

2.?專用數(shù)據(jù)庫服務(wù)器

2.1.?可以設(shè)置的最佳選項是innodb_dedicated_server

2.1.1.?可以處理90%的性能配置

2.1.2.?配置了4個額外的變量（innodb_buffer_pool_size、innodb_log_file_size、innodb_log_files_in_group和innodb_flush_method）

2.1.3.?通常會占用50%～75%的內(nèi)存

2.1.3.1.?MySQL只需要少量的內(nèi)存就能保持一個連接（通常是一個相關(guān)的專用線程）打開

2.2.?無法使用innodb_dedicated_server

2.2.1.?innodb_buffer_pool_size

2.2.1.1.?InnoDB緩沖池大小

2.2.1.2.?需要的內(nèi)存比其他任何組件都多

2.2.1.3.?不僅緩存索引，還緩存行數(shù)據(jù)、自適應(yīng)哈希索引、更改緩沖區(qū)、鎖和其他內(nèi)部結(jié)構(gòu)等

2.2.1.4.?InnoDB嚴(yán)重依賴緩沖池，應(yīng)該確保為其分配足夠的內(nèi)存

2.2.1.5.?大型緩沖池會帶來一些挑戰(zhàn)，比如更長的關(guān)閉時間和預(yù)熱時間

2.2.1.6.?當(dāng)MySQL再次啟動時，緩沖池緩存是空的，也稱為冷緩存

2.2.1.7.?默認(rèn)情況下，innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup這兩個配置可以配合使用，以在啟動時預(yù)熱緩存池

2.2.2.?innodb_log_file_size

2.2.2.1.?日志文件大小

2.2.3.?解決了我們所看到的絕大多數(shù)實際配置問題

2.3.?應(yīng)該設(shè)置一些安全選項

2.3.1.?通常不會提高性能，只會避免問題

3.?MySQL的配置

3.1.?需要永久使用的任何設(shè)置都應(yīng)該寫入全局配置文件，而不是在命令行中指定

3.2.?將所有配置文件保存在一個地方也是一個好主意，這樣可以方便地檢查它們

3.3.?一定要知道服務(wù)器的配置文件在哪里

3.3.1.?Debian服務(wù)器上默認(rèn)不存在/etc/my.cnf，而是會在/etc/mysql/my.cnf中查找配置

3.4.?配置文件采用標(biāo)準(zhǔn)INI格式，被分為多個部分，每個部分都以一行包含在方括號中的該部分名稱開頭

3.5.?配置設(shè)置全部用小寫字母書寫，單詞之間以下畫線或短橫線分隔

3.5.1.?建議選擇一種風(fēng)格并始終如一地使用它

3.6.?全局作用域

3.7.?會話作用域

3.8.?許多會話作用域的變量都有相應(yīng)的全局變量，可以將相應(yīng)的全局變量的值視為會話變量的默認(rèn)值

3.9.?動態(tài)配置變量

3.9.1.?很多變量（但不是全部）還可以在服務(wù)器運行時進(jìn)行更改

3.9.2.?如果重新啟動MySQL，即使使用了SET GLOBAL來更改全局變量，它也將恢復(fù)到配置文件中的狀態(tài)

3.9.3.?必須同時管理MySQL的配置文件和運行時配置，并確保它們保持同步

3.10.?MySQL 8.0引入了一個名為持久化系統(tǒng)變量的新功能

3.10.1.?新的語法SET PERSIST允許在運行時設(shè)置一次值，MySQL將把這個設(shè)置寫入磁盤，以便在下次重啟后繼續(xù)使用該值

3.11.?table_open_cache

3.11.1.?設(shè)置此變量不會立即生效：下一次線程打開表時，MySQL會檢查變量的值

3.11.2.?如果該值大于緩存中的表的數(shù)目，線程可以將新打開的表插入緩存

3.11.3.?如果該值小于緩存中的表的數(shù)目，MySQL將從緩存中刪除未使用的表

3.12.?thread_cache_size

3.12.1.?設(shè)置此變量不會立即生效：下一次關(guān)閉連接時，MySQL會檢查緩存中是否有空間來存儲線程

3.12.2.?如果有，則緩存線程以供其他連接將來重用

3.12.3.?如果沒有，則將線程終止而不是緩存它

3.12.4.?只有當(dāng)查詢需要時，MySQL才會為該緩沖區(qū)分配內(nèi)存，而且會立即分配此變量指定的整塊內(nèi)存

3.12.5.?每個處于線程緩存或休眠狀態(tài)的線程通常使用大約256KB內(nèi)存

3.12.6.?通常應(yīng)該保持線程緩存足夠大，這樣Threads_created就不會經(jīng)常增加

3.13.?open_files_limit選項

3.13.1.?典型的Linux系統(tǒng)中，我們將其設(shè)置得盡可能大

3.13.2.?在現(xiàn)代操作系統(tǒng)中，打開文件句柄的成本很低

3.13.3.?如果這個設(shè)置不夠大，就會看到經(jīng)典的24號錯誤，“too many openfiles”

3.14.?設(shè)置變量時要小心。并不總是越多越好

3.15.?理想情況下，應(yīng)該使用版本控制系統(tǒng)來跟蹤配置文件的更改

3.16.?MySQL并不是一個嚴(yán)格控制內(nèi)存分配的數(shù)據(jù)庫服務(wù)器

3.16.1.?事實是，你不能給MySQL的內(nèi)存消耗設(shè)定上限

4.?I/O行為

4.1.?InnoDB不僅允許你控制其恢復(fù)方式，還允許控制其打開和刷新數(shù)據(jù)的方式，這將極大地影響恢復(fù)和總體性能

4.2.?InnoDB使用日志來降低提交事務(wù)的成本

4.3.?InnoDB假定它使用的是傳統(tǒng)的磁盤，隨機(jī)I/O比順序I/O的開銷要大很多，因為隨機(jī)I/O需要在磁盤上尋找正確的位置，并等待將所需的磁盤部分旋轉(zhuǎn)到磁頭下

4.4.?InnoDB最終必須將更改的數(shù)據(jù)寫入數(shù)據(jù)文件

4.4.1.?日志的大小固定，采取的是循環(huán)寫入的方式

4.4.1.1.?當(dāng)?shù)竭_(dá)日志的末尾時，它會環(huán)繞到日志的開頭

4.4.1.2.?如果日志記錄中包含的更改尚未應(yīng)用于數(shù)據(jù)文件，則無法覆蓋日志記錄，因為這將刪除已提交事務(wù)的唯一永久記錄

4.5.?日志文件的總大小由innodb_log_file_size和innodb_log_files_in_group控制，這對寫入性能非常重要

4.6.?當(dāng)緩沖區(qū)滿了、事務(wù)提交時，或者每秒1次（這三個條件以先滿足者為準(zhǔn)），InnoDB會將緩沖區(qū)刷新到磁盤上的日志文件中

4.7.?不需要將緩沖區(qū)設(shè)置得太大

4.7.1.?建議的范圍是1～8M B

4.8.?innodb_flush_log_at_trx_commit

4.8.1.?1

4.8.1.1.?每次事務(wù)提交時，將日志緩沖區(qū)寫入日志文件，并將其刷新到持久存儲中

4.8.1.2.?默認(rèn)的（也是最安全的）設(shè)置

4.8.1.3.?保證你不會丟失任何已提交的事務(wù)，除非磁盤或操作系統(tǒng)“假裝”進(jìn)行刷新操作（沒有將數(shù)據(jù)真正寫入磁盤）

4.8.1.3.1.?如果驅(qū)動器斷電，數(shù)據(jù)仍可能丟失

4.8.2.?0

4.8.2.1.?每秒定時將日志緩沖區(qū)寫入日志文件，并刷新日志文件，但在事務(wù)提交時不做任何操作

4.8.3.?2

4.8.3.1.?與0設(shè)置最重要的區(qū)別是，如果只是MySQL進(jìn)程崩潰，設(shè)置為2不會丟失任何事務(wù)。但是，如果整個服務(wù)器崩潰或斷電，仍然可能丟失事務(wù)

4.8.4.?設(shè)置為0和2通常會導(dǎo)致最多1秒的數(shù)據(jù)丟失，因為數(shù)據(jù)可能只存在于操作系統(tǒng)的緩存中

4.9.?高性能事務(wù)需求的最佳配置是將innodb_flush_log_at_trx_commit設(shè)置為1，并將日志文件放在具有備用電池的寫緩存和SSD的RAID卷上，這既安全又非?？?/h2>

4.10.?最好為日志文件和數(shù)據(jù)文件分別提供一個配置選項，但目前是組合在一起的

4.11.?如果你使用的是類UNIX操作系統(tǒng)，并且RAID控制器有備用電池的寫緩存，我們建議使用O_DIRECT

4.12.?如果不是，則default或O_DIRECT都可能是最佳選擇，具體取決于應(yīng)用程序

5.?InnoDB表空間

5.1.?表空間本質(zhì)上是一個虛擬文件系統(tǒng)，由磁盤上的一個或多個文件組成

5.2.?包含了Undo日志（重新創(chuàng)建舊行版本所需的信息）、修改緩沖區(qū)、雙寫緩沖區(qū)和其他內(nèi)部結(jié)構(gòu)

5.3.?對日志文件也非常嚴(yán)格

5.4.?innodb_file_per_table

5.4.1.?提供了額外的可管理性和可視性

5.4.2.?通過檢查單個文件來查找表的大小要比使用SHOWTABLE STATUS快得多

5.4.3.?SHOW TABLE STATUS必須執(zhí)行更復(fù)雜的工作來確定為一個表分配了空間

5.4.4.?會使DROPTABLE性能變差。嚴(yán)重時可能導(dǎo)致服務(wù)器范圍內(nèi)明顯的停頓

5.4.4.1.?先將.ibd文件鏈接到一個大小為零的文件，然后手動刪除該文件，而不是等待MySQL來刪除

5.4.4.2.?從8.0.23版本開始，這應(yīng)該不再是一個問題了

5.5.?表空間在寫操作頻繁的環(huán)境中可能會變得非常大

5.5.1.?要限制寫操作，請將innodb_max_purge_lag變量設(shè)置為0以外的值

5.5.1.1.?表示在InnoDB開始延遲更多修改數(shù)據(jù)的查詢之前，可以等待清除的最大事務(wù)數(shù)

5.5.1.2.?設(shè)置innodb_max_purge_lag變量也會降低性能

5.5.2.?未清除的行版本會影響所有查詢，因為它們會使表和索引變大

5.5.3.?清除線程不能跟上進(jìn)度，性能就會下降

5.5.4.?默認(rèn)的可重復(fù)讀取事務(wù)隔離級別，InnoDB將無法刪除行的舊版本，因為未提交的事務(wù)仍需要能夠看到它們

5.5.5.?清除過程是多線程的，但如果遇到清除延遲問題（innodb_purge_threads和innodb_purge_batch_size），則可能需要針對工作負(fù)載進(jìn)行調(diào)優(yōu)

5.5.6.?如果Undo日志很大，并且表空間因此而增長，你可以強(qiáng)制MySQL放慢速度來讓InnoDB的清理線程跟上

5.5.6.1.?InnoDB會不斷地寫入數(shù)據(jù)并填充磁盤，直到磁盤空間耗盡或者表空間達(dá)到所定義的上限

5.6.?sync_binlog選項控制MySQL如何將二進(jìn)制日志刷新到磁盤，默認(rèn)值是1

5.6.1.?MySQL將執(zhí)行刷新并保持二進(jìn)制日志的持久性和安全性

5.6.2.?不建議設(shè)置為任何其他值

6.?MySQL并發(fā)

6.1.?如果遇到InnoDB并發(fā)問題，并且運行的MySQL版本低于5.7，解決方案通常是升級服務(wù)器

6.2.?如果你發(fā)現(xiàn)自己遇到了并發(fā)性瓶頸，最好的選擇是對數(shù)據(jù)進(jìn)行分片

6.2.1.?如果分片不可行，那么可能需要限制并發(fā)性

6.3.?限制并發(fā)性的最基本方法是使用innodb_thread_concurrency變量，該變量限制了內(nèi)核中同時可以有多少線程

6.3.1.?首先將innodb_thread_concurrency設(shè)置為與可用CPU核數(shù)相同的值，然后根據(jù)需要調(diào)整大小

7.?安全設(shè)置

7.1.?安全性和可靠性的保障成本往往更高

7.2.?max_connect_errors

7.2.1.?如果網(wǎng)絡(luò)暫時出現(xiàn)問題、出現(xiàn)應(yīng)用程序或配置錯誤，或者存在另一個問題導(dǎo)致連接無法在短時間內(nèi)成功完成，則客戶端可能會被阻止連接，并且在刷新主機(jī)緩存之前無法再次連接

7.2.2.?默認(rèn)設(shè)置（100）非常小，因此該問題很容易發(fā)生

7.2.3.?如果啟用了skip_name_resolve，則max_connect_errors選項將無效，因為其行為取決于主機(jī)緩存，而主機(jī)緩存被skip_name_resolve禁用

7.3.?skip_name_resolve

7.3.1.?DNS是MySQL連接過程中的一個薄弱環(huán)節(jié)

7.3.2.?某個時間點出現(xiàn)DNS故障的概率幾乎是確定性的

7.4.?max_connections

7.4.1.?設(shè)置得足夠高，以滿足你認(rèn)為將要經(jīng)歷的正常負(fù)載的連接需求，并且額外保留一些連接以便管理服務(wù)器時可以登錄

7.4.2.?默認(rèn)值為151，這對于很多應(yīng)用程序來說都不夠

7.4.3.?如果沒有使用持久連接，但是應(yīng)用程序沒有正常斷開連接，也會出現(xiàn)服務(wù)器連接占滿的情況

7.4.4.?可以顯示服務(wù)器是否在某個時刻出現(xiàn)了連接高峰。如果到達(dá)max_connections，則客戶端可能至少被拒絕了一次

7.5.?sql_mode

7.5.1.?最好讓MySQL在大多數(shù)方面保持原樣，而不要試圖讓它像其他數(shù)據(jù)庫服務(wù)器那樣運行

7.5.2.?在計劃升級數(shù)據(jù)庫時，一定要檢查對默認(rèn)sql_mode的更改

7.6.?sysdate_is_now

7.6.1.?如果你不是明確地希望SYSDATE()函數(shù)具有不確定性行為（這會破壞復(fù)制并使從備份中進(jìn)行的時間點恢復(fù)變得不可靠），那么可以啟用該選項并使其行為具有確定性

7.7.?read_only

7.7.1.?可防止未經(jīng)授權(quán)的用戶對副本進(jìn)行更改，副本應(yīng)僅通過復(fù)制而不是從應(yīng)用程序接收更改

7.7.2.?建議將副本設(shè)置為只讀模式

7.8.?super_read_only

7.8.1.?可阻止擁有SUPER權(quán)限的用戶寫入數(shù)據(jù)

7.8.2.?啟用此功能后，唯一可以將更改寫入數(shù)據(jù)庫的就是復(fù)制

7.8.3.?建議啟用super_read_only

7.8.4.?防止你意外地使用管理員賬戶將數(shù)據(jù)寫入只讀副本，從而引起數(shù)據(jù)不同步

8.?高級InnoDB設(shè)置

8.1.?innodb_autoinc_lock_mode

8.1.1.?控制InnoDB如何生成自動遞增的主鍵值

8.2.?innodb_buffer_pool_instances

8.2.1.?將緩沖池劃分為多個段，這可能是提高多核機(jī)器上MySQL在高并發(fā)工作負(fù)載下可伸縮性最重要的方法之一

8.2.2.?多個緩沖池對工作負(fù)載進(jìn)行分區(qū)，這樣一些全局互斥體就不會成為爭用熱點

8.3.?innodb_io_capacity

8.3.1.?告知InnoDB有多少I/O容量可供其使用

8.4.?innodb_read_io_threads

8.5.?innodb_write_io_threads

8.6.?如果你有很多硬盤和高并發(fā)工作負(fù)載，并且發(fā)現(xiàn)線程很難跟上，那么可以增加線程的數(shù)量，或者簡單地將它們設(shè)置為執(zhí)行I/O操作的物理磁盤數(shù)量

8.7.?innodb_strict_mode

8.7.1.?InnoDB在某些情況下拋出錯誤而不是警告，尤其是無效或可能導(dǎo)致危險的CREATE TABLE選項

8.8.?innodb_old_blocks_time

8.8.1.?一個由兩部分組成的緩沖池LRU列表，設(shè)計目的是防止臨時查詢將長期多次使用的頁面驅(qū)逐出去

8.8.2.?默認(rèn)情況下，它被設(shè)置為0

8.8.3.?將其設(shè)置為一個較小的值如1000（1秒），這在基準(zhǔn)測試中被證明是非常有效的

到了這里，關(guān)于讀高性能MySQL（第4版）筆記05_優(yōu)化服務(wù)器設(shè)置的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！