1

We have a simple master/slave cluster in our environment which is sets up on Ubuntu 20.04 and MariaDB 10.5:

MariaDB [(none)]> SELECT VERSION();
+--------------------------------------------+
| VERSION()                                  |
+--------------------------------------------+
| 10.5.11-MariaDB-1:10.5.11+maria~bionic-log |
+--------------------------------------------+

We set all log directories to /var/lib/mysql including bin log & relay log: /var/lib/mysql/binfiles.

For this directory (/var/lib/mysql), I've created a LVM with 100GB space but after 2 days, I've got an alert which says that this directory is full and I had to add an extra 100GB space to it.

The weird thing is that the files in this directory only used 18GB disk space and I have no idea what happened to the rest:

Output of df -h:

Filesystem                           Size  Used Avail Use% Mounted on
/dev/mapper/vg--data-lv--mysql--log   99G   91G  3.4G  97% /var/log/mysql

Output of du -h --max-depth=1 /var/log/mysql (including the hidden files; which there is none):

13G /var/log/mysql/binfiles
19G /var/log/mysql

Contain of /var/log/mysql (including the hidden files; which there is none):

total 5426300
drwxr-s---  3 mysql adm          4096 Sep 23 00:18 .
drwxrwxr-x 10 root  syslog       4096 Sep 23 00:18 ..
drwxr-sr-x  2 mysql adm          4096 Sep 23 12:21 binfiles
-rw-r-----  1 mysql adm             0 Sep 23 00:00 mariadb-slow.log
-rw-rw----  1 mysql adm      14629234 Sep 23 00:00 mariadb-slow.log.1.gz
-rw-rw----  1 mysql adm    1074177544 Sep 22 21:29 mariadb-slow.log.old
-rw-rw----  1 mysql adm         33699 Sep 22 05:12 mariadb.err
-rw-r-----  1 mysql adm             0 Sep 23 00:00 mysql.log
-rw-rw----  1 mysql adm    4467658081 Sep 23 00:00 mysql.log.1.gz

Contains of /etc/mysql/my.cnf:

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
# port = 3306
socket                  = /run/mysqld/mysqld.sock

[client]
port                    = 3306
socket                  = /var/run/mysqld/mysqld.sock

[isamchk]
key_buffer              = 16M

[mariabackup]
open_files_limit            = 65535

[mysql]
#no-auto-rehash                                                 # faster start of mysql but no tab completion

[mysqld_safe]
socket                  = /var/run/mysqld/mysqld.sock
nice                    = 0

[mysqldump]
quick
quote-names
max_allowed_packet          = 16M

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

P.S.: there are some config files in /etc/mysql/mariadb.conf.d/ as sample and I've commented all the configurations in there. My main configurations are in 2 files located at /etc/mysql/conf.d/: mysqld.cnf & master.cnf

Contains of mysqld.cnf:

[mysqld]
# General
plugin_load_add             = query_response_time
user                    = mysql
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc_messages_dir             = /usr/share/mysql
lc_messages             = en_US
skip-external-locking
skip_name_resolve           = 0
myisam_recover_options          = BACKUP
concurrent_insert           = 2
sql_mode                = NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION
default_storage_engine          = InnoDB
performance_schema          = ON
query_response_time_stats       = ON
userstat                = ON
innodb_buffer_pool_dump_at_shutdown     = OFF
innodb_rollback_on_timeout              = ON


# Performance
max_connections             = 5000
connect_timeout             = 5
wait_timeout                = 600
max_allowed_packet          = 100M
thread_cache_size           = 128
sort_buffer_size            = 4M
bulk_insert_buffer_size         = 16M
tmp_table_size              = 128M
max_heap_table_size         = 128M
key_buffer_size             = 128M
open-files-limit            = 65535
table_open_cache            = 2048
myisam_sort_buffer_size         = 512M
read_buffer_size            = 2M
read_rnd_buffer_size            = 1M
query_cache_limit           = 0
query_cache_size            = 0
long_query_time             = 0
join_buffer_size            = 3M
table_definition_cache                  = 1424

# Innodb
innodb_buffer_pool_size         = 186G
###innodb_buffer_pool_instances     = 64
innodb_monitor_enable           = all
innodb_file_per_table           = 1
innodb_open_files           = 65535
innodb_io_capacity          = 30000
innodb_io_capacity_max          = 40000
innodb_flush_neighbors          = 0
innodb_flush_method         = O_DIRECT
innodb_log_buffer_size          = 8M
innodb_log_file_size            = 6G
###innodb_log_files_in_group        = 2
innodb_log_group_home_dir       = /var/lib/mysql/
innodb_autoinc_lock_mode        = 2 

# Log
general_log             = 1
general_log_file            = /var/log/mysql/mysql.log
slow_query_log              = 1
slow_query_log_file         = /var/log/mysql/mariadb-slow.log
expire_logs_days            = 5
log_error               = /var/log/mysql/mariadb.err
log_warnings                = 0
long_query_time             = 0.5
#log_slow_rate_limit            = 1000
log_queries_not_using_indexes       = ON
log_slow_verbosity          = query_plan,explain
log_slow_admin_statements       = ON
log_slow_slave_statements       = ON
log_bin                 = /var/log/mysql/binfiles/mariadb-bin
log_bin_index               = /var/log/mysql/binfiles/mariadb-bin.index
max_binlog_size             = 100M
binlog_format               = row
relay_log                               = /var/log/mysql/binfiles/mariadb-relay-bin
relay_log_index                         = /var/log/mysql/binfiles/mariadb-relay-bin.index
max_relay_log_size                      = 100M

