Re: Some ideas for comment - Mailing list pgsql-performance

> 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.
[...]
>
> 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.

Linux does a pretty good job of deciding what to cache.  I don't think
this will help much.  You can always look at partial indexes too.

> 2.  I am also thinking of recommending we collapse all databases in a
> cluster into one "mega" database.  I can then use schema's and views
> to control database access and ensure that no customer can see another
> customers data.

hm. keep in mind views are tightly bound to the tables they are created
with (views can't 'float' over tables in different schemas).  pl/pgsql
functions can, though.  This is a more efficient use of server
resources, IMO, but not a windfall.

> This would mean that there are only one set of indexes being loaded
> into the cache.  While they would be larger, I think in combination
> with the partition from idea 1, we would be ahead of the ball game.
> Since there would only be one set of indexes, everyone would be
> sharing them so they should always be in memory.

I would strongly consider adding more memory :).

> I don't have real numbers to give you, but we know that our systems
> are hurting i/o wise and we are growing by about 2GB+ per week (net).
> We actually grow by about 5GB/week/server.  However, when I run my
> weekly maintenance of vacuum full, reindex, and the vacuum analyze, we
> end up getting about 3GB back.  Unfortunately, I do not have the i/o
> bandwidth to vacuum during the day as it causes major slowdowns on our
> system.  Each night, I do run a vacuum analyze across all db's to try
> and help.  I also have my fsm parameters set high (8000000 fsm pages,
> and 5000 fsm relations) to try and compensate.

Generally, you can reduce data turnover for the same workload by
normalizing your database.  IOW, try and make your database more
efficient in the way it stores data.

> Right now, we are still on 7.3.4.  However, these ideas would be
> implemented as part of an upgrade to 8.x (plus, we'll initialize the
> new clusters with a C locale).

yes, do this!

Merlin