MySQL 8.0.23 临时表持续增长

Question

我以前使用的MySQL版本，包括在AWS RDS中运行的8.0.21版本，从未遇到过这个问题。我有一个仅在每年运行一次的查询。以下是相关代码：

create table medicare_fee_history (
        year int unsigned,
        mac int unsigned,
        locality int unsigned,
        hcpcs varchar(10),
        modifier varchar(10),
        index (mac, locality, hcpcs, modifier),
        non_facility decimal(17, 4),
        facility decimal(17, 4)
) engine = myisam;

load data local infile 'PFALL.csv'
into table medicare_fee_history
fields terminated by ',' enclosed by '"'
(year, mac, locality, hcpcs, modifier, non_facility, facility);

create table medicare_fee_first (
        year int unsigned,
        hcpcs varchar(10),
        modifier varchar(10),
        index (hcpcs, modifier),
        facility decimal(17, 4),
        non_facility decimal(17, 4)
) engine = myisam;

insert into medicare_fee_first (year, hcpcs, modifier, facility, non_facility)
(
        select min(year), hcpcs, modifier, avg(facility), avg(non_facility)
        from medicare_fee_history group by hcpcs, modifier
);

在插入选择期间，我遇到了以下错误：

ERROR 1114 (HY000)：表 '/tmp/#sql4984_9_3' 已满

medicare_fee_history 表有 16042724 行数据。要复现此问题，可以在此处找到数据集。

该查询通常返回 10823 行数据。如果去除 avg(facility) 和 avg(non_facility) ，则似乎可以工作。/tmp 中有足够的空间。100G 的空间中有92% 的空闲空间。我将 tmp_table_size 设置为最大值。以下是当前服务器设置：

mysql> show variables like '%tmp%';
+---------------------------------+----------------------+
| Variable_name                   | Value                |
+---------------------------------+----------------------+
| default_tmp_storage_engine      | InnoDB               |
| innodb_tmpdir                   |                      |
| internal_tmp_mem_storage_engine | TempTable            |
| slave_load_tmpdir               | /tmp                 |
| tmp_table_size                  | 18446744073709551615 |
| tmpdir                          | /tmp                 |
+---------------------------------+----------------------+
6 rows in set (0.00 sec)

mysql> show variables like '%temp%';
+-----------------------------+-----------------------+
| Variable_name               | Value                 |
+-----------------------------+-----------------------+
| avoid_temporal_upgrade      | OFF                   |
| innodb_temp_data_file_path  | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | ./#innodb_temp/       |
| show_old_temporals          | OFF                   |
| temptable_max_mmap          | 1073741824            |
| temptable_max_ram           | 1073741824            |
| temptable_use_mmap          | ON                    |
+-----------------------------+-----------------------+
7 rows in set (0.00 sec)

你有任何解决这个问题的想法吗？

- Bala Sambandam

---

对于聚合函数/分组，index（hcpcs，modifier，mac，locality）似乎更适用于medicare_fee_history。 - Joop Eggen

这个回答解决了你的问题吗？[1114（HY000）：表已满]（https://dev59.com/AXRB5IYBdhLWcg3wET1J） - nbk

不确定你的意思。你是指重新排列索引以进行其他潜在查询吗？它们目前按照将来可能使用的可能性进行排序。目前，所有列都用于查询。 - Bala Sambandam

有任何进展更新吗？ - harryghgim

@harryghgim 我在一些 PHP 代码中创建了 medicare_fee_first，其中 facility 和 non_facility 没有设置，然后循环遍历并单独设置列。该代码仅在每年运行一次，我需要继续前进。 - Bala Sambandam

Answer 1

我认为你需要调整的相关设置是temptable_max_mmap

参考：https://docs.amazonaws.cn/en_us/AmazonRDS/latest/AuroraUserGuide/ams3-temptable-behavior.html

例子1

你知道你的临时表累积大小为20 GiB。你想将内存中的临时表设置为2 GiB，并且在磁盘上最多增长到20 GiB。

将temptable_max_ram设置为2,147,483,648，将temptable_max_mmap设置为21,474,836,480。这些值以字节表示。

- TommyN

Answer 2

TommyN给我工作

请查看: https://docs.amazonaws.cn/en_us/AmazonRDS/latest/AuroraUserGuide/ams3-temptable-behavior.html

示例 1

您知道您的临时表将增长到累计大小为20 GiB。您想将内存中的临时表设置为2 GiB，并在磁盘上增长到最大的20 GiB。

将 temptable_max_ram 设置为 2,147,483,648，将 temptable_max_mmap 设置为 21,474,836,480。这些值以字节为单位。

- 9ezi