При переносе mysql данных на отдельный диск почему всё равно используется основной при alter table?

Question

[Виктор]

Есть сервер с базой данных MySQL на 1 диске система на другом директория /var/lib/mysql
почему используется системный диск когда делается alter table на таблице размер в 18 Gb?
вот вывод команд df -h и du -had1 -x / выполненных в середине процесса alter table

devtmpfs        4.0M     0  4.0M   0% /dev
tmpfs            48G     0   48G   0% /dev/shm
tmpfs            19G  8.9M   19G   1% /run
/dev/sdb4        50G   13G   37G  27% /
/dev/sda        1.2T  1.1T  162G  87% /var/lib/mysql
/dev/sdb3       507M  198M  310M  40% /boot
/dev/sdb2       200M  7.1M  193M   4% /boot/efi
tmpfs           9.5G     0  9.5G   0% /run/user/0
tmpfs           9.5G     0  9.5G   0% /run/user/1000

100K	/tmp
24M	/etc
100K	/root
384M	/var
1.9G	/usr
0	/bin
0	/sbin
0	/lib
0	/lib64
0	/afs
168K	/home
0	/media
316M	/mnt
8.0K	/opt
104K	/srv
0	/.autorelabel
2.6G	/

Пример запроса

ALTER TABLE `test` 
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `date`) USING BTREE;

Comments

[Rsa97]

По каким именно данным вы делаете вывод, что mysql использует корневой том?

[Alexey Dmitriev]

Alter table наверняка предполагает создание временных данных, вам нужно уточнить, какой у вас движок и где они у вас хранятся

[Виктор]

Alexey Dmitriev, MySQL 8 использует innodb, всё как обычно лежит в /var/lib/mysql

[Виктор]

Rsa97, Пример запроса

ALTER TABLE `test` 
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `date`) USING BTREE;

[Виктор]

Alexey Dmitriev, да временные создаются, но они в /var/lib/mysql создаются, почему корневой вообще затрагивает?)

[Rsa97]

Виктор, А при чём тут текст запроса? Что показывает lsof /dev/sdb4 | grep mysql | grep -v /usr/lib во время выполнения запроса?

[Виктор]

Rsa97, вывод команды

mysqld    31740     mysql  rtd    DIR   8,20        255      128 /
mysqld    31740     mysql  txt    REG   8,20   56875312   191437 /usr/sbin/mysqld
mysqld    31740     mysql    1w   REG   8,20      38302   191449 /var/log/mysqld.log
mysqld    31740     mysql    2w   REG   8,20      38302   191449 /var/log/mysqld.log
mysqld    31740     mysql    6u   REG   8,20          0  8479701 /tmp/#8479701 (deleted)
mysqld    31740     mysql    7u   REG   8,20          0  8479702 /tmp/#8479702 (deleted)
mysqld    31740     mysql    8u   REG   8,20          0  8479703 /tmp/#8479703 (deleted)
mysqld    31740     mysql  139u   REG   8,20          0  8479704 /tmp/#8479704 (deleted)
mysqld    31740     mysql  432u   REG   8,20          0  8479707 /tmp/#8479707 (deleted)
mysqld    31740     mysql  473u   REG   8,20          0  8428110 /tmp/#8428110 (deleted)
mysqld    31740     mysql  475u   REG   8,20          0  8479705 /tmp/#8479705 (deleted)
mysqld    31740     mysql  485u   REG   8,20          0  8479706 /tmp/#8479706 (deleted)
mysqld    31740     mysql  586u   REG   8,20 1152196608  8465870 /tmp/#8465870 (deleted)
mysqld    31740     mysql  587u   REG   8,20  347324416  8479708 /tmp/#8479708 (deleted)

[Виктор]

Rsa97, это как получается создаёт файл и с разу удаляет? а вывод в df не корректный из-за какой то кэш?

[Rsa97]

Виктор, Если удалить файл, у которого есть открытые хэндлы, то реально он удалится только после закрытия последнего хэндла.
Соответственно процесс открывает файл в /tmp, даёт команду на его удаление, работает с файлом. Как только процесс закроет файл, он автоматически удалится.
Ну а использование в линуксе /tmp для любых временных файлов - общепринятая практика.

[Виктор]

Rsa97, Если я правильно понял то системную директорию временных файлов (/tmp) mysql использует в основном для сортировки индекса при online DDL, да и вообще при любых запросов с сортировкой на диске. Судя по документации при ALTER если таблица достаточно большая то лучше через innodb_tmpdir указать другой диск