Re: [Rails-core] ActiveRecord connection pool management

2015-10-01 Thread James Coleman 
Agreed.

The biggest benefit is going to come to people using a DB like Postgres
where each connection uses an entire backend (forked worker essentially),
and, given the high cost of backends, it's recommended to keep the number
of connections low.

Checking connections in/out of the pool as necessary would, I think,
significantly many medium or larger applications using Postgres as it would
make it easier to scale the number of Rails backends without impacting the
number of connections to PG required.

On Thu, Oct 1, 2015 at 10:57 AM,  wrote:

> We run into problems with connection pools on Heroku.
>
> In Postgres a connection is very expensive. The cheap/free plans on Heroku
> Postgres all have very small connection limits. The hobby plan is limited
> to 20 connections
> https://devcenter.heroku.com/articles/heroku-postgres-plans.
>
> Some people will build really small rack apps that use active record, and
> want to max out their processes/threads. It's not that common but it
> happens.
>
> More commonly people don't know about this behavior and
> get ActiveRecord::ConnectionTimeoutError because they're using default Puma
> which is set to 16 threads and default AR which is set to 5 connections. I
> wrote these docs to help people understand the connection requirements of
> AR
> https://devcenter.heroku.com/articles/concurrency-and-database-connections
>
> Our larger customers end up hitting our max conneciton limit of our
> "production" plans which is 500 connections. They end up having to run
> PgBouncer which does per machine connection multiplexing
> https://devcenter.heroku.com/articles/concurrency-and-database-connections#limit-connections-with-pgbouncer
>
>
>
>
> ---
> Richard Schneeman
> http://www.schneems.com
>
>
>
> On Thu, Oct 1, 2015 at 9:45 AM, Matt Jones  wrote:
>
>> If the concern is long-running threads holding DB connections they don't
>> need, wouldn't a simpler solution be to explicitly return connections to
>> the pool (via release_connection) before doing a long-running non-DB
>> operation? Checking out and back in on most operations seems like
>> optimizing for that uncommon case at a runtime cost for the common one.
>>
>> --Matt Jones
>>
>> On Thu, Oct 1, 2015 at 4:25 AM, Xavier Noria  wrote:
>>
>>> The only time I've seen one connection per thread being an issue was in
>>> one app that ran many processes and it started to reach the connection
>>> limit of their db server in traffic peaks. Even in that scenario,
>>> contention is also a risk if you reduce the pool.
>>>
>>> Other than the mental image that you're using less resources (at the
>>> price of contention), I am not sure there is going to be any significant
>>> practical win. It could be, I am not saying it wouldn't (I have not done
>>> your study), but at first sight the cost/benefit is not clear to me in
>>> practical terms.
>>>
>>> Regarding transactions, #execute is public AR interface, and
>>>
>>> AR::Base.connection.execute('START TRANSACTION')
>>>
>>> is valid AR code, I am not sure if drivers know the connection is in a
>>> transaction and have API to check, but #transaction_open? returns false as
>>> of this writing. Why would anybody do that? I don't know, maybe because
>>> they are writing a heavy SQL oriented script and doing that manually feels
>>> natural... it doesn't matter, it can be done.
>>>
>>> Another practical point is that when a connection is checked out the
>>> connection pool tests if it is alive issuing for example SELECT 1. That
>>> would mean that if a request performs today 200 queries, they would become
>>> 400 queries. I don't know if the alive check could be rethought to run less
>>> frequently, but it probably should to avoid that 2x.
>>>
>>>
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "Ruby on Rails: Core" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to rubyonrails-core+unsubscr...@googlegroups.com.
>>> To post to this group, send email to rubyonrails-core@googlegroups.com.
>>> Visit this group at http://groups.google.com/group/rubyonrails-core.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Ruby on Rails: Core" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to rubyonrails-core+unsubscr...@googlegroups.com.
>> To post to this group, send email to rubyonrails-core@googlegroups.com.
>> Visit this group at http://groups.google.com/group/rubyonrails-core.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Core" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to rubyonrails-core+unsubscr...@googlegroups.com.
> To

Re: [Rails-core] ActiveRecord connection pool management

2015-10-01 Thread richard . schneeman 
We run into problems with connection pools on Heroku.




In Postgres a connection is very expensive. The cheap/free plans on Heroku 
Postgres all have very small connection limits. The hobby plan is limited to 20 
connections https://devcenter.heroku.com/articles/heroku-postgres-plans.




Some people will build really small rack apps that use active record, and want 
to max out their processes/threads. It's not that common but it happens.




