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

# 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