Innodb

max_heap_table_size 和 tmp_table_size (MariaDB 10.1.x) 的不同設置

  • June 9, 2018

我執行並得到了關於和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 使用“臨時表引擎”,這可能導致上述某些討論沒有實際意義。

引用自:https://dba.stackexchange.com/questions/209090