Thread: connections problem

connections problem

From
ivan
Date:
hi

i need to connect to by my database more then 100 connections,
but after ~20-30 conn, postmaster says me "Resource temporarily
unavailable", what are this resource ?

im using debain with kernel 2.4.23-pre7, on P4 , (postgres 7.4.1)
what can be wrong ?




Re: connections problem

From
"Joshua D. Drake"
Date:
>hi
>
>i need to connect to by my database more then 100 connections,
>but after ~20-30 conn, postmaster says me "Resource temporarily
>  
>
>unavailable", what are this resource ?
>  
>

We need more info. What does your postgresql.conf say about
max_connections? How much ram do you have? How much
shared memory have you allocated?

Sincerely,


Joshua D. Drake




>im using debain with kernel 2.4.23-pre7, on P4 , (postgres 7.4.1)
>what can be wrong ?
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>  
>

-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
Postgresql support, programming, shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com




Re: connections problem

From
ivan
Date:
max_connections=200
shared_buffers=2000

ram = 500M + 300M swap
hdd = infinite


On Fri, 26 Dec 2003, Joshua D. Drake wrote:

>
> >hi
> >
> >i need to connect to by my database more then 100 connections,
> >but after ~20-30 conn, postmaster says me "Resource temporarily
> >
> >
> >unavailable", what are this resource ?
> >
> >
>
> We need more info. What does your postgresql.conf say about
> max_connections? How much ram do you have? How much
> shared memory have you allocated?
>
> Sincerely,
>
>
> Joshua D. Drake
>
>
>
>
> >im using debain with kernel 2.4.23-pre7, on P4 , (postgres 7.4.1)
> >what can be wrong ?
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> >      subscribe-nomail command to majordomo@postgresql.org so that your
> >      message can get through to the mailing list cleanly
> >
> >
>
> --
> Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
> Postgresql support, programming, shared hosting and dedicated hosting.
> +1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
>
>


Re: connections problem

From
Tom Lane
Date:
ivan <iv@psycho.pl> writes:
> i need to connect to by my database more then 100 connections,
> but after ~20-30 conn, postmaster says me "Resource temporarily
> unavailable", what are this resource ?

First bet is that you are running into a maximum-number-of-processes-
per-user limit imposed by "ulimit -u".

It's also possible that you need more RAM or swap space.
        regards, tom lane


Re: connections problem

From
"Joshua D. Drake"
Date:
Hello, Perhaps you have too many open files? What else is running on this 
machine?

Sincerely,

Joshua D. Drake


ivan wrote:

>max_connections=200
>shared_buffers=2000
>
>ram = 500M + 300M swap
>hdd = infinite
>
>
>On Fri, 26 Dec 2003, Joshua D. Drake wrote:
>
>  
>
>>>hi
>>>
>>>i need to connect to by my database more then 100 connections,
>>>but after ~20-30 conn, postmaster says me "Resource temporarily
>>>
>>>
>>>unavailable", what are this resource ?
>>>
>>>
>>>      
>>>
>>We need more info. What does your postgresql.conf say about
>>max_connections? How much ram do you have? How much
>>shared memory have you allocated?
>>
>>Sincerely,
>>
>>
>>Joshua D. Drake
>>
>>
>>
>>
>>    
>>
>>>im using debain with kernel 2.4.23-pre7, on P4 , (postgres 7.4.1)
>>>what can be wrong ?
>>>
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>>     subscribe-nomail command to majordomo@postgresql.org so that your
>>>     message can get through to the mailing list cleanly
>>>
>>>
>>>      
>>>
>>--
>>Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
>>Postgresql support, programming, shared hosting and dedicated hosting.
>>+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
>>
>>
>>    
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>  
>

-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
Postgresql support, programming, shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com




Re: connections problem

From
ivan
Date:

.. many others stuf ...

the error is : could not fork new process for connection: Resource
temporarily unavailable


On Fri, 26 Dec 2003, Joshua D. Drake wrote:

> Hello,
>
>   Perhaps you have too many open files? What else is running on this
> machine?
>
> Sincerely,
>
> Joshua D. Drake
>
>
> ivan wrote:
>
> >max_connections=200
> >shared_buffers=2000
> >
> >ram = 500M + 300M swap
> >hdd = infinite
> >
> >
> >On Fri, 26 Dec 2003, Joshua D. Drake wrote:
> >
> >
> >
> >>>hi
> >>>
> >>>i need to connect to by my database more then 100 connections,
> >>>but after ~20-30 conn, postmaster says me "Resource temporarily
> >>>
> >>>
> >>>unavailable", what are this resource ?
> >>>
> >>>
> >>>
> >>>
> >>We need more info. What does your postgresql.conf say about
> >>max_connections? How much ram do you have? How much
> >>shared memory have you allocated?
> >>
> >>Sincerely,
> >>
> >>
> >>Joshua D. Drake
> >>
> >>
> >>
> >>
> >>
> >>
> >>>im using debain with kernel 2.4.23-pre7, on P4 , (postgres 7.4.1)
> >>>what can be wrong ?
> >>>
> >>>
> >>>
> >>>---------------------------(end of broadcast)---------------------------
> >>>TIP 3: if posting/reading through Usenet, please send an appropriate
> >>>     subscribe-nomail command to majordomo@postgresql.org so that your
> >>>     message can get through to the mailing list cleanly
> >>>
> >>>
> >>>
> >>>
> >>--
> >>Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
> >>Postgresql support, programming, shared hosting and dedicated hosting.
> >>+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
> >>
> >>
> >>
> >>
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 7: don't forget to increase your free space map settings
> >
> >
>
> --
> Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
> Postgresql support, programming, shared hosting and dedicated hosting.
> +1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
>
>


Re: connections problem

From
ivan
Date:
ulimit -u = 256 ,it's ok, ?

On Fri, 26 Dec 2003, Tom Lane wrote:

> ivan <iv@psycho.pl> writes:
> > i need to connect to by my database more then 100 connections,
> > but after ~20-30 conn, postmaster says me "Resource temporarily
> > unavailable", what are this resource ?
>
> First bet is that you are running into a maximum-number-of-processes-
> per-user limit imposed by "ulimit -u".
>
> It's also possible that you need more RAM or swap space.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>


Re: connections problem

From
Tom Lane
Date:
> ivan <iv@psycho.pl> writes:
>>> i need to connect to by my database more then 100 connections,
>>> but after ~20-30 conn, postmaster says me "Resource temporarily
>>> unavailable", what are this resource ?

> ulimit -u = 256 ,
>  it's ok, ?

Mph.  Well, that doesn't seem to be the problem, unless you are starting
a whole lot of other processes besides the backends under the same UID.
(However: are you sure the postmaster has been started with this same
ulimit setting?)

It sounds like some system call is failing with EAGAIN, but it's not
real clear which one.  If you're running 7.4, let's see the verbose form
of the error message (see log_error_verbosity, or set psql's VERBOSITY
variable).  If not, updating to 7.4 might be the easiest way to get more
info.
        regards, tom lane


Re: connections problem

From
Tom Lane
Date:
ivan <iv@psycho.pl> writes:
> the error is : could not fork new process for connection: Resource
> temporarily unavailable

We could have saved some time if you'd spelled out the full error
message to begin with.

This is undoubtedly a kernel-imposed restriction you are running into.
I'm not sure if the failure is related to sheer number of processes,
number of open files, or RAM/swap space, but one way or another you are
trying to create more processes than your kernel will support.

It seems moderately unlikely that twenty or thirty Postgres processes
would by themselves be able to run the kernel out of resources (although
if the problem is related to number of open files, reducing
max_files_per_process might help).  What else do you have running on
this machine?
        regards, tom lane


Re: connections problem

From
ivan
Date:

from log :
LOG:  could not fork new process for connection: Resource temporarily unavailable
LOG:  could not fork checkpoint process: Resource temporarily unavailable
....


ulimit -a :

core file size        (blocks, -c) 0
data seg size         (kbytes, -d) unlimited
file size             (blocks, -f) unlimited
max locked memory     (kbytes, -l) unlimited
max memory size       (kbytes, -m) unlimited
open files                    (-n) 2048
pipe size          (512 bytes, -p) 8
stack size            (kbytes, -s) 15000
cpu time             (seconds, -t) unlimited
max user processes            (-u) 400
virtual memory        (kbytes, -v) unlimited

On Fri, 26 Dec 2003, Tom Lane wrote:

> ivan <iv@psycho.pl> writes:
> > the error is : could not fork new process for connection: Resource
> > temporarily unavailable
>
> We could have saved some time if you'd spelled out the full error
> message to begin with.
>
> This is undoubtedly a kernel-imposed restriction you are running into.
> I'm not sure if the failure is related to sheer number of processes,
> number of open files, or RAM/swap space, but one way or another you are
> trying to create more processes than your kernel will support.
>
> It seems moderately unlikely that twenty or thirty Postgres processes
> would by themselves be able to run the kernel out of resources (although
> if the problem is related to number of open files, reducing
> max_files_per_process might help).  What else do you have running on
> this machine?
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>