More commonly people don't know about this behavior and get 
ActiveRecord::ConnectionTimeoutError because they're using default Puma which 
is set to 16 threads and default AR which is set to 5 connections. I wrote 
these docs to help people understand the connection requirements of AR 
https://devcenter.heroku.com/articles/concurrency-and-database-connections





Our larger customers end up hitting our max conneciton limit of our 
"production" plans which is 500 connections. They end up having to run 
PgBouncer which does per machine connection multiplexing 
https://devcenter.heroku.com/articles/concurrency-and-database-connections#limit-connections-with-pgbouncer
 








---Richard Schneeman


http://www.schneems.com

On Thu, Oct 1, 2015 at 9:45 AM, Matt Jones  wrote:

> If the concern is long-running threads holding DB connections they don't
> need, wouldn't a simpler solution be to explicitly return connections to
> the pool (via release_connection) before doing a long-running non-DB
> operation? Checking out and back in on most operations seems like
> optimizing for that uncommon case at a runtime cost for the common one.
> --Matt Jones
> On Thu, Oct 1, 2015 at 4:25 AM, Xavier Noria  wrote:
>> The only time I've seen one connection per thread being an issue was in
>> one app that ran many processes and it started to reach the connection
>> limit of their db server in traffic peaks. Even in that scenario,
>> contention is also a risk if you reduce the pool.
>>
>> Other than the mental image that you're using less resources (at the price
>> of contention), I am not sure there is going to be any significant
>> practical win. It could be, I am not saying it wouldn't (I have not done
>> your study), but at first sight the cost/benefit is not clear to me in
>> practical terms.
>>
>> Regarding transactions, #execute is public AR interface, and
>>
>> AR::Base.connection.execute('START TRANSACTION')
>>
>> is valid AR code, I am not sure if drivers know the connection is in a
>> transaction and have API to check, but #transaction_open? returns false as
>> of this writing. Why would anybody do that? I don't know, maybe because
>> they are writing a heavy SQL oriented script and doing that manually feels
>> natural... it doesn't matter, it can be done.
>>
>> Another practical point is that when a connection is checked out the
>> connection pool tests if it is alive issuing for example SELECT 1. That
>> would mean that if a request performs today 200 queries, they would become
>> 400 queries. I don't know if the alive check could be rethought to run less
>> frequently, but it probably should to avoid that 2x.
>>
>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Ruby on Rails: Core" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to rubyonrails-core+unsubscr...@googlegroups.com.
>> To post to this group, send email to rubyonrails-core@googlegroups.com.
>> Visit this group at http://groups.google.com/group/rubyonrails-core.
>> For more options, visit https://groups.google.com/d/optout.
>>
> -- 
> You received this message because you are subscribed to the Google Groups 
> "Ruby on Rails: Core" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to rubyonrails-core+unsubscr...@googlegroups.com.
> To post to this group, send email to rubyonrails-core@googlegroups.com.
> Visit this group at http://groups.google.com/group/rubyonrails-core.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups "Ruby 
on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to rubyonrails-core+unsubscr...@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.

Re: [Rails-core] ActiveRecord connection pool management

2015-10-01 Thread Matt Jones 
If the concern is long-running threads holding DB connections they don't
need, wouldn't a simpler solution be to explicitly return connections to
the pool (via release_connection) before doing a long-running non-DB
operation? Checking out and back in on most operations seems like
optimizing for that uncommon case at a runtime cost for the common one.

--Matt Jones

On Thu, Oct 1, 2015 at 4:25 AM, Xavier Noria  wrote:

> The only time I've seen one connection per thread being an issue was in
> one app that ran many processes and it started to reach the connection
> limit of their db server in traffic peaks. Even in that scenario,
> contention is also a risk if you reduce the pool.
>
> Other than the mental image that you're using less resources (at the price
> of contention), I am not sure there is going to be any significant
> practical win. It could be, I am not saying it wouldn't (I have not done
> your study), but at first sight the cost/benefit is not clear to me in
> practical terms.
>
> Regarding transactions, #execute is public AR interface, and
>
> AR::Base.connection.execute('START TRANSACTION')
>
> is valid AR code, I am not sure if drivers know the connection is in a
> transaction and have API to check, but #transaction_open? returns false as
> of this writing. Why would anybody do that? I don't know, maybe because
> they are writing a heavy SQL oriented script and doing that manually feels
> natural... it doesn't matter, it can be done.
>
> Another practical point is that when a connection is checked out the
> connection pool tests if it is alive issuing for example SELECT 1. That
> would mean that if a request performs today 200 queries, they would become
> 400 queries. I don't know if the alive check could be rethought to run less
> frequently, but it probably should to avoid that 2x.
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Core" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to rubyonrails-core+unsubscr...@googlegroups.com.
> To post to this group, send email to rubyonrails-core@googlegroups.com.
> Visit this group at http://groups.google.com/group/rubyonrails-core.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups "Ruby 
on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to rubyonrails-core+unsubscr...@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.

