MySQL Lists are EOL. Please join:

List:MySQL++« Previous MessageNext Message »
From:Warren Young Date:June 5 2007 6:40pm
Subject:Re: Stay connected with DB
View as plain text  
reid.madsen@stripped wrote:
> I'm using a connection pool to reduce the overhead associated with
> opening/closing database connections.  Often, one of the connections in
> the pool times out.  Here is a snippet of code from the PooledConnection
> constructor that detects connections that have timed out:
> 	mysqlpp::Connection * conn = getPooledConnection();
> 	if (conn->ping()) {
> 	   delete conn;
> 	   conn = new mysqlpp::Connection( ... );
>       } 
> Is this an acceptable solution?  Anything else I should consider?

No, sorry, I don't like it.  I would make several changes:

1. The logic of ensuring that a connection is good should live inside 
the function that returns the connection.  Callers shouldn't have to 
second-guess the return value.

2. Instead of pinging the connection to see if it's alive, I'd associate 
a last-used timer with it.  I would then always return the most recently 
used connection.

3. While scanning the pool for the most recently used connection, I'd 
remove those last used more than X seconds ago (X is large but less than 
wait_timeout) since we clearly don't need them any more.

4. If we didn't find a connection not in use, create a new one and add 
it to the pool.

5. I wouldn't make the interface to the pool a stand-alone function. 
I'd make it a method on an object that contains the connection pool. 
Something like this:

     class ConnectionPool {
         ConnectionPool() { }
         Connection* connection();
         // subclass overrides
         virtual Connection* create() = 0;
         virtual unsigned int max_lifetime() = 0;
         struct ConnectionInfo {
             Connection* conn;
             time_t last_used;
             bool in_use;
             ConnectionInfo(Connection* c) :
             conn(c), last_used(time(0)), in_use(true) { }
         std::list<ConnectionInfo> pool_;
         Connection* add();
     Connection* ConnectionPool::add()
         return pool_.back().conn;

     Connection* ConnectionPool::connection()
         // scan ConnectionPool::instance_->pool_ for
         // LRU unused conn and delete outdated conns

         // return LRU if found, else return add()

If you'd be willing to implement this interface and release the code, I 
think this might be useful to add to MySQL++.  This possibility is why I 
designed it with create() and max_lifetime() being template methods, 
since MySQL++ cannot know how to properly create the connection or what 
the timeout should be.

A subclass could also turn this into a singleton, another thing that is 
outside MySQL++'s scope, since correct singleton destruction depends on 
the program's design.
Stay connected with DBManuel Jung5 Jun
  • RE: Stay connected with DBJim Wallace5 Jun
    • RE: Stay connected with DBgary clark5 Jun
      • Re: Stay connected with DBWarren Young5 Jun
        • Re: Stay connected with DBgary clark5 Jun
  • RE: Stay connected with DBManuel Jung5 Jun
  • Re: Stay connected with DBWarren Young5 Jun
    • RE: Stay connected with DBreid.madsen5 Jun
      • Re: Stay connected with DBWarren Young5 Jun
        • Re: Stay connected with DBWarren Young5 Jun
        • RE: Stay connected with DBreid.madsen5 Jun
  • RE: Stay connected with DBManuel Jung6 Jun