Docker mysql volume very large

Server
#1

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

#2

Hi, most likely a log file that is getting large. Can you check the *.log and *.err files in your db volume?

2 Likes
#3

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
#4

The bin files are some kind of log files :slight_smile: 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.

1 Like
#5

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

#6

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 Docker Hub? Or which one is used in your compose file?

#7

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.

#8

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!

#9

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?

#10

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.

#11

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

#12

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.

#13

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

1 Like
#14

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

#15

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.