Re: [Rails-core] ActiveRecord connection pool management

2015-10-01 Thread Matt Jones 
On Thu, Oct 1, 2015 at 4:25 AM, Xavier Noria  wrote:

> The only time I've seen one connection per thread being an issue was in
> one app that ran many processes and it started to reach the connection
> limit of their db server in traffic peaks. Even in that scenario,
> contention is also a risk if you reduce the pool.
>
> Other than the mental image that you're using less resources (at the price
> of contention), I am not sure there is going to be any significant
> practical win. It could be, I am not saying it wouldn't (I have not done
> your study), but at first sight the cost/benefit is not clear to me in
> practical terms.
>
> Regarding transactions, #execute is public AR interface, and
>
> AR::Base.connection.execute('START TRANSACTION')
>
> is valid AR code, I am not sure if drivers know the connection is in a
> transaction and have API to check, but #transaction_open? returns false as
> of this writing. Why would anybody do that? I don't know, maybe because
> they are writing a heavy SQL oriented script and doing that manually feels
> natural... it doesn't matter, it can be done.
>
> Another practical point is that when a connection is checked out the
> connection pool tests if it is alive issuing for example SELECT 1. That
> would mean that if a request performs today 200 queries, they would become
> 400 queries. I don't know if the alive check could be rethought to run less
> frequently, but it probably should to avoid that 2x.
>
>
Good point. That's an additional overhead, especially for systems with DBs
that are farther away.

One other point, possibly MySQL-specific: there are connection-specific SQL
variables. Under the current system, code can rely on them being set after
they are set in the same thread:

ActiveRecord::Base.connection.execute("SET SQL_MODE = ''")
# now SQL_MODE for the connection is set to empty string
SomeModel.create(...)

With per-DB-interaction checkin/checkout, it doesn't appear to be possible
to reliably manipulate these variables, as the connection used in the first
statement may not match the one used in the second. More amusing /
worrisome, somebody ELSE gets a connection with an altered SQL_MODE...

--Matt Jones

-- 
You received this message because you are subscribed to the Google Groups "Ruby 
on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to rubyonrails-core+unsubscr...@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.

[Rails-core] backport postgres cast-to-text fix (8c34d10) from master to next minor release?

2015-10-01 Thread Sergio Rabiela 
 

Hi,

Ran into a cast-to-text error when trying to connect to a postgres 8.2 
database with rails 4.2.4. I noticed the fix is already in master (
https://github.com/rails/rails/commit/8c34d106ca66a03a78739c411a2f4ecb9fcad25d),
 
but was wondering if this could get backported to the next minor version 
release of rails. It’d help me out a ton. Thoughts?


Thanks,

-Sergio

-- 
You received this message because you are subscribed to the Google Groups "Ruby 
on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to rubyonrails-core+unsubscr...@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.

Re: [Rails-core] ActiveRecord connection pool management

2015-10-01 Thread Xavier Noria 
The only time I've seen one connection per thread being an issue was in one
app that ran many processes and it started to reach the connection limit of
their db server in traffic peaks. Even in that scenario, contention is also
a risk if you reduce the pool.

Other than the mental image that you're using less resources (at the price
of contention), I am not sure there is going to be any significant
practical win. It could be, I am not saying it wouldn't (I have not done
your study), but at first sight the cost/benefit is not clear to me in
practical terms.

Regarding transactions, #execute is public AR interface, and

AR::Base.connection.execute('START TRANSACTION')

is valid AR code, I am not sure if drivers know the connection is in a
transaction and have API to check, but #transaction_open? returns false as
of this writing. Why would anybody do that? I don't know, maybe because
they are writing a heavy SQL oriented script and doing that manually feels
natural... it doesn't matter, it can be done.

Another practical point is that when a connection is checked out the
connection pool tests if it is alive issuing for example SELECT 1. That
would mean that if a request performs today 200 queries, they would become
400 queries. I don't know if the alive check could be rethought to run less
frequently, but it probably should to avoid that 2x.

-- 
You received this message because you are subscribed to the Google Groups "Ruby 
on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to rubyonrails-core+unsubscr...@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.