MySQL 效能調優概述

影響 MySQL 效能的因素主要有環境問題 (CPU 、磁碟 I/O 、網路效能、作業系統爭用 ) 和 MySQL 配置（資料庫設計 < 索引、資料型別、標準化 > 、應用程式效能 < 特定請求、短時事務 > 、 配置變數 < 緩衝區、快取記憶體、 InnoDB 設定 > ）

MySQL 的效能受主機的效能特徵影響。各種因素都會影響主機效能： CPU 速度和數量、磁碟吞吐量和訪問時間、網路吞吐量以及作業系統上的競爭服務都對 MySQL 例項的效能有一定的影響。

資料庫內容及其配置也會影響 MySQL 效能。

— 包含頻繁小更新的資料庫將因為精心設計和標準化而受益。

— 當您使用最小的適用資料型別來儲存資料時，資料庫吞吐量將提高。

— 僅請求表資料的一個子集的查詢將從精心設計的索引中獲益。

— 僅請求特定行和列的應用程式將減少冗餘請求所產生的開銷。

— 事務越短，越不會導致其他事務的鎖定和延遲。

— 調節得當的伺服器變數可最佳化針對特定工作負荷和資料集的 MySQL 緩衝區、快取記憶體和其他資源的分配。

1.1.         效能監控

要調節伺服器的效能，必須瞭解其效能特徵。為此，可以對整體效能進行基準測試， MySQL 安裝提供了以下基準測試工具：

l   mysqlslap 是標準 MySQL 分發的一部分。這是一個診斷程式，它模擬 MySQL 伺服器例項上的客戶機負載，並顯示每個階段的計時資訊。

l   sql-bench 是 MySQL 原始碼分發的一部分，它是一系列 Perl 指令碼，用於執行多個語句和收集狀態計時資料。

以下是使用 mysqlslap 在 SQL 指令碼中設定模式並執行其他指令碼中的查詢的示例：

shell>mysqlslap --iterations=5000 --concurrency=50 --query=workload.sql --create=schema.sql --delimiter=";"

此外， MySQL 還提供了第三方基準測試套件。

也可以使用慢速查詢日誌、一般查詢日誌， EXPLAIN 、 PROCEDURE ANALYSE 逐個分析事件；慢速查詢日誌用於記錄超過 long_query_time 和 min_examined_row_limit 變數所設定的限制的語句。使用 mysqldumpslow 可檢視慢速查詢日誌的內容。一般查詢日誌用於記錄 MySQL 所接收的所有客戶機連線和請求。使用該日誌可記錄一段時間內（例如，生成供 mysqlslap 或其他基準測試工具使用的工作負荷時）所接收的所有 SQL 語句。

使用 SHOW STATUS 和 mysqladmin extended-status 檢視資料庫狀態，同時可以使用 PERFORMANCE_SCHEMA 按組分析事件。

1.2.         效能模式

“效能模式”是在較低階別監控 MySQL 伺服器執行情況的功能。該功能是使用 PERFORMANCE_SCHEMA 儲存引擎和 performance_schema 資料庫實現的。從 Oracle 下載的所有二進位制版本的 MySQL 中都提供了效能模式。預設情況下，將啟用效能模式，並在伺服器啟動時使用 performance_schema 變數對其進行控制。使用以下語句確認已啟用效能模式：

mysql> SHOW VARIABLES LIKE 'performance_schema';

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

| Variable_name | Value |

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

| performance_schema | ON |

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

效能模式下允許您監控並檢查 MySQL 伺服器中的被檢測程式碼的效能特徵。開發者將檢測函式和其他編碼事件以收集計時資訊；公開的效能資料對 MySQL 程式碼庫的貢獻者、外掛開發者很有幫助，同時對識別低階別的效能瓶頸，如日誌檔案 I/O 等待或緩衝池互斥等任務有極大好處。效能模式所公開的資訊可用於識別低階別瓶頸。該資訊中大部分都是低階別的，可幫助 MySQL 伺服器產品系列的開發者除錯效能問題，或者幫助系統架構師和效能顧問調節 InnoDB 資料和日誌檔案儲存硬體。

l   檢測（ INSTRUMENT ）、例項（ INSTANCE ）、事件（ EVENT ）和使用者（ CONSUMER ）

效能模式資料庫包含配置和事件資訊：

— 檢測 " 是伺服器程式碼中引發要監控的事件的點，在 setup_instruments 表中進行配置；

— 每個被檢測的物件都是該檢測的一個“例項”，記錄在一系列例項表中。

— 當執行緒執行檢測例項中的程式碼時， MySQL 將識別所發生的“事件”，將其記錄在事件和彙總表中。

— 每個“使用者”都是效能模式中表的名稱，用於記錄和查詢事件以及事件的摘要，在 SETUP_CONSUMERS 表中進行配置。

 

效能模式中的檢測是伺服器原始碼中的點， MySQL 從該點引發事件。檢測具有分層命名約定。例如，以下是包含效能模式中數百個檢測中的一部分的簡短列表：

stage/sql/statistics

statement/com/Binlog Dump

wait/io/file/innodb/innodb_data_file

wait/io/file/sql/binlog

wait/io/socket/sql/server_unix_socket

每個檢測由其型別、所屬的模組、該特定檢測的變數或類組成。透過查詢 performance_schema.setup_instruments 表可檢視所有可用的檢測。

效能模式將記錄例項表中的每個檢測例項。例如，以下查詢顯示檢測 wait/io/file/sql/FRM 記錄檔案例項 /var/lib/mysql/mem/tags.frm 上的事件。

mysql> SELECT file_name, event_name FROM file_instances LIMIT 1\G

*************************** 1. row ***************************

FILE_NAME: /var/lib/mysql/mem/tags.frm

EVENT_NAME: wait/io/file/sql/FRM

以下輸出顯示了 setup_consumers 表的內容：

mysql> SELECT * FROM setup_consumers;

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

| NAME | ENABLED |

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

| events_stages_current | NO |

| events_stages_history | YES |

| events_stages_history_long | NO |

| events_statements_current | YES |

| events_statements_history | NO |

| events_statements_history_long | NO |

| events_waits_current | YES |

| events_waits_history | YES |

| events_waits_history_long | NO |

| global_instrumentation | YES |

| thread_instrumentation | YES |

| statements_digest | YES |

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

12 rows in set (0.00 sec)

每個使用者的 NAME 是效能模式中用於查詢事件和摘要的表的名稱。被禁用的使用者不記錄資訊，從而節省了系統資源。

當 MySQL 識別了發生在檢測例項中的事件後，會將其記錄在事件表中。

— 主事件表為 events_waits_current ，該表中儲存了每個執行緒最近的事件。

— events_waits_history 儲存每個執行緒的最近 10 個事件。

— events_waits_history_long 共儲存 10,000 個最近事件。

events_waits_* 表都使用相同的模式。有關該模式的結構資訊，請訪問

http://dev.mysql.com/doc/refman/5.6/en/events-waits-current-table.html

 

當使用效能模式識別瓶頸或其他問題時，請執行以下操作：

