背景：我正在使用hibernate和我的模型层的sql以及spring来公开REST服务。我有一个移动客户端，它在后端使用rest服务。该代码目前部署在AWS微实例上。
问题：当并发用户的数量超过30-40个时，我开始得到一个“com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException:连接太多”。我知道micro-istance上的mysql-db被配置为最多有30-35个连接，但在负载较高的情况下，我希望调用等待而不会出错。变慢一点是可以接受的，但不应该出错。
代码：这里是hibernate cfg文件：
com.jolbox.bonecp.provider.BoneCPConnectionProvider
org.hibernate.dialect.mysql方言com.mysql.jdbc.Driver
<property name="bonecp.driverClass">com.mysql.jdbc.Driver</property>
<property name="bonecp.jdbcUrl">jdbc:mysql://***.rds.amazonaws.com:3306/jooky</property>
<property name="bonecp.username">**</property>
<property name="bonecp.password">**</property>
<property name="bonecp.maxConnectionAgeInSeconds">30</property>
<property name="bonecp.idleConnectionTestPeriodInMinutes">60</property>
<property name="bonecp.maxConnectionsPerPartition">25</property>
<property name="bonecp.minConnectionsPerPartition">10</property>
<property name="bonecp.partitionCount">1</property>
<property name="bonecp.acquireIncrement">5</property>
<property name="bonecp.statementsCacheSize">50</property>
<property name="acquireRetryDelayInMs">500</property>
<property name="acquireRetryAttempts">200</property>
<mapping class="jooky.model.entity.Party" />
<mapping class="jooky.model.entity.LocationRequest" />
<mapping class="jooky.model.entity.Location" />
<mapping class="jooky.model.entity.UserCheckinHistory" />
<mapping class="jooky.model.entity.Person" />
<mapping class="jooky.model.entity.UserLocSearchHistory"/>
</session-factory>
我必须公开 REST 服务的代码是：
@RequestMapping(value = "/getLocations", method = RequestMethod.POST)
public @ResponseBody
String getLocations(@RequestBody String input) {
Gson gson = new GsonBuilder().create();
NearbyLocationsRequest request = gson.fromJson(input,
NearbyLocationsRequest.class);
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
//SessionFactory sessionFactory = ControllerUtil.getSessionFactory();
Session session = sessionFactory.openSession();
session.beginTransaction();
String ret="Error:UNKNOWN_EXCEPTION: Server error.";
List results = null;
try {
// NearbyLocationQueryInput inputGson = gson.fromJson(input,
// NearbyLocationQueryInput.class);
if(request.getPersonId()== null )
{
ret="Error:CANNOT_CONNECT_PER_ID_NULL: Person Id cannot be null";
session.getTransaction().rollback();
session.close();
sessionFactory.close();
return ret;
}
List users = session
.createCriteria(Person.class)
.add(Restrictions.eq("personId", request.getPersonId()))
.list();
Person person = null;
if (users != null && users.size() > 0) {
person = (Person) users.get(0);
if(person.getPoints()==null)
person.setPoints(0);
}
else
{
ret="Error:CANNOT_CONNECT_PER_NOT_FOUND: No person found with id:"+request.getPersonId();
session.getTransaction().rollback();
session.close();
sessionFactory.close();
return ret;
}
double latitude = request.getLatitude();
double longitude = request.getLongitude();
// To search by kilometers instead of miles, replace 3959 with 6371
int distConstant = 6371;
if ("MILES".equals(request.getDistanceUnit()))
distConstant = 3959;
int queryRadius = 100;
if (request.getQueryRadius() != null)
queryRadius = request.getQueryRadius();
int maxResults = 100;
if (request.getMaxResults() != null)
maxResults = request.getMaxResults();
// latitude = input.getLatitude();
// longitude = input.getLongitude();
// Closest within radius of 25 Miles
Query query = session
.createSQLQuery(
" SELECT LOCATION_ID, LOCATION_NAME, LOC_FB_ID, LOC_GOOGLE_ID, LOCATION_EMAIL, FB_PAGE, IMAGE_LOCATION, "
+ " ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, COUNTRY, ZIP, PHONE1, PHONE2, "
+ " PLAYLIST_NAME, PLAYER_STATUS, LOCATION_TYPE, CUSINE, LATITUDE, LONGITUDE, OPENING_TIME, CLOSING_TIME, HEARTBEAT_TIME, PLAYLIST_VERSION, "
+ " LOCATION_MESSAGE, WEBSITE, HIGHLIGHTS , "
+ " ( "
+ distConstant
+ " * acos( cos( radians(:bind_lat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(:bind_long) ) + sin( radians(:bind_lat) ) * sin( radians( latitude ) ) ) ) AS DISTANCE "
+ " FROM location HAVING distance < "
+ queryRadius
+ " "
+ " ORDER BY distance LIMIT " + maxResults)
.addEntity(Location.class);
query.setParameter("bind_long", longitude);
query.setParameter("bind_lat", latitude);
results = query.list();
UserLocSearchHistory history = new UserLocSearchHistory();
history.setId(new UserLocSearchHistoryId(request.getPersonId(), new Date()));
history.setAppVersion(request.getAppVersion());
history.setLatitude(new BigDecimal(latitude));
history.setLongitude(new BigDecimal(longitude));
session.save(history);
NearbyLocationsResponse response = new NearbyLocationsResponse();
response.setUserPoints(person.getPoints());
response.setLocations(results);
session.getTransaction().commit();
session.flush();
session.close();
sessionFactory.close();
ret=gson.toJson(response);
} catch (Exception e) {
session.getTransaction().rollback();
session.flush();
session.close();
sessionFactory.close();
e.printStackTrace();
}
return ret;
}
错误堆栈：HTTP 状态 500 - 请求处理失败;嵌套异常是 org.hibernate.HibernateException： org.hibernate.HibernateException： java.sql.SQLException： 无法打开与给定数据库的测试连接。JDBC url = jdbc：mysql：//***.rds.amazonaws.com：3306/jooky， username = jooky.正在终止连接池。原始异常：------类型异常报告
“消息”请求处理失败；嵌套异常为org . hibernate . hibernate exception:org . hibernate . hibernate exception:Java . SQL . sqlexception:无法打开到给定数据库的测试连接。JDBC URL = JDBC:MySQL://* * * * . Amazon AWS . com:3306/jooky，username = ****。正在终止连接池。原始异常:-
< b >描述服务器遇到一个内部错误，使其无法完成此请求。
例外情况
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.HibernateException: org.hibernate.HibernateException: java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://*****.com:3306/jooky, username = jooky. Terminating connection pool. Original Exception: ------
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Too many connections
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:921)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:885)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3421)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1247)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775)
at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:309)
at com.jolbox.bonecp.BoneCP.<init>(BoneCP.java:346)
at com.jolbox.bonecp.provider.BoneCPConnectionProvider.createPool(BoneCPConnectionProvider.java:167)
at com.jolbox.bonecp.provider.BoneCPConnectionProvider.configure(BoneCPConnectionProvider.java:141)
at com.jolbox.bonecp.provider.BoneCPConnectionProvider.configure(BoneCPConnectionProvider.java:271)
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:89)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:206)org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:973)
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
根本原因org. hibernateHibernateException：org.hibernateHibernateException：java.sql.SQLException：无法打开到给定数据库的测试连接。JDBC url=jdbc：mysql://aa1hw2wctsxa60n.ckmpnrdgeoee.ap-southeast-1.rds.amazonaws.com:3306/jooky，用户名=jooky。终止连接池。原始异常: ------ com.mysql.jdbc.例外。MySQLNonTranentConnectionException：com.mysql.jdbc.SQLEror.createSQLException（SQLError.java:921）at com.mysql.jdbc.MysqlIO.checkErrorPket（MysqlIO.java:2985）at com.mysql.jdbc.MysqlIO.secureAuth411（MysqlIO.java:3421）at com.mysql.jdbc.MysqlIO.doHandshag（MysqlIO.java:1247）at com.mysql.jdbc.MysqlIO.checkErrorPket（MysqlIO.java:885）at com.mysql.jdbc.Mys
这是由于 mysql 连接限制。要删除此类错误/异常，您需要在 mysql 中增加max_connection变量。
