使用 Hibernate 和 mysql 的连接过多
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10783547/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Too many connections using Hibernate and mysql
提问by Laxmikant Kumbhare
I am using Hibernate 3 and mysql sever 5.5 for myweb application with spring 3.0
我正在将 Hibernate 3 和 mysql sever 5.5 用于带有 spring 3.0 的 myweb 应用程序
I am getting exception as Too many connections......
由于连接太多，我遇到了异常......
My java file where I create session is as follows:
我创建会话的java文件如下：
public class DBConnection {
static{
}
public Session getSession(){
Session session = null;
SessionFactory sessionFactory= null;
sessionFactory = new Configuration().configure().buildSessionFactory();
session = sessionFactory.openSession();
return session;
}
}
and I call this method where I need session
我在需要会话的地方调用此方法
as
作为
Session session=new DBConnection().getSession();
and after
之后
transaction.commit();
I close session by using
我通过使用关闭会话
session.close();
please help me in solving problem.......
请帮我解决问题......
my hibernate.cfg.xml is :
我的 hibernate.cfg.xml 是：
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/dbname</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">lax</property>
<property name="hibernate.connection.pool_size">100</property>
<property name="show_sql">true</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.hbm2ddl.auto">update</property>
<property name=""></property>
<property name="hibernate.connection.release_mode">on_close</property>
</session-factory>
</hibernate-configuration>
回答by Love Hasija
This is because you are using a connection pool which got created as soon as you build SessionFactory, but the connections are acquired only when you open a Session. Now, you are closing the session, due to which connections are released, but are not closed and are held up by the pool. Now, you are again creating a SessionFactory, hence creating a new pool, then getting a session, hence creating a new connection and so on.. which will eventually reach the maximum number of connections allowed.
这是因为您使用的是在构建 SessionFactory 后立即创建的连接池，但仅在您打开会话时才获取连接。现在，您正在关闭会话，因为连接被释放，但没有关闭并被池阻止。现在，您再次创建一个 SessionFactory，因此创建一个新池，然后获取一个会话，从而创建一个新连接等等......最终将达到允许的最大连接数。
What you have to do is using one Connection Pool (using one SessionFactory) and getting and releasing the connections from the same pool.
您需要做的是使用一个连接池（使用一个 SessionFactory）并从同一个池中获取和释放连接。
public class DBConnection {
private static SessionFactory factory;
static {
factory = new Configuration().configure().buildSessionFactory();
}
public Session getSession() {
return factory.openSession();
}
public void doWork() {
Session session = getSession();
// do work.
session.close();
}
// Call this during shutdown
public static void close() {
factory.close();
}
}
回答by axtavt
You create new SessionFactorys every time you need a Sessionand don't close them.
SessionFactory每次需要 a 时都会创建新的sSession并且不要关闭它们。
Usually you need to create session factory only once during startup of your application, and close it during shutdown. For example, as follows:
通常您只需要在应用程序启动期间创建一次会话工厂，并在关闭期间关闭它。例如，如下：
public class DBConnection {
private static SessionFactory factory;
static {
factory = new Configuration().configure().buildSessionFactory();
}
public Session getSession() {
return factory.openSession();
}
// Call this during shutdown
public static void close() {
factory.close();
}
}
Also take a look at contextual sessions pattern.
另请查看上下文会话模式。
回答by Pramod
I have used
我用过了
Session session = SessionFactoryUtils.getSession(this.sessionFactory,
true);
to get session. Above the DAO layer, transaction with annotations (@Transactional) is used in add, edit or delete. Our application uses shiro for authentication and authorization. In each page access, it creates at least 5 connections. So the subsequent request keeps on increasing mysql connection. Thanks to @Venu I found out that
获取会话。在 DAO 层之上，带有注释的事务（@Transactional）用于添加、编辑或删除。我们的应用程序使用 shiro 进行身份验证和授权。在每个页面访问中，它至少创建 5 个连接。所以后续请求不断增加mysql连接。感谢@Venu 我发现
show variables like "max_connections";
shows default maximum connection available. Initially (I guess) it was set to 151. This number would soon exhaust because another variable "innodb_open_files" was set to 300. By intuition I can say if one table reaches maximum connection of 300 it would only go for clean up (just like garbage collection in java).
显示可用的默认最大连接数。最初（我猜）它被设置为 151。这个数字很快就会耗尽，因为另一个变量“innodb_open_files”被设置为 300。凭直觉我可以说如果一个表达到 300 的最大连接它只会去清理（就像java中的垃圾收集）。
One would think, if we would increase the value of "max_connections" to 300 it solve this error, but as I said earlier "innodb_open_files" does not go for cleanup until one table reaches that limit. In our case user table which would be called frequently would reach that limit for cleanup to happen. This means, you cannot know before hand what is the perfect number for "max_connections".
有人会认为，如果我们将“max_connections”的值增加到 300 就可以解决这个错误，但正如我之前所说，“innodb_open_files”在一张表达到该限制之前不会进行清理。在我们的例子中，频繁调用的用户表将达到清理发生的限制。这意味着，您无法事先知道“max_connections”的完美数字是多少。
In mysql configuration file "/etc/mysql/my.cnf" max_connections is set as
在mysql配置文件“/etc/mysql/my.cnf”中max_connections设置为
max_connections = 1000
I did not change
我没有改变
value of "innodb_open_files", which is default to "300"
“innodb_open_files”的值，默认为“300”
mysql> show variables like "innodb_open_files";
During execution of your program you can use
在程序执行期间，您可以使用
mysql> show processlist;
to find out how many connections are open. I guess all the processes in Sleep state can be deleted in next cycle of cleanup (but its only my guess).
找出有多少连接是打开的。我想所有处于睡眠状态的进程都可以在下一个清理周期中删除（但这只是我的猜测）。
To simulate more than 1000 users, executing some url in your web project, you can use software like jmeter and set number of thread (users) to 1000. Use of more user also depends upon available RAM in your computer. I was able to simulate more than 3000 connections sending requests at 1 second interval. A good and sufficient tutorial on how to do this is listed on roseindia
要模拟超过 1000 个用户，在您的 web 项目中执行一些 url，您可以使用 jmeter 之类的软件并将线程数（用户）设置为 1000。更多用户的使用还取决于您计算机中的可用 RAM。我能够模拟 3000 多个以 1 秒间隔发送请求的连接。Roseindia上列出了有关如何执行此操作的良好且足够的教程
Now my query. I have not closed session on any place. I guess @Transaction was created for that purpose (mysql proves that, because all those connections get cleaned once the limit reaches). Due to merge problem of detached objects, I had to used OpenSessionInViewFilter. OpenSessionInViewFilter (can be OpenSessionInViewInterceptor) opens the session a little longer for the merge to happen, which might be the reason so many connections remain open in mysql (Don't know clearly). Since it works for so many users it seems a good solution, but is there a better solution to make it work under default max connection size of mysql. i.e 151 connections.
现在我的查询。我没有在任何地方关闭会话。我猜 @Transaction 就是为此目的创建的（mysql 证明了这一点，因为一旦达到限制，所有这些连接都会被清除）。由于分离对象的合并问题，我不得不使用 OpenSessionInViewFilter。OpenSessionInViewFilter（可以是OpenSessionInViewInterceptor）打开会话的时间会更长一些，以便合并发生，这可能是mysql中有很多连接保持打开状态的原因（不清楚）。由于它适用于如此多的用户，因此它似乎是一个很好的解决方案，但是是否有更好的解决方案使其在 mysql 的默认最大连接大小下工作。即 151 个连接。
回答by Shubham Goel
I was facing the same issues with Hibernate mysql conenctions The following two things helped fix my connection problems
我在使用 Hibernate mysql conenctions 时遇到了同样的问题 以下两件事有助于解决我的连接问题
import javax.inject.Singleton;
@Singleton
public class DBConnection {
private static SessionFactory factory;
static public Session getSession() {
if (factory == null) {
System.out.println("--------New connection----");
factory = new Configuration().configure("hibernate.cfg.xml")
.addAnnotatedClass(SomeClass.class)
.buildSessionFactory();
}
//System.out.println("--------old connection----");
return factory.openSession();
}
// Call this during shutdown
public static void close() {
factory.close();
}
}
Create a singleton class for getting a DBConnection
创建一个用于获取 DBConnection 的单例类
Do not forget to perform session.close()when your query is complete
查询完成后不要忘记执行session.close()
Also in "hibernate.cfg.xml" add this line
同样在“hibernate.cfg.xml”中添加这一行
<hibernate-configuration>
<session-factory>
....
<property name="connection.pool_size">100</property>
....
</session-factory>
</hibernate-configuration>
<hibernate-configuration>
<session-factory>
....
<property name="connection.pool_size">100</property>
....
</session-factory>
</hibernate-configuration>
under
在下面
回答by Venu
JAVA ISSUE
Please check this answer : https://stackoverflow.com/a/10785770/598424
爪哇问题
请检查这个答案：https: //stackoverflow.com/a/10785770/598424
MYSQL ISSUE
MYSQL 问题
YOU NEED TO LOGIN AS SUPERUSER. THEN EXECUTE THIS CODE:
您需要以超级用户身份登录。然后执行此代码：
SET GLOBAL MAX_CONNECTIONS = 200;
HOWEVER, THIS ONLY LASTS UNTIL THE MYSQL SERVER RESTARTS. IF YOU WANT TO CHANGE IT PERMANENTLY, YOU CAN ADD THE FOLLOWING LIKE UNDER MYSQLD SECTION.
但是，这只会持续到 MYSQL SERVER 重新启动。如果您想永久更改它，您可以在 MYSQLD 部分下添加以下内容。
MAX_CONNECTIONS = 500
OR
或者
IF YOU HAVE TOO MANY PROCESSES LINGERING AROUND ON THE DATABASE SERVER, THEN YOU MAY REACH A LIMIT AND NO NEW ONES WILL BE ALLOWED TO BE STARTED.
如果您有太多进程在数据库服务器上徘徊，那么您可能会达到一个限制，并且不允许启动新的进程。
To see if this is the case, take a look at your processes from the MySQL Monitor prompt by typing:
要查看是否是这种情况，请通过键入以下内容从 MySQL Monitor 提示符查看您的进程：
show processlist;
Then you will see the lingering processes. Each has an ID, and you can kill them by process ID right in the MySQL Monitor by typing:
然后你会看到挥之不去的过程。每个都有一个 ID，您可以在 MySQL Monitor 中通过输入以下命令通过进程 ID 杀死它们：
kill 2309344;
