Re: Slow Postgresql server

From: Ron
Subject: Re: Slow Postgresql server
Date: ,
Msg-id: E1Hc0lc-0001iE-La@elasmtp-banded.atl.sa.earthlink.net
(view: Whole thread, Raw)
In response to: Re: Slow Postgresql server  (Guido Neitzer)
Responses: Re: Slow Postgresql server  (Guido Neitzer)
List: pgsql-performance

Tree view

Slow Postgresql server  (Jason Lustig, )
 Re: Slow Postgresql server  (Dennis Bjorklund, )
 Re: Slow Postgresql server  (Jeff Frost, )
 Re: Slow Postgresql server  (Ron, )
  Re: Slow Postgresql server  (Guido Neitzer, )
   Re: Slow Postgresql server  (Ron, )
    Re: Slow Postgresql server  (Guido Neitzer, )
     Re: Slow Postgresql server  (Scott Marlowe, )
      Re: Slow Postgresql server  (Jeff Frost, )
       Re: Slow Postgresql server  (Carlos Moreno, )
       Strangely Variable Query Performance  (Steve, )
        Re: Strangely Variable Query Performance  (Tom Lane, )
         Re: Strangely Variable Query Performance  (Steve, )
          Re: Strangely Variable Query Performance  (Tom Lane, )
           Re: Strangely Variable Query Performance  (Steve, )
            Re: Strangely Variable Query Performance  (Tom Lane, )
             Re: Strangely Variable Query Performance  (Steve, )
        Re: Strangely Variable Query Performance  (Scott Marlowe, )
         Re: Strangely Variable Query Performance  (Steve, )
          Re: Strangely Variable Query Performance  (Scott Marlowe, )
           Re: Strangely Variable Query Performance  (Steve, )
           Re: Strangely Variable Query Performance  (Tom Lane, )
            Re: Strangely Variable Query Performance  (Steve, )
             Re: Strangely Variable Query Performance  (Tom Lane, )
              Re: Strangely Variable Query Performance  (Steve, )
             Re: Strangely Variable Query Performance  (Tom Lane, )
              Re: Strangely Variable Query Performance  (Steve, )
               Re: Strangely Variable Query Performance  (Tom Lane, )
                Re: Strangely Variable Query Performance  (Steve, )
                 Re: Strangely Variable Query Performance  (Tom Lane, )
                  Re: Strangely Variable Query Performance  (Steve, )
                   Re: Strangely Variable Query Performance  (Tom Lane, )
                   Re: Strangely Variable Query Performance  (Tom Lane, )
                    Re: Strangely Variable Query Performance  (Steve, )
                     Re: Strangely Variable Query Performance  (Tom Lane, )
                      Fwd: Strangely Variable Query Performance  ("Robins Tharakan", )
                     choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
                      Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Alvaro Herrera, )
                       Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
                       Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
              Re: Strangely Variable Query Performance  (Steve, )
             Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
 Re: Slow Postgresql server  (Jeff Frost, )
  Re: Slow Postgresql server  (Jason Lustig, )
   Re: Slow Postgresql server  (Guido Neitzer, )
 Fwd: Strangely Variable Query Performance  (Robins, )
  Re: Fwd: Strangely Variable Query Performance  (Tom Lane, )

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



pgsql-performance by date:

From: Carlos Moreno
Date:
Subject: Re: Slow Postgresql server
From: Steve
Date:
Subject: Strangely Variable Query Performance