Rails - Sep 2005 - scgi_rails opens new a database connection for every request?

I''m testing scgi_rails 0.3.1 and it appears to be opening a new
database connection for every request, while keeping all previous
connections active (until the database server reaches its connection
limit, at which point the site stops working).  This doesn''t happen
under Webrick or FastCGI.  This happens with rails 0.13.1 and edge
rails (haven''t tried the beta gems).  Is this a known issue? Is anyone
else experiencing the same problem?  Please let me know what
information you need in order to troubleshoot this.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sep 28, 2005, at 4:26 PM, Jeremy Evans wrote:
> I''m testing scgi_rails 0.3.1 and it appears to be opening a new
> database connection for every request, while keeping all previous
> connections active (until the database server reaches its connection
> limit, at which point the site stops working).  This doesn''t
happen
> under Webrick or FastCGI.  This happens with rails 0.13.1 and edge
> rails (haven''t tried the beta gems).  Is this a known issue? Is
anyone
> else experiencing the same problem?  Please let me know what
> information you need in order to troubleshoot this.
Connections are kept open in thread-local storage.  I haven''t looked  
at scgi_rails, but perhaps it''s spawning threads (and hence new db  
connections) per-request.  We''ll be adding a connection pool for this  
sort of scenario before 1.0:  http://dev.rubyonrails.org/ticket/ 
2162.  I''ll take a look at scgi_rails in the meantime.

Regards,
jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDOykPAQHALep9HFYRAjV9AKCFk+MQkKx3fcD6nIvIfYDFjkZyTACfSxpe
HgnPSSxsZwHhe4QO1opZ42w=NEI7
-----END PGP SIGNATURE-----

On 9/28/05, Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org>
wrote:
> Connections are kept open in thread-local storage.  I haven''t
looked
> at scgi_rails, but perhaps it''s spawning threads (and hence new db
> connections) per-request.
That is exactly what it is doing. It creates threads in both listen
and handle_client.  I''m not sure why it was done that way (the
necessity isn''t obvious), but I assume Zed had a good reason.  I
wonder why no one noticed this problem sooner.  I''m tempted to just
modify scgi_rails to remove the thread requirement, but if connection
pooling is coming with 1.0, I''ll just wait.

Also, I noticed that scgi_rails has the following code:

            require_gem ''rails''
            require "config/environment"

Could this make scgi_rails not use edge rails, since the gem is
required before the environment is loaded?  That might explain some
weirdness I experienced earlier, with a gem file location being shown
in a traceback when I was running edge rails under scgi_rails.

Any chance of a patch in the near future?

On 9/28/05, Jeremy Evans
<jeremyevans0-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 9/28/05, Jeremy Kemper
<jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> wrote:
> > Connections are kept open in thread-local storage.  I haven''t
looked
> > at scgi_rails, but perhaps it''s spawning threads (and hence
new db
> > connections) per-request.
>
> That is exactly what it is doing. It creates threads in both listen
> and handle_client.  I''m not sure why it was done that way (the
> necessity isn''t obvious), but I assume Zed had a good reason.  I
> wonder why no one noticed this problem sooner.  I''m tempted to
just
> modify scgi_rails to remove the thread requirement, but if connection
> pooling is coming with 1.0, I''ll just wait.
>
> Also, I noticed that scgi_rails has the following code:
>
>             require_gem ''rails''
>             require "config/environment"
>
> Could this make scgi_rails not use edge rails, since the gem is
> required before the environment is loaded?  That might explain some
> weirdness I experienced earlier, with a gem file location being shown
> in a traceback when I was running edge rails under scgi_rails.
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

I talked to Zed about the edge_rails issue previously, here''s the
solution he suggested:

-----

I figured out a better way which is more "approved" from the
rails-core
folks.  Basically, there''s a slight modification to the scgi_rails
script that makes it load the edge rails dynamically.  Then, if you
want the railties stuff, just copy the
railties/environments/environment.rb and railties/environments/boot.rb
to your config/ directory.

After that you get everything you want.

If you want to try it out now, just change line 90 to read:

require File.dirname(__FILE__) + "/../config/environment"

And copy the two files from vendor and you''ll be hooked up.

-----

Hi Folks,

Missed this thread, I''ve actually heard this from a couple people, but
along with othe weird problems and I could replicate it.  I have a
couple ideas about what could cause it, but I really think that the
scgi_rails use of Threads is just exposing a bad design/usage of
database connection inside AR.  The alternative--not using threads--is
kind of like going back to flint-lock rifles. :-)

Here''s what I know so far, so please augment this:

