"Too many open files in system" error

--001517478d8070fd5c0470f4aed7
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Greetings,
We're seeing numerous "LOG: out of file descriptors: Too many open files in
system; release and retry" entries as well as quite a few "LOG: could not
open temporary statistics file "global/pgstat.tmp": Too many open files in
system"
Much more alarming however, we're seeing errors such as:
FATAL: could not open file "global/pg_database": Too many open files in
system
ERROR: could not open relation 1663/2219053/2601: Too many open files in
system

kern.maxfiles is currently set to 12328 and "sysctl -a | grep
kern.openfiles" showed over 10.000 open files prior to a reboot.
After the reboot kern.openfiles quickly increased from less than 200 to over
4000 and appears to be ever increasing.
"lsof | grep postgres | wc -l" showed approximately 4400 just after reboot
but appears to be increasing at a steady pace (have increased to around 4800
in 30 min)

System is running FreeBSD 7.1 with 2GB RAM and have been running without
issues for over 6 months with PostGreSQL 8.3.6.
We upgraded to PostGreSQL 8.3.7 around a month ago, not sure if this is
related?
The server is a dedicated file server running PostGreSQL and Subversion.
Subversion has very few users so it shouldn't be an issue.

We've tried changing max_files_per_process to 500 from the default 1000 but
this doesn't appear to have had any effect.
Other changes from the default configuration include:
max_connections = 256
shared_buffers = 768MB
temp_buffers = 96MB
max_prepared_transactions = 50
work_mem = 48MB
maintenance_work_mem = 192MB
max_stack_depth = 8MB

Is it normal for PostGreSQL to have close to 5000 file handles open while
running?
Or is there some other circumstance which could cause file descriptors to
leak?

Appreciate any input

Cheers
Jona

--001517478d8070fd5c0470f4aed7
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Greetings, We're seeing numerous "LOG:=A0 out of file descripto=
rs: Too many open files in system; release and retry" entries as well =
as quite a few "LOG:=A0 could not open temporary statistics file "=
;global/pgstat.tmp": Too many open files in system" 
Much more alarming however, we're seeing errors such as: FATAL:=A0 c=
ould not open file "global/pg_database": Too many open files in s=
ystem ERROR:=A0 could not open relation 1663/2219053/2601: Too many open=
files in system 
 kern.maxfiles is currently set to 12328 and "sysctl -a | grep kern=
..openfiles" showed over 10.000 open files prior to a reboot. After =
the reboot kern.openfiles quickly increased from less than 200 to over 4000=
and appears to be ever increasing. 
"lsof | grep postgres | wc -l" showed approximately 4400 just aft=
er reboot but appears to be increasing at a steady pace (have increased to =
around 4800 in 30 min) System is running FreeBSD 7.1 with 2GB RAM an=
d have been running without issues for over 6 months with PostGreSQL 8.3.6.=
 

We upgraded to PostGreSQL 8.3.7 around a month ago, not sure if this is rel=
ated? The server is a dedicated file server running PostGreSQL and Subve=
rsion. Subversion has very few users so it shouldn't be an issue. 
 We've tried changing max_files_per_process to 500 fro=
m the default 1000 but this doesn't appear to have had any effect. O=
ther changes from the default configuration include: max_connections =3D=
256 
shared_buffers =3D 768MB temp_buffers =3D 96MB max_prepared_transacti=
ons =3D 50 work_mem =3D 48MB maintenance_work_mem =3D 192MB max_st=
ack_depth =3D 8MB Is it normal for PostGreSQL to have close to 5000 =
file handles open while running? 
Or is there some other circumstance which could cause file descriptors to l=
eak? Appreciate any input Cheers Jona 

--001517478d8070fd5c0470f4aed7--

2009/8/12 Jonatan Evald Buus <jonatan.buus [at] cellpointmobile.com>:
> Greetings,
> We're seeing numerous "LOG:=C2=A0 out of file descriptors: Too many open =
files in
> system; release and retry" entries as well as quite a few "LOG:=C2=A0 cou=
ld not
> open temporary statistics file "global/pgstat.tmp": Too many open files in
> system"
> Much more alarming however, we're seeing errors such as:
> FATAL:=C2=A0 could not open file "global/pg_database": Too many open file=
s in
> system
> ERROR:=C2=A0 could not open relation 1663/2219053/2601: Too many open fil=
es in
> system
>
> kern.maxfiles is currently set to 12328 and "sysctl -a | grep
> kern.openfiles" showed over 10.000 open files prior to a reboot.
> After the reboot kern.openfiles quickly increased from less than 200 to o=
ver

