Input Mariadb cofiguration file

[UHolthausen]

Hello

i need some input. this morning i carelessly ran the command apt update && apt upgrade -y, this then upgraded mariadb 10.5.10 to 10.5.11 because of -y.
Result: plesk no longer responds, even plesk repair did not help.
I know, it was my fault.
Since the server is only a few days old, I had the system reinstalled without further ado.
So far so good, but compared to the first installation :
my.cnf only 1kn instead of 6kb before.
mariadb.cnf also 1kb, before 4kb.
If I now add or activate some innodb instructions, the sql server is not accessible again.

If I read the maria documentation, these statements should be entered in my.cnf.
What am I missing?

The database was previously well under 1 second, now it takes 2.4 seconds to load.

Translated with www.DeepL.com/Translator (free version)

 

[learning_curve]

@UHolthausen As you probably already know, many things changed with MariaDB 10.5.* compared to previous MariaDB releases...
Premumably you've not got a full back-up taken just before the MariaDB upgrade?

Have you looked in etc/mysql and for things like the contents of mariadb.cnf?
Have you checked that /etc/mysql/my.cnf is not just a symlink now?
There are many more things to check / verify on MariaDB if you're now having to compare a new re-install to a previous live (modified) setup

This other thread is about a different subject, but THIS specific post (#20) has a few useful links in it (you might know all of this already, but just in case...)

FWIW We've run the same MariaDB 10.5.10 to 10.5.11 upgrade that you have, but without any detrimental effects or problems, so there must be some config customisations that you have made, that originally caused the issue (presumably anyway?)

 

[weltonw]

Can confirm patch 10->11 without a hitch. Is MDB running at all? What InnoDB instructions break the server? Anything in error logs? What is the error connecting?

 

[UHolthausen]

Hello

Thanks for your replay and hints.
unfortunately, i don't have a backup of the server configuration, as i was still setting it up.

mariadb.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]
local-infile=0


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

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

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

# 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 = 128M
#max_allowed_packet = 1G
#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

#
# * 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
#slow_query_log_file = /var/log/mysql/mariadb-slow.log
#long_query_time = 10
#log_slow_verbosity = query_plan,explain
#log-queries-not-using-indexes
#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
log_warnings = 4
expire_logs_days = 14
#max_binlog_size = 100M

#
# * SSL/TLS
#

# For documentation, please read
# 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:
# InnoDB System Variables
innodb_buffer_pool_size = 1024M
innodb_log_buffer_size = 40M
innodb_file_per_table = 1
innodb_buffer_pool_instances = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
#innodb_file_size = 128M
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 8G
# 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.5 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.5]


mysql error.log

2021-06-24 8:38:30 0 [Note] InnoDB: Using Linux native AIO
2021-06-24 8:38:30 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-06-24 8:38:30 0 [Note] InnoDB: Uses event mutexes
2021-06-24 8:38:30 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-06-24 8:38:30 0 [Note] InnoDB: Number of pools: 1
2021-06-24 8:38:30 0 [Note] InnoDB: Using SSE2 crc32 instructions
2021-06-24 8:38:30 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-06-24 8:38:30 0 [Note] InnoDB: Completed initialization of buffer pool
2021-06-24 8:38:30 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-06-24 8:38:34 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2021-06-24 8:38:34 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-06-24 8:38:34 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-06-24 8:38:34 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-06-24 8:38:34 0 [Note] InnoDB: 10.3.29 started; log sequence number 9142537; transaction id 8744
2021-06-24 8:38:34 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-06-24 8:38:35 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-06-24 8:38:35 0 [Note] Server socket created on IP: '127.0.0.1'.
2021-06-24 8:38:37 0 [Note] Reading of all Master_info entries succeeded
2021-06-24 8:38:37 0 [Note] Added new Master_info '' to hash table
2021-06-24 8:38:37 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.29-MariaDB-0+deb10u1' socket: '/run/mysqld/mysqld.sock' port: 3306 Debian 10

 

[learning_curve]

~ unfortunately, i don't have a backup of the server configuration, as i was still setting it up ~

