Yesterday I was the beneficiary of a flurry of postings and off-line emails regarding a database connection issue using iBatis. I am overflowing with gratitude because your efforts helped me understand the situation and solve the problem. I had spent several weeks wrestling with this and gone down many dead-ends in futile attempts to figure out what was wrong. Here are the three lessons I learned yesterday:
(1) Every time you generate an instance of SqlMapClient, you create an entirely new connection pool. So, your entire application should share a single instance of SqlMapClient. (2) My application generates a new instance of SqlMapClient every time it runs (thank you so much for pointing this out to me -- I had no idea). (3) Connection pools are cool and not such a mystery after all, now that you folks have explained how they work. All the bizarre behavior I experience in regard to my database connections can be explained by the fact that multiple connection pools are being generated, and they just hang around while waiting for the Garbage Collector to clean things up. Many of you took time yesterday to explain how connection pools work, and that helped clear up much of the mystery for me. I've gone back through all the postings and emails to compile a summary of what I learned, and I'd like to share that with you. Thanks again to all of you who contributed to this effort. =============================== How a database connection pool works - A connection pool is simply a collection of connection objects that are kept "open" as a performance optimization technique (they show up as <IDLE> processes in PostgreSQL's psql --> "select * from pg_stat_activity;"). Creating network connections to a database server can be an expensive operation (in terms of time), so web applications create a "pool" of connections that are left "open," and then used as needed. When a program asks for a new connection, it gets an already opened connection from the pool instead of waiting for a new one to be opened, and the pool marks it as in use so no one else gets it. When you "close" that connection, the pool just marks it as available so that it can be reused. This is why we see <IDLE> connections when not expecting them. After you have closed the connection, it's still open -– that's how a pool works –- but it shows up as <IDLE>. The connection pool will usually expand and contract the number of connections to the database based on the load and how it's configured (which is why it is called a "pool"). The pool will "grow" under heavy use, and then when the load decreases it will "shrink" back to its maxIdle. Connection pools usually have three configurable parameters: (a) minIdle is the smallest number of <IDLE> connections you will ever see being held open by the pool. If this number was two, you would always have at least two <IDLE> connections being held open in the pool. If it were zero, then all the <IDLE> connections in the pool may be closed. If the number were two, on startup, as soon as the pool is created, you will immediately see two additional <IDLE> connections. MinIdle should be at least one so that there is always a connection ready to go; otherwise, the first user will probably notice a delay in their HTTP response. (b) maxActive - you will never have more "active" connections than this because you don't want a "runaway" program (a database client) to chew up all your database connections. (c) maxIdle is a way to communicate when the pool can "give up" connections after it has grown and then shrunk back down. As the pool shrinks, and more active connections go <IDLE>, if this number is exceeded the excess connections will automatically close. So, maxIdle is the largest number of <IDLE> connections ever held open by the pool. =============================== I hope I got this right. As I said, these are your words, not mine, and I thank you all for the contributions. Once again, thank you VERY MUCH for helping me during my search for answers. You folks are the greatest! Wow, I didn't realize that I am creating a new instance of the SqlMapClient every time I call this method. That obviously is not a good thing. Thank you for discovering my oversight and for your suggested fixes. I will scratch my head for a bit and figure out how to fix this situation. Thanks again! IIRC, Struts2 actions are not singletons. So this code is creating a new instance of the SqlMapClient (and it's associated connection pool) each time you hit the web page. They will eventually get cleaned up by the GC, but that might take a while. -- View this message in context: http://www.nabble.com/iBatis---Connections-to-PostgreSQL-Not-Closing-tp25943619p25977425.html Sent from the iBATIS - User - Java mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org For additional commands, e-mail: user-java-h...@ibatis.apache.org
