Connection Timebombs instead of Pools?
Something that Anthony reported from the MySQL conference really struck me yesterday:
MySQL connection setup is very fast and therefore Mark recommends not creating more connections than necessary when pooling. The only use for pooling when dealing with MySQL is to throttle and limit the number of connections available to one particular server.
Umm. Wow.
This changes my mind about a lot of things. I've been battling with db pooling since switchign to Tomcat. (That and character sets - what a pain in the ass). So if this is taken at face value - you could just ditch pooling all together? That'd definitely be nice from a Tomcat standpoint.
But what about timeouts? If you forget to do a conn.close() MySQL can hang on to that connection for a *long* time (i.e. forever). How about overriding the Connection object with a facade that sets off a monitoring thread every time one is created. The thread is simply a timebomb: Set the max connection limit and if the connection is open longer than that, it closes itself.
So if your site gets slammed, it opens up 400 connections. Okay. Then when the site is slower, it opens up less. Any connecitons left open for too long automatically blow up.
Sounds like a plan... I'll have to look into it.
-Russ