MySQL Forums
Forum List  »  Connector/JDBC and Java

Java and mysql high concurrency bottlenecks solutions
Posted by: Frwa Onto
Date: June 23, 2017 12:36PM

We are developing a vehicle tracking system in which several GPS devices keep sending their GPS locations to the server using Tcp connection. Tcp communicator decodes the GPS location and inserts that data into the database and before inserting we do some selects and updates but all I do it using prepared statements. Right now, one thread of TCP communicator serves one device request.Immediately after creating the thread we get one connection from the pool. After decoding the GPS data is where we perform the multiple select, update and insert for each data. As number of devices are increasing, the number of concurrent connections to our Mysql database are also increasing. We are now anticipating 30 to 50 thousand devices pumping data every minute.Currently below is how the snippet of the whole tcp communicator looks like. I know eventually we will facing both insert bottleneck into the database. What will the best solution to over come this scenario? Will Java also be able to handle this many concurrency ?

public class comm8888 {
HikariDataSource connectionPool = null;
private Socket receivedSocketConn1;
ConnectionHandler(Socket receivedSocketConn1) {
this.receivedSocketConn1=receivedSocketConn1;
}
Connection dbconn = null;
public void run() { // etc
DataOutputStream w = null;
DataInputStream r = null;
String message="";
receivedSocketConn1.setSoTimeout(60000);
dbconn = connectionPool.getConnection();
dbconn.setAutoCommit(false);
try {
w = new DataOutputStream(new BufferedOutputStream(receivedSocketConn1.getOutputStream()));
r = new DataInputStream(new BufferedInputStream(receivedSocketConn1.getInputStream()));
while ((m=r.read()) != -1){
//multiple prepared based sql select,update and insert here.
}
}
finally{
try {
if ( dbconn != null ) {
dbconn.close();
}
}
catch(SQLException ex){
ex.printStackTrace();
}
try{
if ( w != null ){
w.close();
r.close();
receivedSocketConn1.close();
}
}
catch(IOException ex){
ex.printStackTrace(System.out);
}
}
}
}


public static void main(String[] args) {
new comm8888();
}
comm8888() {
try {

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/testdata");
config.setUsername("****");
config.setPassword("****");
config.setMaximumPoolSize(20);
connectionPool = new HikariDataSource(config); // setup the connection pool
}
catch (Exception e) {
e.printStackTrace(System.out);
}
try
{
final ServerSocket serverSocketConn = new ServerSocket(8888);
while (true){
try {
Socket socketConn1 = serverSocketConn.accept();
new Thread(new ConnectionHandler(socketConn1)).start();
}
catch(Exception e){
e.printStackTrace(System.out);
}
}
}
catch (Exception e) {
e.printStackTrace(System.out);

}

}
}

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.