Docker Community Forums

Share and learn in the Docker community.

Mysql with volume, alter table eats up space on the host

Hey everyone

I have a mysql docker setup. The /var/lib/mysql dir in the container is put on a volume. It is set up as a bind volume, so the volume is not handled by docker, but it is a directory on the host.

The host itself has 20GB disk space, the volume is on a different device with 50GB total space. The host has around 3GB free space, while the database is around 25GB with one particularly big table. (Should be around 15GB)

After deleting a few records, I wanted to run an “OPTIMIZE TABLE” mysql query on the big table.

Normally mysql starts to rebuild the table in a temporary disk table in the same directory, so it’s necessary to have as much free space on the device which the database is on, as the table.

But short after mysql starts to rebuild the table (the tmp table is only 0.5GB large) when the host runs out of space. When the query fails the space is freed up again.

What’s happenening here?
I could not figure out what’s eating up the space. If mysql did it (log something), it would probably not free up the space. The /tmp and /var/tmp dirs were empty.

Is this some kind of write buffering in docker?

Are you sure the temporary disk table is written into the same folder your volume is mounted into?
If not, the temporary disk table will be written into the container, which will result in data being written in the containers copy-on-write folder in /var/lib/docker/containers/.

Make sure to bind a volume for the location of the temporary disk table as well.

Hey meyay,

Thank you for your response.

I checked again the tmp dirs

Variable_name Value
basedir /usr/
binlog_direct_non_transactional_updates OFF
character_sets_dir /usr/share/mysql/charsets/
datadir /var/lib/mysql/
ignore_db_dirs
innodb_data_home_dir
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 75.000000
innodb_max_dirty_pages_pct_lwm 0.000000
innodb_tmpdir
innodb_undo_directory ./
lc_messages_dir /usr/share/mysql/
plugin_dir /usr/lib/mysql/plugin/
slave_load_tmpdir /tmp
tmpdir /tmp

These are default settings
The innodb_tmpdir is empty, so temporary files should be in the /tmp directory.
But I found there nothing during the alter table

Here is a df + du from the beginning of the alter table from within the mysql container:

overlay 20574780 16915880 2698996 87% /
tmpfs 65536 0 65536 0% /dev
tmpfs 3315768 0 3315768 0% /sys/fs/cgroup
/dev/sda1 20574780 16915880 2698996 87% /etc/hosts
shm 65536 0 65536 0% /dev/shm
/dev/sdb1 51539404 31147720 18173504 64% /var/lib/mysql
tmpfs 3315768 0 3315768 0% /proc/acpi
tmpfs 3315768 0 3315768 0% /sys/firmware

du --max-depth=1 -h
4.6M ./bin
4.0K ./srv
20K ./run
28G ./var
4.0K ./opt
4.0K ./home
0 ./dev
4.0K ./lib64
776K ./etc
4.0K ./media
339M ./usr
4.0K ./boot
4.0K ./mnt
24K ./root
4.0K ./tmp
0 ./sys
3.5M ./sbin
12M ./lib
du: cannot read directory ‘./proc/1/map_files’: Permission denied
du: cannot access ‘./proc/167/task/167/fd/4’: No such file or directory
du: cannot access ‘./proc/167/task/167/fdinfo/4’: No such file or directory
du: cannot access ‘./proc/167/fd/3’: No such file or directory
du: cannot access ‘./proc/167/fdinfo/3’: No such file or directory
0 ./proc
4.0K ./docker-entrypoint-initdb.d
28G .

And here is one near the end:

