List:MySQL++« Previous MessageNext Message »
From:Denis Nagorny Date:April 14 2009 6:29am
Subject:Re: mysql++'s connection hangs after long timeout
View as plain text  
Thank you Warren,

It helps a lot. I'll try to implement pings before queries - if overhead is
acceptable it will be the solution. Another way (with reconnect after error)
is acceptable too but slightly harder in implementation)

Denis

2009/4/13 Warren Young <mysqlpp@stripped>

> Denis Nagorny wrote:
>
>>
>> There are no any requests during weekend
>>
>
> You've just run into a common issue, the default 8-hour MySQL connection
> timeout.  I've added a section to the user manual to cover this issue.
> Here's what it currently says:
>
>
>
> By default, current MySQL servers have an 8 hour idle timeout on
> connections. The most common way to run into this problem is when you have a
> program that runs continually, but you keep finding that it dies overnight
> or over the weekend because no one is making it issue queries. The MySQL
> server closes the connection after the configured idle timeout to free up
> resources, because a networked program can never be sure that the remote
> peer is still there when no data is being transferred.
>
> You cannot detect this by calling Connection::connected(). If that returns
> true, it just means that either the connect-on-create constructor or the
> connect() call succeeded and that we haven’t observed the connection to be
> down since then. If the server closes the connection, you won’t know it
> until the next time you try to talk to it. This is simply due to the nature
> of network programming.
>
> One way around this problem is to configure MySQL to use a different
> timeout. This timeout is in seconds, so the default of 8 hours is 28,800
> seconds. You could figure out the longest possible time that your program
> could be left idle. For instance, it could be left running over a long 4-day
> weekend, which is 345,600 seconds, which you could round up to 350,000 or
> 400,000 just to keep things simple.
>
> You can also change this parameter on a per-connection basis, too, by using
> MySQL++’s ConnectTimeoutOption connection option.
>
> Another way to tackle this, if your program is asynchronous (i.e. doesn’t
> stay blocked forever waiting on I/O while idle) or uses threads, is to
> periodically call Connection::ping(). This sends the smallest possible
> amount of data to the database server, which will reset its idle timer. If
> the server responds, ping() returns true, so this also gives your program
> the ability to detect that the server has gone away before trying a query.
> Don’t ping the server too often, though, as it takes a tiny amount of
> processing capability to handle a ping, which can add up to a significant
> amount if done often enough by a client, or even just rarely by enough
> clients. When I do this, I just assume the database server is there, pinging
> it no more often than half the connection timeout. With the default of 8
> hours, then, I’d ping between every 4 and 7 hours.
>
> An interesting variant is to ping the server before each query, or, better,
> before each group of queries within a larger operation. It has an advantage
> over pinging during idle time in that the client is about to use far more
> server resources to handle the query than it will take to handle the ping,
> so the ping time gets lost in the overhead. On the other hand, if the client
> issues queries frequently when not idle, it can result in a lot more pings
> than would happen if you just pinged every N hours while idle.
>
> Finally, some programmers prefer to wrap the querying mechanism in an error
> handler that catches the “server has gone away” error and tries to
> reestablish the connection and reissue the query. This adds some complexity,
> but it makes your program more robust without taking up unnecessary
> resources. If you did this, you could even change the server to drop idle
> connections more often, thus tying up fewer TCP/IP stack resources.
>
> --
> MySQL++ Mailing List
> For list archives: http://lists.mysql.com/plusplus
> To unsubscribe:
> http://lists.mysql.com/plusplus?unsub=1
>
>

Thread
mysql++'s connection hangs after long timeoutDenis Nagorny13 Apr
  • Re: mysql++'s connection hangs after long timeoutWarren Young13 Apr
    • Re: mysql++'s connection hangs after long timeoutDenis Nagorny14 Apr
    • Re: mysql++'s connection hangs after long timeoutRick Gutleber14 Apr