max_heap_table_size 和 tmp_table_size (MariaDB 10.1.x) 的不同設置
我執行並得到了關於和
pt-variable-advisor
的不同設置的註釋。max_heap_table_size``tmp_table_size
通過網路搜尋,我只找到了舊文章(2007 年左右)。
pt-variable-advisor h=localhost,u=root,p=Quule0juqu7aifohvo2Ahratit --socket /var/vcap/sys/run/mysql/mysqld.sock (...) # NOTE tmp_table_size: The effective minimum size of in-memory implicit temporary tables used internally during query execution is min(tmp_table_size, max_heap_table_size), so max_heap_table_size should be at least as large as tmp_table_size. (...)
我們的配置
max_heap_table_size = 16777216 tmp_table_size = 33554432
我們沒有修改cf-mysql-release的預設值。我看到 MariaDB KB 推薦了其他預設值。
cf_mysql.mysql.tmp_table_size: description: 'The maximum size (in bytes) of internal in-memory temporary tables' default: 33554432 cf_mysql.mysql.max_heap_table_size: description: 'The maximum size (in rows) to which user-created MEMORY tables are permitted to grow' default: 16777216
我還找到了優化 MySQL tmp_table_size並檢查了我們的值和配置:
MariaDB [(none)]> show global status like 'created_tmp_disk_tables'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Created_tmp_disk_tables | 12727901 | +-------------------------+----------+ 1 row in set (0.01 sec) MariaDB [(none)]> show global status like 'created_tmp_tables'; +--------------------+-----------+ | Variable_name | Value | +--------------------+-----------+ | Created_tmp_tables | 115714303 | +--------------------+-----------+ 1 row in set (0.01 sec) MariaDB [(none)]> select (12727901*100)/(115714303 + 12727901) as "Created disk tmp tables ratio" from dual; +-------------------------------+ | Created disk tmp tables ratio | +-------------------------------+ | 9.9094 | +-------------------------------+ 1 row in set (0.00 sec)
我們的(預設)配置有問題嗎?我們不知道我們的工作量。我們為具有不同使用模式的小型 Web 應用程序執行了大約 500 個小型數據庫。
**簡短的回答:**這些都不是“錯誤的”。
這些設置是如何使用的?
這兩個設置有(如果我沒記錯的話)恰好有 2 種用途。正如您引用的一些內容所暗示的那樣,它們是:
- 當你這樣做時
CREATE TABLE ... ENGINE=MEMORY
,它會給出一個目前值的限制max_heap_table_size
。您可以並且“應該”在執行此操作之前更改該設置CREATE
。太大的值可能會浪費寶貴的 RAM。- 當複雜
SELECT
需要創建臨時表時,例如在準備中ORDER BY
,它首先嘗試使用一個MEMORY
表;如果失敗(由於多種原因中的任何一種),它會使用MyISAM
.MEMORY
表大小的限制是min(max_heap_table_size, tmp_table_size)
。我不相信
tmp_table_size
是每一個都單獨使用。如果您從不明確創建
MEMORY
表,那麼將這兩個設置設為相同可以避免混淆和此類討論。因此,使它們平等的警告比您引用的措辭要弱。它是“有點,有點,應該”,而不是“你必須!”。
不同的觀點: 讓我從另一個角度來處理你的問題。
Intra-select tmp 表可能經常出現——在每個連接中,甚至在單個選擇中多次出現。因此,保持
tmp_table_size
“低”對於避免耗盡 RAM 很重要。我建議不超過 1% 的 RAM,但這是相當隨意的。同時,如果您正在使用 MEMORY 表,則需要控制它們的大小。
好吧,我已經走到了死胡同。為什麼他們有“min(…)”?為什麼不把它們分開呢?
我不知道。
未來: 以上討論至少適用於 MySQL 4.0 到 5.7 版本,以及 MariaDB 的所有版本(到目前為止)。MySQL 8.0 使用“臨時表引擎”,這可能導致上述某些討論沒有實際意義。