Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 072D164FC84 for ; Tue, 11 Nov 2008 08:42:01 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 12275-01 for ; Tue, 11 Nov 2008 08:41:58 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from ey-out-2122.google.com (ey-out-2122.google.com [74.125.78.25]) by mail.postgresql.org (Postfix) with ESMTP id D33CA64FDA2 for ; Tue, 11 Nov 2008 08:41:57 -0400 (AST) Received: by ey-out-2122.google.com with SMTP id 6so1085342eyi.61 for ; Tue, 11 Nov 2008 04:41:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=a2rU8kIfNkzWeH4agzfc3Ps/JnGMg9clsUvENq3mPgI=; b=O3FtiNyxyxROMfn3Z5bqJ4NuEL0zfB448jwbpy2NweKVWEWa73D7BiScES4o4ga8+S kxlK3DqZjYWkUsEvNrziXjNIOZwTVa5LVkAje8HhecJfIxp2b286YdgjM7bleDsCuFDe TABN0zOkVr1pBscVUmbLDqon/geN8VUA2YcKE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=DGlqwKM4O0JeiFAn+KSf6GKlfsTgsTjb53aRgiEiFJnFcsRM2gt2gsSRg1URzIpTGQ 342hldakYYRBbQqQCEAlo9Ixti3FQXI73cosoff8B0owosTGCGtdLBiIBB0G6cE8X4VI o6tMiuX/1xSXpjbYCCcsRWKNFC/oT7tgnvJy4= Received: by 10.210.50.5 with SMTP id x5mr9008982ebx.167.1226407316104; Tue, 11 Nov 2008 04:41:56 -0800 (PST) Received: by 10.210.42.15 with HTTP; Tue, 11 Nov 2008 04:41:56 -0800 (PST) Message-ID: <5d5d98b30811110441q772fd56o383d1022a760b9cf@mail.gmail.com> Date: Tue, 11 Nov 2008 14:41:56 +0200 From: "=?ISO-8859-1?Q?Dilek_K=FC=E7=FCk?=" To: "Tom Lane" Subject: Re: max_files_per_process limit Cc: pgsql-admin@postgresql.org In-Reply-To: <6567.1226330664@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_74935_19892935.1226407316097" References: <5d5d98b30811100618v2c0f935fka3f7970b622d6760@mail.gmail.com> <6567.1226330664@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200811/58 X-Sequence-Number: 30712 ------=_Part_74935_19892935.1226407316097 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Mon, Nov 10, 2008 at 5:24 PM, Tom Lane wrote: > "=3D?ISO-8859-1?Q?Dilek_K=3DFC=3DE7=3DFCk?=3D" wri= tes: > > We have a database of about 62000 tables (about 2000 tablespaces) with = an > > index on each table. Postgresql version is 8.1. > > You should probably rethink that schema. A lot of similar tables can be > folded into one table with an extra key column. Also, where did you get > the idea that 2000 tablespaces would be a good thing? There's really no > point in more than one per spindle or filesystem. > > > Although after the initial inserts to about 32000 tables the subsequent > > inserts are considerable fast, subsequent inserts to more than 32000 > tables > > are very slow. > > This has probably got more to do with inefficiencies of your filesystem > than anything else --- did you pick one that scales well to lots of > files per directory? The database is working on FreeBSD 6.3 with UFS file system. It has 32 GB o= f RAM with 2 quadcore Intel Xeon 2.66 GHz processor, and about 11 TB of RAID5 storage. > > > > This seems to be due to the datatype (integer) of max_files_per_process > > option in the postgres.conf file which is used to set the maximum numbe= r > of > > open file descriptors. > > It's not so much the datatype of max_files_per_process as the datatype > of kernel file descriptors that's the limitation ... We do not get any system messages related to the kernel file descriptor limit (like file: table is full) yet we will work again on both the databas= e schema (tablespaces etc.) and system kernel variables. Thanks, Dilek K=FC=E7=FCk > > > regards, tom lane > ------=_Part_74935_19892935.1226407316097 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
On Mon, Nov 10, 2008 at 5:24 PM, Tom Lane <tgl@= sss.pgh.pa.us> wrote:
"=3D?ISO-8859-1?Q?Dilek_K=3DFC=3DE7=3DFCk?=3D" <dilekkucuk@gmail.com= > writes:
> We have a database of about 62000 tables (about 2000 t= ablespaces) with an
> index on each table. Postgresql version is 8.1.

You shoul= d probably rethink that schema.  A lot of similar tables can be
fol= ded into one table with an extra key column.  Also, where did you get<= br>the idea that 2000 tablespaces would be a good thing?  There's = really no
point in more than one per spindle or filesystem.

> Although after the initial inserts to about 32000 tables the = subsequent
> inserts are considerable fast, subsequent inserts to mor= e than 32000 tables
> are very slow.

This has probably g= ot more to do with inefficiencies of your filesystem
than anything else --- did you pick one that scales well to lots of
file= s per directory?
 
The database is working on FreeBSD 6.3 with UFS file system. It has 32= GB of RAM with 2 quadcore Intel Xeon 2.66 GHz processor, and about 11 TB o= f RAID5 storage.
 


> This seems to be due to the datatype (integer) of max_files_p= er_process
> option in the postgres.conf file which is used to set th= e maximum number of
> open file descriptors.

It's no= t so much the datatype of max_files_per_process as the datatype
of kernel file descriptors that's the limitation ...
 
We do not get any system messages related to the kernel file descripto= r limit (like file: table is full) yet we will work again on both the = database schema (tablespaces etc.) and system kernel variables.
 
Thanks,
Dilek K=FC=E7=FCk
 


  = ;            &n= bsp;        regards, tom lane

------=_Part_74935_19892935.1226407316097--