RE: Stay connected with DB - reid...@exgate.tek.com - com.mysql.lists.plusplus

12 messages in com.mysql.lists.plusplusRE: Stay connected with DB

From	Sent On	Attachments
Manuel Jung	05 Jun 2007 03:52
Jim Wallace	05 Jun 2007 04:04
gary clark	05 Jun 2007 05:03
Manuel Jung	05 Jun 2007 05:51
Warren Young	05 Jun 2007 09:53
Warren Young	05 Jun 2007 10:09
reid...@exgate.tek.com	05 Jun 2007 10:38
gary clark	05 Jun 2007 11:07
Warren Young	05 Jun 2007 11:40
Warren Young	05 Jun 2007 11:42
reid...@exgate.tek.com	05 Jun 2007 11:49
Manuel Jung	06 Jun 2007 04:23

Subject:	RE: Stay connected with DB
From:	reid...@exgate.tek.com (reid...@exgate.tek.com)
Date:	06/05/2007 11:49:50 AM
List:	com.mysql.lists.plusplus

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:mysq...@etr-usa.com] Sent: Tuesday, June 05, 2007 1:41 PM To: MySQL++ Mailing List Subject: Re: Stay connected with DB

reid...@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<ConnectionInfo> 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.

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.