Dear All
I would like to ask your opinion on this. We have a java socket application which will keep receiving data from devices then each data will go through numerous different sql query selects, inserts and updates. The problem now we are looking into two solution
Send
1.Solution 1
For the socket connection build one thread and read the data and enqueue. Thenan other thread called is the dbprocessor to dequeue the data and do the processing of the different sql queries. The dbprocessor thread will just share on db connection for all messages being processed.
Solution 2.
The socket connection and both db processor are all in one single thread. For each socket connection open one db connection and keep processing the sql queries for each input data and finally close the db connection.
So which one is advice the share connection or each single d/b connection. We are using the share connection but at times the queue size becomes big and that worries us.
Dear Jcs,
When we use shared connection right if there is more then one thread just say using the same connection will both thread be able to process their queries simultaneously or it will be like one after another? This is what I am would like to know from here?
Dear Jurn,
What I do now in the dequeueing thread I normally create one single connection and it will be forever use for each of the queue coming in. The problem this is how I am doing is like below and the moment I tried to add extra new Thread(new DatabaseProcessor()).start(); but it gives me error when I saying it cannot find the db driver. How to solve it?
void main(){
new Thread(new DatabaseProcessor()).start();
}
class DatabaseProcessor implements Runnable {
public void run(){
createConnection();
while (true) {
message = databaseQueue.take();
//take the message and perform all the d/b queries here.
}
}
}
Only real way to do this is to do perf testing both ways. I would go for solution 2 on the surface – better to be able to scale out across multiple boxes and such. More than likely the single database connection will become a bottleneck real fast unless the message dequeing is real slow. Databases handle multiple connnections very gracefully. And really never worry about performance until you (a) have to and (b) have good metrics in place to understand performance.
Dear WWb,
I have done some improvement on the database on indexing and suddenly I find things are much faster then previously. So for a test I used method 1 with single connection and created just 20 threads for an experiment. So far things have been running smoothly. Will there be a major problem if I share a single connection? I would like to learn what happens when we shared connection is that can multiple statement run parallel or there is block happening?
Dear Jurn,
This is how it looks like . So what dangerous do you think will happen with sharing connection? What I worried individual connection is getting into the problem of too many connections.
void createConnection()
{
try
{
dbconn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?"+"user=****&password=****");
dbconn.setAutoCommit(false);
}
catch(Throwable ex)
{
System.out.println("MyError:SQLException has been caught for dbconn close");
ex.printStackTrace(System.out);
}
}
Dear Jurn,
Now the scope of dbconn is just within the single thread. The problem with single thread is that it the queue size gets bigger very fast. So now I have moved out to another design. Now I do not createConnection in the single thread but I create one globally in the main function and make it shared by 20 thread as I need more thread to counter the queue. Below is snippet of my new codes.So far now it is running smoothly what I worried is once I get more socket connections will this single db connection method give me problem?
public static void main(String args) {
new cServer9000();
}
cServer9000() { // default constructor
createConnection();
new Thread(new DatabaseProcessor()).start(); // put multiple 20 of this remove here due to limited space.
}
I think you may have problems later, that why I suggested a DB connection for each of your DatabaseProcessor threads.
I’d try and understand why the queue size grew if you assigned each DatabaseProcessor its own DB connection.
Is there contention between SQL they are running? (maybe you can see what they are doing if you run ‘show full processlist’ command in mysql)
Dear Jurn,
I am thinking of taking another design approach where for each socket connection make on db connection with that I can sure serialisation too right. What do you think?
Dear Jurn,
Sorry I have rephrase it now. What I do now is for each socket connection I first create a dbconnection. Then in the while loop I will keep receiving new data and for all the data I will this new dbconnection. Once everything is transferred the connection will be closed. So this is what I was trying to explain what is your opinion about this way off course is more costly need to open many connection but is safe right.
public class cServer
{
class ConnectionHandler implements Runnable {
ConnectionHandler(Socket receivedSocketConn1) {
this.receivedSocketConn1=receivedSocketConn1;
}
public void run(){
BufferedWriter w = null;
BufferedReader r = null;
w = new BufferedWriter(new OutputStreamWriter(receivedSocketConn1.getOutputStream()));
r = new BufferedReader(new InputStreamReader(receivedSocketConn1.getInputStream()));
String message=“”;
try {
createConnection();
while ((nextChar=r.read()) != -1) {
//read every single message and process it
}
closeConnection();
}
catch(Exception e){
e.printStackTrace(System.out);
}
}
}
public void main()
{
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);
}
}
Dear Jurn,
I know the drawback in this method but looks like this is the only best method taking into consideration of data integrity and future problem avoidance right.