От: Dan Langille
Тема: Re: seq scan woes
Дата: ,
Msg-id: 40C4B8CA.5077.4983460F@localhost
(см: обсуждение, исходный текст)
Ответ на: Re: seq scan woes  ("Dan Langille")
Ответы: Re: seq scan woes  ("Dan Langille")
Список: pgsql-performance

Скрыть дерево обсуждения

seq scan woes  ("Dan Langille", )
 Re: seq scan woes  ("Dan Langille", )
  Re: seq scan woes  ("Dan Langille", )
  Re: seq scan woes  ("Dan Langille", )
   Re: seq scan woes  ("Dan Langille", )
  Re: seq scan woes  (Rod Taylor, )
 Re: seq scan woes  (Rod Taylor, )

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

The disks in question is:

ad0: 19623MB <IC35L020AVER07-0> [39870/16/63] at ata0-master UDMA100

I guess that might be this disk:

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/

В списке pgsql-performance по дате сообщения:

От: Tom Lane
Сообщение: Re: pl/pgsql and Transaction Isolation
От: Tom Lane
Сообщение: Re: Join slow on "large" tables