Skip site navigation (1) Skip section navigation (2)

Re: Very specific server situation

From: "Mauro N(dot) Infantino" <mauroi(at)digbang(dot)com>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Very specific server situation
Date: 2007-04-28 20:43:01
Message-ID: 000b01c789d5$d0827b40$ec01010a@intranet.db (view raw or flat)
Thread:
Lists: pgsql-performance
Tom, 

Thank you very much for your suggestions.

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us] 
> 
> 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).

Regards,
Mauro.

Attachment: plan.txt
Description: text/plain (2.3 KB)

In response to

pgsql-performance by date

Next:From: henk de witDate: 2007-04-28 22:51:39
Subject: Re: Redundant sub query triggers slow nested loop left join
Previous:From: Carlos MorenoDate: 2007-04-28 20:34:49
Subject: Re: Feature Request --- was: PostgreSQL Performance Tuning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group