1)  I have had a couple people mention the DB connections, but only
people using MySQL seem to have the problem.  If anyone sees this with
other databases let me now.  I can''t replicate it myself, so if you
have a bit of a test scenario that would help too.
2)  I don''t see this problem with postgresql.
3)  scgi_rails uses threads to handle incoming requests as fast as
possible.  Without them you''ll run like a one legged dog in quick sand.
4)  The listen method creates one thread which just continually listens
for incoming connections and spawns off handler threads.  Notice the
while loop that seems to never end and the thread.join?  Without this
you''ll get problems trying to juggle when to accept and when to
handle. Don''t remove it, you''ll go insane.
5)  The handle_client function just spawns a thread that does the whole
request processing and then goes away. This is where the multiple
threads actually come in.
6)  If you have a max connection to your DB that you want to maintain
(say, you don''t want to go over 20), then check out the -m option for
now.  This will make every request over 20 (or whatever) avoid Rails.
This also helps keep Rails responsive under heavier loads.  It''s a hack
if you''re needing a fix now.
7)  As far as I know, the connections do go away after the request is
handled.  This may just be normal behavior, so a pooling mechanism is
needed to avoid this (as mentioned before).

In any case, the threads are necessary to get the good speed and
responsiveness.  Without them you''re screwed.

I''ll look at the three things I thought of when I first heard of the
problem:

1)  Dispatcher (or ActiveRecord) might be leaving DB connection close
to the GC.  This is super bad if that''s true.  Use it.  Close it.
2)  MySQL is retarded for some reason.  A lot of the people who
complain about this also complain about other MySQL issues, especially
on OSX.  Since I can''t replicate it I''m inclined to lean this
way, but
will hold verdict until other people see it with other
databases/platforms.
3) The way AR uses MySQL is retarded.  It''s quite possible
it''s just
not closing like in #1, but could also be other weirdness that pisses
off MySQL.

But, I do think that the Threads aren''t really a problem, but just
exposing a bad design/usage scenario in AR.

Keep feeding me information on this and I''ll fix it up.  Connection
pooling is probably the way to go on this IMHO.  Really helps avoid
connection cycling.

Zed A. Shaw
http://www.zedshaw.com/

On Wed, 28 Sep 2005 16:36:45 -0700
Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> On Sep 28, 2005, at 4:26 PM, Jeremy Evans wrote:
> > I''m testing scgi_rails 0.3.1 and it appears to be opening a
new
> > database connection for every request, while keeping all previous
> > connections active (until the database server reaches its connection
> > limit, at which point the site stops working).  This doesn''t
happen
> > under Webrick or FastCGI.  This happens with rails 0.13.1 and edge
> > rails (haven''t tried the beta gems).  Is this a known issue?
Is
> > anyone else experiencing the same problem?  Please let me know what
> > information you need in order to troubleshoot this.
> 
> Connections are kept open in thread-local storage.  I haven''t
looked
> at scgi_rails, but perhaps it''s spawning threads (and hence new db
> connections) per-request.  We''ll be adding a connection pool for
> this sort of scenario before 1.0:  http://dev.rubyonrails.org/ticket/ 
> 2162.  I''ll take a look at scgi_rails in the meantime.
> 
> Regards,
> jeremy
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.2 (Darwin)
> 
> iD8DBQFDOykPAQHALep9HFYRAjV9AKCFk+MQkKx3fcD6nIvIfYDFjkZyTACfSxpe
> HgnPSSxsZwHhe4QO1opZ42w> =NEI7
> -----END PGP SIGNATURE-----
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

Thanks Ben.  It does work with edge rails if you follow the below
instructions.  But, since it''s Rails on the Edge, you can expect it to
go crazy at any moment and rip you a new one.

BTW Ben, did you get this working in your configuration?  I''m assuming
yes, but just to confirm.

On Thu, 29 Sep 2005 14:04:52 +1000
Ben Myles <ben.myles-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> I talked to Zed about the edge_rails issue previously, here''s the
> solution he suggested:
> 
> -----
> 
> I figured out a better way which is more "approved" from the
> rails-core folks.
<snip>

On Wed, 28 Sep 2005 19:42:31 -0700
Jeremy Evans <jeremyevans0-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
<snip>
> Also, I noticed that scgi_rails has the following code:
> 
>             require_gem ''rails''
>             require "config/environment"
> 
Last I checked you needed to do this to get rails to start up inside
the script.  It''s different with the edge rails stuff though. 
I''ll
double check this, but last time I didn''t have the require_gem line it
really got nasty on me.

Zed A. Shaw
http://www.zedshaw.com/

Zed A. Shaw wrote:
> 1)  I have had a couple people mention the DB connections, but only
> people using MySQL seem to have the problem.  If anyone sees this with
> other databases let me now.  I can''t replicate it myself, so if
you
> have a bit of a test scenario that would help too.
> 2)  I don''t see this problem with postgresql.
This problem is with postgresql too. I noticed opening connection per 
request, when run "ab -n 100 -c 20 " against SCGI under Apache2.

