代码之家 › 专栏 › 技术社区 › user8012596

Java和mysql高并发瓶颈解决方案[已关闭]

mysql java

user8012596 · 技术社区 · 9 年前

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);

          }

    }
}

2 回复 | 直到 9 年前

gati sahu 9 年前

一个问题是,如果设备数量增加,每个设备都会尝试连接db,这不是可扩展的解决方案。一种方法是异步地逐批处理。将所有消息存储在队列中,直到达到批大小,然后作为批插入。它将保存网络,因为您没有逐个插入记录。

Andrew S 9 年前

从评论转移到这里来解释更多。

dbconn = connectionPool.getConnection();
...
while (...) {
   // dbconn is held for up to 60 seconds which is not scalable
}

while (...) {
    // got some more data to process - now get a dbConn 
    dbconn = connectionPool.getConnection();
    // do inserts, etc.
    // commit and release the connection!
}

另一种方法是通过发布到队列或调用REST服务来完全卸载与数据库的交互。

while (...) {
    // got some more data to process
    // publish the data to a JMS queue
    // or post the data to a REST endpoint
    // there is no database interaction here at all!
}

然后,消息使用者或REST端点处理数据并插入/更新数据库。这些消息/请求中的每一个都很小,因此从池中借用db连接的时间只有几毫秒(最多)。处理JMS消息/POST请求的服务器集群可以独立于最初接收原始套接字数据的服务器进行扩展。