From ralph.mason@telogis.com Wed May 9 23:56:36 2007 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-- From pguser@gmail.com Thu May 10 00:26:17 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C73D79FBB0B for ; Wed, 9 May 2007 21:26:17 -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 34439-08 for ; Wed, 9 May 2007 21:26:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.234]) by postgresql.org (Postfix) with ESMTP id 1A7989FB5D8 for ; Wed, 9 May 2007 21:26:13 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id s1so423845nze for ; Wed, 09 May 2007 17:26:12 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ZwDYsfSWFaa5H9jvRbD1JtfcOH0ultUD2OLTHxb+CFp7H2ag+VAIz28h/guxYKeJtiVjXgJHyp+mbygdehfFvNAHCQ1ybMa34rM4A9gDuNGCPuTJrPOxYbL6txeQTxAL38y/jR0+krrBBkQg7wORpy5+mkj4HSM7tZQ+X7AqV3Q= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=dYfq81qRLXpYp9I0FPHPVM8wUrpYO48sIzzc1Hj4v+ei3+P0NVcw5mCFI6QkHKKZ2qc6Bw6wgw4ffhPj8ZtsqZ1TyQrisHvDby6WDuL1JA6MnKe2xorBT5bVnHQlHAgq9DzHbalskuSp6hAq0FllxN2aKgg99vJXvlVfw6ZYGj4= Received: by 10.114.38.2 with SMTP id l2mr307922wal.1178756772340; Wed, 09 May 2007 17:26:12 -0700 (PDT) Received: by 10.114.123.6 with HTTP; Wed, 9 May 2007 17:26:07 -0700 (PDT) Message-ID: <467669b30705091726j31fce52fp2f49649be675d811@mail.gmail.com> Date: Wed, 9 May 2007 17:26:07 -0700 From: "CAJ CAJ" To: "Ralph Mason" Subject: Re: Performance Woes Cc: pgsql-performance@postgresql.org In-Reply-To: <5132236932388612597@unknownmsgid> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_136530_33327556.1178756767284" References: <5132236932388612597@unknownmsgid> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/211 X-Sequence-Number: 24601 ------=_Part_136530_33327556.1178756767284 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline > I have several databases. They are each about 35gb in size and have abou= t > 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 ar= e > 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 statemen= t > > 2007-05-09 03:07:50.083 GMT 1146975740: LOCATION: BasicOpenFile, fd.c:47= 1 > > 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 proces= sor > 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 i= n later releases according to the changelogs ------=_Part_136530_33327556.1178756767284 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

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 in= sert / 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

 

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 ve= rsion of Pg/OS? What is your hardware config?

I had seen these erro= rs with earlier versions of Pg 7.4.x which was fixed in later releases acco= rding to the changelogs

 


