Re: Very specific server situation

From: Mauro N. Infantino
Subject: Re: Very specific server situation
Date: ,
Msg-id: 000b01c789d5$d0827b40$ec01010a@intranet.db
(view: Whole thread, Raw)
In response to: Re: Very specific server situation  (Tom Lane)
List: pgsql-performance


Thank you very much for your suggestions.

> -----Original Message-----
> From: Tom Lane [mailto:]
> Have you checked to make sure the query plans are reasonable?

I've attached the main query and its explain plan. I can't find a way to
improve it.

Does it make any difference if it's executed from a stored procedure? Is
there any difference between the SP's language (PL/pgSQL, PL/php, etc. It
needs to make some other tiny things besides the query)?

> You might want to try contrib/pgstattuple

Thanks. I'll give it a try and report the results here.

> absolutely critical to boost checkpoint_segments far enough

How do I know how ofen checkpoints are done?
I've modified the parameters:

checkpoint_segments = 36 # it was 12 before
checkpoint_timeout = 1000
checkpoint_warning = 300 # so, I'll get a warning if it's too frequent.
commit_delay = 5000
commit_siblings = 2

> adjusting bgwriter and/or vacuum cost delay parameters

I've used a moderate cost delay configuration to see how it responds
(vacuum_cost_delay = 100 & vacuum_cost_limit = 200).
Do you have any advice on how to configure the bgwriter? I have no clue
about it and couldn't find anything clear.

Also, I know an upgrade to 8.2 is always a good thing, but is there any
change that could help this specific situation?

Again, thank you very much for your answers (and, of course, everything you
do in pgsql).



pgsql-performance by date:

From: "henk de wit"
Subject: Re: Redundant sub query triggers slow nested loop left join
From: Greg Smith
Subject: Re: Feature Request --- was: PostgreSQL Performance Tuning