Am using legacy Spring 4 app which uses MySQL5 on the backend.
When using raw Spring JDBC calls, a bunch of DAOs are throwing the following exception:
DAO::org.springframework.jdbc.CannotGetJdbcConnectionException:
Could not get JDBC Connection; nested exception is
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
Data source rejected establishment of connection, message from server:
"Too many connections"
After scouring the web using Google, I get a lot of different choices for solutions so I rather just ask here.
Tried troubleshooting using the following:
When I issue the following commands inside the database:
show status like 'Conn%';
Yields:
Connection_errors_accept 0
Connection_errors_internal 0
Connection_errors_max_connections 395711
Connection_errors_peer_address 2
Connection_errors_select 0
Connection_errors_tcpwrap 0
Connections 423604612
A different SQL call:
show status like '%onn%';
Yields:
Aborted_connects 6857
Connection_errors_accept 0
Connection_errors_internal 0
Connection_errors_max_connections 395711
Connection_errors_peer_address 2
Connection_errors_select 0
Connection_errors_tcpwrap 0
Connections 423605165
Max_used_connections 1001
Performance_schema_session_connect_attrs_lost 0
Ssl_client_connects 0
Ssl_connect_renegotiates 0
Ssl_finished_connects 0
Threads_connected 12
Question(s):
What is the max amount of connections that can be set by MySQL without it causing any breakage or further performance issues?
Checked for max connections like this:
show variables like "max_connections";
Yields:
max_connections 1000
A lot of articles on the Internet states that I need to grant / edit the max_connections
and max_user_connections
in my.cnf file under the database server. Also, read that I could something like this:
mysql> SET GLOBAL max_connections = 3000;
What is the best course of action to take regarding this issue?
Is it by just simply grant / edit the max_connections and max_user_connections in my.cnf file under the database server as root? Or, are more steps needed?