4

I have not had this issue with older versions of MySQL including up to 8.0.21 which I run in production within AWS RDS. I have a query that gets run only once a year. Here is the relevant code:

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
);

During the insert select I get the following error:

ERROR 1114 (HY000): The table '/tmp/#sql4984_9_3' is full

Table medicare_fee_history has 16042724 rows. To reproduce this, the dataset can be found at https://drive.google.com/file/d/1p7Yf7wsCnBXl7UaxeFC1AP0youl-KCdZ/view?usp=sharing

The query generally returns 10823 rows. If you eliminate avg(facility) and avg(non_facility) it seems to work. There is plenty of space in /tmp. 92% of 100G is free. I set tmp_table_size to max. Here are the current server settings:

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)

Any ideas on how to work around this?

Bala Sambandam
  • 83
  • 5
  • `index (hcpcs, modifier, mac, locality)` seems better for `medicare_fee_history` for aggregation functions/grouping. – Joop Eggen Feb 14 '21 at 00:59
  • Does this answer your question? [1114 (HY000): The table is full](https://stackoverflow.com/questions/730579/1114-hy000-the-table-is-full) – nbk Feb 14 '21 at 01:37
  • Not sure what you mean. Are you talking about just reordering the index for other potential queries? They are currently ordered according to the likelihood that they might be used in the future. Currently all columns are being used for queries. – Bala Sambandam Feb 14 '21 at 01:38
  • any progress update? – harryghgim Jun 29 '22 at 07:27
  • @harryghgim I created medicare_fee_first with facility and non_facility not set and then looped through and set the columns separately in some PHP code. The code only runs once a year and I needed to move on. – Bala Sambandam Jun 30 '22 at 13:58

2 Answers2

0

I think the relevant setting for you to adjust is temptable_max_mmap

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

Example 1

You know that your temporary tables grow to a cumulative size of 20 GiB. You want to set in-memory temporary tables to 2 GiB and to grow to a maximum of 20 GiB on disk.

Set temptable_max_ram to 2,147,483,648 and temptable_max_mmap to 21,474,836,480. These values are in bytes.

TommyN
  • 2,252
  • 22
  • 19
-1

Work for me from TommyN

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

Example 1

You know that your temporary tables grow to a cumulative size of 20 GiB. You want to set in-memory temporary tables to 2 GiB and to grow to a maximum of 20 GiB on disk.

Set temptable_max_ram to 2,147,483,648 and temptable_max_mmap to 21,474,836,480. These values are in bytes.

9ezi
  • 1
  • 2