我自己写的jdbc连接mysql,用了好久好久了,昨天重启了mysql服务器后,现在访问mysql好慢,几次都失败

知行合一1 发布于 2016/06/21 17:05
阅读 1K+
收藏 1
 
我自己写的jdbc连接mysql,用了好久好久了,昨天重启了mysql服务器后,现在访问mysql好慢,几次都失败

之前一直都好好的,而且还是做数据量特别大,sql特别灵活的查询,啥问题都没有,现在一个简单的查询都很慢,连接创建失败,

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
 



import java.util.concurrent.ConcurrentHashMap;

import com.fz.util.Global;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
 
public class MySQLPool {
    private static volatile MySQLPool pool;
    private MysqlDataSource ds;
    private Map<Connection, Boolean> map;
    
    private static   String url = Global.getConfig("jdbc.url");
    private static	 String username = Global.getConfig("jdbc.username");
    private static  String password = Global.getConfig("jdbc.password");
  
    private int initPoolSize = 100;
    private int maxPoolSize = 1000;
    private int waitTime = 10000;
     
    private MySQLPool() {
        init();
    }
     
    public static MySQLPool getInstance() {
        if (pool == null) {
            synchronized (MySQLPool.class) {
                if(pool == null) {
                    pool = new MySQLPool();
                }
            }
        }
        return pool;
    }
     
    private void init() {
        try {
            ds = new MysqlDataSource();
            ds.setUrl(url);
            ds.setUser(username);
            ds.setPassword(password);
            ds.setCacheCallableStmts(true);
            ds.setConnectTimeout(1000);
            ds.setLoginTimeout(2000);
            ds.setUseUnicode(true);
            ds.setEncoding("UTF-8");
            ds.setZeroDateTimeBehavior("convertToNull");
            ds.setMaxReconnects(5);
            ds.setAutoReconnect(true);
            map = new ConcurrentHashMap<Connection, Boolean>(initPoolSize);

            for (int i = 0; i < initPoolSize; i++) {
                map.put(getNewConnection(), true);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
     
    public Connection getNewConnection() {
        try {
            return ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
     
    public synchronized Connection getConnection() {
        Connection conn = null;
        try {
            for (Entry<Connection, Boolean> entry : map.entrySet()) {
                if (entry.getValue()) {
                    conn = entry.getKey();
                    map.put(conn, false);
                    break;
                }
            }
            if (conn == null) {
                if (map.size() < maxPoolSize) {
                    conn = getNewConnection();
                    map.put(conn, false);
                } else {
                    wait(waitTime);
                    conn = getConnection();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    
/*    public synchronized  Statement createSta(Statement stmt) throws Exception {
        Connection conn = pool.getConnection();
        try {
            if (stmt == null) {
            	stmt=conn.createStatement();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return stmt;
    }*/
     
    public void releaseConnection(Connection conn  ,Statement stmt ,ResultSet rs) {
        if (conn == null) {
            return;
        }
        try {
        	if(rs!=null){
    			try {
    				rs.close();
    			} catch (Exception e2) {
    				// TODO: handle exception
    				e2.printStackTrace();
    			}
    			rs=null;
    		}
    		
    		if(stmt!=null){
    			try {
    				stmt.close();
    			} catch (Exception e2) {
    				// TODO: handle exception
    				e2.printStackTrace();
    			}
    			stmt=null;
    		}
        	
            if(map.containsKey(conn)) {
                if (conn.isClosed()) {
                    map.remove(conn);
                } else {
                    if(!conn.getAutoCommit()) {
                        conn.setAutoCommit(true);
                    }
                    map.put(conn, true);
                }
            } else {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
 
/**
 * 测试类
 */



com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 5 times. Giving up.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
	at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2385)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.GeneratedConstructorAccessor4.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:443)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:141)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:111)
	at cn.qjj.mysql.MySQLPool.getNewConnection(MySQLPool.java:72)
	at cn.qjj.mysql.MySQLPool.init(MySQLPool.java:63)
	at cn.qjj.mysql.MySQLPool.<init>(MySQLPool.java:32)
	at cn.qjj.mysql.MySQLPool.getInstance(MySQLPool.java:39)
	at cn.qjj.mysql.SqlUtils.<clinit>(SqlUtils.java:32)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1112)
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2483)
	at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2324)
	... 17 more
java.lang.NullPointerException
	at java.util.concurrent.ConcurrentHashMap.putVal(ConcurrentHashMap.java:1011)
	at java.util.concurrent.ConcurrentHashMap.put(ConcurrentHashMap.java:1006)
	at cn.qjj.mysql.MySQLPool.init(MySQLPool.java:63)
	at cn.qjj.mysql.MySQLPool.<init>(MySQLPool.java:32)
	at cn.qjj.mysql.MySQLPool.getInstance(MySQLPool.java:39)
	at cn.qjj.mysql.SqlUtils.<clinit>(SqlUtils.java:32)



加载中
0
Ambitor
Ambitor
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server:"Too many connections"
0
inmyfree
inmyfree

log已经描述的很明白了

Data source rejected establishment of connection, message from server:"Too many connections"

create connection to database server. Attempted reconnect5 times. Giving up.

mysql的链接太多了,没有获取到连接,尝试5次后就放弃了

猜测可能是mysql配置的最大链接过小,或者你的连接池管理有问题,没有释放之前的链接




ahdkk
ahdkk
他是自己写的jdbc,没用连接池
0
知行合一1
知行合一1

经过,我大脑冷却和思考之后,我把连接池的改成这样

只是初始化3个连接, 这样速度就可以大大提高了,连接池初始化耗时间,大数据的时候再该回去。。。

    private int initPoolSize = 3;
    private int maxPoolSize = 10;
    private int waitTime = 1000;

0
zheng_pat
zheng_pat
为啥不用开源的?连接池
知行合一1
知行合一1
不方便,不一定要用呀,跑个小demo,做点统计啥的
不日小鸡
为什么要用?
0
败家老头☞起个网名真难
败家老头☞起个网名真难
‍以后别用这种连接方式了,处理太麻烦,直接用连接池处理,或者用ibatis试试。
0
尚浩宇
尚浩宇
你navicat看下连接,是你程序连接太多还是其他连接太多,要是程序,你就看看你配置的连接数和数据库配置的最大连接数
0
灰机的灰
灰机的灰
在你的连接池的某些逻辑中,是否有catch到异常后在finally块中对资源进行释放的操作?
知行合一1
知行合一1
谢谢
返回顶部
顶部