Em quarta-feira, 24 de outubro de 2012 13h38min36s UTC-2, Jeremy Evans
escreveu:
>
> On Wednesday, October 24, 2012 6:07:58 AM UTC-7, Rodrigo Rosenfeld Rosas
> wrote:
>>
>> Another thing to consider is removing all connections from the pool when
>> a connection error is detected in one of them. Most likely it will happen
>> with the other ones as well. For instance if you restart the PG server, all
>> connections will become invalid. But still Sequel calls will fail 50 times
>> consecutively if there are 50 connections in the pool. Maybe there should
>> be an option to clear the pool once it has been detected some problem in
>> any of the connections. Maybe that could be the default behavior. Does it
>> make sense?
>>
>
> It makes sense as an option, but not as the default behavior (I'll
> consider patches, but won't work on it myself). I don't think very many
> people are doing what you are doing (restarting the database while the
> application is running). Personally, I consider that a bad idea. The
> problem that far more people have (I'm guessing based on reported issues)
> is that idle connections in the connection pool get disconnected by the
> database after a period of time. In that case, removing all connections
> from the pool just because a single one idled out is definitely the wrong
> approach.
>
> Note that with the connection verification extension I'm working on, you
> can verify connections on pool checkout before use, so your only chance of
> issues there is a disconnect while a connection is checked out (in which
> case I don't think anything could help). Of course, this can result in a
> performance hit, especially if you aren't attempting to manage connection
> checkouts manually in a coarse manner (e.g. in a rack middleware). Using
> coarse connection checkouts hurts multithreaded use, basically limiting the
> number of concurrent web requests to the number of database connections, so
> there is a definite tradeoff in that area.
>
>
In the meanwhile I'm just monkey patching Sequel 3.35.0:
# Patch Sequel to try the connection first before using it
class Sequel::ThreadedConnectionPool < Sequel::ConnectionPool
private
def available
begin
(@available_connections.pop || make_new(DEFAULT_SERVER)).tap{|c|
c.execute 'select 1' }
rescue
# reconnects on invalid connection (disconnected for some reason)
DB.disconnect
DB.pool.all_connections{}
@available_connections.pop
end
end
end
This works for me, but as soon as you get your extension released I'll
change to it :)
There are other reasons for the connections to be closed. I don't often
restart my database and only did that once because I was restoring a dump
from another server into this one and I needed to rename the database to
database-old and database-new to database but I had to kill all connections
to the database first before being able to rename them. I didn't have much
time to investigate how to do that in that time so I just restarted the pg
service. Now I'd probably use this instead:
https://github.com/rosenfeld/command-reference/blob/master/en/databases/postgresql/kill-database-connections.md
But I had this disconnection issue yesterday again even not restarting the
database. I don't know the cause yet, but while researching the subject,
one possible reason could be that a switch is killing idle connections
after a while. So, suppose PostgreSQL keep alive is set to 2h and the
switch is set to kill TCP connections idle for 15 minutes. All connections
could be invalid after a while where no one is using the database (at
night, for instance). I don't know if that is our case though and I'm still
investigating what could be causing the connections to become invalid
sometimes. I'm just saying that the testOnBorrow option available in most
Java libraries proved to be pretty useful in cases like this. So I'm
willing to accept a minimum overhead (select 1) to avoid such kind of
problems in my Ruby applications as well. For now the monkey patch above is
working for me, but I'd prefer to use your extension when it is ready.
I'm just saying that I can think of more reasons for a connection to become
invalid even when not restarting the database. In most cases I think it
would affect all connections instead of a single one. Another option would
be someone replacing a switch with another one...
Thanks,
Rodrigo.
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/iqilvc9cWV4J.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
