A few weeks ago I had some performance problems which were solved by swapping hard drives.
System utilization was back to under 1.0 all the time.
The only thing I’ve done since then is to upgrade to version 29.0, but I didn’t have any problems for the first few days.
Now it looks like this
and it never gets under 4.0
iotop shows a lot of traffic like this
I set nextcloud to maintenance mode and stopped apache and nginx so see if nextcloud is the cause of the problems. Is this sufficient? Because there is still a lot of IO from mysql and I don’t know where this should came from if not from nextcloud.
Personalities : [linear] [multipath] [raid0] [raid1] [raid6] [raid5] [raid4] [raid10]
md2 : active raid1 sdb4[3] sda4[2]
1944880128 blocks super 1.2 [2/2] [UU]
bitmap: 4/15 pages [16KB], 65536KB chunk
md0 : active raid1 sdb2[3] sda2[2]
498688 blocks super 1.2 [2/2] [UU]
md1 : active raid1 sdb3[3] sda3[2]
7995392 blocks super 1.2 [2/2] [UU]
unused devices: <none>
/etc/mysql/my.cnf
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 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.
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = ::ffff:127.0.0.1
local-infile=0
/etc/mysql/mariadb.conf.d/50-server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /run/mysqld/mysqld.pid
socket = /run/mysqld/mysqld.sock
#port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
#skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
#key_buffer_size = 16M
#max_allowed_packet = 16M
#thread_stack = 192K
#thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam_recover_options = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
#query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log_file = /var/log/mysql/mariadb-slow.log
#long_query_time = 10
#log_slow_rate_limit = 1000
#log_slow_verbosity = query_plan
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
#max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = exclude_database_name
#
# * Security Features
#
# Read the manual, too, if you want chroot!
#chroot = /var/lib/mysql/
#
# For generating SSL certificates you can use for example the GUI tool "tinyca".
#
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#
# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
#ssl-cipher = TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
#ssl = on
#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Unix socket authentication plugin is built-in since 10.0.22-6
#
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
#
# Also available for other users if required.
# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/
# this is only for embedded server
[embedded]
# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
# This group is only read by MariaDB-10.3 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.3]
You did not provide any information about the version of your database. You can get new packaged versions and repository links for all distributions here: https://mariadb.org/download/
I would recommend you to take 10.11, which is the latest and actual Long Term Release (with 5 years of support).
Your database is poorly configured and not realy optimized. No innodb optimizations at all (maybe in another file?) You have got plenty of memory space, so you can optimize A LOT!
Just for comparison, this is a good config for a machine with 8 GB ram with mariadb 10.11 (Long Term Release):
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
# this is read by the standalone daemon and embedded servers
[server]
#
# * Query Cache Configuration
#
#query_cache_type = 1 # Default 0 1 if query_cache_size != 0
#query_cache_size = 128M
query_cache_limit = 4M
query_cache_min_res_unit = 2k
tmp_table_size = 128M
max_heap_table_size = 128M
# this is only for the mysqld standalone daemon
[mysqld]
performance_schema = ON
innodb_stats_on_metadata = 0
#
# * Basic Settings
#
#user = mysql
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
#lc-messages = en_US
skip-external-locking
# Broken reverse DNS slows down connections considerably and name resolve is
# safe to skip if there are no "host by domain name" access grants
#skip_name_resolve = 1
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = $HOSTNAME
#
# * Fine Tuning
#
# https://mariadb.com/kb/en/configuring-mariadb-for-optimal-performance/
# If you don't use MyISAM tables explicitly (true for most MariaDB 10.4+ users), you can set key_buffer_size to a very low value, like 64K.
key_buffer_size = 8
key_cache_block_size = 512
key_cache_file_hash_size = 128
myisam_sort_buffer_size = 4K
#read_buffer_size = 1K
#read_rnd_buffer_size = 1K
#write_buffer_size = 1K
max_allowed_packet = 1G
thread_stack = 192K
thread_cache_size = 8
join_buffer_size = 3G
#table_cache = 64
table_open_cache = 1400
table_definition_cache = 800
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam_recover_options = BACKUP
#max_connections = 100
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# Recommend only changing this at runtime for short testing periods if needed!
general_log_file = /var/log/mysql/mysql.log
#general_log = 1
# When running under systemd, error logging goes via stdout/stderr to journald
# and when running legacy init error logging goes to syslog due to
# /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf
# Enable this if you want to have error logging into a separate file
log_error = /var/log/mysql/error.log
# Enable the slow query log to see queries with especially long duration
log_slow_query = 1
log_slow_query_file = /var/log/mysql/mariadb-slow.log
log_slow_query_time = 1
log_slow_verbosity = query_plan,explain
log-queries-not-using-indexes = 1
log_slow_min_examined_row_limit= 1000
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
#expire_logs_days = 10
#max_binlog_size = 100M
#
# * SSL/TLS
#
# For documentation, please read
# https://mariadb.com/kb/en/securing-connections-for-client-and-server/
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#require-secure-transport = on
#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# Most important is to give InnoDB 80 % of the system RAM for buffer use:
# https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size
innodb_buffer_pool_size = 4G
#innodb_buffer_pool_instances = 1 # Removed: MariaDB 10.6.0
#innodb_buffer_pool_chunk_size = 48M # default = autosize (0), resulting in innodb_buffer_pool_size/64, if large_pages round down to multiple of largest page size, with 1MiB minimum (>= MariaDB 10.8.1)
innodb_flush_log_at_trx_commit= 2
innodb_log_buffer_size = 32M # default = 16M
innodb_log_file_size = 1G
#innodb_max_dirty_pages_pct = 90 # defeault = 90.000000 (>= MariaDB 10.5.7)
#innodb_large_prefix = on # Removed: MariaDB 10.6.0
#innodb_file_format = barracuda # Removed: MariaDB 10.6.0
#innodb_file_per_table = 1 # default = ON
# this is only for embedded server
[embedded]
# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
transaction_isolation = READ-COMMITTED # new
# This group is only read by MariaDB-10.11 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.11]
Then I can warmly recommend the mysqltuner Perl script. With a little investment of time, you get a really optimally configured MariaDB Server:
Sorry for my late reply, but since the server went down, I had to move completely and then we also went on vacation. So it was perfect timing again. Even though I have a backup, Nextcloud is not running on the new server.
