6月 232020
 

CentOS 8 Mariadb Server系统资源限制配置

https://mariadb.com/kb/en/configuring-linux-for-mariadb/
https://mariadb.com/kb/en/systemd/#configuring-the-open-files-limit

方案一

在系统层面配置openfile值(默认值为1024)

[root@centos-s-1vcpu-2gb-sfo3-01 ~]# ulimit -a
core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 7182
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 7182
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
[root@centos-s-1vcpu-2gb-sfo3-01 ~]#

使用通配符或用户名进行配置

[root@centos-s-1vcpu-2gb-sfo3-01 ~]# vi /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535

mysql soft nofile 65535
mysql hard nofile 65535

确认修改成功

[root@centos-s-1vcpu-2gb-sfo3-01 ~]# ulimit -n
65535
[root@centos-s-1vcpu-2gb-sfo3-01 ~]# ulimit -Sn
65535
[root@centos-s-1vcpu-2gb-sfo3-01 ~]# ulimit -Hn
65535
[root@centos-s-1vcpu-2gb-sfo3-01 ~]#

方案二

基于systemd的服务启动参数配置openfile值

Mariadb Server服务启动配置文件(默认不建议修改,会随软件包升级而覆盖)

[root@centos-s-1vcpu-2gb-sfo3-01 ~]# cat /usr/lib/systemd/system/mariadb.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. If you want to customize, the
# best way is to create a file "/etc/systemd/system/mariadb.service",
# containing
# .include /usr/lib/systemd/system/mariadb.service
# ...make your changes here...
# or create a file "/etc/systemd/system/mariadb.service.d/foo.conf",
# which doesn't need to include ".include" call and which will be parsed
# after the file mariadb.service itself is parsed.
#
# For more info about custom unit files, see systemd.unit(5) or
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# For example, if you want to increase mysql's open-files-limit to 10000,
# you need to increase systemd's LimitNOFILE setting, so create a file named
# "/etc/systemd/system/mariadb.service.d/limits.conf" containing:
# [Service]
# LimitNOFILE=10000

# Note: /usr/lib/... is recommended in the .include line though /lib/...
# still works.
# Don't forget to reload systemd daemon after you change unit configuration:
# root> systemctl --system daemon-reload

# Use [mysqld.INSTANCENAME] as sections in my.cnf to configure this instance.

[Unit]
Description=MariaDB 10.3 database server
Documentation=man:mysqld(8)
Documentation=https://mariadb.com/kb/en/library/systemd/
After=network.target

[Install]
WantedBy=multi-user.target
Alias=mysql.service
Alias=mysqld.service

[Service]
Type=notify
User=mysql
Group=mysql

ExecStartPre=/usr/libexec/mysql-check-socket
# '%n' expands to 'Full unit name'; man systemd.unit
ExecStartPre=/usr/libexec/mysql-prepare-db-dir %n
# MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb@.service.d/MY_SPECIAL.conf
# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# per bug #547485
ExecStart=/usr/libexec/mysqld --basedir=/usr $MYSQLD_OPTS $_WSREP_NEW_CLUSTER
ExecStartPost=/usr/libexec/mysql-check-upgrade

# Setting this to true can break replication and the Type=notify settings
# See also bind-address mysqld option.
PrivateNetwork=false

KillMode=process
KillSignal=SIGTERM

# Don't want to see an automated SIGKILL ever
SendSIGKILL=no

# Restart crashed server only, on-failure would also restart, for example, when
# my.cnf contains unknown option
Restart=on-abort
RestartSec=5s

UMask=007

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

# Place temp files in a secure directory, not /tmp
PrivateTmp=true
[root@centos-s-1vcpu-2gb-sfo3-01 ~]#

通用配置值(LimitNOFILE=infinity)

sudo tee /etc/systemd/system/mariadb.service.d/limitnofile.conf <<EOF
[Service]

LimitNOFILE=infinity
EOF
sudo systemctl daemon-reload

通用配置值infinity实际值来源

[centos@mariadb ~]$ cat /proc/sys/fs/nr_open
1048576
[centos@mariadb ~]$

建议配置值(较大的正整数)

sudo tee /etc/systemd/system/mariadb.service.d/limitnofile.conf <<EOF
[Service]

LimitNOFILE=1048576
EOF
sudo systemctl daemon-reload

Mairadb最大连接数参数详情

https://mariadb.com/docs/reference/mdb/system-variables/max_connections/#mdb-system-variables-max-connections
https://mariadb.com/kb/en/server-system-variables/#max_connections
Minimum Value 10
Maximum Value 100000
Default Value 151

修改Mariadb最大连接数配置(上限值)

[root@centos-s-1vcpu-2gb-sfo3-01 ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
max_connections = 100000
[root@centos-s-1vcpu-2gb-sfo3-01 ~]# systemctl restart mariadb
[root@centos-s-1vcpu-2gb-sfo3-01 ~]#

确认Mariadb最大连接数配置生效

MariaDB [(none)]> show variables like "max_connections";
+-----------------+--------+
| Variable_name   | Value  |
+-----------------+--------+
| max_connections | 100000 |
+-----------------+--------+
1 row in set (0.002 sec)

MariaDB [(none)]>