MariaDB tmpdir & table copy operations

Question

I have a Linux server & MariaDB v10.0.33, where tmpdir is tmpfs (ram), for fast temporary table access; tmpfs is obviously limited in size.

MariaDB seems to use the same tmpdir location for alter table operations (table copy), that require a table copy; these can require large amounts of space if tables are many Gb.

Old MySQL versions configure 'table copy' and 'temp table' separately; Table copy written to tablespace & temporary tables written to tmpdir.

I am aware I can set 'innodb_tmpdir' per command. Can MariaDB configured so that table copy & temporary tables use different locations?

Answer 1

I think it is a bad idea to rob space in RAM for things other than innodb_buffer_pool_size.

Furthermore, your example is flawed. An ALTER that needs to copy works this way:

1. Create tmp table and populate it;
2. "Move" the table into where the original table is.

Step 2 is the issue:

• If the tmp and the original are in the same filesystem, these are fast operations involving the directory.
• If they are in different filesystems (and tmpfs is definitely different), the "move" is really a more-costly "copy".