amrit wrote:
> I try to adjust my server for a couple of weeks with some sucess but
it
> still
> slow when the server has stress in the moring from many connection . I
> used
> postgresql 7.3.2-1 with RH 9 on a mechine of 2 Xeon 3.0 Ghz and ram of
4
> Gb.
> Since 1 1/2 yr. when I started to use the database server after
optimizing
> the
> postgresql.conf everything went fine until a couple of weeks ago , my
> database
> grew up to 3.5 Gb and there were more than 160 concurent connections.
> The server seemed to be slower in the rush hour peroid than before .
There
> is some swap process too. My top and meminfo are shown here below:
well, you've hit the 'wall'...your system seems to be more or less at
the limit of what 32 bit technology can deliver. If upgrade to Opteron
and 64 bit is out of the question, here are a couple of new tactics you
can try. Optimizing postgresql.conf can help, but only so much.
Optimize queries:
One big often looked performance gainer is to use functional indexes to
access data from a table. This can save space by making the index
smaller and more efficient. This wins on cache and speed at the price
of some flexibility.
Optimize datums: replace numeric(4) with int2, numeric(6) with int4,
etc. This will save a little space on the tuple which will ease up on
the cache a bit. Use constraints where necessary to preserve data
integrity.
Materialized views: These can provide an enormous win if you can deal
incorporate them into your application. With normal views, multiple
backends can share a query plan. With mat-views, backends can share
both the plan and its execution.
Merlin
