DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=Yahoo.com;
	h=Received:Received:Message-ID:Date:From:User-Agent:X-Accept-Language:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
	b=xouP/JROa9vAJdgBDQRT2/n6K3VYS7v8bhyl+psLnxCL6jS1FytrDcPjh9WsVsb+XHPSu3QDJ8w7TAdR1+jQ7rQUOaYdlg13yo9CGaNyoD+tiL7VuCChxYwUWi4G790U1vv7+nSqEcXVkxx4qtFqu42vMsuiEfj2eEpK3J/Vyr4=
	;
Message-ID: <4390654C.90805@Yahoo.com>
Date: Fri, 02 Dec 2005 10:16:28 -0500
From: Jan Wieck <JanWieck@Yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.7.1) Gecko/20040707
MIME-Version: 1.0
To: Michael Riess <mlriess@gmx.de>
CC: pgsql-performance@postgresql.org
Subject: Re: 15,000 tables
References: <dmmujf$2rgd$1@news.hub.org>
	<c2d9e70e0512010742p5e5fb61dx84dfb8e332c05352@mail.gmail.com>
	<dmn6tm$1n9n$1@news.hub.org>
	<60psogzqui.fsf@dba2.int.libertyrms.com>
	<dmnj8g$1hrt$1@news.hub.org>
In-Reply-To: <dmnj8g$1hrt$1@news.hub.org>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

On 12/1/2005 2:34 PM, Michael Riess wrote:
>> VACUUM FULL was probably always overkill, unless "always" includes
>> versions prior to 7.3...
> 
> Well, we tried switching to daily VACUUM ANALYZE and weekly VACUUM FULL, 
> but the database got considerably slower near the end of the week.

This indicates that you have FSM settings that are inadequate for that 
many tables and eventually the overall size of your database. Try 
setting those to

     max_fsm_relations = 80000
     max_fsm_pages = (select sum(relpages) / 2 from pg_class)

Another thing you might be suffering from (depending on the rest of your 
architecture) is file descriptor limits. Especially if you use some sort 
of connection pooling or persistent connections like PHP, you will have 
all the backends serving multiple of your logical applications (sets of 
30 tables). If on average one backend is called for 50 different apps, 
then we are talking 50*30*4=6000 files accessed by that backend. 80/20 
rule leaves 1200 files in access per backend, thus 100 active backends 
lead to 120,000 open (virtual) file descriptors. Now add to that any 
files that a backend would have to open in order to evict an arbitrary 
dirty block.

With a large shared buffer pool and little more aggressive background 
writer settings, you can avoid mostly that regular backends would have 
to evict dirty blocks.

If the kernel settings allow Postgres to keep that many file descriptors 
open, you avoid directory lookups.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #