Re: Low Performance for big hospital server .. - Mailing list pgsql-performance

From Robert Treat
Subject Re: Low Performance for big hospital server ..
Date
Msg-id 200501031351.55065.xzilla@users.sourceforge.net
Whole thread Raw
In response to Re: Low Performance for big hospital server ..  (amrit@health2.moph.go.th)
List pgsql-performance
On Monday 03 January 2005 10:40, amrit@health2.moph.go.th wrote:
> > I realize you may be stuck with 7.3.x but you should be aware that 7.4
> > is considerably faster, and 8.0 appears to be even faster yet.
>
> There are a little bit incompatibility between 7.3 -8 , so rather difficult
> to change.
>

Sure, but even moving to 7.4 would be a bonus, especially if you use a lot of
select * from tab where id in (select ... ) type queries, and the
incompataibility is less as well.

> > I would seriously consider upgrading, if at all possible.
> >
> > A few more hints.
> >

One thing I didn't see mentioned that should have been was to watch for index
bloat, which was a real problem on 7.3 machines.  You can determine which
indexes are bloated by studying vacuum output or by comparing index size on
disk to table size on disk.

Another thing I didn't see mentioned was to your free space map settings.
Make sure these are large enough to hold your data... max_fsm_relations
should be larger then the total # of tables you have in your system (check
the archives for the exact query needed) and max_fsm_pages needs to be big
enough to hold all of the pages you use in a day... this is hard to calculate
in 7.3, but if you look at your vacuum output and add the number of pages
cleaned up for all tables, this could give you a good number to work with. It
would certainly tell you if your setting is too small.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

pgsql-performance by date:

Previous
From: Yann Michel
Date:
Subject: query rewrite using materialized views
Next
From: Mitch Pirtle
Date:
Subject: Re: Hardware purchase question