Did you request about kern.maxfiles?

--
Emanuel Calvo Franco
Database consultant at:
www.siu.edu.ar
www.emanuelcalvofranco.com.ar

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--000e0cd483203df81f0470f57dfb
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

since the message is "*out of file descriptors: Too many open files in
system; release and retry*" i think you should also be checking the system
ulimit.

try commands like "ulimit -a" to check the number of files currently open in
the system. Also try "ulimit -Hn" to get "The maximum number of open file
descriptors (most systems do not allow this value to be set)" see man pages
of ulimit for more details.

you could also increase the ulimit by making changes in
"ac/etc/security/limits.conf".

However i hope you have already checked the possibility of some other
process which might be continuously opening files and not closing it
properly. Linux has a fixed number of fd's that can be used and if the
process dont relese them it coudl lead to such a problem.

Hope this helps.

Thanks,
- Jinson.

On Wed, Aug 12, 2009 at 10:44 PM, Emanuel Calvo Franco <
postgres.arg [at] gmail.com> wrote:

> 2009/8/12 Jonatan Evald Buus <jonatan.buus [at] cellpointmobile.com>:
> > Greetings,
> > We're seeing numerous "LOG: out of file descriptors: Too many open files
> in
> > system; release and retry" entries as well as quite a few "LOG: could
> not
> > open temporary statistics file "global/pgstat.tmp": Too many open files
> in
> > system"
> > Much more alarming however, we're seeing errors such as:
> > FATAL: could not open file "global/pg_database": Too many open files in
> > system
> > ERROR: could not open relation 1663/2219053/2601: Too many open files in
> > system
> >
> > kern.maxfiles is currently set to 12328 and "sysctl -a | grep
> > kern.openfiles" showed over 10.000 open files prior to a reboot.
> > After the reboot kern.openfiles quickly increased from less than 200 to
> over
>
> Did you request about kern.maxfiles?
>
>
> --
> Emanuel Calvo Franco
> Database consultant at:
> www.siu.edu.ar
> www.emanuelcalvofranco.com.ar
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--000e0cd483203df81f0470f57dfb
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

since the message is "out of file descriptors: Too many open files =
in system; release and retry" i think you should also be checking =
the system ulimit. try commands like "ulimit -a" to check =
the number of files currently open in the system. Also try "ulimit -Hn=
" to get=A0 "The maximum number of open file descriptors (most sy=
stems do not allow this value to be set)" see man pages of ulimit for =
more details. 
 you could also increase the ulimit by making changes in "ac/etc/se=
curity/limits.conf". However i hope you have already checked t=
he possibility of some other process which might be continuously opening f=
iles and not closing it properly. Linux has a fixed number of fd's that=
can be used and if the process dont relese them it coudl lead to such a pr=
oblem. 
 Hope this helps. Thanks, =A0- Jinson. <div class=
=3D"gmail_quote">On Wed, Aug 12, 2009 at 10:44 PM, Emanuel Calvo Franco <sp=
an dir=3D"ltr"><<a href=3D"mailto:postgres.arg [at] gmail.com">postgres.arg [at] g=
mail.com</a>> wrote: 
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">2009/8/12 Jonatan=
Evald Buus <<a href=3D"mailto:jonatan.buus [at] cellpointmobile.com">jonatan=
..buus [at] cellpointmobile.com</a>>: 

<div class=3D"im">> Greetings, 
> We're seeing numerous "LOG:=A0 out of file descriptors: Too m=
any open files in 
> system; release and retry" entries as well as quite a few "L=
OG:=A0 could not 
> open temporary statistics file "global/pgstat.tmp": Too many=
open files in 
> system" 
> Much more alarming however, we're seeing errors such as: 
> FATAL:=A0 could not open file "global/pg_database": Too many=
open files in 
> system 
> ERROR:=A0 could not open relation 1663/2219053/2601: Too many open fil=
es in 
> system 
> 
> kern.maxfiles is currently set to 12328 and "sysctl -a | grep 
> kern.openfiles" showed over 10.000 open files prior to a reboot.<=
br>
> After the reboot kern.openfiles quickly increased from less than 200 t=
o over 
 
