Intermitten java slow MySQL query
In my application server which is written in JAVA I have the following symptom:
Once or twice an hour, the MySQL query is extremely slow (8-10s / query).
My server is requesting two different database servers and they both have this symptom, but not at the same time.
To address the root cause of the network, I run Network Monitor and it reports that the network between the application server and the database servers is good. Also, my app server has 4 threads, only one thread has a slow request, the other 3 still scan well.
On both DB servers, I set the connection timeout to 10 seconds, there are several requests whose timeout (> 10 seconds), some requests are not timeout, but slow (request time is more than 1s, usually 8s-9).
1 strangely, despite the slow request on the client side, there is no slow request log on the database server side (the time of the slow configuration request i is 1s).
Here is a snippet of my code that I am using to connect to the DB:
public boolean checkSession(String sessionId) {
Connection conn = null;
conn = getDBConnection();
if(conn == null)
return false;
try {
PreparedStatement stm = conn.prepareStatement("SELECT uid FROM sessions WHERE sid=?");
stm.setString(1, sessionId);
ResultSet rs = stm.executeQuery();
if(rs.next()){
if(rs.getInt("uid") == tamtayId){
conn.close();
return true;
}
}
conn.close();
return false;
} catch (SQLException e1) {
e1.printStackTrace();
}
return false;
}
public void setDbConfigString(String str){
conStr = str;
}
public Connection getDBConnection(){
Connection conn = null;
try{
conn = DriverManager.getConnection(conStr);
}
catch (Exception e) {
e.printStackTrace();
}
return conn;
}
source to share
I would highly recommend using the correct JDBC connection pool instead DriverManager
, the downside of the latter is that it creates a new connection every time it is needed, there is no pool. Creating a connection to a database is usually a time consuming operation.
Alongside the fact that your code is broken, imho, your connection stays open in case of an exception and you have to close ResultSet
and PreparedStatement
as well as the connection. Preferably in a block finally
.
I suggest rewriting the code as follows.
public boolean checkSession(String sessionId) {
Connection conn = getDBConnection()
PreparedStatement stm = null;
ResultSet rs = null;
boolean val = false;
try {
if (conn != null) {
stm = conn.prepareStatement("SELECT uid FROM sessions WHERE sid=?");
stm.setString(1, sessionId);
rs = stm.executeQuery();
if(rs.next()){
val = rs.getInt("uid") == tamtayId;
}
}
} catch (SQLException e1) {
e1.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {}
}
if (stm != null) {
try {
stm.close();
} catch (SQLException e) {}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {}
}
}
return val;
}
public void setDbConfigString(String str){
conStr = str;
}
public Connection getDBConnection() throws SQLException {
return DriverManager.getConnection(conStr);
}
Instead of installing, dbConfigString
you should ideally install DataSource
and use this to get connections.
public void setDataSource(DataSource ds) {
this.ds=ds;
}
public void getDBConnection() throws SQLException {
return ds.getConnection();
}
This way you can enter the correct connection pool like Commons DBCP or HikariCP .
source to share