Re: [Rails-core] ActiveRecord connection pool management

[Michael Vigilante]

On Monday, 5 October 2015 14:37:56 UTC+11, Michael Vigilante wrote:

> This is also an excellent point; I wasn't aware this was the case. It 
> certainly feels like a waste to be doing this every time a database 
> operation is run. I'm not sure what an appropriate level of alive-checking 
> is; I'll see if I can find out how other systems do this.
>

OK; looks like other systems either do an alive-check on each checkout (I 
don't *think* this necessarily needs to run a query on the database; for 
example, JDBC provides a `connection#isAlive` method; I can't find any 
source for what this actually *does* though. Maybe it does just run a 
query), or they do it scheduled in a background thread, which probably 
isn't an option here because of the GIL. 

My guess is that because my tests were run with the database so close to 
the app (on the same machine), this wasn't really an issue; I guess latency 
would be the real performance-killer in this case. I'll rerun the tests 
with an external database just to be sure.

The other issue I noticed (which I just realised I hadn't mentioned) was 
that I had to disable the query cache middleware in order to make checking 
in and out connections possible; I'm not entirely sure what that middleware 
is for (it appears to just semi-temporarily enable the query cache on all 
requests?), but it poses a problem to checkin-checkout (even in the manual 
sense).

-- 
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

[Michael Vigilante]

On Friday, 2 October 2015 05:28:01 UTC+10, Matt jones wrote:
>
>
> 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...
>

I would *personally* actually prefer to have to open and close a connection 
around variables like this, if only because it would serve as a reminder to 
set, say, SQL_MODE back to what it was; it only feels slightly harder in 
the current system to hand somebody else a connection with an altered 
SQL_MODE.

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

> 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.
>

It's not so much the mental image that you're using less resources as the 
*actual 
ability to* use less resources. The practical benefit is that for 
applications which don't use the database to do *all* their grunt work 
(which seems to be most of them; rendering seems to take at least as long 
as a DB round trip for at least most things), you don't have to saturate 
your threads with database connections, which means you can scale your app 
further. 

I guess my theory was that there would be negligibly more contention, and 
my research seems to have backed that up, especially given the below. Happy 
to be proven wrong! 

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

> 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.
>

This is certainly an issue; I'm not sure how to get around it without 
either connection-per-thread or #transaction_open?. It does feel like a bad 
thing to do, though; is keeping support for this really that important?

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

>  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.


This is also an excellent point; I wasn't aware this was the case. It 
certainly feels like a waste to be doing this every time a database 
operation is run. I'm not sure what an appropriate level of alive-checking 
is; I'll see if I can find out how other systems do this.

>

-- 
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

[Michael Vigilante]

>
> On Thursday, 1 October 2015 03:30:55 UTC+10, Xavier Noria wrote:

>
> Transactions are per connection, how does that approach handle them?
>

>From what I can tell, ActiveRecord only provides one way to manually open a 
transaction, which is a `transaction do ... end` block; in this case, if 
the connection is checked out and in around `transaction`, it will still 
work OK; you'll have the same connection and the same transaction for 
everything within that block.

Aaron, I have no idea if I'll be able to work that out, but I'll give it a 
shot.

-- 
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

[Michael Vigilante]

OK, mock-up is here: 
https://github.com/stranger-zedd/activerecord-connection-pool-tests

On Tuesday, 29 September 2015 08:37:43 UTC+10, Michael Vigilante wrote:
>
> On Tuesday, 29 September 2015 00:46:14 UTC+10, Aaron Patterson wrote:
>>
>> I don't really follow.  If you have 5 request threads, and 5 connections 
>> available, no request thread will contend on a lock for a connection 
>> (unless you are using threads, which means you'd have 1 + new 
>> threads.count 
>> connections used, which I said is off the beaten path). 
>>
>  
> Sorry, I meant in the case where you have, say, a pool of five connections 
> and ten request threads (which is entirely possible given the default Rails 
> and, say Puma configuration). Where you have a thread per connection, I'd 
> expect the existing model to perform very slightly better because you only 
> have to go through the process of acquiring the connection once per thread 
> (rather than going through the locking/synchronising process multiple 
> times). I'd expect them to wait about the same time in this situation when 
> all (or at least a large majority) of the work being done by the app is in 
> the database; the more time you spend processing in the app, the more you 
> could benefit from releasing the connection between database work.
>
> Working on that test case right now, will get back to you :)
>

