Re: seq scan woes

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/