RE: Stay connected with DB

Thank you for your prompt! reply.

In regards to #1, this snippet was from such a class as you described -- the getPoolConnection just managed the list of connections. (A minor nit).

I agree all or in part to all other points. I'll consider formalizing this into a class that could be included in MySQL++.

Reid

-----Original Message-----
From: Warren Young [mailto:mysqlpp@etr-usa.com] Sent: Tuesday, June 05, 2007 1:41 PM
To: MySQL++ Mailing List
Subject: Re: Stay connected with DB

reid.madsen@exgate.tek.com 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 {
     public:
         ConnectionPool() { }
         Connection* connection();
     protected:
         // subclass overrides
         virtual Connection* create() = 0;
         virtual unsigned int max_lifetime() = 0;
     private:
         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 pool_;
         Connection* add();
     };
	
     Connection* ConnectionPool::add()
     {
         pool_.push_back(ConnectionInfo(create()));
         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.

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

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.

-- 
MySQL++ Mailing List
For list archives: 
http://lists.mysql.com/plusplus
To unsubscribe:
http://lists.mysql.com/plusplus?unsub=reid.madsen@tek.com


-- 
MySQL++ Mailing List
For list archives: 
http://lists.mysql.com/plusplus
To unsubscribe:    
http://lists.mysql.com/plusplus?unsub=lists@pantek.com