I receive the message: The table ‘/tmp/#sql1_8_4’ is full
when I try executing the following query
CREATE TABLE temp SELECT DATE_FORMAT(createdDate, "%Y-%m-01") `date`, userId, COUNT(id) AS `jobsCount` FROM backup_jobs jobs GROUP BY DATE_FORMAT(createdDate, "%Y-%m-01"), userId;
backup_jobs table is more than 19Gb, but I have more than 300Gb available on the partition where
ibdata1 is located
set tmp_table_size = 1024 * 1024 * 32; set max_heap_table_size = 1024 * 1024 * 32;
And in mysql config:
innodb_data_file_path = ibdata1:100M:autoextend
I run mysql on docker, but the volume is mounted (I saw that the max image size could be a reason in case the mysql files ae stored in the image)
I can CREATE OR SELECT the table without the GROUP BY
But I can’t CREATE or SELECT with GROUP BY
Thanks for your help
try turn tempfile_use_mmap off and use the disk to create the temp table or try increasing temptable_max_ram variable max ram
Defines whether the TempTable storage engine allocates space for internal in-memory temporary tables as memory-mapped temporary files when the amount of memory occupied by the TempTable storage engine exceeds the limit defined by the temptable_max_ram variable. When temptable_use_mmap is disabled, the TempTable storage engine uses InnoDB on-disk internal temporary tables instead.
check your temporary disk allocation rules. make sure the datafile can grow to the adequate size for the temp file. confirm you have adequate harddrive partition space where your datafile resides
SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
see https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html for setup your tablespace to datafile configurations. Make sure autoextend feature is on
SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME = 'innodb_temporary'\G
this query will show how much data_free space if available
check your mysql configuration variable and use the max key word
see Implicit temporary table variables (https://www.percona.com/blog/2019/07/17/mysql-disk-space-exhaustion-for-implicit-temporary-tables/)
tmp_table_size max_heap_table_size set tmp_table_size = 1024 * 1024 * 32; set max_heap_table_size = 1024 * 1024 * 32;
confirm your recordsize with the allocated memory amounts. confirm your groupby and joins did not create a cross product cardesian result set.
restart the server
quote: As any other InnoDB table in the database, the temporary tables have their own tablespace file. The new file is in the data directory together with the general tablespace, with the name ibtmp1. It stores all the tmp tables. A tablespace file cannot be shrunk, and it grows constantly as long as you don’t run a manual OPTIMIZE TABLE. The ibtmp1 makes no difference, as you cannot use OPTIMIZE. The only way to shrink ibtmp1 size to zero is to restart the server.
In memory temp table exhaustion results in usage of the disk file
Defines the maximum amount of memory (in bytes) the TempTable storage engine is permitted to allocate from memory-mapped temporary files before it starts storing data to InnoDB internal temporary tables on disk. A setting of 0 disables allocation of memory from memory-mapped temporary files.
Answered By – Golden Lion