Yeah, that's how your original post did read to be fair, which is unfortunate, as that would have saved you lots of time...
The details you've posted, confirm that the content of your mariadb.cnf file is not relevant here & that your /etc/mysql/my.cnf is not just a symlink back to it.
From this point forward, the previous post by @john0001 has the most relevent questions for you now, so you'll need to post your answers to that, to progress further really (and also confirm exactly what changes you've made and where, since the new MariaDB 10.5.* install) If it's going to be sorted here in the forum.

 

[UHolthausen]

Hello

I have entered the following in the my.cnf file:

* 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 logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings = 2

# * 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!
default_storage_engine = InnoDB
innodb_buffer_pool_size = 1024M
innodb_log_buffer_size = 40M
innodb_file_per_table = 1
innodb_buffer_pool_instances = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
#innodb_file_size = 128M
#innodb_flush_log_at_trx_commit = 2

then stopped the service and restarted it.
After the restart, the following message appeared when I tried to call plesk
the repair page appeared, logging in was not possible, because the db was not available/accessible.

After deactivating the options and restarting, everything worked again.

At the moment I have the impression that it takes time until the db server is running.
Currently the times are the same as yesterday afternoon. Can see it in the forum software.
Today at noon it was around 3 ~ 4 sec, now I am at 0,1~0,5 sec.

I'll leave it like this for now, I'll read the documentation right away.

First of all, many thanks for the tips/help.

Addition: backups are all set, should run one after the other from midnight.

Translated with www.DeepL.com/Translator (free version)

 

[weltonw]

Which directive exactly was changed? All I see is innodb_flush_log_at_trx_commit = 2 - that should not cause any issues.

Also: Anything in error logs? What is the error connecting? (plesk db)

Is the socket there? Can you successfully authenticate?

 

[learning_curve]

~ All I see is innodb_flush_log_at_trx_commit = 2 - that should not cause any issues ~

A specific reference to this ^^ is here: InnoDB System Variables but the chosen value of 2 is commented out (#) anyway @UHolthausen so the default value of 1 should still be being applied, without causing any issues, as noted ^^ by @john0001

The innodb_file_size value of 128M is also commented out (#) too. Specific references for that are here: InnoDB System Variables and here: How to Choose the MySQL innodb_log_file_size - Percona Database Performance Blog

The only other item that appears to have been changed from the default values in the InnoDB section that you have posted, is the value of the innodb_log_buffer_size but again, that on its own, shouldn't be an issue (although many log related settings are interconnected, so....) Specific reference is here: InnoDB System Variables

Out of interest, have you ever run major/MySQLTuner-perl on your server previously and then changed any values as a result?

Also: Anything in error logs? What is the error connecting? (plesk db)

Is the socket there? Can you successfully authenticate?

Those ^^ are the next answers needed
Edit: There is an 'error log' section shown in post #4 but it's just startup entries. Have you nothing else in here: /var/log/mysql/error.log & error.log1.gz etc etc? Another quick look option would be for you to run this CLI command: journalctl --unit=mariadb.service -n 100 --no-pager and post the results

 

[Nik G]

hello @UHolthausen ,

what is the reason why you have added option 'innodb_flush_method = O_DIRECT' ?
do you have hardware raid with a battery-backed writecache?

O_DIRECT disables the OS level cache, where double caching can be disabled which show some better I/O throughput
So it can speed-up your I/O operations but in case you have hardware raid controller with cache for this operations.
Otherwise it would be better to leave this option by default.

an option 'innodb_flush_log_at_trx_commit = 2' should help you speed-up I/O operations.
It switches MDB to flush data on disk once per some time instead of after each committed transaction.
But be careful, in this case you can lost some(or all) of you data in case power lost or for example MariaDB daemon was killed by OOM.

an option 'innodb_file_per_table = 1' may decrease your performance, because in this case much more file descriptors should be opened. However it may simplify backup process (especially in case you have very large innodb tables)

option 'innodb_buffer_pool_instances = 1' :
The total size innodb_buffer_pool_size you specified is divided among all the buffer pools.
it can speed-up your performance in case you're using multithread machine.
But in this case it is also recommended to set innodb_write_io_threads and innodb_read_io_threads to the count of how many physical CPU you have.

if I'm not mistaken in MDB 10.5 innodb_buffer_pool_instances = 8 by default
which is better for performance because buffer can be accessed for read and write simultaneously by several threads.

and as learning_curve said before, you need to investigate error.log1.gz, error.log2.gz etc in /var/log/mysql/ to find exactly error message why it does not work.

 

[weltonw]

I do want to point out - innodb_flush_log_at_trx_commit = 2 is generally safe unless you need ACID compliance - only an OS-level crash will cause the latest 1 sec or so of data to potentially not be flushed - something like OOM won't impact i (it will if it's 0)

Also:

> Today at noon it was around 3 ~ 4 sec, now I am at 0,1~0,5 sec.

Could be language translation issue - there's a lot of causes for a "slow" MySQL startup - a potential culprit could be crash recovery, which would also render MySQL inactive while it recovers.

 

[learning_curve]

what is the reason why you have added option 'innodb_flush_method = O_DIRECT' ?

FWIW That is & was a default setting for us too, having upgraded from MariaDB 10.4.* > MariaDB 10.5.* when quite a few other things were changed too.
Useful references: Open Source Database (RDBMS) for the Enterprise | MariaDB and InnoDB System Variables showing rational behind the changes to this.

if I'm not mistaken in MDB 10.5 innodb_buffer_pool_instances = 8 by default
which is better for performance because buffer can be accessed for read and write simultaneously by several threads.

Again FWIW innodb_buffer_pool_instances was depreciated from MariaDB 10.5.1 onwards.
References here: InnoDB System Variables and in here under the InnoDb section: MariaDB 10.5.1 Release Notes

 

[UHolthausen]

Hello

Thanks for the help and all the hints.
Yeah, maybe its a translation error, my native tongue is german.

Overnight, the system(mariadb) has settled in and is currently running satisfactorily.
I will read the new documentation for mariadb 10.5.x over the weekend.

Regarding the log file question: after the shutdown yesterday morning, no more entries, even the restart is not documented (next construction site).

Regarding editing my.cnf

i had copied this in the my.cnf file:

# * 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!
default_storage_engine = InnoDB
innodb_buffer_pool_size = 1024M
innodb_log_buffer_size = 40M
innodb_file_per_table = 1
innodb_buffer_pool_instances = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
#innodb_file_size = 128M
#innodb_flush_log_at_trx_commit = 2

after the restart plesk didnt find the db ....

now the original configuration of debian buster is running again

 

[learning_curve]

~ Regarding the log file question: after the shutdown yesterday morning, no more entries, even the restart is not documented (next construction site) ~

If you run this CLI command: journalctl --unit=mariadb.service -n 100 --no-pagerand post the results, we'll all be able to see that (increase the 100 - if needed)

~ i had copied this in the my.cnf file ~

now the original configuration of debian buster is running again

Can you post ^^ that? i.e. the my.cnf file content that you're now actually using? Not the previous one that you had added copied entries into.

 

[UHolthausen]

Hello

The query of journalctl see appendix
the current my.cnf looks like this. This is the original file after the update to 10.5.

 

[learning_curve]

@UHolthausen Presumably, having read it yourself, we're assuming that you've already seen the (previous) errors in the journal entries that you've posted?

The next assumption being made here now, is that having removed all of your own innodb related entries that you had added to the defaut my.cnf file and then reverted back to using the default my.cnf file that was provided with your MariaDB 10.5.* upgrade, your current MariaDB instaltion is running fine / without warnings or faults, apart from... those shown on the last 4 lines of the journal entries? They will take time to resolve... but here's some possible useful links:

Issue - Aborted connection to db: 'psa'

Hi, I see a lot of these errors in the log, mysqld: 2019-03-03 0:00:32 123553 [Warning] Aborted connection 123553 to db: 'psa' user: 'admin' host: 'localhost' (Got timeout reading communication packets) any idea what the issue is?

talk.plesk.com

Question - Aborted connection occurs frequently

When I look at mysqld.log, the following error warning almost every 5-6 minutes. `[Warning] Aborted connection XXXXX to db: 'psa' user: 'admin' host: 'localhost' (Got timeout reading communication packets)` "psa" is Plesk, right? How can this be fixed? I ran the Plesk repair command or reboot...

talk.plesk.com

& From within those ^^ are these links:

Database importing hangs in Plesk: Got an error reading communication packets

Applicable to: Plesk Onyx for Linux Symptoms Database importing hangs at 100%. The database is not big. MySQL server significantly loads CPU: CONFIG_TEXT: PID USER PR NI VIRT RES ...

support.plesk.com

How to troubleshoot slow performance of MySQL on a Plesk server?

Applicable to: Plesk Question How to troubleshoot slow performance of MySQL on a Plesk server? Answer Note: Database performance tuning is out of the scope of Plesk Free Technical Support. This ...

support.plesk.com

Plus an old but still valid Percona link:

MySQL "Got an error reading communication packet"

Possible reasons for MySQL "Got an error reading communication packet" errors and how to address them.

www.percona.com

Plus, there's lots more online references with similar issues / setups to your own (using Debian etc), but they need sufficient search time to find them all.

The innodb related entries & anything else that you want to add into my.cnf file, will take more reference work & patience, to determine the correct setup for your own server using MariaDB 10.5.*. E.G. That default my.cnf file has very limited content & is, as you've stated above, just 28 lines of code & 1k. FWIW Our own my.cnf file is a symlink to /etc/mysql/mariadb.cnf which, has 203 lines of code & is 5,460k. There's lots & lots that can be customized starting from there.

 

[UHolthausen]

Hello

After restoring the original my.cnf and then rebooting, the sql runs perfectly.
I started reading the mariadb documentation over the weekend, it will take some time, but I will probably add one or two things.

Many thanks for the support/help so far.

 

[mow]

To be clear: You put innodb options directly in my.cnf without removing the same options from the included mariadb.conf.d/50-server.cnf?

In your second post, the mariadb.conf.d/50-server.cnf had the buffer pool size set twice:

innodb_buffer_pool_size = 1024M
innodb_log_buffer_size = 40M
innodb_file_per_table = 1
innodb_buffer_pool_instances = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
#innodb_file_size = 128M
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 8G

Maybe mariadb doesn't like that?

 

[learning_curve]

To be clear: You put innodb options directly in my.cnf without removing the same options from the included mariadb.conf.d/50-server.cnf?

Good point, well made FWIW Even on last cloud server that we setup (fresh install OS / Plesk / MariaDB 10.5.* etc but then with a migration via Plesk carried out, once it was all up and running) the /etc/mysql/mariadb.conf.d/50-server.cnf was as expected i.e. empty by default, in terms of lines related to innodb options. Hence all of the subsequent /etc/mysql/mariadb.cnf customization. In the case that @UHolthausen has posted here, there's a few added potential variables 'cos of the fail / re-install / and no MariaDB re-verification process mentioned, as well as the human double entry error, being the most likley cause of the dupication. We'll know more, soon, but your's is a good point.

 

[weltonw]

Unlike say, NGINX, MySQL/MDB don't error out with duplicate configurations - the closest one takes precedence iirc - so for the most part, that's not an issue. It is worth taking a look at the other files though to see if there's a configuration causing issues that wasn't posted here.

 

[learning_curve]

Unlike say, NGINX, MySQL/MDB don't error out with duplicate configurations - the closest one takes precedence iirc - so for the most part, that's not an issue. It is worth taking a look at the other files though to see if there's a configuration causing issues that wasn't posted here.

Another good point well made! Yes, there certainly is precedence applied in MariaDB that's very true, however, back when we ran MariaDB at the 10.1.* release, we did see errors, warnings and some package failures, on 2 domains that were using MariaDB, which were due to some small specific duplications, but not Plesk itself & its use of MariaDB. Consequently, those errors, warnings and package failures did not cause a server failure, like happened to @UHolthausen