Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id AE95B9FB783 for ; Wed, 9 May 2007 20:56:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 29547-04 for ; Wed, 9 May 2007 20:56:27 -0300 (ADT) X-Greylist: delayed 00:05:02.115103 by SQLgrey-1.7.4 Received: from telogis.co.nz (telogis.co.nz [203.98.10.169]) by postgresql.org (Postfix) with ESMTP id D34F49FB599 for ; Wed, 9 May 2007 20:56:31 -0300 (ADT) Received: from RALPH2 ([::ffff:192.168.3.157]) by telogis.co.nz with esmtp; Thu, 10 May 2007 11:51:16 +1200 id 00978C10.46425E7B.000016F9 From: "Ralph Mason" To: pgsql-performance@postgresql.org Subject: Performance Woes Date: Thu, 10 May 2007 11:51:12 +1200 Message-ID: <011401c79294$f12223b0$d3666b10$@mason@telogis.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0115_01C792F9.865703B0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AceSlOxWa5BqvLfbTN+sfFEsmooCLQ== Content-Language: en-gb X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/210 X-Sequence-Number: 24600 This is a multipart message in MIME format. ------=_NextPart_000_0115_01C792F9.865703B0 Content-Type: text/plain; charset="windows-1250" Content-Transfer-Encoding: quoted-printable Hi, =20 I have several databases. They are each about 35gb in size and have = about 10.5K relations (count from pg_stat_all_tables) in them. Pg_class is = about 26k rows and the data directory contains about 70k files. These are = busy machines, they run about 50 xactions per second, ( aproxx insert / = update / delete about 500 rows per second). =20 We started getting errors about the number of open file descriptors =20 : 2007-05-09 03:07:50.083 GMT 1146975740: LOG: 53000: out of file descriptors: Too many open files; release and retry 2007-05-09 03:07:50.083 GMT 1146975740: CONTEXT: SQL statement "insert = =85.. " PL/pgSQL function "trigfunc_whatever" line 50 at execute = statement 2007-05-09 03:07:50.083 GMT 1146975740: LOCATION: BasicOpenFile, = fd.c:471 2007-05-09 03:07:50.091 GMT 0: LOG: 00000: duration: 12.362 ms 2007-05-09 03:07:50.091 GMT 0: LOCATION: exec_simple_query, = postgres.c:1090 =20 So we decreased the max_files_per_process to 800. This took care of = the error *BUT* about quadrupled the IO wait that is happening on the = machine. It went from a peek of about 50% to peeks of over 200% (4 processor machines, 4 gigs ram, raid). The load on the machine remained constant. =20 I am really to get an understanding of exactly what this setting is and =91what=92 is out of file descriptors and how I can fix that. I need to = bring that IO back down. =20 =20 Thanks for any help. Ralph =20 =20 =20 --=20 Internal Virus Database is out-of-date. Checked by AVG Free Edition. Version: 7.5.432 / Virus Database: 268.15.9/573 - Release Date: = 05/12/2006 16:07 =20 ------=_NextPart_000_0115_01C792F9.865703B0 Content-Type: text/html; charset="windows-1250" Content-Transfer-Encoding: quoted-printable

Hi,

I have several databases. They are each about = 35gb in size and have about 10.5K relations (count from pg_stat_all_tables) in = them. Pg_class is about 26k rows and the data directory contains about 70k = files. These are busy machines, they run about 50 xactions per second, ( aproxx = insert / update / delete about 500 rows per second).

We started getting errors about the number of open = file descriptors

: 2007-05-09 03:07:50.083 GMT 1146975740: = LOG: 53000: out of file descriptors: Too many open files; release and = retry

2007-05-09 03:07:50.083 GMT 1146975740: = CONTEXT: SQL statement "insert ….. "

PL/pgSQL = function "trigfunc_whatever" line 50 at execute = statement

2007-05-09 03:07:50.083 GMT 1146975740: = LOCATION: BasicOpenFile, fd.c:471

2007-05-09 03:07:50.091 GMT 0: LOG: 00000: = duration: 12.362 ms

2007-05-09 03:07:50.091 GMT 0: LOCATION: exec_simple_query, postgres.c:1090

So we decreased the max_files_per_process to = 800. This took care of the error *BUT* about quadrupled the IO = wait that is happening on the machine. It went from a peek of about 50% to = peeks of over 200% (4 processor machines, 4 gigs ram, raid). The load on = the machine remained constant.

I am really to get an understanding of exactly what = this setting is and ‘what’ is out of file descriptors and how I = can fix that. I need to bring that IO back down.

Thanks for any help.

Ralph

--
Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.15.9/573 - Release Date: = 05/12/2006 16:07

------=_NextPart_000_0115_01C792F9.865703B0--