Sysctl setting that may suitable for MariaDB

sysctlconf-mariadb.md

This /etc/sysctl.conf settings are generally suitable for a MariaDB Galera Cluster. Use this wisely. Execute command sysctl -p after editing /etc/sysctl.conf.

# Increase the maximum number of memory map areas a process may have
# This can help prevent out-of-memory errors in large applications
vm.max_map_count=262144

# Increase the maximum number of file handles and inode cache for large file transfers
# This can improve performance when dealing with a large number of files
fs.file-max = 3261780

# Increase the maximum buffer size for TCP
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216

# Increase Linux autotuning TCP buffer limits
net.ipv4.tcp_rmem = 4096 12582912 16777216
net.ipv4.tcp_wmem = 4096 12582912 16777216

# Disable caching of ssthresh from previous TCP connection
net.ipv4.tcp_no_metrics_save = 1

# Reduce the kernel's tendency to swap
vm.swappiness = 1

# Set the default queueing discipline for network devices
net.core.default_qdisc = fq_codel

# Enable TCP BBR congestion control
net.ipv4.tcp_congestion_control=bbr

# Enable TCP MTU probing
net.ipv4.tcp_mtu_probing=1

# Increase the maximum input queue length of a network device
net.core.netdev_max_backlog = 32768

# Increase the maximum accept queue limit
net.core.somaxconn = 65535

# Reduce the number of SYN and SYN+ACK retries before packet expires
net.ipv4.tcp_syn_retries = 1
net.ipv4.tcp_synack_retries = 1

# Reduce the timeout to close client connections in TIME_WAIT state
net.ipv4.tcp_fin_timeout = 30

# Disable SYN cookie flood protection
net.ipv4.tcp_syncookies = 0

# Increase the local port range used by TCP and UDP
net.ipv4.ip_local_port_range = 1024 65535

# Additional Galera Cluster optimizations
# Increase the number of allowed open files per process for MariaDB
####fs.file-max = 3261780

# Increase the number of file handles specifically for MariaDB
# Adjust according to the needs of your Galera Cluster
fs.aio-max-nr = 1048576

# https://www.suse.com/support/kb/doc/?id=000017857
# Flushing a large cache can take extensive time and force many things to wait.
# In contrast, flushing a small cache (even multiple times)  is far less disruptive.
# Good performance (for both slow storage and quick storage, and for both low RAM systems vs high RAM systems)
# does not usually require more than a few hundred megabytes of memory in dirty cache.

vm.dirty_bytes = 629145600
vm.dirty_background_bytes = 314572800

Here are some settings from the sysctl.conf file that would be particularly beneficial for a MariaDB database:

1. Increase the maximum number of memory map areas a process may have:

   • vm.max_map_count=262144
   • This can help prevent out-of-memory errors in large applications, such as databases that need to map a large number of files or regions of memory.

2. Increase the maximum number of file handles and inode cache:

   • fs.file-max = 3261780
   • Databases often need to open many files simultaneously, so increasing this limit can improve performance.

3. Reduce the kernel's tendency to swap:

   • vm.swappiness = 1
   • Databases perform best when their data is kept in memory. By reducing the system's tendency to swap, you can help ensure that your database's memory stays in RAM and is not swapped to disk.

4. Increase the maximum input queue length of a network device:

   • net.core.netdev_max_backlog = 32768
   • If your database receives a high volume of incoming network traffic, increasing this value can help ensure that packets do not get dropped.

5. Increase the maximum accept queue limit:

   • net.core.somaxconn = 65535
   • If your database needs to handle many incoming connections, increasing this value can help prevent connection delays or failures.

6. Increase the local port range used by TCP and UDP:

   • net.ipv4.ip_local_port_range = 1024 65535
   • If your database opens many network connections, increasing the local port range can prevent port exhaustion.

Please remember to handle system configurations securely when using such tools and ensure you have the necessary permissions to modify configuration files. Always backup your configuration files before making any changes. Also, keep in mind that these settings may need to be adjusted based on the specific requirements and constraints of your system. It's always a good idea to monitor system performance and adjust settings as needed.

# Increase the maximum number of memory map areas a process may have
# This can help prevent out-of-memory errors in large applications
# Reference: https://bl.ocks.org/magnetikonline/2760f98f6bf654d5ad79
vm.max_map_count=262144

# Increase the maximum number of file handles and inode cache for large file transfers
# This can improve performance when dealing with a large number of files
fs.file-max = 3261780

# Increase the maximum buffer size for TCP
# Larger buffers can improve network throughput
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216

# Increase Linux autotuning TCP buffer limits
# This can improve network performance by allowing TCP to use more memory for buffering
net.ipv4.tcp_rmem = 4096 12582912 16777216
net.ipv4.tcp_wmem = 4096 12582912 16777216

# Disable caching of ssthresh from previous TCP connection
# This can improve network performance by preventing TCP from using stale information
net.ipv4.tcp_no_metrics_save = 1

# Reduce the kernel's tendency to swap
# This can improve performance by keeping more data in memory
vm.swappiness = 1

# Set the default queueing discipline for network devices
# This can improve network performance by managing packet scheduling
net.core.default_qdisc = fq_codel

# Enable TCP BBR congestion control
# This can improve network performance by using a more modern congestion control algorithm
net.ipv4.tcp_congestion_control=bbr

# Enable TCP MTU probing
# This can improve network performance by allowing TCP to discover the optimal MTU size
net.ipv4.tcp_mtu_probing=1

# Increase the maximum input queue length of a network device
# This can improve network performance by allowing more packets to be queued for processing
net.core.netdev_max_backlog = 32768

# Increase the maximum SYN backlog queue
# This can improve network performance by allowing more TCP connections to be initiated simultaneously
net.ipv4.tcp_max_syn_backlog = 32768

# Increase the maximum accept queue limit
# This can improve network performance by allowing more established connections to be queued for acceptance
net.core.somaxconn = 65535

# Reduce the number of SYN and SYN+ACK retries before packet expires
# This can improve network performance by reducing the time spent on unsuccessful connection attempts
net.ipv4.tcp_syn_retries = 1
net.ipv4.tcp_synack_retries = 1

# Reduce the timeout to close client connections in TIME_WAIT state
# This can improve network performance by freeing up resources more quickly
net.ipv4.tcp_fin_timeout = 30

# Disable SYN cookie flood protection
# This can improve network performance by disabling a feature that is not needed in most environments
net.ipv4.tcp_syncookies = 0

# Increase the local port range used by TCP and UDP
# This can improve network performance by allowing more simultaneous connections
net.ipv4.ip_local_port_range = 1024 65535

# Enable reuse of TIME_WAIT sockets
# This can improve network performance by allowing sockets in the TIME_WAIT state to be reused
net.ipv4.tcp_tw_reuse = 1

# Increase the maximum number of TIME_WAIT sockets
# This can improve network performance by allowing more sockets to be kept in the TIME_WAIT state
net.ipv4.tcp_max_tw_buckets = 400000

# Increase the maximum number of orphaned sockets
# This can improve network performance by allowing more sockets to be kept open without an associated process
net.ipv4.tcp_max_orphans = 60000

execute command sysctl -p after editing /etc/sysctl.conf

root@myserver:/etc/sysctl.d# cat README.sysctl
Kernel system variables configuration files

Files found under the /etc/sysctl.d directory that end with .conf are
parsed within sysctl(8) at boot time. If you want to set kernel variables
you can either edit /etc/sysctl.conf or make a new file.

The filename isn't important, but don't make it a package name as it may clash
with something the package builder needs later. It must end with .conf though.

My personal preference would be for local system settings to go into
/etc/sysctl.d/local.conf but as long as you follow the rules for the names
of the file, anything will work. See sysctl.conf(8) man page for details
of the format.

After making any changes, please run "service procps force-reload" (or, from
a Debian package maintainer script "deb-systemd-invoke restart procps.service").

===

To check status:

systemctl status systemd-sysctl.service