MariaDB的最大连接数/最大可打开表数/最大可打开文件数
笺注:MariaDB的安装可参考 CentOS8安装LAMP+phpMyAdmin
查看MariaDB的版本信息:
MariaDB [(none)]> select version();
+-----------------+
| version() |
+-----------------+
| 10.3.17-MariaDB |
+-----------------+
1 row in set (0.000 sec)
查看MariaDB的最大连接数:
MariaDB [(none)]> show variables like '%max_connections%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| extra_max_connections | 1 |
| max_connections | 151 |
+-----------------------+-------+
2 rows in set (0.001 sec)
查看MariaDB当前打开的连接数:
MariaDB [(none)]> show status like 'threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 26 |
+-------------------+-------+
1 row in set (0.001 sec)
查看MariaDB当前处于激活状态的连接数:
MariaDB [(none)]> show status like 'threads_running';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_running | 1 |
+-----------------+-------+
1 row in set (0.002 sec)
查看每个客户端的连接失败的最大允许次数:
MariaDB [(none)]> show variables like '%max_connect_errors%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
+--------------------+-------+
1 row in set (0.001 sec)
查看MariaDB的“最大可打开表数”:(能同时打开的表的总数量)
MariaDB [(none)]> show variables like 'table_open%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache | 2000 |
| table_open_cache_instances | 8 |
+----------------------------+-------+
2 rows in set (0.002 sec)
查看MariaDB当前打开的表的数量:
MariaDB [(none)]> show status like 'open_table%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Open_table_definitions | 124 |
| Open_tables | 127 |
+------------------------+-------+
2 rows in set (0.001 sec)
笺注:假如Open_tables的值比较大,或等于table_open_cache的值,就可以把table_open_cache的值调大一些。
查看MariaDB的“最大可打开文件数”:
MariaDB [(none)]> show global variables like '%open_files_limit%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 4183 |
+------------------+-------+
1 row in set (0.001 sec)
修改MariaDB的主配置文件:
[root@centos8 ~]# vi /etc/my.cnf
#在[mysqld]下面插入:
table_open_cache = 3000 #修改MariaDB能同时打开的表的总数量
max_connections = 10008 #修改MariaDB的最大连接数
max_connect_errors = 1000 #修改每个客户端的连接失败的最大允许次数
open_files_limit = 65535 #修改MariaDB的最大可打开文件数
重启MariaDB:
[root@centos8 ~]# systemctl restart mariadb
再次查看MariaDB的最大连接数:(服务器重启后依旧生效)
[root@centos8 ~]# mysql -e "show variables like '%max_connections%';"
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| extra_max_connections | 1 |
| max_connections | 10008 |
+-----------------------+-------+
再次查看每个客户端的连接失败的最大允许次数:(服务器重启后依旧生效)
[root@centos8 ~]# mysql -e "show variables like '%max_connect_errors%';"
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 1000 |
+--------------------+-------+
再次查看MariaDB的“最大可打开表数”:(服务器重启后依旧生效)
[root@centos8 ~]# mysql -e "show variables like 'table_open%';"
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache | 3000 |
| table_open_cache_instances | 8 |
+----------------------------+-------+
再次查看MariaDB当前打开的表的数量:
[root@centos8 ~]# mysql -e "show status like 'open_table%';"
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Open_table_definitions | 70 |
| Open_tables | 63 |
+------------------------+-------+
再次查看MariaDB的最大可打开文件数:(服务器重启后依旧生效)
[root@centos8 ~]# mysql -e "show global variables like '%open_files_limit%';"
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+
操作系统的“进程最大可打开文件数”的查询方法:
[root@centos8 ~]# ulimit -n
1024
操作系统的“进程最大可打开文件数”的修改方法:
[root@centos8 ~]# tail -5 /etc/security/limits.conf
# End of file
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
重启服务器:
[root@centos8 ~]# reboot
服务器重启后,更改生效:
[root@centos8 ~]# ulimit -n
65535
相关文章:
CentOS8使用Python3脚本管理MariaDB
CentOS8_lnmp1.7_单独安装数据库(MariaDB)
Zabbix调用Python3脚本监控Linux下的MariaDB
Windows2008R2_UPUPW搭建WordPress+Discuz!7.2
MySQL的最大连接数/最大可打开表数/最大可打开文件数 |