Bug #96071 TempTable engine uses much more memory than MEMORY engine for temporary table
Submitted: 2 Jul 10:43 Modified: 3 Jul 1:51
Reporter: Øystein Grøvlen Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.16 OS:Any
Assigned to: CPU Architecture:Any

[2 Jul 10:43] Øystein Grøvlen
Description:
According to performance schema the new TempTable engine uses about three times as much memory as MEMORY for internal temptable created by Query 10 of DBT3:

mysql> truncate performance_schema.memory_summary_global_by_event_name; 
Query OK, 0 rows affected (0.00 sec)

mysql>  select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue,        c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey   and o_orderdate >= '1993-11-01'   and o_orderdate < date_add( '1993-11-01' ,interval '3' month)   and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20;
...
20 rows in set (13.56 sec)

mysql> SELECT *        FROM performance_schema.memory_summary_global_by_event_name where event_name like 'memory/temptable%';                   
+--------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| EVENT_NAME                     | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED |
+--------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| memory/temptable/physical_disk |           0 |          0 |                         0 |                        0 |              0 |                  0 |               0 |                        0 |                            0 |                         0 |
| memory/temptable/physical_ram  |          12 |         11 |                1074790400 |               1073741824 |              1 |                  1 |              11 |                  1048576 |                      1048576 |                1073741824 |
+--------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
2 rows in set (0.01 sec)

In other words, performance schema reports that exactly 1GB was allocated for the temporary table (temptable_max_ram was set to default).  If I reduce temptable_max_ram a little, we see that TempTable engine will use disk:

mysql> set global temptable_max_ram = 1000*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate performance_schema.memory_summary_global_by_event_name;                                                                                                                                                                   Query OK, 0 rows affected (0.00 sec)

mysql>  select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue,        c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey   and o_orderdate >= '1993-11-01'   and o_orderdate < date_add( '1993-11-01' ,interval '3' month)   and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20;
...
20 rows in set (14.04 sec)

mysql> SELECT *        FROM performance_schema.memory_summary_global_by_event_name where event_name like 'memory/temptable%';
+--------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| EVENT_NAME                     | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED |
+--------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| memory/temptable/physical_disk |           1 |          1 |                 536870912 |                536870912 |              0 |                  0 |               1 |                        0 |                            0 |                 536870912 |
| memory/temptable/physical_ram  |          11 |         10 |                 537919488 |                536870912 |              1 |                  1 |              10 |                  1048576 |                      1048576 |                 536870912 |
+--------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
2 rows in set (0.01 sec)

Compare this to using MEMORY engine for tmp tables where there is no overflow when max in-memory size is 352 MB:

mysql> set internal_tmp_mem_storage_engine = MEMORY;
Query OK, 0 rows affected (0.00 sec)

mysql> set max_heap_table_size = 352*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> set tmp_table_size = @@max_heap_table_size;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@max_heap_table_size, @@tmp_table_size;                                                                                                                                                                                    +-----------------------+------------------+
| @@max_heap_table_size | @@tmp_table_size |
+-----------------------+------------------+
|             369098752 |        369098752 |
+-----------------------+------------------+
1 row in set (0.00 sec)

mysql> flush status;                                                                                                                                                                                                                      Query OK, 0 rows affected (0.00 sec)

mysql>  select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue,        c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey   and o_orderdate >= '1993-11-01'   and o_orderdate < date_add( '1993-11-01' ,interval '3' month)   and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20;
...
20 rows in set (15.98 sec)

mysql> show status WHERE Variable_name like 'Created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

Note that no tmp tables on disk was created in this case.  

How to repeat:
First, create a DBT3 database, scale factor 10, then

truncate performance_schema.memory_summary_global_by_event_name; 

select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue,        c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey   and o_orderdate >= '1993-11-01'   and o_orderdate < date_add( '1993-11-01' ,interval '3' month)   and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20;

SELECT * FROM performance_schema.memory_summary_global_by_event_name where event_name like 'memory/temptable%';

set global temptable_max_ram = 1000*1024*1024;

truncate performance_schema.memory_summary_global_by_event_name; 

select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue,        c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey   and o_orderdate >= '1993-11-01'   and o_orderdate < date_add( '1993-11-01' ,interval '3' month)   and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20;

SELECT * FROM performance_schema.memory_summary_global_by_event_name where event_name like 'memory/temptable%';

set internal_tmp_mem_storage_engine = MEMORY;

set max_heap_table_size = 352*1024*1024;

set tmp_table_size = @@max_heap_table_size;

select @@max_heap_table_size, @@tmp_table_size;  

 flush status;

select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue,        c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey   and o_orderdate >= '1993-11-01'   and o_orderdate < date_add( '1993-11-01' ,interval '3' month)   and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20;

 show status WHERE Variable_name like 'Created_tmp%';
[2 Jul 13:17] Sinisa Milivojevic
HI Oystein,

How are you ???

Thank you for your bug report.

What you reported is a true bug, but it is already reported. Hence, your bug is a duplicate of the following one:

https://bugs.mysql.com/bug.php?id=95713

You can not read it, since it is a security bug.
[3 Jul 1:51] Øystein Grøvlen
Hi Sinisa,

It is not clear to me how this can be a security issue, but I guess I have to trust you on that.
[3 Jul 12:50] Sinisa Milivojevic
Hi Øystein,

First of all, I got that 'Ø' correct this time, thanks to copy / paste ...

It is a security issue due to some issue with memory management. I can not write anything else ...