</div>Did you request about kern.maxfiles? 
 
 
-- 
=A0 =A0 =A0 =A0 =A0 =A0 =A0Emanuel Calvo Franco 
=A0 =A0 =A0 =A0 =A0 =A0 Database consultant at: 
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0<a href=3D"http://www.siu.edu.ar" t=
arget=3D"_blank">www.siu.edu.ar</a> 
=A0 =A0 =A0 =A0<a href=3D"http://www.emanuelcalvofranco.com.ar" target=3D"=
_blank">www.emanuelcalvofranco.com.ar</a> 
 
-- 
Sent via pgsql-admin mailing list (<a href=3D"mailto:pgsql-admin [at] postgresql=
..org">pgsql-admin [at] postgresql.org</a>) 
To make changes to your subscription: 
<a href=3D"http://www.postgresql.org/mailpref/pgsql-admin" target=3D"_blank=
">http://www.postgresql.org/mailpref/pgsql-admin</a> 
</blockquote></div> 

--000e0cd483203df81f0470f57dfb--

--00151747b690fc9bf10470f6445f
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

"ulimit -Hn" shows 11095 which is the same as kern.maxfilesperproc.

"ulimit -a" shows the following
socket buffer size (bytes, -b) unlimited
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) 524288
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 11095 <-------------- Samee as
kern.maxfilesperproc
pipe size (512 bytes, -p) 1
stack size (kbytes, -s) 65536
cpu time (seconds, -t) unlimited
max user processes (-u) 5547
virtual memory (kbytes, -v) unlimited

"sysctl -a | grep kern.maxfile" gives the following kernel limits:
kern.maxfiles: 12328
kern.maxfilesperproc: 11095

PostGreSQL seems to have settled around 4800 open file descriptors as
counted by "lsof | grep postgres | wc -l" and the number of open files
around 3500 as indicated by "sysctl -a | grep kern.openfile"

The only things that's running on the machine is PostGreSQL, hence first
suspicion naturally falls on the database.
It just seems odd that it'd start behaving like this after having run with
no problems for 6 months.

Any insight or suggestions as to where to start digging would be greatly
appreciated

Cheers
Jona

On Wed, Aug 12, 2009 at 7:52 PM, jinson abraham <abraham.jinson [at] gmail.com>wrote:

> since the message is "*out of file descriptors: Too many open files in
> system; release and retry*" i think you should also be checking the system
> ulimit.
>
> try commands like "ulimit -a" to check the number of files currently open
> in the system. Also try "ulimit -Hn" to get "The maximum number of open
> file descriptors (most systems do not allow this value to be set)" see man
> pages of ulimit for more details.
>
> you could also increase the ulimit by making changes in
> "ac/etc/security/limits.conf".
>
> However i hope you have already checked the possibility of some other
> process which might be continuously opening files and not closing it
> properly. Linux has a fixed number of fd's that can be used and if the
> process dont relese them it coudl lead to such a problem.
>
> Hope this helps.
>
> Thanks,
> - Jinson.
>
>
> On Wed, Aug 12, 2009 at 10:44 PM, Emanuel Calvo Franco <
> postgres.arg [at] gmail.com> wrote:
>
>> 2009/8/12 Jonatan Evald Buus <jonatan.buus [at] cellpointmobile.com>:
>> > Greetings,
>> > We're seeing numerous "LOG: out of file descriptors: Too many open
>> files in
>> > system; release and retry" entries as well as quite a few "LOG: could
>> not
>> > open temporary statistics file "global/pgstat.tmp": Too many open files
>> in
>> > system"
>> > Much more alarming however, we're seeing errors such as:
>> > FATAL: could not open file "global/pg_database": Too many open files in
>> > system
>> > ERROR: could not open relation 1663/2219053/2601: Too many open files
>> in
>> > system
>> >
>> > kern.maxfiles is currently set to 12328 and "sysctl -a | grep
>> > kern.openfiles" showed over 10.000 open files prior to a reboot.
>> > After the reboot kern.openfiles quickly increased from less than 200 to
>> over
>>
>> Did you request about kern.maxfiles?
>>
>>
>> --
>> Emanuel Calvo Franco
>> Database consultant at:
>> www.siu.edu.ar
>> www.emanuelcalvofranco.com.ar
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
>

--
JONATAN EVALD BUUS

Executive Vice President Open Systems and Telecommunications

Mobile US +1 (305) 331-5242
Mobile DK +45 2888 2861
Telephone +1 (305) 777-0392
Fax. +1 (305) 777-0449
jonatan.buus [at] cellpointmobile.com
www.cellpointmobile.com

CellPoint Mobile Inc.
4000 Ponce de Leon Boulevard
Suite 470
Coral Gables, FL 33146
USA

'Mobilizing the Enterprise'

--00151747b690fc9bf10470f6445f
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

"ulimit -Hn" shows 11095 which is the same as kern.maxfilesperpro=
c. "ulimit -a" shows the following socket buffer size=
=A0=A0=A0=A0=A0=A0 (bytes, -b) unlimited core file size=A0=A0=A0=A0=A0=
=A0=A0=A0=A0 (blocks, -c) unlimited 
data seg size=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (kbytes, -d) 524288 file siz=
e=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (blocks, -f) unlimited max l=
ocked memory=A0=A0=A0=A0=A0=A0 (kbytes, -l) unlimited max memory size=A0=
=A0=A0=A0=A0=A0=A0=A0 (kbytes, -m) unlimited open files=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (-n) 11095 <-----------=
--- Samee as kern.maxfilesperproc 
pipe size=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (512 bytes, -p) 1 stack size=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (kbytes, -s) 65536 cpu time=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (seconds, -t) unlimited max user=
processes=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (-u) 5547 virtual memo=
ry=A0=A0=A0=A0=A0=A0=A0=A0=A0 (kbytes, -v) unlimited 
 "sysctl -a | grep kern.maxfile" gives the following kernel li=
mits: 
kern.maxfiles: 12328 
kern.maxfilesperproc: 11095 
 PostGreSQL seems to have settled around 4800 open file descriptors as c=
ounted by "lsof | grep postgres | wc -l" and the number of open f=
iles around 3500 as indicated by "sysctl -a | grep kern.openfile"=
 
 The only things that's running on the machine is PostGreSQL, hence =
first suspicion naturally falls on the database. It just seems odd that =
it'd start behaving like this after having run with no problems for 6 m=
onths. 
 Any insight or suggestions as to where to start digging would be greatl=
y appreciated Cheers Jona <div class=3D"gmail_quote">On We=
d, Aug 12, 2009 at 7:52 PM, jinson abraham <<a href=3D=
"mailto:abraham.jinson [at] gmail.com">abraham.jinson [at] gmail.com</a>> w=
rote: 
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">since the message=
is "out of file descriptors: Too many open files in system; releas=
e and retry" i think you should also be checking the system ulimit=
.. 
 try commands like "ulimit -a" to check the number of files cu=
rrently open in the system. Also try "ulimit -Hn" to get=A0 "=
;The maximum number of open file descriptors (most systems do not allow thi=
s value to be set)" see man pages of ulimit for more details. 

 you could also increase the ulimit by making changes in "ac/etc/se=
curity/limits.conf". However i hope you have already checked t=
he possibility of some other process which might be continuously opening f=
iles and not closing it properly. Linux has a fixed number of fd's that=
can be used and if the process dont relese them it coudl lead to such a pr=
oblem. 

 Hope this helps. Thanks, =A0- Jinson. <div class=
=3D"gmail_quote"><div><div></div><div class=3D"h5">On Wed, Aug 12, 2009 at =
10:44 PM, Emanuel Calvo Franco <<a href=3D"mailto:post=
gres.arg [at] gmail.com" target=3D"_blank">postgres.arg [at] gmail.com</a>>=
wrote: 

</div></div><blockquote class=3D"gmail_quote" style=3D"border-left: 1px sol=
id rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div>=
<div></div><div class=3D"h5">2009/8/12 Jonatan Evald Buus <<a href=3D"ma=
ilto:jonatan.buus [at] cellpointmobile.com" target=3D"_blank">jonatan.buus [at] cellp=
ointmobile.com</a>>: 

<div>> Greetings, 
> We're seeing numerous "LOG:=A0 out of file descriptors: Too m=
any open files in 
> system; release and retry" entries as well as quite a few "L=
OG:=A0 could not 
> open temporary statistics file "global/pgstat.tmp": Too many=
open files in 
> system" 
> Much more alarming however, we're seeing errors such as: 
> FATAL:=A0 could not open file "global/pg_database": Too many=
open files in 
> system 
> ERROR:=A0 could not open relation 1663/2219053/2601: Too many open fil=
es in 
> system 
> 
> kern.maxfiles is currently set to 12328 and "sysctl -a | grep 
> kern.openfiles" showed over 10.000 open files prior to a reboot.<=
br>
> After the reboot kern.openfiles quickly increased from less than 200 t=
o over 
 
</div>Did you request about kern.maxfiles? 
 
 
-- 
=A0 =A0 =A0 =A0 =A0 =A0 =A0Emanuel Calvo Franco 
=A0 =A0 =A0 =A0 =A0 =A0 Database consultant at: 
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0<a href=3D"http://www.siu.edu.ar" t=
arget=3D"_blank">www.siu.edu.ar</a> 
=A0 =A0 =A0 =A0<a href=3D"http://www.emanuelcalvofranco.com.ar" target=3D"=
_blank">www.emanuelcalvofranco.com.ar</a> 
</div></div> 
-- 
Sent via pgsql-admin mailing list (<a href=3D"mailto:pgsql-admin [at] postgresql=
..org" target=3D"_blank">pgsql-admin [at] postgresql.org</a>) 
To make changes to your subscription: 
<a href=3D"http://www.postgresql.org/mailpref/pgsql-admin" target=3D"_blank=
">http://www.postgresql.org/mailpref/pgsql-admin</a> 
</blockquote></div> 
</blockquote></div> -- JONATAN EVALD BUUS <=
br>Executive Vice President Open Systems and Telecommunications Mobi=
le US =A0+1 (305) 331-5242 Mobile DK =A0+45 2888 2861 Telephone =A0+1=
(305) 777-0392 
Fax. =A0 =A0 =A0 =A0 =A0+1 (305) 777-0449 <a href=3D"mailto:jonatan.buus=
[at] cellpointmobile.com">jonatan.buus [at] cellpointmobile.com</a> <a href=3D"ht=
tp://www.cellpointmobile.com">www.cellpointmobile.com</a> CellPoint=
Mobile Inc. 
4000 Ponce de Leon Boulevard Suite 470 Coral Gables, FL 33146 USA<=
br> 'Mobilizing the Enterprise' 

--00151747b690fc9bf10470f6445f--

Jonatan Evald Buus <jonatan.buus [at] cellpointmobile.com> wrote:

> [too many files open in system]

> Any insight or suggestions as to where to start digging would be
> greatly appreciated

You didn't start truncating temporary tables inside a loop somewhere,
did you?

http://archives.postgresql.org/pgsql-hackers/2009-08/msg0044 4.php

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--00504502d6c45150280470f6ac10
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Nope, though that sounds fun!! ;-)

The application hasn't really changed much over the past 6 months either
(and definitively not its use of the database which is mainly INSERT queries
used for logging a few SELECTS for sharing data between the clustered
application nodes, all in all pretty simply and straight forward)

/Jona

On Wed, Aug 12, 2009 at 9:02 PM, Kevin Grittner <Kevin.Grittner [at] wicourts.gov
> wrote:

> Jonatan Evald Buus <jonatan.buus [at] cellpointmobile.com> wrote:
>
> > [too many files open in system]
>
> > Any insight or suggestions as to where to start digging would be
> > greatly appreciated
>
> You didn't start truncating temporary tables inside a loop somewhere,
> did you?
>
> http://archives.postgresql.org/pgsql-hackers/2009-08/msg0044 4.php
>
> -Kevin
>

--00504502d6c45150280470f6ac10
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Nope, though that sounds fun!! ;-) The application hasn't really=
changed much over the past 6 months either (and definitively not its use o=
f the database which is mainly INSERT queries used for logging a few SELECT=
S for sharing data between the clustered application nodes, all in all pret=
ty simply and straight forward) 
 /Jona <div class=3D"gmail_quote">On Wed, Aug 12, 2009 at 9:02 PM=