Every couple hours (at several requests per minute) the SCGI server
times out on me and i have to restart it (just the scgi, not apache or
linux).  Could the mysql issue be at the root of this?  How would I go
about troubleshooting?  Apache''s error_log just has generic 500
errors, and scgi.log is empty.

Love SCGI in general.  If there''s anything I can do to help, let me
know.

Thanks!
Kyle

On 9/28/05, Lugovoi Nikolai
<meadow.nnick-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Zed A. Shaw wrote:
>
> > 1)  I have had a couple people mention the DB connections, but only
> > people using MySQL seem to have the problem.  If anyone sees this with
> > other databases let me now.  I can''t replicate it myself, so
if you
> > have a bit of a test scenario that would help too.
> > 2)  I don''t see this problem with postgresql.
>
> This problem is with postgresql too. I noticed opening connection per
> request, when run "ab -n 100 -c 20 " against SCGI under Apache2.
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Hi

I have this problem with postgresql too. Each request opens a  
connection and they never get closed.

/ Jonas

On 29 sep 2005, at 07.57, Lugovoi Nikolai wrote:
> Zed A. Shaw wrote:
>
>
>> 1)  I have had a couple people mention the DB connections, but only
>> people using MySQL seem to have the problem.  If anyone sees this  
>> with
>> other databases let me now.  I can''t replicate it myself, so
if you
>> have a bit of a test scenario that would help too.
>> 2)  I don''t see this problem with postgresql.
>>
>
> This problem is with postgresql too. I noticed opening connection  
> per request, when run "ab -n 100 -c 20 " against SCGI under
Apache2.
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>

> > 2)  I don''t see this problem with postgresql.
>
> This problem is with postgresql too. I noticed opening connection per
> request, when run "ab -n 100 -c 20 " against SCGI under Apache2.
I''m using PostgreSQL and lighttpd, so I can confirm the problem is not
MySQL specific.  I don''t know much about ruby threads, but I would
think that you wouldn''t need them as long as you had more worker
processes than concurrent requests.  I''m guessing the benefit to using
threads is that you can handle multiple simultaneous connections per
worker process.  I know when I started using rails it was not
threadsafe, but I remember that changing at some point.
> 1)  Dispatcher (or ActiveRecord) might be leaving DB connection close
> to the GC.  This is super bad if that''s true.  Use it.  Close it.
This sounds likely to me.  If the DB connection is thread local, it
should go away when the thread terminates, but something else must be
holding a reference to it.

I can''t really use SCGI until this issue is fixed, but hopefully by
rails 1.0 it will be.  SCGI setup is nice and easy, much more so than
FastCGI (though lighttpd/FastCGI has been trouble free once setup). 
Personally, I''d like to see scgi_rails included in rails, so it is
supported out of the box.

Jeremy Evans wrote:
>>>2)  I don''t see this problem with postgresql.
>>
>>This problem is with postgresql too. I noticed opening connection per
>>request, when run "ab -n 100 -c 20 " against SCGI under
Apache2.
> 
> 
> I''m using PostgreSQL and lighttpd, so I can confirm the problem is
not
> MySQL specific.  I don''t know much about ruby threads, but I would
> think that you wouldn''t need them as long as you had more worker
> processes than concurrent requests.  I''m guessing the benefit to
using
> threads is that you can handle multiple simultaneous connections per
> worker process.  I know when I started using rails it was not
> threadsafe, but I remember that changing at some point.
I don''t think it has changed. On 20th June 2005, in the thread
"[Rails]
downsides to WEBrick?", Tobi Luetke wrote:

"Hmm as far as I know webrick is multi threaded by default. Its just
that the rails dispatcher puts in a big mutex around the request
because ActionController isn''t."

and DHH responded:

"Right, but AC should be fairly easy to make thread safe. Each request
is already being handled by a new instance, so I think its just a few
simple details holding back the thread support. And it would indeed
speed up WEBrick considerably if it could loose the mutex."

I haven''t heard of any change since then.
>>1)  Dispatcher (or ActiveRecord) might be leaving DB connection close
>>to the GC.  This is super bad if that''s true.  Use it.  Close
it.
> 
> 
> This sounds likely to me.  If the DB connection is thread local, it
> should go away when the thread terminates, but something else must be
> holding a reference to it.
> 
> I can''t really use SCGI until this issue is fixed, but hopefully
by
> rails 1.0 it will be.  SCGI setup is nice and easy, much more so than
> FastCGI (though lighttpd/FastCGI has been trouble free once setup). 
> Personally, I''d like to see scgi_rails included in rails, so it is
> supported out of the box.
I was assuming/hoping that Rails under SCGI would have the same 
listening/processing model as under FastCGI, would use a persistent 
database connection in the same way, and wouldn''t require an
application
or the libraries it used to be thread-safe.

