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)]>