SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Shared connection or individual connection?

    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.

    2. 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.

  2. #2
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would say a shared connection to prevent too many connections at once but as you said the queue could get too big so I am clueless.

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If your dequeueing is the bottleneck, then add more dequeuing threads?

  4. #4
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    }
    }
    }

  6. #6
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  8. #8
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what is inside your "createConnection()" method?

    you can also consider connection pooling (e.g. commons-dbcp / proxool / c3p0) instead of create a new connection each time.

  9. #9
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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);
    }

    }

  10. #10
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what is the scope of 'dbconn'?

    If you limit the number of threads, and give each thread 1 DB connection, then the number of MySQL connections will also be limited.

  11. #11
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    }

  12. #12
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  13. #13
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  14. #14
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry, I don't understand the question... could you rephrase it?

  15. #15
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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);
    }

    }
    }

  16. #16
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yep that is safe, as long as your server can cope with opening/closing DB connection time you should be ok

  17. #17
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •