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?

Rick James · Answer

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:

Create tmp table and populate it;
"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".

MariaDB tmpdir & table copy operations

One Answer

Add your own answers!

Ask a Question