From: Jan Wieck <JanWieck@Yahoo.com>
To: Michael Riess <mlriess@gmx.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: 15,000 tables
Date: Fri, 02 Dec 2005 10:16:28 -0500
Message-ID: <4390654C.90805@Yahoo.com> (raw)
In-Reply-To: <dmnj8g$1hrt$1@news.hub.org>
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>
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 #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: JanWieck@Yahoo.com, mlriess@gmx.de
Subject: Re: 15,000 tables
In-Reply-To: <4390654C.90805@Yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox