From: Chris Hoover <revoohc@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Some ideas for comment
Date: Wed, 24 Aug 2005 12:56:54 -0400
Message-ID: <1d219a6f05082409563775a8a7@mail.gmail.com> (raw)
Ok, there is always a lot of talk about tuning PostgreSQL on linux and
how PostgreSQL uses the linux kernel cache to cache the tables and
indexes.
My question is, is there anyway to see what files linux is caching at
this moment?
My reasoning behind this question is:
I have several database systems each with 1 PostgreSQL cluster.
However, each cluster has a large number of identical databases on
it. Since there can be a great amount of size disparity between the
databases, I am wondering if some of slowness we see might be caused
by the kernel cache having to reload a lot of data that keeps getting
swapped out. (most systems have at least 100GB of data/indexes on them
with 8 or 12GB ram).
If this is the case, what sort of symptoms would you expect?
To help mitigate this potential, I have been researching the
following, and am thinking of proposing it to management. Any
comments would be appreciated.
1. Implement a partition type layout using views and rules - This
will allow me to have one table in each view with the "active" data,
and the inactive data stored by year in other tables.
So I would have the following (for each major table):
Table View as
select * from active_table
union all
select * from table_2005
union all
select * from table_2004
etc.
Each table would have identical indexes, however only the
"active_table" would have data that is actively being worked. The
rules and a nightly job can keep the data correctly stored.
I am thinking that with this setup, the active table indexes should
almost always be in memory. And, if they do happen to be pushed out,
they are much smaller than the indexes I have today (where all data is
in one table), so they should load faster with less i/o pressure.
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: revoohc@gmail.com
Subject: Re: Some ideas for comment
In-Reply-To: <1d219a6f05082409563775a8a7@mail.gmail.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