Thread: seq scan woes

From:
"Dan Langille"
Date:

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/


From:
"Dan Langille"
Date:

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/


From:
"Dan Langille"
Date:

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/


From:
"Dan Langille"
Date:

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/


From:
"Dan Langille"
Date:

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/


From:
Rod Taylor
Date:

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


From:
Rod Taylor
Date:

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