Contains of master.cnf:

[mysqld]
# Master/Slave settings for master node
bind-address                = a.b.c.d
server-id               = 10
gtid_domain_id              = 0
log_slave_updates           = 1
innodb_flush_log_at_trx_commit      = 1

I don't think it's relevant but I also set these in my /etc/sysctl.conf file:

net.core.somaxconn=65535
fs.file-max=2097152
fs.nr_open=2097152
vm.max_map_count=262144
net.core.rmem_max=16777216
net.core.wmem_max=16777216
net.core.rmem_default=262144
net.core.wmem_default=262144
net.ipv4.tcp_max_syn_backlog=65535
net.ipv4.tcp_fin_timeout=30
net.ipv4.tcp_syn_retries=2
net.core.netdev_max_backlog=300000
net.ipv4.ip_local_port_range=1024 65535
net.ipv4.tcp_wmem=8192 65536 16777216
net.ipv4.tcp_rmem=8192 87380 16777216
net.ipv4.tcp_keepalive_intvl=30
net.ipv4.tcp_keepalive_time=120

Could you please guide me what is using the space of /var/log/mysql when there is no other file there and how to fix it?

Edit 1: the data directory (/var/lib/mysql) and the log directory (/var/log/mysql) each has a separate partition; so it must be something about this directory (/var/log/mysql), not anything else.

Update 1: It seems that when log, slow-log and error log files are rotated, mariadb still using them and this happens. Any idea how to fix this (obviously I cand just restart the service)?

3
  • I don't have any issue with this and the partition on /var/lib/mysql and /var/log/mysql are separated. Sep 23, 2021 at 9:43
  • As you can see, there is no hidden file in /var/log/mysql directory. Is there a mechanism or something that may create a large file or multiple large files in there that I should check? Sep 23, 2021 at 9:50
  • Or maybe a bug in mariadb 10.5 that causes this? Sep 23, 2021 at 9:51

1 Answer 1

2

OK, I've find the problem and it's what I added as "Update 1" to the question. The main issue was about logrotate; lets see the original content:

# - I put everything in one block and added sharedscripts, so that mysql gets
#   flush-logs'd only once.
#   Else the binary logs would automatically increase by n times every day.
# - The error log is obsolete, messages go to syslog now.
/var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log /var/log/mysql/mariadb-slow.log /var/log/mysql/error.log /var/log/mysql/mariadb.err {
    daily
    rotate 7
    missingok
    create 640 mysql adm
    compress
    sharedscripts
    postrotate
          test -x /usr/bin/mysqladmin || exit 0
          if [ -f `my_print_defaults --mysqld | grep -oP "pid-file=\K[^$]+"` ]; then
            # If this fails, check debian.conf!
            mysqladmin --defaults-file=/etc/mysql/debian.cnf --local flush-error-log \
              flush-engine-log flush-general-log flush-slow-log
          fi
    endscript
}

First I've tested if I can access mysqladmin, I've ran the bellow command:

mysqladmin --defaults-file=/etc/mysql/debian.cnf ping

It's says:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

That's because I've restored my own data and deleted everything (including mysql DB); So I've change the /etc/mysql/debian.cnf as it should be and ran the same command to see if everything is fine and it was:

mysqld is alive

Then to free up the disk, I've executed the logrotate procedure, manually:

logrotate --force /etc/logrotate.d/mysql-server

But it says:

logrotate_script: 3: [: /var/run/mysqld/mysqld.pid: unexpected operator

I've checked my configuration by running the bellow command and searched for pid:

grep -inR "pid" /etc/mysql

The output was:

/etc/mysql/conf.d/mysqld.cnf:5:pid-file             = /var/run/mysqld/mysqld.pid
/etc/mysql/mariadb.conf.d/50-server.cnf:16:pid-file                = /run/mysqld/mysqld.pid

So I was not be careful enough with my configuration and left 2 different pid-file; I've commented the second one but I can't just restart it; so I've change the logrotate file and added the -m1 option to grep:

/var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log /var/log/mysql/mariadb-slow.log /var/log/mysql/error.log /var/log/mysql/mariadb.err {
    daily
    rotate 7
    missingok
    create 640 mysql adm
    compress
    sharedscripts
    postrotate
          test -x /usr/bin/mysqladmin || exit 0
          if [ -f `my_print_defaults --mysqld | grep -m1 -oP "pid-file=\K[^$]+"` ]; then
            # If this fails, check debian.conf!
            mysqladmin --defaults-file=/etc/mysql/debian.cnf --local flush-error-log \
              flush-engine-log flush-general-log flush-slow-log
          fi
    endscript
}

Then I ran the logrotate again, and that was it.

3
  • 1
    A logrotate depending on a pid-file is a poor implementation. The modern age of systemd means pid-files aren't needed (like logrotate for the error-log, if stdout/error is used). MariaDB replaced this with mysqladmin ping. Expand @bindir@ to /usr/bin. You should take a look in these log files, they shouldn't be that big.
    – danblack
    Sep 25, 2021 at 1:11
  • Thanks @danblack . I will check it out. Sep 25, 2021 at 9:03
  • 1
    Your self answer is pretty good. If we bring this back to the cause, this is exactly the same sysadmin issue as "my filesystem is full, but du doesn't show this". Because the flush-logs didn't occur, mariadb had a file descriptor open on the logs. The compress wrote the compressed file removing the original, leaving mariadb's log file descriptors open on a files that didn't have a reference on the filesystem, still consuming space, but not visible.
    – danblack
    Sep 26, 2021 at 0:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.