-- 
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

[Michael Vigilante]

On Tuesday, 29 September 2015 00:46:14 UTC+10, Aaron Patterson wrote:
>
> I don't really follow.  If you have 5 request threads, and 5 connections 
> available, no request thread will contend on a lock for a connection 
> (unless you are using threads, which means you'd have 1 + new 
> threads.count 
> connections used, which I said is off the beaten path). 
>
 
Sorry, I meant in the case where you have, say, a pool of five connections 
and ten request threads (which is entirely possible given the default Rails 
and, say Puma configuration). Where you have a thread per connection, I'd 
expect the existing model to perform very slightly better because you only 
have to go through the process of acquiring the connection once per thread 
(rather than going through the locking/synchronising process multiple 
times). I'd expect them to wait about the same time in this situation when 
all (or at least a large majority) of the work being done by the app is in 
the database; the more time you spend processing in the app, the more you 
could benefit from releasing the connection between database work.

Working on that test case right now, will get back to you :)

-- 
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

[Michael Vigilante]

On Sunday, 27 September 2015 06:54:16 UTC+10, Aaron Patterson wrote:

> Is it counter-intuitive? If you're doing database intensive work, then 
> you'd probably want the same number of connections as you have request 
> threads in order to maximize throughput, otherwise you'll still be 
> blocking other requests at some point (see Amdahl's law). 
>

Yes, in case you're doing heavy database work you certainly want to have 
enough connections available to avoid contention. In our case, we had a 
couple of short database transactions wrapped around other computational 
work, including a call to an external service (we know). The thing we found 
counter-intuitive was that when the external call got slow (as they always 
do), we started being unable to checkout database connections. Not a 
particularly common or desirable use-case, I know, and I now get that we 
can work around this by manually checking out connections, so that part is 
good.

I'm certainly not suggesting that optimising for the above case is a good 
idea; like I said, I was just interested. I don't really come from a web or 
Ruby background, I'm used to resource sharing systems either forcing you to 
explicitly checkout/checkin manually or automatically checkout/checkin when 
you request the resource. I guess threads in web are pretty short lived so 
it wouldn't normally be an issue.

On Sunday, 27 September 2015 06:54:16 UTC+10, Aaron Patterson wrote:

> Constantly checking out a connection then checking it back in seems like 
> it would cause a performance bottleneck.  The assumption is that lock 
> contention will become the bottleneck if we have to constantly check in 
> / check out connections.
>
 
I dunno, it feels to me like under normal circumstances you'd not see any 
*more* lock contention. I would *guess* that under the current scheme, the 
first few web transactions would be fast (no locking at all), but after 
that you'd end up waiting on average about the same amount of time (since 
each thread has to wait for a connection before it can get started and then 
needs to do *all* its operations before it checks it back in). I'm curious, 
I'll see if I can mock something up to test this using the existing 
checkout/checkin functionality.

-- 
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] ActiveRecord connection pool management

[Michael Vigilante]

Hi, just a small question on the way connections are managed in 
ActiveRecord.

>From the best I can gather reading the code (and based on behaviour), 
ActiveRecord will check a connection out of the pool the first time a 
thread asks for one, then that thread will continue to hang on to the 
connection until it is closed. What is the reasoning behind this approach 
as opposed to checking out a connection for each DB operation and letting 
go of it once the operation is completed?

My team has had a couple of issues with this (leading to us needing to use 
a connection pool equal to the number of threads being used by our server, 
which is not really a big problem, but feels a bit counter-intuitive). This 
pos 
t 
is on much the same subject. I'm really just curious about the way this 
works.

-- 
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.