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--