JAVA MYSQL chat performance issue with 100 users

i'm trying to develop a client-server chat application using java servlets and mysql(innoDB engine) and jetty server. i tested the connection code with 100 simulated users hitting the server at once using jmeter but i got 40 secs as average time :( for all of them to get connected with min time taken by thread( 2 secs ) and max time( 80 secs). My connection database table has the followng structure two columns connect(user,stranger) and my servlet code is shown below.I'm using innoDB engine for row level locking.I also used explicit write lock SELECT...... FOR UPDATE inside transaction.I'm looping the transaction if it rollbacks due to deadlock until it executes atleast once.Once two users get connected they update their stranger's column with eachother's randomly generated unique number.

i'm using c3p0 connection pooling with min 100 threads open and jetty with min 100 threads. please help me to identify the bottle necks or tools needed to find them.

 import java.io.*;
 import java.util.*;
 import java.sql.*;
 import javax.servlet.ServletException;
 import javax.servlet.http.HttpServlet;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 import javax.naming.*; 
 import javax.sql.*;



public class connect extends HttpServlet {

public void doGet(HttpServletRequest req, HttpServletResponse res)
throws java.io.IOException {

String unumber=null;
String snumber=null;

String status=null;
InitialContext contxt1=null;
DataSource ds1=null;
Connection conxn1=null;
PreparedStatement stmt1=null;
ResultSet rs1=null;
PreparedStatement stmt2=null;
InitialContext contxt3=null;
DataSource ds3=null;
Connection conxn3=null;
PreparedStatement stmt3=null;
ResultSet rs3=null;
PreparedStatement stmt4=null;
ResultSet rs4=null;
PreparedStatement stmt5=null;
boolean checktransaction = true;


unumber=req.getParameter("number");       // GET THE USER's UNIQUE NUMBER 

try {
contxt1 = new InitialContext();
ds1 =(DataSource)contxt1.lookup("java:comp/env/jdbc/user");
conxn1 = ds1.getConnection(); 

stmt1 = conxn1.prepareStatement("SELECT * FROM profiles WHERE number=?");   //    GETTING USER DATA FROM PROFILE
stmt1.setString(1,unumber);
rs1 = stmt1.executeQuery();

if(rs1.next()) {
res.getWriter().println("user found in PROFILE table.........");
uage=rs1.getString("age");
usex=rs1.getString("sex");
ulocation=rs1.getString("location");
uaslmode=rs1.getString("aslmode");
stmt1.close();
stmt1=null;
conxn1.close();
conxn1 = null;

contxt3 = new InitialContext();
ds3 =(DataSource)contxt3.lookup("java:comp/env/jdbc/chat");

conxn3 = ds3.getConnection(); 
conxn3.setAutoCommit(false);

while(checktransaction) {

  // TRANSACTION STARTS HERE
try {

stmt2 = conxn3.prepareStatement("INSERT INTO "+ulocation+" (user,stranger) VALUES (?,'')");  //  INSERTING RECORD INTO LOCAL CHAT TABLE
stmt2.setString(1,unumber);
stmt2.executeUpdate();

stmt2.close();
stmt2 = null;
res.getWriter().println("inserting row into LOCAL CHAT TABLE........."); 

System.out.println("transaction starting........."+unumber);


stmt3 = conxn3.prepareStatement("SELECT user FROM "+ulocation+" WHERE (stranger='' && user!=?) LIMIT 1 FOR UPDATE");
stmt3.setString(1,unumber);                                            //   SEARCHING FOR STRANGER
  rs3=stmt3.executeQuery();

if (rs3.next()) {                // stranger found   

stmt4 = conxn3.prepareStatement("SELECT stranger FROM "+ulocation+" WHERE user=?");  
stmt4.setString(1,unumber);                      //CHECKING FOR USER STATUS BEFORE CONNECTING TO STRANGER
rs4=stmt4.executeQuery();

if(rs4.next()) {
 status=rs4.getString("stranger");
}
stmt4.close();
stmt4=null;

if(status.equals("")) {           // user status is also null
snumber = rs3.getString("user");

stmt5 = conxn3.prepareStatement("UPDATE "+ulocation+" SET stranger=? WHERE user=?"); // CONNECTING USER AND STRANGER
stmt5.setString(1,snumber);
stmt5.setString(2,unumber);
stmt5.executeUpdate();

stmt5.setString(2,snumber);
stmt5.setString(1,unumber);
stmt5.executeUpdate();

stmt5.close开发者_JS百科();
stmt5=null;
}
}         // end of stranger found

stmt3.close();
stmt3 = null;

conxn3.commit();     // TRANSACTION ENDING

checktransaction = false;
}  // END OF TRY INSIDE WHILE
catch(java.sql.SQLTransactionRollbackException e) {
System.out.println("transaction restarted......."+unumber);
counttransaction = counttransaction+1;

}
}          //END OF WHILE LOOP    
conxn3.close();                
conxn3 = null;
}        //  END OF USER FOUND IN PROFILE TABLE

}  // end of try

catch(java.sql.SQLException sqlexe) {

try {conxn3.rollback();}
catch(java.sql.SQLException exe) {conxn3=null;}
sqlexe.printStackTrace();
res.getWriter().println("UNABE TO GET CONNECTION FROM POOL!");

}
catch(javax.naming.NamingException namexe) {
namexe.printStackTrace();
res.getWriter().println("DATA SOURCE LOOK UP FAILED!");
}

}
}

