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