duplicating a MariaDB table with a small /tmp filesystem-CodePudding

I am duplicating a table with this command:

CREATE TABLE new_table LIKE old_table; 
INSERT INTO new_table SELECT * FROM old_table;

Unfortunately, on my system /tmp is placed on separate filesystem with only 1gb of space. If a large query is executed, that 1gb gets filled by mariadb very quickly, making it impossible to execute large queries. It is a production server so I'd rather leave the file systems as they are and instruct mariadb to make smaller temporary files and delete them on the fly.

How do you instruct mariadb to split large query into multiple temporary files so that /tmp doesn't get jammed with temporary files that cause query termination?

CodePudding user response：

You can always split the INSERT into smaller pieces.

In below example I assume that i is the primary key:

WITH RECURSIVE cte1 AS (
   SELECT 1 as s, 9999 as e
   UNION ALL
   SELECT e 1, e 9999
   FROM cte1
   WHERE e<=(select count(*) from old_table)
)
select CONCAT('INSERT INTO new_table SELECT * FROM old_table WHERE i BETWEEN ',s,' AND ',e) from cte1 limit 10;

This script produces several insert statement that you can run 1 after the other...

output:

 INSERT INTO new_table SELECT * FROM old_table WHERE i BETWEEN 1 AND 9999             |
 INSERT INTO new_table SELECT * FROM old_table WHERE i BETWEEN 10000 AND 19998        |
 INSERT INTO new_table SELECT * FROM old_table WHERE i BETWEEN 19999 AND 29997

You are, of course, free to change the 9999 to any other number.

CodePudding user response：

It may be related to the table content. As explained in the doc,

Some query conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead

If you have the privileges, you can try to set another disk location using

Temporary files are created in the directory defined by the tmpdir variable

Other than that, the doc is pretty clear unfortunately :

In some cases, the server creates internal temporary tables while processing statements. Users have no direct control over when this occurs.