df; du --max-depth=1 -h .
Filesystem 1K-blocks Used Available Use% Mounted on
overlay 20574780 19596952 17924 100% /
tmpfs 65536 0 65536 0% /dev
tmpfs 3315768 0 3315768 0% /sys/fs/cgroup
/dev/sda1 20574780 19596952 17924 100% /etc/hosts
shm 65536 0 65536 0% /dev/shm
/dev/sdb1 51539404 31856352 17464872 65% /var/lib/mysql
tmpfs 3315768 0 3315768 0% /proc/acpi
tmpfs 3315768 0 3315768 0% /sys/firmware
4.6M ./bin
4.0K ./srv
20K ./run
28G ./var
4.0K ./opt
4.0K ./home
0 ./dev
4.0K ./lib64
776K ./etc
4.0K ./media
339M ./usr
4.0K ./boot
4.0K ./mnt
24K ./root
4.0K ./tmp
0 ./sys
3.5M ./sbin
12M ./lib
du: cannot read directory ‘./proc/1/map_files’: Permission denied
du: cannot access ‘./proc/193/task/193/fd/4’: No such file or directory
du: cannot access ‘./proc/193/task/193/fdinfo/4’: No such file or directory
du: cannot access ‘./proc/193/fd/3’: No such file or directory
du: cannot access ‘./proc/193/fdinfo/3’: No such file or directory
0 ./proc
4.0K ./docker-entrypoint-initdb.d
29G .

/var/lib/mysql is the volume.
As you can see the disk space from /dev/sda1 is gone, while the disk usage of the container is only ~1 GB higher.

I even tried to modify innodb_tmpdir to /tmp to make sure tmp writes go there but /tmp was still empty

What am I missing?

If I compare your before and during values for the overlayfs which is mounted in / of the container, it seems like the data is written there. The number of inodes match your drive /dev/sda1.

Before:

During:

Like I already wrote: it appears like the data is written into the container and therfore is located somewhere on /var/lib/docker/containers. You need to find the folder inside the container it is writting into and map a (bind) volume into that (container) folder to preverent the data beeing written into the container’s cow overlayfs.

If the data is somehow written into the container, shouldn’t I see which folder contains the new data inside the container when I run du --max-depth=1 -h in the root directory of the container?

du reported only 1GB of disk usage increase from within the container. That’s why I don’t understand

I freed up some space by removing some old images. Now I have 7GB free space on the system disk, instead of 3.

I made a few new tests. This time I ran df and du on the host as root:

Before:

