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%';
