On 26-12-2009 10:36:41 +0800, xulizhong wrote:
Dear Martin, In fact, this program is an extracted version of a big system. In this system, we're using apache-commons-dbutils to execute the database operations. I know the commons-dbutils always use preparedstatement to execute sql, and always close the resultset and the preparedstatement after each query, so I write this simple program to verify my guess. I add the close statement of the resultset and the jdbc statement, and rerun the benchmark, the result is same: with jdbc normal statement, the memory usage starts from 17M, to 85M after first 10,000 queries, then to 92M after the second 10000 queries, then 93M, 94M ....about 1M memory increase per 10000 queries. But with the jdbc prepared statement(I have closed the resultset and the preparedstatement and the connection after each query), the memory usage starts from 17M to 800M after first 10000 queries, after 4-5 times benchmark, the mserver5 process will use more then 2G memory and crashed.
The problem with your example program is that you create many prepared statements (on the server side) but only use them once. For that use a normal statement is better, since it doesn't create a server-side executor for the prepared query. The problem you are triggering here is that MonetDB doesn't clean up server-side executors as time goes, since it cannot know that you will never reference them again. I guess we can solve this by implementing a release for the executors which is triggered by a close of the PreparedStatement (either by you or the garbage collector, as it would be before you added the close() calls).
Finally, Is the latest monetdb5 program stable enough for production use? I love its light speed:) It's really a great database for query intensive program.
This is something which you can only judge yourself, as every workload is different, and hence behaves differently.