df; du --max-depth=1 -h
Filesystem 1K-blocks Used Available Use% Mounted on
udev 3304668 0 3304668 0% /dev
tmpfs 663156 78076 585080 12% /run
/dev/sda1 20574780 12171244 7443632 63% /
tmpfs 3315768 0 3315768 0% /dev/shm
tmpfs 5120 0 5120 0% /run/lock
tmpfs 3315768 0 3315768 0% /sys/fs/cgroup
/dev/sdb1 51539404 31114812 18206412 64% /mnt/data
tmpfs 663152 0 663152 0% /run/user/1006
tmpfs 663152 0 663152 0% /run/user/1001
overlay 20574780 12171244 7443632 63% /var/lib/docker/overlay2/bf7891cc8f2edb30e70132b5bd5664dcd32c0b18b7ad2e2c5811af2cc5c4b8cd/merged
overlay 20574780 12171244 7443632 63% /var/lib/docker/overlay2/6d48d9b051c03b415b759408e3b5a9b8a970e0b0aa90defb175c49c33088f95f/merged
overlay 20574780 12171244 7443632 63% /var/lib/docker/overlay2/6ed48babcdda39505dbd6edefd44120a87744841eeff62cc5a2d75d6d726f15a/merged
shm 65536 0 65536 0% /var/lib/docker/containers/190d18dc200352688592b451632e748537e1cc553c71f6fa8a0343f1c907dda6/mounts/shm
shm 65536 0 65536 0% /var/lib/docker/containers/04ddca320bdeebd5ecb2b1766f08b8b95f5f523df635d8283ac3e1f7fbbdd140/mounts/shm
shm 65536 0 65536 0% /var/lib/docker/containers/a2cdd625c0301dac4a59c8f2e0987c2e74804973b9e1d57109f06de4fd309d1d/mounts/shm
overlay 20574780 12171244 7443632 63% /var/lib/docker/overlay2/a36a5fd93921cd8ee3c9dbb31491889d96051cb2aec0b581a9affa1977056efa/merged
shm 65536 0 65536 0% /var/lib/docker/containers/371d3a94c08f879fcda9dd109f8484d08a859a2258df3461920c25cf4cff70fd/mounts/shm
overlay 20574780 12171244 7443632 63% /var/lib/docker/overlay2/2cd5606f909fb26ae438e260ff53ad678c45cc29d78b961a4fdae7cbaff750d9/merged
shm 65536 4 65532 1% /var/lib/docker/containers/3eb7878de54855a50046551f7c10f17636c6e3275dbeb89f9ff4310a60085dbf/mounts/shm
overlay 20574780 12171244 7443632 63% /var/lib/docker/overlay2/026cd941cb22ab170e6798c423b7b45a5d4ccf5d906b5d743631a68f770c7ade/merged
shm 65536 0 65536 0% /var/lib/docker/containers/b466019f2f5426aae05f9a29dc7e46000ce76b5a4c75ab92687a5b887c0c3b54/mounts/shm
overlay 20574780 12171244 7443632 63% /var/lib/docker/overlay2/678c9425ffdb67eb2c837524cf56cb5d4892f67e0be7b8e4f35ffb30f24f29cc/merged
overlay 20574780 12171244 7443632 63% /var/lib/docker/overlay2/7a12ddeeab7dddca14164d313d972f88a346b650e05edccc9d3e8995a603dd89/merged
shm 65536 0 65536 0% /var/lib/docker/containers/6fd96ebc3ef8b13dab087fd9a7117df492f28b7533dd22b85a06e03b60e49a05/mounts/shm
shm 65536 0 65536 0% /var/lib/docker/containers/956955bba913117c89f83ebaf25c35ec23bc523a283eb9580304e654c9219b67/mounts/shm
overlay 20574780 12171244 7443632 63% /var/lib/docker/overlay2/5fdcad6fe320a1adb3c9daecb6cc9d02ca117b3830c790ca346907464654b890/merged
shm 65536 0 65536 0% /var/lib/docker/containers/6facae5c70a89283e631f80167014142afcc39103e55cb312adc02c2304a492e/mounts/shm
overlay 20574780 12171244 7443632 63% /var/lib/docker/overlay2/2107b4de7efbcd448d800daf246887ffd291820adfdb2ea2192dbafccc84e2b1/merged
shm 65536 0 65536 0% /var/lib/docker/containers/d1386e4af01173d417b4e79c146f291d2f7b7481a6945c78b992fc9775711039/mounts/shm
8.0M ./bin
4.0K ./srv
77M ./run
15G ./var
78M ./opt
12M ./home
0 ./dev
4.0K ./lib64
7.2M ./etc
4.0K ./media
16K ./lost+found
1.6G ./usr
58M ./boot
30G ./mnt
68M ./root
256K ./tmp
0 ./sys
6.4M ./sbin
414M ./lib
du: cannot access ‘./proc/10791/task/10791/fd/4’: No such file or directory
du: cannot access ‘./proc/10791/task/10791/fdinfo/4’: No such file or directory
du: cannot access ‘./proc/10791/fd/3’: No such file or directory
du: cannot access ‘./proc/10791/fdinfo/3’: No such file or directory
du: cannot access ‘./proc/10810’: No such file or directory
du: cannot read directory ‘./proc/11334/task/11334/net’: Invalid argument
du: cannot read directory ‘./proc/11334/net’: Invalid argument
du: cannot access ‘./proc/13344/task/10847’: No such file or directory
du: cannot access ‘./proc/13344/task/10848’: No such file or directory
du: cannot read directory ‘./proc/13801/task/13801/net’: Invalid argument
du: cannot read directory ‘./proc/13801/net’: Invalid argument
du: cannot read directory ‘./proc/21381/task/21381/net’: Invalid argument
du: cannot read directory ‘./proc/21381/net’: Invalid argument
0 ./proc
46G .