regards

    Justin

On Thu, 29 Sep 2005 01:07:07 -0700
Jeremy Evans <jeremyevans0-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > 1)  Dispatcher (or ActiveRecord) might be leaving DB connection
> > close to the GC.  This is super bad if that''s true.  Use it. 
Close
> > it.
> 
> This sounds likely to me.  If the DB connection is thread local, it
> should go away when the thread terminates, but something else must be
> holding a reference to it.
Aha! Wait, the connection is thread local, and *scgi_rails* is not
explicitly joining the thread and reaping it.  That''d make the
connections leak.  I bet that''s the cause.

Because, if Dispatcher was doing it, then it wouldn''t make sense since
I run each Dispatcher.dispatch call inside a synchronize block, so it
technically runs rails "single threaded" to keep it happy.

I''ll look at this.

Zed

On Thu, 29 Sep 2005 10:11:34 +0100
Justin Forder <justin-zSfPWr5aQuznITO/+xaoB7VCufUGDwFn@public.gmane.org>
wrote:
> 
> I was assuming/hoping that Rails under SCGI would have the same 
> listening/processing model as under FastCGI, would use a persistent 
> database connection in the same way, and wouldn''t require an
> application or the libraries it used to be thread-safe.
> 
It doesn''t.  I synchronize the Dispatcher.dispatch call so that Rails
will run in the way it expects and not fight with other threads.  The
only time threading is used is for the all the other SCGI related
processing.

Zed A. Shaw
http://www.zedshaw.com/

Zed A. Shaw wrote:
> On Thu, 29 Sep 2005 10:11:34 +0100
> Justin Forder
<justin-zSfPWr5aQuznITO/+xaoB7VCufUGDwFn@public.gmane.org> wrote:
> 
> 
>>I was assuming/hoping that Rails under SCGI would have the same 
>>listening/processing model as under FastCGI, would use a persistent 
>>database connection in the same way, and wouldn''t require an
>>application or the libraries it used to be thread-safe.
>>
> 
> 
> It doesn''t.  I synchronize the Dispatcher.dispatch call so that
Rails
> will run in the way it expects and not fight with other threads.  The
> only time threading is used is for the all the other SCGI related
> processing.
That''s good to hear. But it *does* appear that the listening/processing
model and the use of database connections are different from FastCGI 
behaviour. You wrote that without the multi-threading you would not have 
sufficient responsiveness. Could you give some measurements? And if 
Rails itself is single-threaded, why carry the overhead of opening and 
closing database connections per-thread within the SCGI process?

I believe that the issues around integrating Rails with a well-known and 
trusted web server (Apache), on all the platforms supported by that 
server, are crucial to the success of Rails. I''m hoping you have the 
answer, even if it needs a few tweaks.

Keep up the good work!

   Justin

On freebsd 5.4-STABLE, running under apache with Postgresql 8.0.1 and rails
0.13.1 each request makes a new connection, and the connection is held until
the next request, at which point the current database connection is dropped
and a new one opened.


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Justin, the problem with many DB connections is mostly solved by
setting ActiveRecord::Base.threaded_connections = false in your
environment.rb.  If you wait for the next scgi_rails release then
you''ll get this for free.

The problems with AR and threading are actually deeper than this
though.  The scgi_rails thread usage just made it rear it''s ugly head.
Basically, nothing in AR about threads is really working, so I''m
contemplating getting and attacking the AR code in the near future.

As for performance metrics:  Yes, way back in the day I wrote a simple
looping one that handled all requests simultaneously.  It was about the
same speed, but it''s performance degraded much quicker as the
connection
rate went higher.

But, feel free to rip out the threads in the scgi_rails script and try
yourself.  I''m all about evidence.  Remember when you test to use a
tool like httperf to get accurate stats, and make sure you test a
simultaneous rate of hits (like 50 connections/second) so that you see
the number of concurrent connections increase.

Zed A. Shaw
http://www.zedshaw.com/


On Fri, 30 Sep 2005 01:29:49 +0100
Justin Forder <justin-zSfPWr5aQuznITO/+xaoB7VCufUGDwFn@public.gmane.org>
wrote:
> Zed A. Shaw wrote:
> > It doesn''t.  I synchronize the Dispatcher.dispatch call so
that
> > Rails will run in the way it expects and not fight with other
> > threads.  The only time threading is used is for the all the other
> > SCGI related processing.
> 
> That''s good to hear. But it *does* appear that the
> listening/processing model and the use of database connections are
> different from FastCGI behaviour. You wrote that without the
> multi-threading you would not have sufficient responsiveness. Could
> you give some measurements? And if Rails itself is single-threaded,
> why carry the overhead of opening and closing database connections
> per-thread within the SCGI process?