Hi, I noticed that the docker mysql volume in /var/lib/docker/volumes/owncloud_mysql/ got very very large (18GB) while the data folder is only 3GB.
Why is that? Is there a way to clean the tables or to configure something?
Thank you
Regards
Hi, I noticed that the docker mysql volume in /var/lib/docker/volumes/owncloud_mysql/ got very very large (18GB) while the data folder is only 3GB.
Why is that? Is there a way to clean the tables or to configure something?
Thank you
Regards
Hi, most likely a log file that is getting large. Can you check the *.log
and *.err
files in your db volume?
Here’s the content con the volume in /var/lib/docker/volumes/owncloud_mysql/_data
.
It seems like there are huge tables, not log files:
-rw-r----- 1 ubuntu ubuntu 972 Mar 13 2021 ib_buffer_pool
-rw-rw---- 1 ubuntu ubuntu 0 Mar 13 2021 multi-master.info
-rw-rw---- 1 ubuntu ubuntu 4 Apr 22 2021 38a8a12d47c6.pid
-rw-rw---- 1 ubuntu ubuntu 4 Aug 17 23:34 1300baa02c1d.pid
-rw-rw---- 1 ubuntu ubuntu 4 Aug 21 13:08 bf02ea4189cb.pid
-rw-rw---- 1 ubuntu ubuntu 52 Sep 1 07:00 aria_log_control
-rw-rw---- 1 ubuntu ubuntu 12M Aug 21 13:08 ibtmp1
-rw-rw---- 1 ubuntu ubuntu 19M Mar 13 2021 mysql-bin.000002
-rw-rw---- 1 ubuntu ubuntu 24K Apr 22 2021 38a8a12d47c6.err
-rw-rw---- 1 ubuntu ubuntu 24K Sep 1 07:00 aria_log.00000001
-rw-rw---- 1 ubuntu ubuntu 28K Mar 13 2021 mysql-bin.000001
-rw-rw---- 1 ubuntu ubuntu 31M May 20 19:05 mysql-bin.000039
-rw-rw---- 1 ubuntu ubuntu 42M Dec 5 2021 mysql-bin.000024
-rw-rw---- 1 ubuntu ubuntu 47M Aug 17 23:33 mysql-bin.000045
-rw-rw---- 1 ubuntu ubuntu 64M Sep 4 20:20 ib_logfile0
-rw-rw---- 1 ubuntu ubuntu 64M Sep 4 20:20 ib_logfile1
-rw-rw---- 1 ubuntu ubuntu 74M Sep 4 20:20 ibdata1
-rw-rw---- 1 ubuntu ubuntu 84K Aug 21 13:04 1300baa02c1d.err
-rw-rw---- 1 ubuntu ubuntu 893 Aug 21 13:08 mysql-bin.index
-rw-rw---- 1 ubuntu ubuntu 93K Apr 5 2021 mysql-bin.000006
-rw-rw---- 1 ubuntu ubuntu 93K Apr 9 2021 mysql-bin.000008
-rw-rw---- 1 ubuntu ubuntu 96M Aug 16 13:15 mysql-bin.000044
-rw-rw---- 1 ubuntu ubuntu 1.1G Aug 13 14:20 mysql-bin.000043
-rw-rw---- 1 ubuntu ubuntu 1.1G Jan 5 2022 mysql-bin.000026
-rw-rw---- 1 ubuntu ubuntu 1.1G Jul 5 2021 mysql-bin.000013
-rw-rw---- 1 ubuntu ubuntu 1.1G Mar 25 10:35 mysql-bin.000031
-rw-rw---- 1 ubuntu ubuntu 1.1M Mar 25 18:29 mysql-bin.000033
-rw-rw---- 1 ubuntu ubuntu 100M Jan 8 2022 mysql-bin.000027
-rw-rw---- 1 ubuntu ubuntu 111M Mar 16 2021 mysql-bin.000003
-rw-rw---- 1 ubuntu ubuntu 116M Aug 21 12:59 mysql-bin.000046
-rw-rw---- 1 ubuntu ubuntu 141K May 19 20:29 mysql-bin.000038
-rw-rw---- 1 ubuntu ubuntu 142M Apr 9 2021 mysql-bin.000007
-rw-rw---- 1 ubuntu ubuntu 168M Jul 10 2021 mysql-bin.000014
-rw-rw---- 1 ubuntu ubuntu 2.7K Aug 21 13:08 bf02ea4189cb.err
-rw-rw---- 1 ubuntu ubuntu 206M Aug 7 2021 mysql-bin.000017
-rw-rw---- 1 ubuntu ubuntu 240M Jan 16 2022 mysql-bin.000028
-rw-rw---- 1 ubuntu ubuntu 252M Apr 5 2021 mysql-bin.000005
-rw-rw---- 1 ubuntu ubuntu 255K Dec 5 2021 mysql-bin.000025
-rw-rw---- 1 ubuntu ubuntu 336M Jul 20 2021 mysql-bin.000015
-rw-rw---- 1 ubuntu ubuntu 354M Dec 3 2021 mysql-bin.000023
-rw-rw---- 1 ubuntu ubuntu 363M May 31 23:13 mysql-bin.000040
-rw-rw---- 1 ubuntu ubuntu 379M Aug 1 2021 mysql-bin.000016
-rw-rw---- 1 ubuntu ubuntu 388M Jan 28 2022 mysql-bin.000029
-rw-rw---- 1 ubuntu ubuntu 388M Mar 28 2021 mysql-bin.000004
-rw-rw---- 1 ubuntu ubuntu 393M May 5 2021 mysql-bin.000010
-rw-rw---- 1 ubuntu ubuntu 394K May 19 20:22 mysql-bin.000037
-rw-rw---- 1 ubuntu ubuntu 423M Jun 3 2021 mysql-bin.000012
-rw-rw---- 1 ubuntu ubuntu 425M Apr 22 2021 mysql-bin.000009
-rw-rw---- 1 ubuntu ubuntu 474M Sep 4 20:20 mysql-bin.000047
-rw-rw---- 1 ubuntu ubuntu 484M May 19 20:04 mysql-bin.000036
-rw-rw---- 1 ubuntu ubuntu 532K Sep 4 2021 mysql-bin.000019
-rw-rw---- 1 ubuntu ubuntu 546M May 21 2021 mysql-bin.000011
-rw-rw---- 1 ubuntu ubuntu 558M May 4 22:45 mysql-bin.000035
-rw-rw---- 1 ubuntu ubuntu 566M Jul 12 23:53 mysql-bin.000042
-rw-rw---- 1 ubuntu ubuntu 744M Apr 17 17:52 mysql-bin.000034
-rw-rw---- 1 ubuntu ubuntu 776M Nov 22 2021 mysql-bin.000022
-rw-rw---- 1 ubuntu ubuntu 784M Sep 28 2021 mysql-bin.000020
-rw-rw---- 1 ubuntu ubuntu 796M Jun 25 12:43 mysql-bin.000041
-rw-rw---- 1 ubuntu ubuntu 809M Feb 21 2022 mysql-bin.000030
-rw-rw---- 1 ubuntu ubuntu 9.6M Mar 25 17:40 mysql-bin.000032
-rw-rw---- 1 ubuntu ubuntu 904M Sep 4 2021 mysql-bin.000018
-rw-rw---- 1 ubuntu ubuntu 1005M Oct 29 2021 mysql-bin.000021
drwx------ 2 ubuntu ubuntu 4.0K Aug 21 13:08 owncloud/
drwx------ 2 ubuntu ubuntu 4.0K Mar 13 2021 mysql/
drwx------ 2 ubuntu ubuntu 4.0K Mar 13 2021 performance_schema/
drwx-----x 3 root root 4.0K Mar 13 2021 ../
drwxr-xr-x 5 ubuntu ubuntu 4.0K Aug 21 13:08 ./
total 18G
The bin
files are some kind of log files looks like the binary log is enabled. Please don’t just delete these files from the file system, as this can damage your database.
Do you use docker-compose or something else? Can you show us what mysql/mariadb image you use and the container configuration/env vars? Normally, the binary log is not enabled by default.
@rkaussow i’ve got the same issue with bin logs over 60gb.
you’re totally right, disabling binary logs helps to solve it.
@gygabyte017 read the manual for mysql/maria (depends on what are you using). also there’s a command to clear them after disabling.
if you’re using docker-compose, you can simply add
- MARIADB_LOG_BIN=0
as an environment option of db service into your yaml file and restart containers.
Glad it helped. If you still want to use the binlog there are also options to configure a retention policy.
if you’re using docker-compose , you can simply add
That highly depends on the used container. Do you use the official one from https://hub.docker.com/_/mariadb? Or which one is used in your compose file?
Looks like you use this one https://github.com/dockhippie/mariadb… and this is IMO a misconfiguration and badly chosen default. I would recommend to stick to official containers whenever possible and only rely on third-party containers for a good reason.
The official mariadb container does not enable the bin log by default because it is only useful in some environments and otherwise wastes a lot of memory.
Hi, yes i’m using docker compose and here’s the yaml:
version: '2.1'
volumes:
files:
driver: local
mysql:
driver: local
backup:
driver: local
redis:
driver: local
services:
owncloud:
image: owncloud/server:${OWNCLOUD_VERSION}
restart: always
ports:
- ${HTTP_PORT}:8080
depends_on:
- db
- redis
environment:
- OWNCLOUD_DOMAIN=${OWNCLOUD_DOMAIN}
- OWNCLOUD_DB_TYPE=mysql
- OWNCLOUD_DB_NAME=owncloud
- OWNCLOUD_DB_USERNAME=owncloud
- OWNCLOUD_DB_PASSWORD=owncloud
- OWNCLOUD_DB_HOST=db
- OWNCLOUD_ADMIN_USERNAME=${ADMIN_USERNAME}
- OWNCLOUD_ADMIN_PASSWORD=${ADMIN_PASSWORD}
- OWNCLOUD_MYSQL_UTF8MB4=true
- OWNCLOUD_REDIS_ENABLED=true
- OWNCLOUD_REDIS_HOST=redis
healthcheck:
test: ["CMD", "/usr/bin/healthcheck"]
interval: 30s
timeout: 10s
retries: 5
volumes:
- files:/mnt/data
db:
image: webhippie/mariadb:latest
restart: always
environment:
- MARIADB_ROOT_PASSWORD=owncloud
- MARIADB_USERNAME=owncloud
- MARIADB_PASSWORD=owncloud
- MARIADB_DATABASE=owncloud
- MARIADB_MAX_ALLOWED_PACKET=128M
- MARIADB_INNODB_LOG_FILE_SIZE=64M
healthcheck:
test: ["CMD", "/usr/bin/healthcheck"]
interval: 30s
timeout: 10s
retries: 5
volumes:
- mysql:/var/lib/mysql
- backup:/var/lib/backup
redis:
image: webhippie/redis:latest
restart: always
environment:
- REDIS_DATABASES=1
healthcheck:
test: ["CMD", "/usr/bin/healthcheck"]
interval: 30s
timeout: 10s
retries: 5
volumes:
- redis:/var/lib/redis
I don’t remember that I changed something besides the default config, however is there something I should change here?
Thanks again!
I see that webhippie images are not the correct ones (were they in the past?!) so I would say I have to change the yaml with the official one. Are there any risk of compromising something?
Yes, if you would like to change the DB container there is a risk to break something, so you should create a proper DB backup first (and verify that a restore works).
At the end, it’s up to you which container to choose. If you want to stick to the current one to avoid issues, @Heyon as posted already the correct solution Docker mysql volume very large - #5 by Heyon to get rid of the binary log.
What is the command to clear them after disabling?
Tried with PURGE BINARY LOGS BEFORE ‘2023-01-21’; but I get
Access denied; you need (at least one of) the SUPER privilege(s) for this operation
I’m using default MARIADB_USERNAME and MARIADB_PASSWORD.
Thanks
Access denied; you need (at least one of) the SUPER privilege(s) for this operation
As the error message indicates already, the SUPER privilege is required which is by default only granted to the MariaDB root user.
Thanks, I thought that ‘owncloud’ was the root user.
Anyway for those who may need it, the command to purge the bin logs is RESET MASTER; but it must be executed before setting MARIADB_LOG_BIN=0, otherwise it won’t have effect.
Thanks everyone,
Regards
Hi gygabyte017,
In addition to the steps you performed I’d recommend to configure the activity_expire_days
variable in your config.php
. From my expierience this table is growing very fast.
see core/config.apps.sample.php at f89cd10d23b0ee18b271d4b3e12ea8a676c971fb · owncloud/core · GitHub
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.