---

How many users do you have? Can you load them all into memory first and do a memory lookup? If you separate you DB layer from your presentation layer, this is something you can change without changing the servlet (as it shouldn't care where the data comes from)

If you use Java memory it shouldn't take more than a 20 ms per user.

---

Here is a test which creates one million profiles in memory, looks them up and creates chat entries, which is removed later. The average time per operation was 640 ns (nano-seconds, or billionths of a second)

import java.util.LinkedHashMap;
import java.util.Map;

public class Main {
    public static void main(String... args) {
        UserDB userDB = new UserDB();
        // add 1000,000 users
        for (int i = 0; i < 1000000; i++)
            userDB.addUser(
                    new Profile(i,
                            "user+i",
                            (short) (18 + i % 90),
                            i % 2 == 0 ? Profile.Sex.Male : Profile.Sex.Female,
                            "here", "mode"));
        // lookup a users and add a chat session.
        long start = System.nanoTime();

        int operations = 0;
        for(int i=0;i<userDB.profileCount();i+=2) {
            Profile p0 = userDB.getProfileByNumber(i);
            operations++;
            Profile p1 = userDB.getProfileByNumber(i+1);
            operations++;
            userDB.chatsTo(i, i+1);
            operations++;
        }
        for(int i=0;i<userDB.profileCount();i+=2) {
            userDB.endChat(i);
            operations++;
        }
        long time = System.nanoTime() -start;
        System.out.printf("Average lookup and update time per operation was %d ns%n", time/operations);
    }
}

class UserDB {
    private final Map<Long, Profile> profileMap = new LinkedHashMap<Long, Profile>();
    private final Map<Long, Long> chatsWith = new LinkedHashMap<Long, Long>();

    public void addUser(Profile profile) {
        profileMap.put(profile.number, profile);
    }

    public Profile getProfileByNumber(long number) {
        return profileMap.get(number);
    }

    public void chatsTo(long number1, long number2) {
        chatsWith.put(number1, number2);
        chatsWith.put(number2, number1);
    }

    public void endChat(long number) {
        Long other = chatsWith.get(number);
        if (other == null) return;
        Long number2 = chatsWith.get(other);
        if (number2 != null && number2 == number)
            chatsWith.remove(other);
    }

    public int profileCount() {
        return profileMap.size();
    }
}

class Profile {
    final long number;
    final String name;
    final short age;
    final Sex sex;
    final String location;
    final String aslmode;

    Profile(long number, String name, short age, Sex sex, String location, String aslmode) {
        this.number = number;
        this.name = name;
        this.age = age;
        this.sex = sex;
        this.location = location;
        this.aslmode = aslmode;
    }

    enum Sex {Male, Female}

}

prints

Average lookup and update time per operation was 636 ns

If you need this to be faster you could look at using Trove4j which could be twice as fast in this case. Given this is likely to be fast enough, I would try to keep things simple.

---

Have you considered caching reads and batching writes?

---

I'm not sure how you can realistically expect anyone to determine where the bottle-necks are by merely looking at the source code.

To find the bottlenecks, you should run your app and the load test with a profiler attached, such as JVisualVM or YourKit or JProfiler. This will tell you exactly how much time is spent in each area of the code.

The only thing that anyone can really critique from looking at your code is the basic architecture:

• Why are you looking up the DataSource on each doGet()?
• Why are you using transactions for what appears to be unrelated database insertions and queries?
• Is using a RDBMS to back a chat system really the best idea in the first place?

---

If your response times are so high, you need to properly index your db tables. Based on the times you provided I will assume this was not done.You need to speed up your read and writes.

Look up Execution Plans and how to read them. An execution plan will show you if/when indexes are being used with your queries; if you are performing seeks or scans etc on the tables. by using these, you can tweak your query/indexes/tables to be more optimal.

As others have stated, RDMS wont be your best option in large scale applications, but since you are just starting out it should be ok until you learn more.

Learn to properly setup those tables and you should see your deadlock counts and response times go down