Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id BBFB79FBDD0 for ; Wed, 9 May 2007 21:35:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 35297-06 for ; Wed, 9 May 2007 21:34:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from telogis.co.nz (telogis.co.nz [203.98.10.169]) by postgresql.org (Postfix) with ESMTP id 26F3B9FBD7F for ; Wed, 9 May 2007 21:34:54 -0300 (ADT) Received: from RALPH2 ([::ffff:192.168.3.157]) by telogis.co.nz with esmtp; Thu, 10 May 2007 12:34:51 +1200 id 0056B034.464268AD.000017B5 From: "Ralph Mason" To: pgsql-performance@postgresql.org References: <5132236932388612597@unknownmsgid> <467669b30705091726j31fce52fp2f49649be675d811@mail.gmail.com> In-Reply-To: <467669b30705091726j31fce52fp2f49649be675d811@mail.gmail.com> Subject: Re: Performance Woes Date: Thu, 10 May 2007 12:34:47 +1200 Message-ID: <013101c7929b$046a25c0$0d3e7140$@mason@telogis.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0132_01C792FF.999F05C0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AceSmhIfMGxHIWyfRHumTScCKCtImwAAHczQ Content-Language: en-gb X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/213 X-Sequence-Number: 24603 This is a multipart message in MIME format. ------=_NextPart_000_0132_01C792FF.999F05C0 Content-Type: text/plain; charset="windows-1250" Content-Transfer-Encoding: quoted-printable =20 =20 From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of CAJ CAJ Sent: 10 May 2007 12:26 To: Ralph Mason Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Performance Woes =20 =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). 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 = =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. >What version of Pg/OS? What is your hardware config?=20 >I had seen these errors with earlier versions of Pg 7.4.x which was = fixed in later releases according to the changelogs=20 "PostgreSQL 8.1.4 on x86_64-redhat-linux-gnu, compiled by GCC x86_64-redhat-linux-gcc (GCC) 4.1.0 20060304 (Red Hat 4.1.0-3)" =20 su postgres -c 'ulimit -a' =20 core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited max nice (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 49152 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 1000000 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 max rt priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 49152 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited file locks (-x) unlimited Seems like I should be able to use lots and lots of open files. Machines are quad processor opterons, 4gb ram with raid 5 data and = logging to a raid 0. Ralph =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_0132_01C792FF.999F05C0 Content-Type: text/html; charset="windows-1250" Content-Transfer-Encoding: quoted-printable

 

 

From: = pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of CAJ = CAJ
Sent: 10 May 2007 12:26
To: Ralph Mason
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Performance Woes

 

 

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.

>What = version of Pg/OS? What is your hardware config?

>I had seen these errors with = earlier versions of Pg 7.4.x which was fixed in later releases according to the changelogs

"PostgreSQL 8.1.4 on x86_64-redhat-linux-gnu, = compiled by GCC x86_64-redhat-linux-gcc (GCC) 4.1.0 20060304 (Red Hat = 4.1.0-3)"

 

su postgres -c 'ulimit -a'

 

core file size          (blocks, -c) = 0

data seg size           = (kbytes, -d) unlimited

max nice           &nb= sp;            (-e) 0

file size           &nb= sp;   (blocks, -f) unlimited

pending signals           =       (-i) 49152

max locked memory       = (kbytes, -l) 32

max memory = size         (kbytes, -m) unlimited

open files           &n= bsp;          (-n) 1000000

pipe size            = (512 bytes, -p) 8

POSIX message queues     (bytes, -q) = 819200

max rt priority           = ;      (-r) 0

stack size           &nb= sp;  (kbytes, -s) 10240

cpu time           &nb= sp;   (seconds, -t) unlimited

max user processes          &nbs= p;   (-u) 49152

virtual memory          (kbytes, = -v) unlimited

file locks           &n= bsp;          (-x) unlimited

file locks           &n= bsp;          (-x) unlimited

Seems like I should be able to use lots = and lots of open files.

Machines are quad processor opterons, 4gb ram with raid 5 data and logging to a = raid 0.

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_0132_01C792FF.999F05C0--