Postgres max_connections & multithreading

[Marco Sehrer [ninjaconcept.com]]

Hi Group,

we have a lot of LocalParticipants which need to connect  Postgres (via ActiveRecord).

Trying to run them multithreaded leads to too many postgres connections quite fast:

postgres.log:

FATAL:  sorry, too many clients already

Finally we run all Participants connecting to postgres via:

def do_not_thread

    true

end

regardless of multithreaded or not I encountered the following warning:

postgres.log

LOG:  unexpected EOF on client connection

btw. our Postgres (version 9.1) max_connections is set to 100.

the scenario happens regardless of the number of ruote-workers (1-5) 

Thanks a lot!

Marco

[John Mettraux]

On Tue, Jul 31, 2012 at 12:32:31PM +0200, Marco Sehrer [ninjaconcept.com] wrote:
> Hi Group,

Hello Marco,

> we have a lot of LocalParticipants which need to connect Postgres (via ActiveRecord).
> Trying to run them multithreaded leads to too many postgres connections quite fast:
>
> postgres.log:
> FATAL: sorry, too many clients already
>
> Finally we run all Participants connecting to postgres via:
>
> def do_not_thread
> true
> end

Well done, limiting the participants to use the same thread as the worker
should limit them to use the one connection bound to the thread (IIRC that's
how activerecord does its connection management).

Thanks for sharing the tip!

> regardless of multithreaded or not I encountered the following warning:
>
> postgres.log
> LOG: unexpected EOF on client connection

Sorry, I don't know what your participant looks like, how db-intensive it is.

I'd google for that error message, maybe pitching in "activerecord".

> btw. our Postgres (version 9.1) max_connections is set to 100.
> the scenario happens regardless of the number of ruote-workers (1-5)

Well, Ruby runtimes won't share connections...

By scenario, do you mean the scenario with #do_not_thread set to false that
triggers the "FAIL: too many clients already" or the scenario where
#do_not_thread is set to true and triggers "LOG: unexpected EOF"?

I am sorry, I have trouble understanding the variant articulation in your
report.


Best regards,

--
John Mettraux - http://lambda.io/jmettraux

[Marco Sehrer [ninjaconcept.com]]

Hi John, 

thanks for the fast response! 

I was digging deeper, and found a workaround :-)

begin

   .. process the AR model .. 

ensure     

   ActiveRecord::Base.clear_active_connections!

end

we have a lot of LocalParticipants which need to connect  Postgres (via ActiveRecord).

Trying to run them multithreaded leads to too many postgres connections quite fast:

postgres.log:

FATAL:  sorry, too many clients already

Finally we run all Participants connecting to postgres via:

def do_not_thread

 true

end

Well done, limiting the participants to use the same thread as the worker
should limit them to use the one connection bound to the thread (IIRC that's
how activerecord does its connection management).

Thanks for sharing the tip!

regardless of multithreaded or not I encountered the following warning:

postgres.log

LOG:  unexpected EOF on client connection

LOG:  unexpected EOF on client connection

was because of an resque-background job .. seams not to be the fault of ruote :-)

Sorry, I don't know what your participant looks like, how db-intensive it is.

I'd google for that error message, maybe pitching in "activerecord".

btw. our Postgres (version 9.1) max_connections is set to 100.

the scenario happens regardless of the number of ruote-workers (1-5)

Well, Ruby runtimes won't share connections...

By scenario, do you mean the scenario with #do_not_thread set to false that
triggers the "FAIL: too many clients already" or the scenario where
#do_not_thread is set to true and triggers "LOG: unexpected EOF"?

I am sorry, I have trouble understanding the variant articulation in your
report.

Now in my participant I use this workaround for the 'too many clients already' error.

begin

   .. process the AR model .. 

ensure     

   ActiveRecord::Base.clear_active_connections!

end

Seams to solve the problem :-)

Thanks,

Marco

Best regards,

--
John Mettraux - http://lambda.io/jmettraux

--
you received this message because you are subscribed to the "ruote users" group.
to post : send email to openwfe...@googlegroups.com
to unsubscribe : send email to openwferu-use...@googlegroups.com
more options : http://groups.google.com/group/openwferu-users?hl=en

Schöne Grüße Marco

-- 

NinjaConcept GmbH
Marco Sehrer Geschäftsführung
Amalienstrasse. 44
76133 Karlsruhe
fon:	(+49) 0721 1803523-1
fax:	(+49) 721 961402-99
mobile:	(+49) 151 20314416
email:	m...@ninjaconcept.com
www:	http://www.ninjaconcept.com/

[Hartog De Mik]

Marco,

Don't know if you saw my comments on #ruote - but did you have a look at pgpool (www.pgpool.net) or pgbouncer (http://pgbouncer.projects.postgresql.org/)? 

You would ''just'' have to beware of this issue: https://github.com/rails/rails/issues/1627

Kind regards,

Hartog.

2012/7/31 Marco Sehrer [ninjaconcept.com] <m...@ninjaconcept.com>

[Marco Sehrer [ninjaconcept.com]]

Hi Hartog,

thanks a lot, I didn't know about pgpool + pgbouncer.

looking forward for an afterwork beer after holiday :-)

Bye

Hartog.

Schöne Grüße Marco

-- 

<logo_240x60.gif>