, Kevin Grittner <<a href=3D"mailto:Kevin.Grittner [at] wic=
ourts.gov">Kevin.Grittner [at] wicourts.gov</a>> wrote: <blockquote=
class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, 204, 204); =
margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Jonatan Evald Buus <<a href=3D"mailto:jonatan.buus [at] cellpointmobile.com">=
jonatan.buus [at] cellpointmobile.com</a>> wrote: 
 
> [too many files open in system] 
<div class=3D"im"> 
> Any insight or suggestions as to where to start digging would be 
> greatly appreciated 
 
</div>You didn't start truncating temporary tables inside a loop somewh=
ere, 
did you? 
 
<a href=3D"http://archives.postgresql.org/pgsql-hackers/2009-08/msg00444.ph=
p" target=3D"_blank">http://archives.postgresql.org/pgsql-hacke rs/2009-08/m=
sg00444.php</a> 
 
-Kevin 
</blockquote></div>

--00504502d6c45150280470f6ac10--

Jonatan Evald Buus <jonatan.buus [at] cellpointmobile.com> writes:
> Is it normal for PostGreSQL to have close to 5000 file handles open while
> running?

It can be, if you have enough active backends and enough tables that
they are touching. You have not provided nearly enough information to
gauge what the expected number of actual open files might be in your
installation, but I'll just point out that at max_connections = 256 and
max_files_per_process = 500 you have no grounds to complain if Postgres
tries to use 128000 open files. If that value of max_connections
actually is representative of what you need to do, I'd recommend cutting
max_files_per_process to a couple hundred and upping the kernel limit
to somewhere north of 50000.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--001636c5b15bb86c180470ffa1a6
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Cheers for the insight Tom,
We generally have anywhere between 60 and 100 active connections to Postgres
under normal load, but at peak times this may increase hence the
max_connections = 256.
There're several databases in the Postgres cluster so an estimate would be
approximately 200 tables in totals.
At least 2 of the databases contains 50 - 100 tables (most of the tables are
static) each which are located in 5 - 10 schemas within each database.
Is this enough to gauge the expected number of open files?
If not, what additional information would be required?

Also, what would a reasonable setting for "max_files_per_process" based on a
machine with 2GB RAM running FreeBSD 7.1 be?
The comments mention that "max_files_per_process may" be set as low as 25
but what would the implications of this restriction be?
Based on your suggestion, 50.000 / 256 = 191, so setting
"max_files_per_process" to around 200 seems reasonable in addition to
increasing the kern.maxfiles limit?

By the way, we've only been able to find the following log entries by the
kernel which appears related to the problem:
Aug 8 06:30:50 node5 kernel: kern.maxfiles limit exceeded by uid 70, please
see tuning(7).
Aug 8 06:30:57 node5 kernel: kern.maxfiles limit exceeded by uid 70, please
see tuning(7).
Aug 8 06:32:57 node5 last message repeated 5 times
Aug 8 06:36:57 node5 last message repeated 9 times
"uid 70" is the Postgres user but Postgres logged the "too many files open"
error several times in the days following the 8th.
Also the system never appeared to be unresponsive at any given time while
the errors occurred, which appears to be a common problem based on previous
discussions for this type of event.
We've previously run stress tests on the clusters where the database server
reached a very very high load (30 or so) without any errors being logged (by
PostGres or otherwise) thus it's quite curious that this problem would
"suddenly" appear.

Appreciate the input

Cheers
Jona

On Thu, Aug 13, 2009 at 1:25 AM, Tom Lane <tgl [at] sss.pgh.pa.us> wrote:

> Jonatan Evald Buus <jonatan.buus [at] cellpointmobile.com> writes:
> > Is it normal for PostGreSQL to have close to 5000 file handles open while
> > running?
>
> It can be, if you have enough active backends and enough tables that
> they are touching. You have not provided nearly enough information to
> gauge what the expected number of actual open files might be in your
> installation, but I'll just point out that at max_connections = 256 and
> max_files_per_process = 500 you have no grounds to complain if Postgres
> tries to use 128000 open files. If that value of max_connections
> actually is representative of what you need to do, I'd recommend cutting
> max_files_per_process to a couple hundred and upping the kernel limit
> to somewhere north of 50000.
>
> regards, tom lane
>

