TransWikia.com

MariaDB tmpdir & table copy operations

Database Administrators Asked by user120053 on November 11, 2021

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?

One 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:

  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".

Answered by Rick James on November 11, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP