Description:
As InnoDB is an ACID transaction engine, which contains too many features (and thus overhead) not needed temp table scenario. Starting from 5.7, there has been continuous effort to simplify/optimize InnoDB temptable table, to totally replace MyISAM temp table.
I think these 2 articles has listed most of these work:
https://mysqlserverteam.com/mysql-5-7-innodb-intrinsic-tables/
http://mysqlserverteam.com/whats-new-in-mysql-5-7-so-far/#innodb_temp_table_perf
For the buffer pool management part, there are also some specific handle for temptable, below is the specific logic I found if not all of them,
1. the checkpoint don't care about dirty temp tablespace blocks, as temptable don't need to be crash safe (check buf_pool_get_oldest_modification_approx())
2. block->lock latch is not used to sync access to page frame temp tablespaces (buf_page_get_gen())
3. mtr can hold beyond trasanction life time (check class last_ops_cur_t())
4. AHI is explictly disabled for intrinsic temp table (index->disable_ahi = true;)
5. the page cleaner threads are enabled even for read only mode (check srv_start())
But the buf blocks of InnoDB temp tablespace are still managed together with normal ones, they are managed using the same LRU list and flush list. The specific handling loic sometimes could hurt the whole system, and I have reported several related bugs:
1. bug #98974, caused by the specific checkpoint logic
2. bug #96236, caused by the lazy mtr_comit()
3. bug #98869, caused by temp tablespace truncation at disconnecting, and full LRU list scanning
There could be some other issues not spotted or not aware for me ...
Based on current situation and existed issues, I think it's better to manage temp tablespace block separately from normal tablespace blocks, such as
1. maintain a dedicated LRU_temp and flush_list_tmp
2. employing more aggressive LRU/flushing logic for LRU_temp, flush_list_tmp
3. put a limit on the number buf blocks can be used by temp tablespace in total
4. etc ...
==============================
Another little suggestion about 8.0:
As these InnoDB temptable issues exists, and the quick workaround when bitten them is setting internal_tmp_disk_storage_engine = MyISAM, but 8.0.16 has removed this sys var (checking https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_internal_tmp_d...), so there is no easy workaround after 8.0.16.
Consider the optimization of this feature request may be complex (if adopted hopefully), and need time to be stable, a quick hot fix may be adding internal_tmp_disk_storage_engine back, and give option for choose.
At least for our customers, set internal_tmp_disk_storage_engine = MyISAM is a best practice when encountering InnoDB temp table issues.
How to repeat:
Read the desc.