------=_Part_136530_33327556.1178756767284-- From jd@commandprompt.com Thu May 10 00:29:41 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 87C0B9FBE05 for ; Wed, 9 May 2007 21:29:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 36870-09 for ; Wed, 9 May 2007 21:29:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id C16449FBDD0 for ; Wed, 9 May 2007 21:29:36 -0300 (ADT) Received: from [192.168.1.50] (or-67-76-146-141.sta.embarqhsd.net [67.76.146.141]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l4A0TaxC014407; Wed, 9 May 2007 17:29:37 -0700 Message-ID: <4642677F.80209@commandprompt.com> Date: Wed, 09 May 2007 17:29:51 -0700 From: "Joshua D. Drake" User-Agent: Thunderbird 1.5.0.10 (X11/20070403) MIME-Version: 1.0 To: CAJ CAJ CC: Ralph Mason , pgsql-performance@postgresql.org Subject: Re: Performance Woes References: <5132236932388612597@unknownmsgid> <467669b30705091726j31fce52fp2f49649be675d811@mail.gmail.com> In-Reply-To: <467669b30705091726j31fce52fp2f49649be675d811@mail.gmail.com> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Wed, 09 May 2007 17:29:37 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/212 X-Sequence-Number: 24602 > 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. > Sounds to me like you just need to up the total amount of open files allowed by the operating system. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ From ralph.mason@telogis.com Thu May 10 00:35:01 2007 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-- From jd@commandprompt.com Thu May 10 00:39:50 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 5BE959FBE75 for ; Wed, 9 May 2007 21:39:50 -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 34653-08 for ; Wed, 9 May 2007 21:39:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 663989FB7D3 for ; Wed, 9 May 2007 21:39:46 -0300 (ADT) Received: from [192.168.1.50] (or-67-76-146-141.sta.embarqhsd.net [67.76.146.141]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l4A0dmbM014632; Wed, 9 May 2007 17:39:48 -0700 Message-ID: <464269E3.9010802@commandprompt.com> Date: Wed, 09 May 2007 17:40:03 -0700 From: "Joshua D. Drake" User-Agent: Thunderbird 1.5.0.10 (X11/20070403) MIME-Version: 1.0 To: Ralph Mason CC: pgsql-performance@postgresql.org Subject: Re: Performance Woes References: <5132236932388612597@unknownmsgid> <467669b30705091726j31fce52fp2f49649be675d811@mail.gmail.com> <013101c7929b$046a25c0$0d3e7140$@mason@telogis.com> In-Reply-To: <013101c7929b$046a25c0$0d3e7140$@mason@telogis.com> Content-Type: text/plain; charset=windows-1250; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Wed, 09 May 2007 17:39:49 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/214 X-Sequence-Number: 24604 Hello, You likely need to increase your file-max parameters using sysctl.conf. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ From pgsql@j-davis.com Thu May 10 00:42:47 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 96FB69FBEED for ; Wed, 9 May 2007 21:42:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 41611-09 for ; Wed, 9 May 2007 21:42:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from serv01.siteground137.com (ns1.siteground137.com [67.15.250.10]) by postgresql.org (Postfix) with ESMTP id 49B249FBEBF for ; Wed, 9 May 2007 21:42:30 -0300 (ADT) Received: from nat.vinton.com ([209.162.219.253]:62491 helo=dogma.v10.wvs) by serv01.siteground137.com with esmtpa (Exim 4.52) id 1Hlwja-00016e-1G; Wed, 09 May 2007 19:42:18 -0500 Subject: Re: Performance Woes From: Jeff Davis To: "Joshua D. Drake" Cc: CAJ CAJ , Ralph Mason , pgsql-performance@postgresql.org In-Reply-To: <4642677F.80209@commandprompt.com> References: <5132236932388612597@unknownmsgid> <467669b30705091726j31fce52fp2f49649be675d811@mail.gmail.com> <4642677F.80209@commandprompt.com> Content-Type: text/plain Date: Wed, 09 May 2007 17:42:19 -0700 Message-Id: <1178757739.24902.78.camel@dogma.v10.wvs> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-7) Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - serv01.siteground137.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - j-davis.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/215 X-Sequence-Number: 24605 On Wed, 2007-05-09 at 17:29 -0700, Joshua D. Drake wrote: > > 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. > > > > Sounds to me like you just need to up the total amount of open files > allowed by the operating system. It looks more like the opposite, here's the docs for max_files_per_process: "Sets the maximum number of simultaneously open files allowed to each server subprocess. The default is one thousand files. If the kernel is enforcing a safe per-process limit, you don't need to worry about this setting. But on some platforms (notably, most BSD systems), the kernel will allow individual processes to open many more files than the system can really support when a large number of processes all try to open that many files. If you find yourself seeing "Too many open files" failures, try reducing this setting. This parameter can only be set at server start." To me, that means that his machine is allowing the new FD to be created, but then can't really support that many so it gives an error. Ralph, how many connections do you have open at once? It seems like the machine perhaps just can't handle that many FDs in all of those processes at once. That is a lot of tables. Maybe a different OS will handle it better? Maybe there's some way that you can use fewer connections and then the OS could still handle it? Regards, Jeff Davis From ralph.mason@telogis.com Thu May 10 01:45:47 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id CF05F9FB737 for ; Wed, 9 May 2007 22:45:47 -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 43226-07 for ; Wed, 9 May 2007 22:45:38 -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 91E4A9FB563 for ; Wed, 9 May 2007 22:45:43 -0300 (ADT) Received: from RALPH2 ([::ffff:192.168.3.157]) by telogis.co.nz with esmtp; Thu, 10 May 2007 13:45:39 +1200 id 0096CFA6.46427944.0000185D From: "Ralph Mason" To: "'Jeff Davis'" , "'Joshua D. Drake'" Cc: "'CAJ CAJ'" , pgsql-performance@postgresql.org References: <5132236932388612597@unknownmsgid> <467669b30705091726j31fce52fp2f49649be675d811@mail.gmail.com> <4642677F.80209@commandprompt.com> <1178757739.24902.78.camel@dogma.v10.wvs> In-Reply-To: <1178757739.24902.78.camel@dogma.v10.wvs> Subject: Re: Performance Woes Date: Thu, 10 May 2007 13:45:28 +1200 Message-ID: <014501c792a4$e8407a20$b8c16e60$@mason@telogis.com> MIME-Version: 1.0 Content-Type: text/plain; charset="windows-1250" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AceSnMa6mAj6HNPTTiO9vpOXkq9gPgAB0wuw Content-Language: en-gb X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.353 tagged_above=0 required=5 tests=AWL=-0.821, BAYES_20=-0.74, MSGID_MULTIPLE_AT=1.914 X-Spam-Level: X-Archive-Number: 200705/216 X-Sequence-Number: 24606 >To me, that means that his machine is allowing the new FD to be = created, >but then can't really support that many so it gives an error. files-max is 297834 ulimit is 1000000 (doesn't make sense but there you go) What I don=92t really understand is with max_files_per_process at 800 we = don't get the problem, but with 1000 we do. $lsof | wc -l=20 14944 $cat /proc/sys/fs/file-nr=20 12240 0 297834 >Ralph, how many connections do you have open at once? It seems like the >machine perhaps just can't handle that many FDs in all of those >processes at once. There are only 30 connections - of those probably only 10 are really = active. It doesn't seem like we should be stressing this machine/ >That is a lot of tables. Maybe a different OS will handle it better? >Maybe there's some way that you can use fewer connections and then the >OS could still handle it? It would be less but then you can't maintain the db b/c of the constant vacuuming needed :-( I think the linux folks would get up in arms if = you told them they couldn't handle that many open files ;-) Thanks, Ralph --=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 From scott.mohekey@telogis.com Thu May 10 01:50:33 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id DF8059FBE8A for ; Wed, 9 May 2007 22:50:33 -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 41606-06 for ; Wed, 9 May 2007 22:50:23 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id E0CA59FBABF for ; Wed, 9 May 2007 22:50:28 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id k3so357058ugf for ; Wed, 09 May 2007 18:50:27 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=fSbGnuAHiloidSzX5EEFBcxUbi5malninWH/Vb1cyJxKXdCH3VIe+Pk8TMg+koyOJvrzjDgMLbZtRO7y99KHE+lBUjcgujgcTj8ihYB0atv+E6VZLLP3gCiPKbhaNPhtRgrrJv3N6biiPguUIgG/WbH0KmdaDPrtBTNU+9hDYtI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=ucTCtMdVLV+XhuMWAaqOxESlgrbptTPhOXDTiDjPZs7ACwtE9WulgvCHvXi/AQZ2XvnQtcgx6r3PBvrpWo9pponhQV4x8nDCvccRRH/lwSziH8DyutTC0OCimJMi1xnut09eJeAP2nvNMBEOL5Crd6IoUOYNcjBbhsP8vEx4ynI= Received: by 10.82.156.12 with SMTP id d12mr2189345bue.1178761826630; Wed, 09 May 2007 18:50:26 -0700 (PDT) Received: by 10.82.180.7 with HTTP; Wed, 9 May 2007 18:50:26 -0700 (PDT) Message-ID: Date: Thu, 10 May 2007 13:50:26 +1200 From: "Scott Mohekey" To: "Jeff Davis" Subject: Re: Performance Woes Cc: "Joshua D. Drake" , "CAJ CAJ" , "Ralph Mason" , pgsql-performance@postgresql.org In-Reply-To: <1178757739.24902.78.camel@dogma.v10.wvs> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_83774_16220035.1178761826578" References: <5132236932388612597@unknownmsgid> <467669b30705091726j31fce52fp2f49649be675d811@mail.gmail.com> <4642677F.80209@commandprompt.com> <1178757739.24902.78.camel@dogma.v10.wvs> X-Google-Sender-Auth: be9185135f9315d4 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/217 X-Sequence-Number: 24607 ------=_Part_83774_16220035.1178761826578 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Just adding a bit of relevant information: We have the kernel file-max setting set to 297834 (256 per 4mb of ram). /proc/sys/fs/file-nr tells us that we have roughly 13000 allocated handles of which zero are always free. On 10/05/07, Jeff Davis wrote: > > On Wed, 2007-05-09 at 17:29 -0700, Joshua D. Drake wrote: > > > 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. > > > > > > > Sounds to me like you just need to up the total amount of open files > > allowed by the operating system. > > It looks more like the opposite, here's the docs for > max_files_per_process: > > "Sets the maximum number of simultaneously open files allowed to each > server subprocess. The default is one thousand files. If the kernel is > enforcing a safe per-process limit, you don't need to worry about this > setting. But on some platforms (notably, most BSD systems), the kernel > will allow individual processes to open many more files than the system > can really support when a large number of processes all try to open that > many files. If you find yourself seeing "Too many open files" failures, > try reducing this setting. This parameter can only be set at server > start." > > To me, that means that his machine is allowing the new FD to be created, > but then can't really support that many so it gives an error. > > Ralph, how many connections do you have open at once? It seems like the > machine perhaps just can't handle that many FDs in all of those > processes at once. > > That is a lot of tables. Maybe a different OS will handle it better? > Maybe there's some way that you can use fewer connections and then the > OS could still handle it? > > Regards, > Jeff Davis > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Scott Mohekey Systems Administrator Telogis Intelligent Location Technologies NOTICE: This message (including any attachments) contains CONFIDENTIAL INFORMATION intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited ------=_Part_83774_16220035.1178761826578 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline Just adding a bit of relevant information:

We have the kernel file-max setting set to 297834 (256 per 4mb of ram).

/proc/sys/fs/file-nr tells us that we have roughly 13000 allocated handles of which zero are always free.



On 10/05/07, Jeff Davis <pgsql@j-davis.com> wrote:
On Wed, 2007-05-09 at 17:29 -0700, Joshua D. Drake wrote:
> >     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.
> >
>
> Sounds to me like you just need to up the total amount of open files
> allowed by the operating system.

It looks more like the opposite, here's the docs for
max_files_per_process:

"Sets the maximum number of simultaneously open files allowed to each
server subprocess. The default is one thousand files. If the kernel is
enforcing a safe per-process limit, you don't need to worry about this
setting. But on some platforms (notably, most BSD systems), the kernel
will allow individual processes to open many more files than the system
can really support when a large number of processes all try to open that
many files. If you find yourself seeing "Too many open files" failures,
try reducing this setting. This parameter can only be set at server
start."

To me, that means that his machine is allowing the new FD to be created,
but then can't really support that many so it gives an error.

Ralph, how many connections do you have open at once? It seems like the
machine perhaps just can't handle that many FDs in all of those
processes at once.

That is a lot of tables. Maybe a different OS will handle it better?
Maybe there's some way that you can use fewer connections and then the
OS could still handle it?

Regards,
        Jeff Davis


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster



--
Scott Mohekey
Systems Administrator
Telogis
Intelligent Location Technologies

NOTICE:
This message (including any attachments) contains CONFIDENTIAL INFORMATION intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited ------=_Part_83774_16220035.1178761826578-- From alvherre@commandprompt.com Thu May 10 02:05:20 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 71CF69FB609 for ; Wed, 9 May 2007 23:05:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 77351-01-10 for ; Wed, 9 May 2007 23:05:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 9F3809FBEAC for ; Wed, 9 May 2007 23:05:16 -0300 (ADT) Received: from perhan.alvh.no-ip.org (201-221-217-243.bk12-dsl.surnet.cl [201.221.217.243]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l4A25FDV016218; Wed, 9 May 2007 19:05:17 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 2D7F832A0F; Wed, 9 May 2007 22:05:08 -0400 (CLT) Date: Wed, 9 May 2007 22:05:08 -0400 From: Alvaro Herrera To: Ralph Mason Cc: pgsql-performance@postgresql.org Subject: Re: Performance Woes Message-ID: <20070510020508.GB4504@alvh.no-ip.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Wed, 09 May 2007 19:05:19 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/218 X-Sequence-Number: 24608 Ralph Mason wrote: > 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). Is it always the same trigger the problematic one? Is it just PL/pgSQL, or do you have something else? Something that may be trying to open additional files for example? Something that may be trying to open files behind your back? PL/Perl with funky operators or temp files? Also, what PG version is this? > 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. The max_files_per_process settings controls how many actual file descriptors each process is allowed to have. Postgres uses internally a "virtual file descriptor", which normally have one file descriptor open each. However, if your transactions need to access lots of files, the VFDs will close the kernel FDs to allow other VFDs to open theirs. So it sounds like your transaction has more than 800 files open. The extra IO wait could be caused by the additional system calls to open and close those files as needed. I would actually expect it to cause extra "system" load (as opposed to "user") rather than IO, but I'm not sure. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. From tgl@sss.pgh.pa.us Thu May 10 04:30:20 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 18B369FB427 for ; Thu, 10 May 2007 01:30:20 -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 58992-07 for ; Thu, 10 May 2007 01:30:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 5C1779FB3E1 for ; Thu, 10 May 2007 01:30:13 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l4A4U4h7006686; Thu, 10 May 2007 00:30:04 -0400 (EDT) To: Jeff Davis cc: "Joshua D. Drake" , CAJ CAJ , Ralph Mason , pgsql-performance@postgresql.org Subject: Re: Performance Woes In-reply-to: <1178757739.24902.78.camel@dogma.v10.wvs> References: <5132236932388612597@unknownmsgid> <467669b30705091726j31fce52fp2f49649be675d811@mail.gmail.com> <4642677F.80209@commandprompt.com> <1178757739.24902.78.camel@dogma.v10.wvs> Comments: In-reply-to Jeff Davis message dated "Wed, 09 May 2007 17:42:19 -0700" Date: Thu, 10 May 2007 00:30:04 -0400 Message-ID: <6685.1178771404@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/219 X-Sequence-Number: 24609 Jeff Davis writes: > On Wed, 2007-05-09 at 17:29 -0700, Joshua D. Drake wrote: >> Sounds to me like you just need to up the total amount of open files >> allowed by the operating system. > It looks more like the opposite, here's the docs for > max_files_per_process: I think Josh has got the right advice. The manual is just saying that you can reduce max_files_per_process to avoid the failure, but it's not making any promises about the performance penalty for doing that. Apparently Ralph's app needs a working set of between 800 and 1000 open files to have reasonable performance. > That is a lot of tables. Maybe a different OS will handle it better? > Maybe there's some way that you can use fewer connections and then the > OS could still handle it? Also, it might be worth rethinking the database structure to reduce the number of tables. But for a quick-fix, increasing the kernel limit seems like the easiest answer. regards, tom lane