1. 確保已針對適用於您所遇到的問題型別的一系列檢測和使用者啟用了效能模式。例如，如果您確定問題出在 I/O 限制上，請使用 wait/io/file/* 檢測；如果不確定根本原因，請使用更廣範圍的檢測。

2. 執行用於產生該問題的測試用例。

3. 查詢 events_waits_* 表等使用者，尤其是使用適用的 WHERE 子句過濾器查詢 events_waits_history_long ，以便進一步縮小問題原因的範圍。

4. 禁用那些用於評估已排除的問題的檢測。

5. 重試該測試用例。

有關事件過濾的更多資訊，請訪問：

http://dev.mysql.com/doc/refman/5.6/en/performance-schema-filtering.html

1.3.         一般資料庫最佳化

1)          標準化

對資料進行標準化可以消除冗餘資料，提高事務性工作負荷的效能，提供對資料的靈活訪問，最大限度地減少資料不一致情況。

標準化是移除資料庫中的冗餘和不當依賴關係（以避免將相同的資料儲存在多個地方以及出現異常的風險）的行為。標準化通常會產生以下結果：許多表的列變少，整體儲存要求降低， I/O 需求降低以及單次插入、更新和刪除操作加快。這提高了頻繁執行小更新的事務性工作負荷的效能，但會使檢索大量資料的查詢變得複雜。

2)          資料型別和大小

選擇正確的資料型別和大小可以避免 NULL ，提高效能，保護資料，在適當情況下使用資料壓縮。

選擇正確的資料型別是表設計中一個很重要卻常常被忽視的部分，資料型別的大小可能會對錶操作產生較大的影響。例如，選擇將 SMALLINT 數字儲存為 INT 會使該列所需的空間翻倍。在包含一百萬個行的表中，該決策將導致浪費額外的 2 MB 儲存空間，並且磁碟操作速度會變慢，緩衝區和快取記憶體將需要使用更多記憶體。使用 INSERT … COMPRESS(field_name) … 和 SELECT … UNCOMPRESS(column_name) ... 可以在儲存和檢索字串資料時對其進行壓縮和解壓縮。儘管也可以使用 CHAR 或 VARCHAR 欄位來實現此目的，但是透過使用 VARBINARY 或 BLOB 列儲存壓縮資料可以避免字符集轉換出現問題。

3)          高效索引

建立最佳索引可以提高查詢吞吐量，減少 I/O 開銷。

如果您透過在 WHERE 子句中指定一個欄位來查詢表中的特定行，並且該表沒有為該欄位建立索引， MySQL 將讀取該表中的每一行以找到每個匹配的行。這將導致很多不必要的磁碟訪問，並且對於大型表效能將顯著降低。索引是有序的成組資料，透過索引， MySQL 可以更容易地找到查詢行的正確位置。預設情況下， InnoDB 將按主鍵排列表的順序；該有序表稱為群集索引。 InnoDB 表上的每個附加索引或輔助索引會在檔案系統中佔用額外的空間，因為索引包含索引欄位的額外副本以及主鍵的副本。每次使用 INSERT 、 UPDATE 、 REPLACE 或 DELETE 操作修改資料時， MySQL 也必須更新所有包含修改欄位的索引。因此，向表中新增多個索引會降低影響該表的資料修改操作的效能。不過，如果對索引進行了適當設計，依賴於索引欄位的查詢便會在效能上有較大的獲益。如果查詢無法使用索引找到特定行，則必須執行全表掃描；即，必須讀取整個表來找到該行。使用索引的查詢可以直接讀取相應行而不讀取其他行，這極大地提高了此類查詢的效能。

1.4.         PROCEDURE ANALYSE

PROCEDURE ANALYSE() ，在最佳化表結構時可以輔助參考分析語句。利用此語句， MySQL 幫你去分析你的欄位和其實際的資料，並會給你一些有用的建議。但是，只有表中有實際的資料，這些建議才會變得有用，因為要做一些大的決定是需要有資料作為基礎的。

語法如下：

SELECT ... FROM table_name WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]]);

l   max_elements ( 預設值 256) 是 analyse 注意到每列不同值的最高數目。 analyse 使用此引數來檢查是否最最佳化的列的型別是 ENUM 型別。

l   max_memory ( 預設值 8192) 是 analyse 在查詢所有不同值時分配給每列的最大記憶體數。

示例 1 ：

                                             

以第 4 行的分析舉例，可以看出， br_Task.task_name 欄位：

列最小值 :121 new

列最大值 : 韓 - 軟文 - 全國 - 銀牌見

最小長度 :3 位元組

最大長度 :52 位元組

平均長度 :24.1852

最佳化建議：欄位的資料型別改成 VARCHAR(52) NOT NULL 。

 

示例 2 ：

PROCEDURE ANALYSE 分析給定查詢中的列，並提供對每個欄位的調節反饋：

mysql> SELECT CountryCode, District, Population

-> FROM City PROCEDURE ANALYSE(250,1024)\G

預設設定通常建議使用 ENUM 型別來最佳化表的設計。如果確定不想在分析列時使用 PROCEDURE ANALYSE() 所建議的 ENUM 值，請使用非預設引數。

第一個引數是分析 ENUM 值是否適當時要考慮的不同元素數。此引數的預設值為 256 。

第二個引數是用於收集不同的值以供分析的最大記憶體量。此引數的預設值為 8192 ，表示 8 KB 。如果為此引數設定值 ，則 PROCEDURE ANALYSE() 無法檢查不同的值以建議使用 ENUM 型別。如果 PROCEDURE ANALYSE() 無法儲存可接受範圍內的候選 ENUM 值（在引數設定的限制內），則不會建議對該列使用 ENUM 型別。

本示例建議對 City.CountryCode 列使用 CHAR(3) 型別。另一方面，如果使用預設引數，則 PROCEDURE ANALYSE() 將建議 ENUM('ABW','AFG',...,'ZMB','ZWE') ，這是一種包含超過 200 個元素的 ENUM 型別，其中針對每個相應的 CountryCode 值都包含一個不同值。

【參考】

https://blog.csdn.net/ty_hf/article/details/54895466

 

1.5.         EXPLAIN

EXPLAIN 命令描述 MySQL 打算如何執行特定的 SQL 語句，不返回資料集的任何資料，並提供有關 MySQL 打算如何執行該語句的資訊

使用 EXPLAIN 可檢查 SELECT 、 INSERT 、 REPLACE 、 UPDATE 和 DELETE 語句。 將 EXPLAIN 置於語句之前， EXPLAIN SELECT ... 、 EXPLAIN UPDATE... ；

EXPLAIN 將為語句中使用的每個表生成一行輸出。該輸出包含以下列：

— table ：輸出行所對應的表

— select_type ：查詢中使用的選擇型別。 SIMPLE 意味著查詢未使用 UNION 或子查詢。

— key ：最佳化程式所選擇的索引

— ref ：與索引比較的列

— rows ：最佳化程式所檢查的行的估計數目

— Extra ：最佳化程式提供的每個查詢的其他資訊

有關輸出列的完整論述，請訪問：

http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

使用 EXPLAIN EXTENDED ... 可檢視最佳化程式提供的其他資訊。有關完整論述，請訪問：

http://dev.mysql.com/doc/refman/5.6/en/explain-extended.html

 

例如，以下查詢可聯接兩個表的欄位並執行聚合：

mysql> SELECT COUNT(*) as 'Cities', SUM(Country.Population) AS Population,

> Continent FROM Country JOIN City ON CountryCode = Code

> GROUP BY Continent ORDER BY Population DESC;

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

| Cities | Population | Continent |

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

| 1765 | 900934498400 | Asia |

| 580 | 95052481000 | North America |

| 842 | 55127805400 | Europe |

| 470 | 48533025000 | South America |

| 366 | 16179610000 | Africa |

| 55 | 307500750 | Oceania |

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

6 rows in set (0.01 sec)

以下輸出顯示了在查詢前使用 EXPLAIN 的結果：

mysql> EXPLAIN SELECT COUNT(*) as 'Cities', SUM(Country.Population) AS Population,

> Continent FROM Country JOIN City ON CountryCode = Code

> GROUP BY Continent ORDER BY Population DESC\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: Country

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 239

Extra: Using temporary; Using filesort

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: City

type: ref

possible_keys: CountryCode

key: CountryCode

key_len: 3

ref: world_innodb.Country.Code

rows: 9

Extra: Using index

2 rows in set (0.00 sec)

 

EXPLAIN 格式

EXPLAIN 輸出也提供其他格式：

1 ）視覺化 EXPLAIN ，圖形格式的輸出在 MySQL Workbench 中提供

2 ） EXPLAIN FORMAT=JSON ， JSON 格式的輸出，當要將 EXPLAIN 輸出傳遞給程式以供進一步處理 / 分析時十分有用

JSON （ JavaScript Object Notation ， JavaScript 物件表示法）是一種簡單的資料交換格式。以下輸出顯示了在 EXPLAIN 語句中使用 FORMAT=JSON 的結果：

mysql> EXPLAIN FORMAT=JSON SELECT COUNT(*) as 'Cities', SUM(Country.Population) AS Population, Continent FROM Country JOIN City ON CountryCode = Code GROUP BY Continent ORDER BY Population DESC\G

*************************** 1. row ***************************

EXPLAIN: {

"query_block": {

"select_id": 1,

"ordering_operation": {

"using_filesort": true,

"grouping_operation": {

"using_temporary_table": true,

"using_filesort": false,

…

1 row in set, 1 warning (0.00 sec)

1.6.         伺服器狀態

1)          檢查伺服器狀態的方法

MySQL 提供了多種檢視伺服器狀態變數的方法：

l   在 mysql 提示符下檢視，命令 STATUS 、 SHOW STATUS;

l   在終端上檢視：

mysqladmin --login-path=login-path status

mysqladmin -u user -p extended-status

MySQL 透過 mysql 命令 STATUS 和 mysqladmin 命令 status 提供簡短的狀態訊息。透過 mysql 命令 SHOW STATUS 和 mysqladmin 命令 extended-status 顯示的長格式狀態輸出包含許多系統狀態變數的值，後面的將討論其中最重要的值。

對 mysqladmin 使用選項可提供附加功能。例如， --sleep （或 -i ）選項可指定在迭代之間等待的秒數，並在等待該時間後自動重新執行命令。 --relative （或 -r ）選項顯示自上次迭代後每個變數的差異，而不是變數值。使用 grep 等命令列工具可擴充套件 mysqladmin 的使用方式。例如，使用以下命令可僅顯示包含字串 cache_hits 的變數：

shell> mysqladmin --login-path=admin extended-status | grep cache_hits

| Qcache_hits | 0 |

| Ssl_callback_cache_hits | 0 |

| Ssl_session_cache_hits | 0 |

| Table_open_cache_hits | 280 |

2)          主要狀態變數

Ø   Created_tmp_disk_tables ：顯示磁碟上的內部臨時表的數量；獲取執行語句時伺服器所建立的臨時表數。如果該數值較高，則伺服器已在磁碟上（而不是在記憶體中）建立多個臨時表，從而導致查詢執行較慢。

Ø   Handler_read_first ：顯示索引中第一個條目的讀取次數，如果該數值較高，則伺服器已執行多次完整索引掃描以完成查詢請求。

Ø   Innodb_buffer_pool_wait_free ：顯示伺服器等待乾淨頁面的次數，等待 InnoDB 緩衝池中的頁面重新整理後才可以完成查詢請求。如果該數值較高，則未正確設定 InnoDB 緩衝池的大小，因而查詢效能受到影響。

Ø   Max_used_connections ：顯示自伺服器啟動以來的最大併發連線數；此變數提供非常有用的資訊來確定伺服器必須支援的併發連線數。

Ø   Open_tables ：顯示給定時間內開啟的表的數量；將此變數與伺服器系統變數 table_cache 比較，可提供有關應該為表快取記憶體預留多少記憶體量的有用資訊。如果 Open_tables 狀態變數的值通常很低，請減小伺服器系統變數 table_cache 的大小。如果該值很高（接近伺服器系統變數 table_cache 的值），請增加分配給表快取記憶體的記憶體量來縮短查詢響應時間。

Ø   Select_full_join ：顯示執行表掃描而不是使用索引的聯接數量，如果該值不是 ，則應該仔細檢查表的索引。

Ø   Slow_queries ：顯示用時比 long_query_time 系統變數所指定的秒數長的查詢數；此狀態變數取決於對 long_query_time 變數（預設值為 10 秒）設定的瞭解。如果 Slow_queries 狀態變數不是 ，請檢查 long_query_time 的值和慢速查詢日誌，並改進所捕獲的查詢。

Ø   Sort_merge_passes ：顯示排序演算法所執行的合併傳遞次數；排序操作需要記憶體中的緩衝區。此狀態變數計算排序操作所需的經過排序緩衝區的傳遞次數。如果該值較高，則可能表明排序緩衝區大小不足以執行查詢的一次透過排序；請考慮增大 sort_buffer_size 系統變數的值。

Ø   Threads_connected ：顯示當前開啟的連線數；定期捕獲該值可提供有關伺服器何時最活躍的有用資訊。使用此變數可確定執行伺服器維護的最佳時間，或者可將其作為為伺服器分配更多資源的依據。

Ø   Uptime ：顯示伺服器持續執行的秒數；該值可以提供有關伺服器執行狀況的有用資訊，例如伺服器需要重新啟動的頻率。

 

1.7.         系統變數調優簡介

MySQL 效能調優應該首先調節查詢、模式和索引，因為每次操作可獲得比調節變數更多的收益；其次考慮針對伺服器大小進行調節，例如記憶體和 I/O ；然後針對應用程式配置進行調節；例如，儲存引擎設定，將物理 RAM 的 70% – 85% 提供給 InnoDB 緩衝池，最小化 MyISAM 快取記憶體和緩衝區等；最後，根據伺服器負載型別（事務伺服器、報告伺服器）調整連線數等

一個常見的誤區是認為伺服器變數配置是伺服器調節中最重要的部分。事實上，從花費的精力來看，最佳化模式、常見查詢和典型資料庫的索引可獲得比調節變數更多的好處。

²   預設設定

Oracle 的 MySQL 工程師選擇預設設定來適應大多數生產系統，這些系統常常要處理頻繁的小事務、許多更新和少數大型慢速查詢（如用於生成報告的查詢）。然而，由於 MySQL 在從小型裝置（如銷售點系統和路由器）到具有大量記憶體和快速磁碟陣列的大型 Web 伺服器等各種系統上都在使用，您可能會發現，對於您的特定環境和工作負荷，可以從更改伺服器的某些預設設定中獲益。

²   InnoDB 設定

例如，在僅使用 InnoDB 使用者表的 MySQL 專用伺服器上，可以將 innodb_buffer_pool_size 的值增大到佔伺服器總記憶體的較大比例 (70% – 85%) ，同時要記住作業系統的需要，如 cron 作業、備份、病毒掃描以及管理連線和任務。如果有幾 GB 的 RAM ，則還可以透過使用多個 innodb_buffer_pool_instances 而獲益，該設定可啟用多個緩衝池，從而避免爭用。

²   降低 MyISAM 設定

在不將 MyISAM 用作使用者表的系統上，減小僅適用於 MyISAM 的選項的值（例如將 key_buffer_size 的值減小為 16 MB 等較小值），同時要記住某些內部 MySQL 操作將使用 MyISAM 。

²   報告系統

在用於執行少數大型慢速查詢（例如用於業務智慧報表的查詢）的伺服器上，使用 join_buffer_size 和 sort_buffer_size 等設定增加專用於緩衝區的記憶體量。雖然預設伺服器設定更適合事務系統，但預設的 my.cnf 檔案包含這些變數適用於報告伺服器的替代值。

²   事務系統

在用於支援許多反覆斷開並重新連線的快速併發事務的伺服器上，請將 thread_cache_size 的值設定為足夠大的值，以便大多數新連線可以使用快取記憶體的執行緒；這可避免建立和斷開每個連線的執行緒時的伺服器開銷。

在支援多寫入操作的伺服器上，請提高 innodb_log_file_size 和 innodb_log_buffer_size 等日誌設定，因為資料修改操作的效能在很大程度上依賴於 InnoDB 日誌的效能。請考慮更改 innodb_flush_log_at_trx_commit 的值以提高每次提交的效能，但風險是：如果伺服器出現故障，可能會丟失某些資料。

如果您的應用程式反覆執行相同的查詢（或多個相同的查詢），請考慮啟用查詢快取記憶體，並根據常見查詢的結果調節其大小，方法是為 query_cache_type 和 query_cache_size 設定適當的值。

²   平衡記憶體使用

當您為每個查詢或每個連線的快取記憶體和緩衝區設定較大的值時，會減少緩衝池的可用大小。調節伺服器的配置變數是一個平衡過程，需要從預設值開始，提供儘可能多的記憶體給緩衝池，然後調節與以下項最緊密相關的變數：調節目標、透過檢查伺服器狀態識別出的問題以及透過查詢效能模式識別出的瓶頸。

 

1)          主要伺服器系統變數：

Ø   innodb_buffer_pool_size ：定義 InnoDB 用於快取表資料和索引的記憶體緩衝區大小（以位元組為單位）；要想獲得最佳效能，請將此值設定為儘可能大，同時要記住值過高會導致作業系統交換頁面，從而大大降低效能。如果在專用資料庫伺服器上僅使用了 InnoDB 使用者表，請考慮將此變數設定為介於物理 RAM 的 70% 到 85% 之間的值。

Ø   innodb_flush_log_at_trx_commit ：定義 InnoDB 將日誌緩衝區寫入日誌檔案的頻率，以及對日誌檔案執行重新整理到磁碟操作的頻率；此變數有三種可能的設定：

n   ：每秒將日誌緩衝區寫入磁碟一次。

n   1 ：每次提交時將日誌重新整理到磁碟；如果未發生提交，則每秒重新整理一次。

n   2 ：將日誌重新整理到作業系統快取記憶體中，並且每隔 innodb_flush_log_at_timeout 秒（預設為一秒）重新整理到磁碟一次。

Ø   innodb_log_buffer_size ：定義 InnoDB 用於寫入磁碟上的日誌檔案的緩衝區的大小（以位元組為單位）；此變數的預設值為 8 MB 。事務超過此大小會導致 InnoDB 在事務提交之前將日誌重新整理到磁碟，從而降低效能。對於使用大量 BLOB 或者在更新活動中具有較大峰值的應用程式，可透過增大該值提高事務效能。

Ø   innodb_log_file_size ：定義日誌組中每個日誌檔案的大小（以位元組為單位）；對於大型資料集上的寫入密集型工作負荷，請設定此變數以便所有日誌檔案的最大總大小（透過 innodb_log_files_in_group 設定）小於或等於緩衝池的大小。大型日誌檔案會減緩故障恢復，但可以透過減少檢查點重新整理活動來提高整體效能。

Ø   join_buffer_size ：定義用於使用表掃描的聯接的最小緩衝區大小；對於包含無法使用索引的聯接的查詢，請以預設值 (256 KB) 為起點增大該值。執行此類查詢時請更改每個會話的值，以避免設定全域性設定而使無需這麼大值的查詢浪費記憶體。

Ø   query_cache_size ：定義為快取查詢結果而分配的記憶體量；透過使用查詢快取記憶體，提高針對極少更改的資料發出重複查詢的應用程式的效能。作為基線，請根據重複查詢的數量和所返回資料的大小將此變數設定為介於 32 MB 和 512 MB 之間的值。請監控快取記憶體命中率以確定此變數的有效性，並根據您的觀察調節其值。

Ø   sort_buffer_size ：定義分配給需要進行排序的會話的最大記憶體量；如果 Sort_merge_passes 狀態變數的值很高，請增大該值以提高 ORDER BY 和 GROUP BY 操作的效能。

Ø   table_open_cache ：定義所有執行緒開啟的表的數量；請設定該值以使其大於 N * max_connections ，其中， N 是在應用程式的所有查詢中所使用的最大表數量。該值過高會導致出現錯誤“ Too many open files （開啟的檔案太多）”。 Open_tables 狀態變數的值較高表示 MySQL 頻繁開啟和關閉表，因此應該增大 table_open_cache 。

Ø   thread_cache_size ：定義伺服器應快取以供重用的執行緒數；預設情況下，此變數將自動調節大小。評估 Threads_created 狀態變數可確定是否需要更改 thread_cache_size 的值。

 

2)          準備調節

調節資料庫伺服器可以與調節樂器類比，選擇要更改的值並確定一個目標，向上和向下調節該值並同時測試檢測的行為，確定最佳設定。

準備調節環境要儘可能地複製生產系；要減小與正在調節的變數無關的已更改因素的影響，請在停機期間對生產伺服器執行調節，或者最好在複製的系統上進行調節。

然後確定調節目標，例如每秒處理更多事務，更快生成複雜報表，透過併發連線的峰值提高效能；調節之前，請確定一個目標。所選的調節變數取決於您設定的目標。有很少幾個連線的報告伺服器的最佳設定與有許多連線且每秒處理數百個小型事務的事務應用伺服器的最佳設定有很大的不同。記憶體與資料庫大小的比率較高的伺服器與記憶體較小但資料庫較大的伺服器相比，具有非常不同的效能特徵。繁重的寫入工作負荷需要的設定不同於只讀系統。選擇適當的變數進行調節，如緩衝區、快取記憶體、日誌設定等；

最後，應用程式程式碼，一般查詢日誌等方法收集代表性語句；為了最準確地模擬正在針對其進行調節的工作負荷，請收集一組有代表性的語句。從應用程式中選擇查詢和修改操作比例正確的語句序列。在要最佳化的每天或每週期間內，使用一般查詢日誌從生產伺服器收集實際語句。

3)          練習調節

查詢每個變數的最佳值的基準測試，首先將變數設定為低於其預設值的設定，然後進行基準測試，測量相關度量，如虛擬記憶體使用、所花費的平均時間、相關狀態變數；

然後，增大變數值並重復基準測試，如果需要，重新整理狀態變數。

最後，將結果繪製成圖，查詢收益的下降點和效能的高峰，並根據所用資源和效能之間的最佳平衡來決定最終變數值。

要檢視所選度量的值，請使用：

— mysqlslap 或 mysql 來執行工作負荷並獲取平均執行時間

— sql-bench 來執行更一般的基準測試

— mysqladmin extended-status 來獲取工作負荷前後的狀態變數的值

— top 等作業系統工具或 /proc 檔案系統來訪問過程度量

如果要針對特定變數使用多個不同值執行微調基準測試，或者如果要在很長一段時間內反覆執行相同的基準測試，請考慮使用指令碼語言來自動化基準測試中所使用的步驟。

 

4)          調節示例：排序緩衝區大小

本示例顯示了一系列針對具有繁重排序工作負荷的資料庫的測試結果，其中，執行測試時更改了 sort_buffer_size 變數。

圖表顯示：

— 在 sort_buffer_size 從 32 KB 增大到 512 KB 時， Sort_merge_passes 狀態變數的值（可使用 mysqladmin extended_status -r 檢視）急劇下降，在此之後又緩慢降低；

— 測試工作負荷所花的平均時間（可使用 mysqlslap 檢視）在 sort_buffer_size 為 512 KB 時降低，在 4 MB 時達到極大峰值，然後在 8 MB 時下降，最終在 32 MB 時達到最佳效能；

— mysqld 程式的總虛擬記憶體（可使用 top 檢視）在 sort_buffer_size 為 512 KB 時最小，此後一直到 16 MB 都穩步上升，在 32 MB 時急劇上升；

查詢的平均時間最短時， sort_buffer_size 為 32 MB ，該設定使用了大量記憶體，而緩衝池本來可以更好地利用這些記憶體。在本示例中，針對測試中所使用的工作負荷、伺服器和資料庫的特定組合， 512 KB 設定可在效能和所用記憶體之間提供最佳平衡。