Description:
"WL#16179 Change default values for InnoDB variables" has disabled system variable `temptable_use_mmap`and deprecated it as well.
This worklog also changed the default value of `temptable_max_mmap` to 0 .
If one wishes the TempTable engine to allocate memory mapped files
for temporary tables, then one must set the valid non-zero value of this variable.
In 8.4, despite setting the valid value of sys variable `temptable_max_mmap`, TempTable doesn't create memory mapped files unless one turns ON the system variable `temptable_use_mmap`. It creates the temporary table from RAM to INNODB.
This is not expected behavior as per the description of temptable_max_mmap in the manual :
https://dev.mysql.com/doc/refman/8.4/en/internal-temporary-tables.html
P.S. The variable `temptable_use_mmap` is later removed in 9.4.0. So this issue is specific to 8.4.x
How to repeat:
Current behavior :
------------------
(root@localhost) {18000} [test]> SET SESSION internal_tmp_mem_storage_engine = TempTable;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) {18000} [test]> SET GLOBAL temptable_max_ram = 2*1024*1024;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) {18000} [test]> SET GLOBAL temptable_max_mmap=18446744073709551615;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) {18000} [test]> SET GLOBAL temptable_use_mmap=OFF;
Query OK, 0 rows affected, 1 warning (0.01 sec)
(root@localhost) {18000} [test]> SET SESSION cte_max_recursion_depth = 50000000;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) {18000} [test]> FLUSH STATUS;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) {18000} [test]> WITH RECURSIVE cte (c) AS
-> (SELECT 1 UNION ALL SELECT 1+c FROM cte WHERE c < 81901)
-> SELECT MIN(c), MAX(c), AVG(c) FROM cte;
+--------+--------+------------+
| MIN(c) | MAX(c) | AVG(c) |
+--------+--------+------------+
| 1 | 81901 | 40951.0000 |
+--------+--------+------------+
1 row in set (0.76 sec)
(root@localhost) {18000} [test]> SHOW SESSION STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| Created_tmp_tables | 1 |
+--------------------+-------+
1 row in set (0.00 sec)
(root@localhost) {18000} [test]> SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1 |
+-------------------------+-------+
1 row in set (0.00 sec)
Suggested fix:
Apply the following patch :
diff --git a/storage/temptable/include/temptable/allocator.h b/storage/temptable/include/temptable/allocator.h
index cef47b3df6c..d8e2d383df6 100644
--- a/storage/temptable/include/temptable/allocator.h
+++ b/storage/temptable/include/temptable/allocator.h
@@ -101,11 +101,7 @@ struct MemoryMonitor {
*
* @return MMAP-memory threshold. */
static size_t threshold() {
- if (temptable_use_mmap) {
return temptable_max_mmap;
- } else {
- return 0;
- }
}
/** Get current level of MMAP-backed memory consumption.
*
and repeat the test again
(root@localhost) {18000} [test]> SET SESSION internal_tmp_mem_storage_engine = TempTable;
Query OK, 0 rows affected (0.05 sec)
(root@localhost) {18000} [test]> SET GLOBAL temptable_max_ram = 2*1024*1024;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) {18000} [test]> SET GLOBAL temptable_max_mmap=18446744073709551615;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) {18000} [test]> SET GLOBAL temptable_use_mmap=OFF;
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root@localhost) {18000} [test]> SET SESSION cte_max_recursion_depth = 50000000;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) {18000} [test]> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) {18000} [test]> WITH RECURSIVE cte (c) AS
-> (SELECT 1 UNION ALL SELECT 1+c FROM cte WHERE c < 81901)
-> SELECT MIN(c), MAX(c), AVG(c) FROM cte;
+--------+--------+------------+
| MIN(c) | MAX(c) | AVG(c) |
+--------+--------+------------+
| 1 | 81901 | 40951.0000 |
+--------+--------+------------+
1 row in set (0.12 sec)
(root@localhost) {18000} [test]> SHOW SESSION STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| Created_tmp_tables | 1 |
+--------------------+-------+
1 row in set (0.01 sec)
(root@localhost) {18000} [test]> SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
+-------------------------+-------+
1 row in set (0.00 sec)
Notice no table was created on the disk this time.