--001636c5b15bb86c180470ffa1a6
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Cheers for the insight Tom, We generally have anywhere between 60 and 10=
0 active connections to Postgres under normal load, but at peak times this =
may increase hence the max_connections =3D 256. There're several dat=
abases in the Postgres cluster so an estimate would be approximately 200 ta=
bles in totals. 
At least 2 of the databases contains 50 - 100 tables (most of the tables ar=
e static) each which are located in 5 - 10 schemas within each database.<br=
>Is this enough to gauge the expected number of open files? If not, what=
additional information would be required? 
 Also, what would a reasonable setting for "max_files_per_process&q=
uot; based on a machine with 2GB RAM running FreeBSD 7.1 be? The comment=
s mention that "max_files_per_process may" be set as low as 25 bu=
t what would the implications of this restriction be? 
Based on your suggestion, 50.000 / 256 =3D 191, so setting "max_files_=
per_process" to around 200 seems reasonable in addition to increasing =
the kern.maxfiles limit? By the way, we've only been able to fin=
d the following log entries by the kernel which appears related to the prob=
lem: 
Aug=A0 8 06:30:50 node5 kernel: kern.maxfiles limit exceeded by uid 70, ple=
ase see tuning(7). Aug=A0 8 06:30:57 node5 kernel: kern.maxfiles limit e=
xceeded by uid 70, please see tuning(7). Aug=A0 8 06:32:57 node5 last me=
ssage repeated 5 times 
Aug=A0 8 06:36:57 node5 last message repeated 9 times "uid 70"=
is the Postgres user but Postgres logged the "too many files open&quo=
t; error several times in the days following the 8th. Also the system ne=
ver appeared to be unresponsive at any given time while the errors occurred=
, which appears to be a common problem based on previous discussions for th=
is type of event. 
We've previously run stress tests on the clusters where the database
server reached a very very high load (30 or so) without any errors being lo=
gged (by PostGres or otherwise) thus it's quite curious that this probl=
em would "suddenly" appear. Appreciate the input 
Cheers Jona <div class=3D"gmail_quote">On Thu, Aug 13, 2009 at 1:=
25 AM, Tom Lane <<a href=3D"mailto:tgl [at] sss.pgh.pa.us">=
tgl [at] sss.pgh.pa.us</a>> wrote: <blockquote class=3D"gmail_quote=
" style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0=
..8ex; padding-left: 1ex;">
<div class=3D"im">Jonatan Evald Buus <<a href=3D"mailto:jonatan.buus [at] cel=
lpointmobile.com">jonatan.buus [at] cellpointmobile.com</a>> writes: 
> Is it normal for PostGreSQL to have close to 5000 file handles open wh=
ile 
> running? 
 
</div>It can be, if you have enough active backends and enough tables that<=
br>
they are touching. =A0You have not provided nearly enough information to<br=
>
gauge what the expected number of actual open files might be in your 
installation, but I'll just point out that at max_connections =3D 256 a=
nd 
max_files_per_process =3D 500 you have no grounds to complain if Postgres<b=
r>
tries to use 128000 open files. =A0If that value of max_connections 
actually is representative of what you need to do, I'd recommend cuttin=
g 
max_files_per_process to a couple hundred and upping the kernel limit 
to somewhere north of 50000. 
 
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane 
</blockquote></div> 

--001636c5b15bb86c180470ffa1a6--

Jonatan Evald Buus <jonatan.buus [at] cellpointmobile.com> writes:
> Also, what would a reasonable setting for "max_files_per_process" based on a
> machine with 2GB RAM running FreeBSD 7.1 be?
> The comments mention that "max_files_per_process may" be set as low as 25
> but what would the implications of this restriction be?

At some point you're going to start losing performance because of too
many close() and open() calls that have to be issued to switch a small
number of file descriptors around to different tables. AFAIK no one
has really tried to measure the issue. Personally I'd be pretty
concerned about setting it to less than 100 or so.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

"Too many open files in system" error

Re: "Too many open files in system" error

Re: "Too many open files in system" error

Re: "Too many open files in system" error

Re: "Too many open files in system" error

Re: "Too many open files in system" error

Re: "Too many open files in system" error

Re: "Too many open files in system" error

Re: "Too many open files in system" error