MySQL - Caused by: java.sql.SQLException: oo many connections,问题解决!
Caused by: java.sql.SQLException: oo many connections
我们在做压力测试的时候,平时单机模式下挺稳定的一个服务突然报了一个连接mariadb的错误,刚开始还以为是配置里面的参数配置小了。。。
driverClassName: org.mariadb.jdbc.Driver
max-active: 100
max-idle: 10
min-idle: 5
initial-size: 10
validation-query: select 1
test-on-borrow: false
test-on-return: false
test-while-idle: true
改为:
driverClassName: org.mariadb.jdbc.Driver
max-active: 1000
max-idle: 10
min-idle: 5
initial-size: 10
validation-query: select 1
test-on-borrow: false
test-on-return: false
test-while-idle: true
但是重启服务后依旧有这个错误,想着是不是数据库安装的时候没有该默认的连接参数啊,查看数据库目前配置的最大连接数;才200当然不够啊。。。
show processlist 可以看到当前连接已经200个满了,其他再有连接进来会被拒绝掉的。。。
MariaDB [(none)]> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 200 |
+-----------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> show processlist ;
+--------+---------------+-----------------+--------------+---------+------+----------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+--------+---------------+-----------------+--------------+---------+------+----------+------------------+----------+
+--------+---------------+-----------------+--------------+---------+------+----------+------------------+----------+
201 rows in set (0.001 sec)
我们安装的是mariadb配置文件在/etc/my.cnf,修改max_connections的值,重启mariadb服务就行了
[root@mariadb /]# vim /etc/my.cnf
[root@mariadb cloud-user]# vim /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
skip-name-resolve
##设置3306端口
port = 3306
##设置mysql的安装目录
basedir=/opt/mariadb
##设置mysql数据库的数据的存放目录
datadir=/opt/mariadb/data
##允许最大连接数
max_connections=1500 ## ----------------> 由原来200改为1500
##服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
##创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[root@mariadb /]# systemctl restart mysqld
[root@mariadb /]# systemctl status mysqld
● mysqld.service - LSB: start and stop MariaDB
Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
Active: active (running) since Thu 2021-01-28 01:39:03 UTC; 2s ago
Docs: man:systemd-sysv-generator(8)
Process: 26783 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
Process: 26934 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
Tasks: 47
Memory: 93.6M
CGroup: /system.slice/mysqld.service
├─26972 /bin/sh /opt/mariadb/bin/mysqld_safe --datadir=/opt/mariadb/data --pid-file=/opt/mariadb/data/mariadb.pid
└─27072 /opt/mariadb/bin/mariadbd --basedir=/opt/mariadb --datadir=/opt/mariadb/data --plugin-dir=/opt/mariadb/lib/plugin --user=mysql --log-err...
Jan 28 01:39:02 mariadb systemd[1]: Starting LSB: start and stop MariaDB...
Jan 28 01:39:02 mariadb mysqld[26934]: Starting MariaDB.210128 01:39:02 mysqld_safe Logging to '/opt/mariadb/data/mariadb.err'.
Jan 28 01:39:02 mariadb mysqld[26934]: 210128 01:39:02 mysqld_safe Starting mariadbd daemon with databases from /opt/mariadb/data
Jan 28 01:39:03 mariadb mysqld[26934]: SUCCESS!
Jan 28 01:39:03 mariadb systemd[1]: Started LSB: start and stop MariaDB.
再次查看最大连接数已经修改成功了。
MariaDB [(none)]> show global variables like '%max_conn%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| extra_max_connections | 1 |
| max_connect_errors | 100 |
| max_connections | 1500 |
+-----------------------+-------+
3 rows in set (0.001 sec)