pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedseq scan woes
7+ messages / 2 participants
[nested] [flat]
* seq scan woes
@ 2004-06-07 19:45 Dan Langille <dan@langille.org>
0 siblings, 2 replies; 7+ messages in thread
From: Dan Langille @ 2004-06-07 19:45 UTC (permalink / raw)
To: pgsql-performance
A production system has had a query recently degrade in performance.
What once took < 1s now takes over 1s. I have tracked down the
problem to a working example.
Compare http://rafb.net/paste/results/itZIx891.html
with http://rafb.net/paste/results/fbUTNF95.html
The first shows the query as is, without much change (actually, this
query is nested within a larger query, but it demonstrates the
problem). The query time is about 1 second.
In the second URL, a "SET ENABLE_SEQSCAN TO OFF;" is done, and the
time drops to 151ms, which is acceptable.
What I don't understand is why the ports table is scanned in the
first place. Clues please?
--
Dan Langille : http://www.langille.org/
BSDCan - http://www.bsdcan.org/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: seq scan woes
@ 2004-06-07 20:00 Rod Taylor <ports@rbt.ca>
parent: Dan Langille <dan@langille.org>
1 sibling, 0 replies; 7+ messages in thread
From: Rod Taylor @ 2004-06-07 20:00 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-performance
On Mon, 2004-06-07 at 15:45, Dan Langille wrote:
> A production system has had a query recently degrade in performance.
> What once took < 1s now takes over 1s. I have tracked down the
> problem to a working example.
What changes have you made to postgresql.conf?
Could you send explain analyse again with SEQ_SCAN enabled but with
nested loops disabled?
Off the cuff? I might hazard a guess that effective_cache is too low or
random_page_cost is a touch too high. Probably the former.
--
Rod Taylor <rbt [at] rbt [dot] ca>
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: seq scan woes
@ 2004-06-07 20:12 Dan Langille <dan@langille.org>
parent: Dan Langille <dan@langille.org>
1 sibling, 3 replies; 7+ messages in thread
From: Dan Langille @ 2004-06-07 20:12 UTC (permalink / raw)
To: Rod Taylor <ports@rbt.ca>; +Cc: pgsql-performance
On 7 Jun 2004 at 16:00, Rod Taylor wrote:
> On Mon, 2004-06-07 at 15:45, Dan Langille wrote:
> > A production system has had a query recently degrade in performance.
> > What once took < 1s now takes over 1s. I have tracked down the
> > problem to a working example.
>
> What changes have you made to postgresql.conf?
Nothing recently (ie. past few months). Nothing at all really.
Perhaps I need to start tuning that.
> Could you send explain analyse again with SEQ_SCAN enabled but with
> nested loops disabled?
See http://rafb.net/paste/results/zpJEvb28.html
13s
> Off the cuff? I might hazard a guess that effective_cache is too low or
> random_page_cost is a touch too high. Probably the former.
I grep'd postgresql.conf:
#effective_cache_size = 1000 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch cost
NOTE: both above are commented out.
Thank you
--
Dan Langille : http://www.langille.org/
BSDCan - http://www.bsdcan.org/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: seq scan woes
@ 2004-06-07 20:38 Rod Taylor <ports@rbt.ca>
parent: Dan Langille <dan@langille.org>
2 siblings, 1 reply; 7+ messages in thread
From: Rod Taylor @ 2004-06-07 20:38 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-performance
On Mon, 2004-06-07 at 16:12, Dan Langille wrote:
> On 7 Jun 2004 at 16:00, Rod Taylor wrote:
>
> > On Mon, 2004-06-07 at 15:45, Dan Langille wrote:
> > > A production system has had a query recently degrade in performance.
> > > What once took < 1s now takes over 1s. I have tracked down the
> > > problem to a working example.
> >
> > What changes have you made to postgresql.conf?
>
> Nothing recently (ie. past few months). Nothing at all really.
> Perhaps I need to start tuning that.
>
> > Could you send explain analyse again with SEQ_SCAN enabled but with
> > nested loops disabled?
>
> See http://rafb.net/paste/results/zpJEvb28.html
This doesn't appear to be the same query as we were shown earlier.
> > Off the cuff? I might hazard a guess that effective_cache is too low or
> > random_page_cost is a touch too high. Probably the former.
>
> I grep'd postgresql.conf:
>
> #effective_cache_size = 1000 # typically 8KB each
> #random_page_cost = 4 # units are one sequential page fetch cost
This would be the issue. You haven't told PostgreSQL anything about your
hardware. The defaults are somewhat modest.
http://www.postgresql.org/docs/7.4/static/runtime-config.html
Skim through the run-time configuration parameters that can be set in
postgresql.conf.
Pay particular attention to:
* shared_buffers (you may be best with 2000 or 4000)
* effective_cache_size (set to 50% of ram size if dedicated db
machine)
* random_page_cost (good disks will bring this down to a 2 from a
4)
--
Rod Taylor <rbt [at] rbt [dot] ca>
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: seq scan woes
@ 2004-06-07 21:02 Dan Langille <dan@langille.org>
parent: Dan Langille <dan@langille.org>
2 siblings, 0 replies; 7+ messages in thread
From: Dan Langille @ 2004-06-07 21:02 UTC (permalink / raw)
To: Rod Taylor <ports@rbt.ca>; +Cc: pgsql-performance
On 7 Jun 2004 at 16:38, Rod Taylor wrote:
> On Mon, 2004-06-07 at 16:12, Dan Langille wrote:
> > On 7 Jun 2004 at 16:00, Rod Taylor wrote:
> >
> > > On Mon, 2004-06-07 at 15:45, Dan Langille wrote:
> > > > A production system has had a query recently degrade in performance.
> > > > What once took < 1s now takes over 1s. I have tracked down the
> > > > problem to a working example.
> > >
> > > What changes have you made to postgresql.conf?
> >
> > Nothing recently (ie. past few months). Nothing at all really.
> > Perhaps I need to start tuning that.
> >
> > > Could you send explain analyse again with SEQ_SCAN enabled but with
> > > nested loops disabled?
> >
> > See http://rafb.net/paste/results/zpJEvb28.html
>
> This doesn't appear to be the same query as we were shown earlier.
My apologies. I should try to cook dinner and paste at the same time.
;)
http://rafb.net/paste/results/rVr3To35.html is the right query.
> > > Off the cuff? I might hazard a guess that effective_cache is too low or
> > > random_page_cost is a touch too high. Probably the former.
> >
> > I grep'd postgresql.conf:
> >
> > #effective_cache_size = 1000 # typically 8KB each
> > #random_page_cost = 4 # units are one sequential page fetch cost
>
> This would be the issue. You haven't told PostgreSQL anything about your
> hardware. The defaults are somewhat modest.
>
> http://www.postgresql.org/docs/7.4/static/runtime-config.html
>
> Skim through the run-time configuration parameters that can be set in
> postgresql.conf.
>
> Pay particular attention to:
> * shared_buffers (you may be best with 2000 or 4000)
> * effective_cache_size (set to 50% of ram size if dedicated db
> machine)
> * random_page_cost (good disks will bring this down to a 2 from a
> 4)
I'll have a play with that and report back.
Thanks.
--
Dan Langille : http://www.langille.org/
BSDCan - http://www.bsdcan.org/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: seq scan woes
@ 2004-06-07 22:49 Dan Langille <dan@langille.org>
parent: Dan Langille <dan@langille.org>
2 siblings, 0 replies; 7+ messages in thread
From: Dan Langille @ 2004-06-07 22:49 UTC (permalink / raw)
To: Rod Taylor <ports@rbt.ca>; +Cc: pgsql-performance
On 7 Jun 2004 at 16:38, Rod Taylor wrote:
> On Mon, 2004-06-07 at 16:12, Dan Langille wrote:
> > I grep'd postgresql.conf:
> >
> > #effective_cache_size = 1000 # typically 8KB each
> > #random_page_cost = 4 # units are one sequential page fetch cost
>
> This would be the issue. You haven't told PostgreSQL anything about your
> hardware. The defaults are somewhat modest.
>
> http://www.postgresql.org/docs/7.4/static/runtime-config.html
>
> Skim through the run-time configuration parameters that can be set in
> postgresql.conf.
>
> Pay particular attention to:
> * shared_buffers (you may be best with 2000 or 4000)
I do remember increasing this in the past. It was now at 1000 and is
now at 2000.
see http://rafb.net/paste/results/VbXQcZ87.html
> * effective_cache_size (set to 50% of ram size if dedicated db
> machine)
The machine has 512MB RAM. effective_cache_size was at 1000. So
let's try a 256MB cache. Does that the match a 32000 setting? I
tried it. The query went to 1.5s. At 8000, the query was 1s. At
2000, the query was about 950ms.
This machine is a webserver/database/mail server, but the FreshPorts
database is by far its biggest task.
> * random_page_cost (good disks will bring this down to a 2 from a
> 4)
I've got mine set at 4. Increasing it to 6 gave me a 1971ms query.
At 3, it was a 995ms. Setting it to 2 gave me a 153ms query.
How interesting.
For camparison, I reset shared_buffers and effective_cache_size back
to their original value (both at 1000). This gave me a 130-140ms
query.
The disks in question is:
ad0: 19623MB <IC35L020AVER07-0> [39870/16/63] at ata0-master UDMA100
I guess that might be this disk:
http://www.harddrives4less.com/ibmdes6020ua2.html
I invite comments upon my findings.
Rod: thanks for the suggestions.
>
>
> --
> Rod Taylor <rbt [at] rbt [dot] ca>
>
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> PGP Key: http://www.rbt.ca/signature.asc
>
>
--
Dan Langille : http://www.langille.org/
BSDCan - http://www.bsdcan.org/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: seq scan woes
@ 2004-06-07 22:55 Dan Langille <dan@langille.org>
parent: Rod Taylor <ports@rbt.ca>
0 siblings, 0 replies; 7+ messages in thread
From: Dan Langille @ 2004-06-07 22:55 UTC (permalink / raw)
To: Rod Taylor <ports@rbt.ca>; +Cc: pgsql-performance
On 7 Jun 2004 at 18:49, Dan Langille wrote:
> On 7 Jun 2004 at 16:38, Rod Taylor wrote:
> > * random_page_cost (good disks will bring this down to a 2 from a
> > 4)
>
> I've got mine set at 4. Increasing it to 6 gave me a 1971ms query.
> At 3, it was a 995ms. Setting it to 2 gave me a 153ms query.
>
> How interesting.
The explain analyse: http://rafb.net/paste/results/pWhHsL86.html
--
Dan Langille : http://www.langille.org/
BSDCan - http://www.bsdcan.org/
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-06-07 22:55 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-07 19:45 seq scan woes Dan Langille <dan@langille.org>
2004-06-07 20:00 ` Rod Taylor <ports@rbt.ca>
2004-06-07 20:12 ` Dan Langille <dan@langille.org>
2004-06-07 20:38 ` Rod Taylor <ports@rbt.ca>
2004-06-07 22:55 ` Dan Langille <dan@langille.org>
2004-06-07 21:02 ` Dan Langille <dan@langille.org>
2004-06-07 22:49 ` Dan Langille <dan@langille.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox