MariaDB (10.3)

It may have taken awhile but here we are. In my MySQL 5.5 Guide I go over whether the Query Cache is relevant for you, and how to make use of compression in the barracuda file format. These bits are still useful.

Buster at least includes mysqltuner, which is a useful guide program to watch for issues.

After installing mariadb be sure to

systemctl stop mariadb
chown mysql:mysql /innodb/
chmod 0750 /innodb
usermod -a -G hugepager mysql

And either

mv /var/lib/mysql/ibdata1 /innodb/

or remove it entirely if you are on a virgin installation.

Replace /innodb/ or ignore as necessary.

If you are changing the size of the ib_logfiles, you will want to first remove them:

rm /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1

cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/99-server.cnf

To make your override file.

Unit File Override

We'll need to increase the filesystem limit to something more generous:

mkdir /etc/systemd/system/mariadb.service.d

/etc/systemd/system/mariadb.service.d/override.conf

[Service]
LimitNOFILE=
LimitNOFILE=1048576

Then run

systemctl daemon-reload

Note this is going to be limited by ulimit -n (nofile in /etc/security/limits.conf). 1048576 is the maximum in Debian Buster.

Open file descriptors requested is: table_open_cache_instances * table_open_cache * 2

Plus max_connections and other misc. stuff.

You'll want to set accordingly. For a 'smaller' DB server (24 cores or less) scaling table_open_cache_instances down to 7 isn't going to hurt.

/etc/mysql/mariadb.conf.d/99-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

# Note if you mount /tmp as tmpfs, and are running a replication slave,
# you will need to make a separate /mysqltmp mount or something similar
# to make sure critical temporary files survive remoot.
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
#skip-external-locking

# Security
# No community software I am aware of actually needs LOAD DATA LOCAL,
# so I just disable it, and probably would not use software that did. See:
# http://dev.mysql.com/doc/refman/5.1/en/load-data-local.html
local_infile             = 0
# This prevents granting access to stored procedures automagically just
# for creating them.
automatic_sp_privileges  = 0
# secure-auth is probably not relevant if your server was built in the
# past decade. I just like it on.
secure-auth              = 1
# Remove skip-show-database if you use phpMyAdmin or a similar tool to
# manage your databases, it will just frustrate you or your users.
skip_show_database
# This limits where the LOAD_FILE, LOAD_DATA and SELECT INTO OUTFILE
# statements can read from or write to. This is a good option to set.
secure_file_priv         = /tmp

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

# And yet for a single server machine I don't need to.
skip-networking

# There's no reason to waste time resolving domain names. If the ip
# changes, we'll know.
skip-name-resolve

#
# * Fine Tuning
# You will need to enable Hugepages on your server to make use of
# the large-pages option
large-pages
# The client default is 16M, while the server default used to be 1M.
# Server default is now also 16M.
# I've since hit 16M limits, so I use 32M now.
max_allowed_packet       = 32M
# There's no serious reason to have a long interactive timeout. If you
# are low on connections, you shouldn't set this higher than
# wait_timeout
interactive_timeout      = 3600
# I am of the opinion that the default value is far too high. If you
# use persistent connections, even a timeout of 300 may be too high.
wait_timeout             = 300
# The following are best set to the same size, because the size
# of temporary tables is limited by the lower of them.
# Setting these to the SkySQL defaults.
tmp_table_size           = 64M
tmp_memory_table_size    = 64M
max_heap_table_size      = 64M
# table_definition_cache should be big enough for every table
# in your database, plus temporary tables, and table_open_cache
# should be a reflection of how many of these will be open in a live
# connection at once - it will likely exceed your definition cache.
# It doesn't hurt to set these to relatively large values. They don't
# take a lot of RAM and it's better than hitting the limit.
# I have actually topped 16K open tables. Yikes.
table_definition_cache   = 8192
table_open_cache         = 65536

# Open file descriptors requested is
# table_open_cache_instances * table_open_cache * 2
# Plus max_connections and other misc. stuff.
# table_open_cache_instances = 7 should be fine for 'small' servers
table_open_cache_instances = 7

# The default optimizer_search_depth is 62. This causes MySQL to take
# an obscene amount of time planning a query, though when it finally
# executes, it is pretty close to optimal. I've set it to 8, which is
# still a bit on the high end.
optimizer_search_depth   = 8

# Buffers
# 256k for everyone! 256k blocks will generally mean that we are not
# switching to the rather expensive mmap() operation most of the time.
# read_rnd_buffer can be pretty greedy, at least for my setup.
# If you are using forums or other software that generates large results,
# you may find using 2mb is closer to optimal.
join_buffer_size         = 384K
sort_buffer_size         = 384K
read_buffer_size         = 384K
mrr_buffer_size          = 384K
read_rnd_buffer_size     = 2M
# As of MariaDB 10 most of these are now 256K as appropriate. Again
# for forums, still needed to boost these a bit.

# Threads
# Defaults are moving up and so should we, it seems.
thread_stack             = 384K
# max_connections is how many connections your server will tolerate at
# once, while thread_cache_size is how many of these your server will
# cache. There is no reason not to set these to be an equal number; I
# have seen no evidence that the trivial amount of RAM a low
# thread_cache_size is worth the performance hit of opening up a new
# thread under load.
# In realistic terms, you should 'tune to failure' - you don't want
# to support more active connections than your system can feasibly handle.
# 256 is a good number for most low-end servers produced these days.
# Increase if you have multiple drive arrays or faster disks.
thread_cache_size        = 256
max_connections          = 256

# Query cache
# You may want to check the how useful the
# Query cache may actually be for you.
# The only additional point I would make here is that changing
# query_cache_min_res_unit from the default is effectively useless,
# especially if you flush regularly.
query_cache_type           = ON
query_cache_limit          = 384K
query_cache_size           = 128M
query_cache_min_res_unit   = 4K
query_cache_strip_comments = ON

# MyISAM
# 16M is the most MySQL will store entirely in large-pages. Past that,
# it will start shunting some of it off to normal memory. Since almost
# everything I do is InnoDB, this isn't a problem. Otherwise, however,
# it can make calculating how much space you need difficult.
# It is possible that MariaDB has improved on this. However, I am well
# past caring for the scant remaining tables that still demand this.
key_buffer_size           = 16M
# Set this to the size of a filesystem block - e.g. 4k
key_cache_block_size     = 4K
# These two should both be the default values. If you are bulk-loading
# data from a script, you may want to increase bulk_insert_buffer_size
# to speed up operation.
bulk_insert_buffer_size  = 8M
myisam_sort_buffer_size  = 128M
# Auto-creates a backup when running the recover operation.
myisam_recover_options   = BACKUP
# By default, MyISAM tables will INSERT into deleted row space before
# appending to the end of the disk. In exchange for saving this trivial
# amount of space, once a row gets deleted, only one insert operation
# may occur at a time until holes are filled. Setting concurrent_insert
# to 2 stops this silly behavior, at the cost of wasting a bit of disk
# space, for a significant performance improvement in MyISAM tables.
concurrent_insert        = 2

# InnoDB
# In theory, you want this to encompass your entire database. In
# practice, you only have so much RAM available.
# Fortunately, as long as you can store a big enough chunk that it
# gets a 99% hit rate, you will be fine.
innodb_buffer_pool_size         = 8192M
# As with everything, only move these if you know you need to.
# the log_group files belong with the rest of your database. They use
# very little io
# innodb_log_group_home_dir       = /var/lib/mysql
# ibdata1, on the other hand, will be roughly half of your writes.
innodb_data_home_dir            = /innodb
# I set 128M as the size of ibdata1 because that's how big individual
# extent entries are in the ext4 filesystem.
innodb_autoextend_increment     = 128
innodb_data_file_path           = ibdata1:128M:autoextend
innodb_temp_data_file_path      = ibtmp1:128M:autoextend

# These now have a saner recommendation. Should total 25% of pool size.
innodb_log_files_in_group       = 2
innodb_log_file_size            = 1G
# Setting innodb_flush_log_at_trx_commit to 0 causes InnoDB to only
# flush to disk once per second, improving performance considerably.
# In a community environment, you are more likely to lose a topic
# read entry or something equally trivial than a post, so the data
# is very rarely going to be critical, and users often have backups
# of their own posts.
innodb_flush_log_at_trx_commit  = 0
# file_per_table makes checking out which tables are doing the heavy
# lifting a lot easier, for sure. It is also required for the
# barracuda file format.
innodb_file_per_table
innodb_file_format              = barracuda
# Note that if you have an established database, and haven't used
# file_per_table, you'll need to ALTER TABLE each table you want to
# split off from your ibdata file.
# Enabling strict mode helps prevent messing up creating or altering
# a table to support the new row formats in barracuda.
# Default is now ON in MariaDB
innodb_strict_mode              = ON
# I set this to sixty because I have obsessive compulsive disorder.
# Don't fret over it.
innodb_lock_wait_timeout        = 60
# O_DIRECT bypasses the operating system's handling of data. This
# can drastically improve how well your system handles large amounts
# of RAM by removing double buffers (once in InnoDB's cache, again
# in the filesystem's cache), at a slight cost to reliability.
# This appears to be more dramatic the more RAM you have.
# Note that if you've mounted /tmp to tmpfs like a good admin,
# InnoDB will whine at you regularly unless you've changed tmpdir to
# something that isn't tmpfs.
# Changing tmpdir in such a case only matters for your slave server,
# but a good idea if you do.
innodb_flush_method             = O_DIRECT
# We like to be able to do cold backups
innodb_fast_shutdown = 0



#
# * 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
# You can set this to non-integers. 1 second has been fine for me for ages for this, however.
long_query_time        = 1
#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
binlog_cache_size         = 256K
# Setting sync_binlog fairly high is generally beneficial if the average
# value of data is fairly small.
sync_binlog             = 16
expire_logs_days        = 14
max_binlog_size         = 512M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = exclude_database_name

# You can also ignore tables. This can help ease the load on tour replicant servers,
# especially for less important data.

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

Final steps

Virgin Installation

If changing the page size, you'll need to regenerate system tables:

mysql_install_db --user=mysql

Start the server, then

mysql_secure_installation

Query Cache

If you are using the Query Cache, then you will need to setup a cron to flush it regularly, else you are going to see the problems people complain about a lot sooner. First, make an appropriate user:

1. In MySQL:
2. GRANT RELOAD ON *.* TO qcmaint@localhost IDENTIFIED BY 'crazylongpass';
3. In a script file, run
4. /usr/bin/mysql -u qcmaint -p"crazylongpass" -e 'FLUSH QUERY CACHE;'
5. via cron, two to three times an hour. My /etc/cron.d/mysql:
6. 4,24,44 * * * * root /root/flushqc.sh
7. My experience is that there is a spot between running it every half hour and every 15 minutes where flushing no longer helps - you should probably stop using the query cache entirely if this breaks down for you.

/root/.my.cnf

No point in hiding this, naturally. Since root can always set the MySQL password, it can be handy just to stick a giant uncrackable mess in /root/.my.cnf rather than have something crackable:

[client]
user=root
password=yourrandompasshere
socket=/run/mysqld/mysqld.sock

/etc/mysql/debian.cnf

'Automatically generated' ha.

You'll need to add the root password here for a number of purposes.

Debian Buster

Initial: Designing a Server - IPMI Installation - Second Steps

---

Core: Network - OpenSSH - Packages - sysctl.conf - Iptables - Security - User Management

---

Stack: Unbound - MariaDB - Nginx - PHP (7)

---

Mail

Mail Setup - Mail Tables - OpenDKIM - Mail and DNS - Spamassassin - ClamAV - Postfix - Dovecot - Reputation Management

---

Logs and Security: RKHunter - Logging - Logcheck - Backups - AppArmor