What is a good tool for the investigation of Database Connection usage in Java?
什么是研究Java中数据库连接使用情况的好工具?
A developer is supporting a complex Java program which is ocassionally exhausting the number of Database connections available. Since the problem is sporadic it would be useful to know which thread has opened multiple connections to the database to focus the effort in this area.
开发人员正在支持一个复杂的Java程序，它偶尔会耗尽可用的数据库连接的数量。由于这个问题是零星的，所以知道哪个线程已经打开了数据库的多个连接以集中精力在这个领域是很有用的。
In the end, the correct fix seems to be to rewrite the program to reuse connections and not open multiple connections per thread.
最后，正确的解决方案似乎是重写程序以重用连接，而不是每个线程打开多个连接。
I am asking, what tools should the developer have in his tool box to be able to investigate the resources i.e. Database Connections that have been allocated by a thread.
我想问的是，开发人员应该在他的工具箱中有什么工具来研究资源，例如，由线程分配的数据库连接。
5 个解决方案
#1
3
Not a specific tool, but rather a debugging technique for tracking down which code is responsible for open connections or other resources.
不是一个特定的工具，而是跟踪哪个代码负责打开连接或其他资源的调试技术。
I am assuming you are using a consistent method on the java side to get a db connection (pooled or not doesn't matter).
我假设您在java端使用一致的方法来获得db连接(池化与否并不重要)。
The idea is to create a very light wrapper class around your connection factory/pool or whatever it is. The wrapper will implement whatever jdbc interface makes sense so you can swap it in for your normal connection object but most methods will just transparently call/return the underlying connection.
其思想是围绕您的连接工厂/池或它是什么创建一个非常轻的包装类。包装器将实现任何jdbc接口，因此您可以将其替换为正常的连接对象，但是大多数方法将透明地调用/返回底层连接。
If you are using some sort of IoC framework (e.g. spring) you should be able to easily swap out the connection/factory class at a config level. Now all your java code will be using your new db connection wrapper.
如果您正在使用某种IoC框架(例如spring)，您应该能够在配置级别轻松地交换连接/工厂类。现在，所有java代码都将使用新的db连接包装器。
If you are using a pool, then calling connection.close() usually just returns the object to the pool instead of destroying the connection. So this technique works for normal connection leak or just "not returned to pool (pool exhausted)" leak.
如果您正在使用一个池，那么调用connection.close()通常只是将对象返回到池中，而不是破坏连接。因此，该技术适用于正常的连接泄漏或“未返回到池(池已耗尽)”泄漏。
Now we just need to log the interesting bits and set a trap for leaked connections.
现在我们只需要记录有趣的数据，并为泄漏的连接设置一个陷阱。
Stack trace to identify creator
In the constructor or factory method for your connection wrapper create a new Throwable object and store it as a local variable within your wrapper for later. We use a Throwable because it is faster/cheaper than using Thread.currentThread().getStackTrace().
在连接包装器的构造函数或工厂方法中，创建一个新的可抛出对象，并将其作为本地变量存储在包装器中，以便以后使用。我们使用一次性的，因为它比使用Thread.currentThread(). getstacktrace()更快/更便宜。
Set the "trap"
Implement the finally method in your wrapper class. This is a cleanup method called by the GC when the object is being destroyed because it is no longer used.
在包装器类中实现最后的方法。这是GC在对象被销毁时调用的清理方法，因为它不再被使用。
The finally method should check "am I closed?". If already closed, then everything is fine... however if the connection is being GCed and it hasn't been closed... then this is a "leaked" connection.
最后的方法应该检查“我关闭了吗?”如果已经关闭，那么一切都没问题……但是如果连接是GCed并且它还没有被关闭…这是一个“泄露”的连接。
Now the Throwable comes back into play. We can grab the Throwable and output a nice log message saying something like: "I'm a leaked connection and here is a stack trace implicating my creator."
现在，可扔的东西又回来了。我们可以获取可抛出的日志，并输出一个很好的日志消息，比如:“我是一个泄漏的连接，这里有一个堆栈跟踪，它指示我的创建者。”
Expanding the idea
This method can be adapted for a variety of situations. You can of course keep other types of data in your wrapper for troubleshooting your specific issue. For instance creation time. Then you can poll for long-lived connections and again implicate the creator. Or you can poll existing connections and parse the Throwable stack traces to get data on which code is using how many connections over time.
这种方法适用于各种情况。当然，您可以将其他类型的数据保存在包装器中，以解决您的特定问题。例如创建时间。然后，您可以轮询长期存在的连接，并再次暗示创建者。或者，您可以轮询现有的连接，并解析可抛出的堆栈跟踪，以获取数据，其中的代码在一段时间内使用了多少连接。
There is probably an off-the-shelf tool that can also do these types of things, but the amount of code required to apply this technique is very minimal in most cases (assuming you have an easy way to swap our your db connection factory without search-replacing your whole codebase).
可能有一种现成的工具也可以做这些事情，但是在大多数情况下，应用这种技术所需的代码量非常小(假设您有一种简单的方法来交换我们的db连接工厂，而无需搜索替换整个代码基)。
#2
7
Have a look at log4jdbc. It enables you to have a look at all stuff going over your jdbc, including opening/closing connections as well as connection number information.
看看log4jdbc。它允许您查看jdbc上的所有内容，包括打开/关闭连接以及连接编号信息。
#3
4
Someone showed me ConnLeakFinder recently, "a simple tool to pinpoint jdbc connection leaks in java code". I haven't tested it myself so far but it should allow you To see who did not close the connection after use. See Connection+Leak+How+To+Find.htm.
最近有人向我展示了ConnLeakFinder，“一个在java代码中精确定位jdbc连接泄漏的简单工具”。到目前为止我还没有对它进行测试，但是它应该可以让您看到谁在使用后没有关闭连接。看到连接+泄漏+有+ + Find.htm。
But indeed, you should conslder using a connection pool (for example c3p0).
但是实际上，您应该使用连接池(例如c3p0)进行conslder。
#4
1
Connection pools can give you some diagnostics. For example check out debugUnreturnedConnectionStackTraces property for C3P0 connection pool:
连接池可以提供一些诊断。例如，检查C3P0连接池的debugunreturnedconnectionstacktrace属性:
http://www.mchange.com/projects/c3p0/index.html#debugUnreturnedConnectionStackTraces
http://www.mchange.com/projects/c3p0/index.html debugUnreturnedConnectionStackTraces
#5
0
P6Spy is an open source framework to support applications that intercept and optionally modify database statements.
P6Spy是一个开源框架，它支持拦截和修改数据库语句的应用程序。
From http://www.p6spy.com/about.html
The P6Spy distribution includes the following modules:
从http://www.p6spy.com/about.html, P6Spy发行版包含以下模块:
- P6Log. P6Log intercepts and logs the database statements of any application that uses JDBC. This application is particularly useful for developers to monitor the SQL statements produced by EJB servers, enabling the developer to write code that achieves maximum efficiency on the server. P6Spy is designed to be installed in minutes and requires no code changes.
- P6Log。P6Log拦截并记录任何使用JDBC的应用程序的数据库语句。这个应用程序对于开发人员监视EJB服务器生成的SQL语句特别有用，使开发人员能够编写在服务器上实现最大效率的代码。P6Spy被设计成在几分钟内安装，不需要修改代码。
- P6Outage. P6Outage detects long-running statements that may be indicative of a database outage proble and will log any statement that surpasses the configurable time boundary during its execution. P6Outage was designed to minimize any logging performance penalty by logging only long running statements.
- P6Outage。p6宕机检测长时间运行的语句，这些语句可能指示数据库停机，并将记录在执行过程中超过可配置时间边界的任何语句。p6中断被设计为通过只记录长时间运行的语句来最小化日志记录性能损失。
#1
3
Not a specific tool, but rather a debugging technique for tracking down which code is responsible for open connections or other resources.
不是一个特定的工具，而是跟踪哪个代码负责打开连接或其他资源的调试技术。
I am assuming you are using a consistent method on the java side to get a db connection (pooled or not doesn't matter).
我假设您在java端使用一致的方法来获得db连接(池化与否并不重要)。
The idea is to create a very light wrapper class around your connection factory/pool or whatever it is. The wrapper will implement whatever jdbc interface makes sense so you can swap it in for your normal connection object but most methods will just transparently call/return the underlying connection.
其思想是围绕您的连接工厂/池或它是什么创建一个非常轻的包装类。包装器将实现任何jdbc接口，因此您可以将其替换为正常的连接对象，但是大多数方法将透明地调用/返回底层连接。
If you are using some sort of IoC framework (e.g. spring) you should be able to easily swap out the connection/factory class at a config level. Now all your java code will be using your new db connection wrapper.
如果您正在使用某种IoC框架(例如spring)，您应该能够在配置级别轻松地交换连接/工厂类。现在，所有java代码都将使用新的db连接包装器。
If you are using a pool, then calling connection.close() usually just returns the object to the pool instead of destroying the connection. So this technique works for normal connection leak or just "not returned to pool (pool exhausted)" leak.
如果您正在使用一个池，那么调用connection.close()通常只是将对象返回到池中，而不是破坏连接。因此，该技术适用于正常的连接泄漏或“未返回到池(池已耗尽)”泄漏。
Now we just need to log the interesting bits and set a trap for leaked connections.
现在我们只需要记录有趣的数据，并为泄漏的连接设置一个陷阱。
Stack trace to identify creator
In the constructor or factory method for your connection wrapper create a new Throwable object and store it as a local variable within your wrapper for later. We use a Throwable because it is faster/cheaper than using Thread.currentThread().getStackTrace().
在连接包装器的构造函数或工厂方法中，创建一个新的可抛出对象，并将其作为本地变量存储在包装器中，以便以后使用。我们使用一次性的，因为它比使用Thread.currentThread(). getstacktrace()更快/更便宜。
Set the "trap"
Implement the finally method in your wrapper class. This is a cleanup method called by the GC when the object is being destroyed because it is no longer used.
在包装器类中实现最后的方法。这是GC在对象被销毁时调用的清理方法，因为它不再被使用。
The finally method should check "am I closed?". If already closed, then everything is fine... however if the connection is being GCed and it hasn't been closed... then this is a "leaked" connection.
最后的方法应该检查“我关闭了吗?”如果已经关闭，那么一切都没问题……但是如果连接是GCed并且它还没有被关闭…这是一个“泄露”的连接。
Now the Throwable comes back into play. We can grab the Throwable and output a nice log message saying something like: "I'm a leaked connection and here is a stack trace implicating my creator."
现在，可扔的东西又回来了。我们可以获取可抛出的日志，并输出一个很好的日志消息，比如:“我是一个泄漏的连接，这里有一个堆栈跟踪，它指示我的创建者。”
Expanding the idea
This method can be adapted for a variety of situations. You can of course keep other types of data in your wrapper for troubleshooting your specific issue. For instance creation time. Then you can poll for long-lived connections and again implicate the creator. Or you can poll existing connections and parse the Throwable stack traces to get data on which code is using how many connections over time.
这种方法适用于各种情况。当然，您可以将其他类型的数据保存在包装器中，以解决您的特定问题。例如创建时间。然后，您可以轮询长期存在的连接，并再次暗示创建者。或者，您可以轮询现有的连接，并解析可抛出的堆栈跟踪，以获取数据，其中的代码在一段时间内使用了多少连接。
There is probably an off-the-shelf tool that can also do these types of things, but the amount of code required to apply this technique is very minimal in most cases (assuming you have an easy way to swap our your db connection factory without search-replacing your whole codebase).
可能有一种现成的工具也可以做这些事情，但是在大多数情况下，应用这种技术所需的代码量非常小(假设您有一种简单的方法来交换我们的db连接工厂，而无需搜索替换整个代码基)。
#2
7
Have a look at log4jdbc. It enables you to have a look at all stuff going over your jdbc, including opening/closing connections as well as connection number information.
看看log4jdbc。它允许您查看jdbc上的所有内容，包括打开/关闭连接以及连接编号信息。
#3
4
Someone showed me ConnLeakFinder recently, "a simple tool to pinpoint jdbc connection leaks in java code". I haven't tested it myself so far but it should allow you To see who did not close the connection after use. See Connection+Leak+How+To+Find.htm.
最近有人向我展示了ConnLeakFinder，“一个在java代码中精确定位jdbc连接泄漏的简单工具”。到目前为止我还没有对它进行测试，但是它应该可以让您看到谁在使用后没有关闭连接。看到连接+泄漏+有+ + Find.htm。
But indeed, you should conslder using a connection pool (for example c3p0).
但是实际上，您应该使用连接池(例如c3p0)进行conslder。
#4
1
Connection pools can give you some diagnostics. For example check out debugUnreturnedConnectionStackTraces property for C3P0 connection pool:
连接池可以提供一些诊断。例如，检查C3P0连接池的debugunreturnedconnectionstacktrace属性:
http://www.mchange.com/projects/c3p0/index.html#debugUnreturnedConnectionStackTraces
http://www.mchange.com/projects/c3p0/index.html debugUnreturnedConnectionStackTraces
#5
0
P6Spy is an open source framework to support applications that intercept and optionally modify database statements.
P6Spy是一个开源框架，它支持拦截和修改数据库语句的应用程序。
From http://www.p6spy.com/about.html
The P6Spy distribution includes the following modules:
从http://www.p6spy.com/about.html, P6Spy发行版包含以下模块:
- P6Log. P6Log intercepts and logs the database statements of any application that uses JDBC. This application is particularly useful for developers to monitor the SQL statements produced by EJB servers, enabling the developer to write code that achieves maximum efficiency on the server. P6Spy is designed to be installed in minutes and requires no code changes.
- P6Log。P6Log拦截并记录任何使用JDBC的应用程序的数据库语句。这个应用程序对于开发人员监视EJB服务器生成的SQL语句特别有用，使开发人员能够编写在服务器上实现最大效率的代码。P6Spy被设计成在几分钟内安装，不需要修改代码。
- P6Outage. P6Outage detects long-running statements that may be indicative of a database outage proble and will log any statement that surpasses the configurable time boundary during its execution. P6Outage was designed to minimize any logging performance penalty by logging only long running statements.
- P6Outage。p6宕机检测长时间运行的语句，这些语句可能指示数据库停机，并将记录在执行过程中超过可配置时间边界的任何语句。p6中断被设计为通过只记录长时间运行的语句来最小化日志记录性能损失。