MySQL 1114 错误，表/tmp/#sql 已满

Question

我正在尝试在 MySQL 服务器中运行 TPC-H 查询。目前，我使用的是8.0.23版本(当我以某种方式使用8.0.19时，没有出现该错误)。TPC-H的基准大小为10GB。

当我使用 8 个线程同时提交多个查询时，出现此错误(1114 错误，表/tmp/#sql~~~ 已满)。有可能使用不同的线程同时提交相同或相似的查询。

但是，当我在 MySQL shell 中提交单个查询时，它会返回一个没有错误的结果。

这是查询(还有其他查询引发相同的错误。)

with year_total as ( select c_customer_id customer_id , c_first_name customer_first_name , c_last_name customer_last_name , c_preferred_cust_flag customer_preferred_cust_flag , c_birth_country customer_birth_country , c_login customer_login , c_email_address customer_email_address , d_year dyear , sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total , 's' sale_type from tpcds1.customer , tpcds1.store_sales , tpcds1.date_dim where c_customer_sk = ss_customer_sk and ss_sold_date_sk = d_date_sk group by c_customer_id , c_first_name , c_last_name , c_preferred_cust_flag , c_birth_country , c_login , c_email_address , d_year union all select c_customer_id customer_id , c_first_name customer_first_name , c_last_name customer_last_name , c_preferred_cust_flag customer_preferred_cust_flag , c_birth_country customer_birth_country , c_login customer_login , c_email_address customer_email_address , d_year dyear , sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total , 'c' sale_type from tpcds1.customer , tpcds1.catalog_sales , tpcds1.date_dim where c_customer_sk = cs_bill_customer_sk and cs_sold_date_sk = d_date_sk group by c_customer_id , c_first_name , c_last_name , c_preferred_cust_flag , c_birth_country , c_login , c_email_address , d_year union all select c_customer_id customer_id , c_first_name customer_first_name , c_last_name customer_last_name , c_preferred_cust_flag customer_preferred_cust_flag , c_birth_country customer_birth_country , c_login customer_login , c_email_address customer_email_address , d_year dyear , sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) ) year_total , 'w' sale_type from tpcds1.customer , tpcds1.web_sales , tpcds1.date_dim where c_customer_sk = ws_bill_customer_sk and ws_sold_date_sk = d_date_sk group by c_customer_id , c_first_name , c_last_name , c_preferred_cust_flag , c_birth_country , c_login , c_email_address , d_year ) select t_s_secyear.customer_id , t_s_secyear.customer_first_name , t_s_secyear.customer_last_name , t_s_secyear.customer_login from year_total t_s_firstyear , year_total t_s_secyear , year_total t_c_firstyear , year_total t_c_secyear , year_total t_w_firstyear , year_total t_w_secyear where t_s_secyear.customer_id = t_s_firstyear.customer_id and t_s_firstyear.customer_id = t_c_secyear.customer_id and t_s_firstyear.customer_id = t_c_firstyear.customer_id and t_s_firstyear.customer_id = t_w_firstyear.customer_id and t_s_firstyear.customer_id = t_w_secyear.customer_id and t_s_firstyear.sale_type = 's' and t_c_firstyear.sale_type = 'c' and t_w_firstyear.sale_type = 'w' and t_s_secyear.sale_type = 's' and t_c_secyear.sale_type = 'c' and t_w_secyear.sale_type = 'w' and t_s_firstyear.dyear = 1999 and t_s_secyear.dyear = 1999+1 and t_c_firstyear.dyear = 1999 and t_c_secyear.dyear = 1999+1 and t_w_firstyear.dyear = 1999 and t_w_secyear.dyear = 1999+1 and t_s_firstyear.year_total > 0 and t_c_firstyear.year_total > 0 and t_w_firstyear.year_total > 0 and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end > case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end order by t_s_secyear.customer_id , t_s_secyear.customer_first_name , t_s_secyear.customer_last_name , t_s_secyear.customer_login limit 100

我检查了这个帖子， error while executing sql file (ERROR 1114 mysql table is full)

但是，这没有帮助。这是我从上面的链接查看的列表。

1. 我有足够的磁盘空间。
2. 我增加了 max_heap_table_size 和 tmp_table_size 的大小，如下所示。
3. 我重新启动了 MySQL 服务器。
4. innodb_data_file_path 设置为自动扩展。因此，我希望它能够自动扩展。

我还检查了我的 tmpdir 设置是否正确并且有足够的磁盘空间。

这是我的 MySQL 配置。

innodb_buffer_pool_size=16GB
select @@innodb_buffer_pool_size; -> 17179869184

select @@max_heap_table_size; -> 2147483648
select @@tmp_table_size; -> 2147483648

+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
+-----------------------+------------------------+

如有任何建议，我们将不胜感激。感谢您的阅读。

Answer 1

事实证明，我需要更改 temptable_max_ram (默认为 1 GB)，因为 MySQL 默认使用 TempTable。如果我将 internal_tmp_mem_storage_engine 设置为 MEMORY(来自 TempTable)，则更改 tmp_table_size、max_heap_table_size 就可以了。