我试图在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
我检查了这个线程，执行sql文件时出错(error 1114 mysql table is full)
然而，它没有帮助。以下是我从上面的链接中查看的列表。
- 我有足够的磁盘空间。
- 我增加了max_heap_table_size和tmp_table_size的大小。 我重新启动MySQL服务器。
- innodb_data_file_path设置autoextend。所以，我希望它能自动扩展。
我还检查了我的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 |
+-----------------------+------------------------+
如有任何建议，将不胜感激。谢谢你的阅读。
事实证明，我需要改变temptable_max_ram(默认为1 GB)，因为MySQL使用TempTable作为默认值。如果我将internal_tmp_mem_storage_engine设置为MEMORY(来自TempTable)，那么更改tmp_table_size, max_heap_table_size是可以的。