 | | | Stay connected with DB | Stay connected with DB 2007-06-06 - By reid.madsen@(protected)
Back
Warren,
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?
Thanks
-- --Original Message-- --
From: Warren Young [mailto:mysqlpp@(protected)]
Sent: Tuesday, June 05, 2007 12:09 PM
To: MySQL++ Mailing List
Subject: Re: Stay connected with DB
Manuel Jung wrote:
>
> It stays connected for a day or so, but always after a night (where
> probably not querys where started) the connections are lost.
This is a relatively recent change in the MySQL server defaults. (I
believe it changed sometime in the middle of the 4.1 series.) If the
server doesn't see a query (or a ping) on a connection for 8 hours, it
drops the connection. This is a desirable feature, because it avoids
problems with too many idle connections building up, wasting server
resources.
If you don't like this behavior, it's better to change the wait_timeout
setting in the [mysqld] section of the my.cnf file, which controls this
behavior. The default is 28800, or 8 hours, measured in seconds. If
you know you will get at least one query a day, you could change it to
86400.
> Im trying to use Connection::ping for possibly reconnect, before i
> start some MySQL Work, but it doesn't help.
Of course not. The connection is already closed by that point.
If you must use pings to work around this, you need to ping separately
from the main work loop, so the pings go out regardless of what work is
happening.
If your program just sits there doing nothing while waiting for work,
just set it to ping the server every 4 hours or so.
If instead it's an event-based system, where your code doesn't get
called until there is work to do (as in a web application) there's not
much you can do about this from the client side. One more argument in
favor of fixing this on the server side.
> I also create a new query object after pinging.
> (Query objects are just alive for a short time in my application).
That's not going to affect this. If you recreate the _Connection_
object each time, that would fix the problem, but you'll pay a speed
penalty. This is why I keep coming back to fixing the server policy to
suit your situation, or if that cannot work, adding a background pinger.
> What is the preferred solution to keep connections alive or reconnect
> them automaticly?
The preferred solution is to avoid getting into a situation where you
need to do keepalives or reconnects. Those solutions attack the
symptom. I prefer to attack the cause, where possible.
--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:
http://lists.mysql.com/plusplus?unsub=reid.madsen@(protected)
--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe: http://lists.mysql.com/plusplus?unsub=mysql@(protected)
|
|
|