Near the end of available disk space on sda:

df; du --max-depth=1 -h
Filesystem 1K-blocks Used Available Use% Mounted on
udev 3304668 0 3304668 0% /dev
tmpfs 663156 78492 584664 12% /run
/dev/sda1 20574780 19185656 429220 98% /
tmpfs 3315768 0 3315768 0% /dev/shm
tmpfs 5120 0 5120 0% /run/lock
tmpfs 3315768 0 3315768 0% /sys/fs/cgroup
/dev/sdb1 51539404 32962176 16359048 67% /mnt/data
tmpfs 663152 0 663152 0% /run/user/1006
tmpfs 663152 0 663152 0% /run/user/1001
overlay 20574780 19185656 429220 98% /var/lib/docker/overlay2/bf7891cc8f2edb30e70132b5bd5664dcd32c0b18b7ad2e2c5811af2cc5c4b8cd/merged
overlay 20574780 19185656 429220 98% /var/lib/docker/overlay2/6d48d9b051c03b415b759408e3b5a9b8a970e0b0aa90defb175c49c33088f95f/merged
overlay 20574780 19185656 429220 98% /var/lib/docker/overlay2/6ed48babcdda39505dbd6edefd44120a87744841eeff62cc5a2d75d6d726f15a/merged
shm 65536 0 65536 0% /var/lib/docker/containers/190d18dc200352688592b451632e748537e1cc553c71f6fa8a0343f1c907dda6/mounts/shm
shm 65536 0 65536 0% /var/lib/docker/containers/04ddca320bdeebd5ecb2b1766f08b8b95f5f523df635d8283ac3e1f7fbbdd140/mounts/shm
shm 65536 0 65536 0% /var/lib/docker/containers/a2cdd625c0301dac4a59c8f2e0987c2e74804973b9e1d57109f06de4fd309d1d/mounts/shm
overlay 20574780 19185656 429220 98% /var/lib/docker/overlay2/a36a5fd93921cd8ee3c9dbb31491889d96051cb2aec0b581a9affa1977056efa/merged
shm 65536 0 65536 0% /var/lib/docker/containers/371d3a94c08f879fcda9dd109f8484d08a859a2258df3461920c25cf4cff70fd/mounts/shm
overlay 20574780 19185656 429220 98% /var/lib/docker/overlay2/2cd5606f909fb26ae438e260ff53ad678c45cc29d78b961a4fdae7cbaff750d9/merged
shm 65536 4 65532 1% /var/lib/docker/containers/3eb7878de54855a50046551f7c10f17636c6e3275dbeb89f9ff4310a60085dbf/mounts/shm
overlay 20574780 19185656 429220 98% /var/lib/docker/overlay2/026cd941cb22ab170e6798c423b7b45a5d4ccf5d906b5d743631a68f770c7ade/merged
shm 65536 0 65536 0% /var/lib/docker/containers/b466019f2f5426aae05f9a29dc7e46000ce76b5a4c75ab92687a5b887c0c3b54/mounts/shm
overlay 20574780 19185656 429220 98% /var/lib/docker/overlay2/678c9425ffdb67eb2c837524cf56cb5d4892f67e0be7b8e4f35ffb30f24f29cc/merged
overlay 20574780 19185656 429220 98% /var/lib/docker/overlay2/7a12ddeeab7dddca14164d313d972f88a346b650e05edccc9d3e8995a603dd89/merged
shm 65536 0 65536 0% /var/lib/docker/containers/6fd96ebc3ef8b13dab087fd9a7117df492f28b7533dd22b85a06e03b60e49a05/mounts/shm
shm 65536 0 65536 0% /var/lib/docker/containers/956955bba913117c89f83ebaf25c35ec23bc523a283eb9580304e654c9219b67/mounts/shm
overlay 20574780 19185656 429220 98% /var/lib/docker/overlay2/5fdcad6fe320a1adb3c9daecb6cc9d02ca117b3830c790ca346907464654b890/merged
shm 65536 0 65536 0% /var/lib/docker/containers/6facae5c70a89283e631f80167014142afcc39103e55cb312adc02c2304a492e/mounts/shm
overlay 20574780 19185656 429220 98% /var/lib/docker/overlay2/2107b4de7efbcd448d800daf246887ffd291820adfdb2ea2192dbafccc84e2b1/merged
shm 65536 0 65536 0% /var/lib/docker/containers/d1386e4af01173d417b4e79c146f291d2f7b7481a6945c78b992fc9775711039/mounts/shm
8.0M ./bin
4.0K ./srv
77M ./run
15G ./var
78M ./opt
12M ./home
0 ./dev
4.0K ./lib64
7.2M ./etc
4.0K ./media
16K ./lost+found
1.6G ./usr
58M ./boot
32G ./mnt
68M ./root
256K ./tmp
0 ./sys
6.4M ./sbin
414M ./lib
du: cannot read directory ‘./proc/11334/task/11334/net’: Invalid argument
du: cannot read directory ‘./proc/11334/net’: Invalid argument
du: cannot read directory ‘./proc/13801/task/13801/net’: Invalid argument
du: cannot read directory ‘./proc/13801/net’: Invalid argument
du: cannot read directory ‘./proc/21381/task/21381/net’: Invalid argument
du: cannot read directory ‘./proc/21381/net’: Invalid argument
du: cannot access ‘./proc/22057/task/22057/fd/4’: No such file or directory
du: cannot access ‘./proc/22057/task/22057/fdinfo/4’: No such file or directory
du: cannot access ‘./proc/22057/fd/3’: No such file or directory
du: cannot access ‘./proc/22057/fdinfo/3’: No such file or directory
du: cannot access ‘./proc/22284’: No such file or directory
du: cannot access ‘./proc/22285’: No such file or directory
du: cannot access ‘./proc/22286’: No such file or directory
du: cannot access ‘./proc/22287’: No such file or directory
du: cannot access ‘./proc/22288’: No such file or directory
du: cannot access ‘./proc/22303’: No such file or directory
du: cannot access ‘./proc/22304’: No such file or directory
du: cannot access ‘./proc/22305’: No such file or directory
du: cannot access ‘./proc/22306’: No such file or directory
du: cannot access ‘./proc/22307’: No such file or directory
du: cannot access ‘./proc/22308’: No such file or directory
du: cannot access ‘./proc/22309’: No such file or directory
du: cannot access ‘./proc/22310’: No such file or directory
0 ./proc
48G .

As you can see du reports higher disk usage by ~2GB. This is the size of the new tmp table being built by mysql. /mnt (sdb is mounted to /mnt/data) disk usage is higher by 2GB.
But I can’t find the missing ~7GB, not even in /var/lib/docker/container

Neither

docker ps --size

nor

docker system df

showed the missing GBs

I tried

dd if=/dev/zero of=/var/lib/mysql/swapfile bs=1K count=6000000

from within the mysql conteiner to imitate the writing process.

But the output of this command did not touch the sda device. It was properly written to the volume.

If I create a new table and insert all records from the old table. The problem does not appear.

so what can go wrong with ALTER TABLE ?

It’s quite interesting

If I run OPTIMIZE TABLE which is translated into ALTER TABLE XXX ENGINE=INNODB in case of an innoDB table. It eats up space on my sda

If I add a new column, which is also ALTER TABLE, it doesn’t

Did you ever find the solution to this problem? I am experiencing the same thing, or something very similar. When creating a new table, my storage device runs out of space and yet du never reports an increase in directory sizes whether it’s run from inside or outside the container.