Re: Slow Postgresql server

At 10:08 AM 4/12/2007, Guido Neitzer wrote:
>On 12.04.2007, at 07:26, Ron wrote:
>
>>You need to buy RAM and HD.
>
>Before he does that, wouldn't it be more useful, to find out WHY he
>has so much IO?

1= Unless I missed something, the OP described pg being used as a
backend DB for a webserver.

I know the typical IO demands of that scenario better than I sometimes want to.
:-(

2= 1GB of RAM + effectively 1 160GB HD = p*ss poor DB IO support.
~ 1/2 that RAM is going to be used for OS stuff, leaving only ~512MB
of RAM to be used supporting pg.
That RAID 1 set is effectively 1 HD head that all IO requests are
going to contend for.
Even if the HD in question is a 15Krpm screamer, that level of HW
contention has very adverse implications.

Completely agree that at some point the queries need to be examined
(ditto the table schema, etc), but this system is starting off in a
Bad Place for its stated purpose IME.
Some minimum stuff is obvious even w/o spending time looking at
anything beyond the HW config.

Cheers,
Ron Peacetree

>Have I missed that or has nobody suggested finding the slow queries
>(when you have much IO on them, they might be slow at least with a
>high shared memory setting).
>
>So, my first idea is, to turn on query logging for queries longer
>than a xy milliseconds, "explain analyse" these queries and see
>wether there are a lot of seq scans involved, which would explain the
>high IO.
>
>Just an idea, perhaps I missed that step in that discussion
>somewhere ...
>
>But yes, it might also be, that the server is swapping, that's
>another thing to find out.
>
>cug
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq