Re: Finding bottleneck

From: Kari Lavikka
Subject: Re: Finding bottleneck
Date: ,
Msg-id: Pine.HPX.4.62.0508082020421.3361@purple.bdb.fi
(view: Whole thread, Raw)
In response to: Re: Finding bottleneck  (Tom Lane)
Responses: Re: Finding bottleneck  (Tom Lane)
List: pgsql-performance

Tree view

Finding bottleneck  (Kari Lavikka, )
 Re: Finding bottleneck  (Gavin Sherry, )
 Re: Finding bottleneck  (Claus Guttesen, )
 Re: Finding bottleneck  ("Merlin Moncure", )
 Re: Finding bottleneck  ("Luke Lonergan", )
  Re: Finding bottleneck  (Kari Lavikka, )
   Re: Finding bottleneck  (Tom Lane, )
 Re: Finding bottleneck  ("Merlin Moncure", )
  Re: Finding bottleneck  (Tom Lane, )
  Re: Finding bottleneck  (Kari Lavikka, )
   Re: Finding bottleneck  (Tom Lane, )
    Re: Finding bottleneck  (Kari Lavikka, )
     Re: Finding bottleneck  (Tom Lane, )
    Re: Finding bottleneck  (Kari Lavikka, )
     Re: Finding bottleneck  (Tom Lane, )
 Re: Finding bottleneck  (Ron, )
 Re: Finding bottleneck  ("Merlin Moncure", )
  Re: Finding bottleneck  (Tom Lane, )
 Re: Finding bottleneck  ("Merlin Moncure", )
 Re: Finding bottleneck  ("Merlin Moncure", )
  Re: Finding bottleneck  (Tom Lane, )
 Re: Finding bottleneck  ("Merlin Moncure", )

On Mon, 8 Aug 2005, Tom Lane wrote:
> What that sounds like to me is a machine with inadequate disk I/O bandwidth.
> Your earlier comment that checkpoint drives the machine into the ground
> fits right into that theory, too.  You said there is "almost no IO-wait"
> but are you sure you are measuring that correctly?

Currently there's some iowait caused by "fragmentation" of the comment
table. Periodic clustering helps a lot.

Disk configurations looks something like this:
   sda: data (10 spindles, raid10)
   sdb: xlog & clog (2 spindles, raid1)
   sdc: os and other stuff

Usually iostat (2 second interval) says:
   avg-cpu:  %user   %nice    %sys %iowait   %idle
             32.38    0.00   12.88   11.62   43.12

   Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
   sda             202.00      1720.00         0.00       3440          0
   sdb             152.50         4.00      2724.00          8       5448
   sdc               0.00         0.00         0.00          0          0

And during checkpoint:
   avg-cpu:  %user   %nice    %sys %iowait   %idle
             31.25    0.00   14.75   54.00    0.00

   Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
   sda            3225.50      1562.00     35144.00       3124      70288
   sdb             104.50        10.00      2348.00         20       4696
   sdc               0.00         0.00         0.00          0          0

I think (insufficiency of) disk IO shouldn't cause those lingering queries
because dataset is rather small and it's continuously accessed. It should
fit into cache and stay there(?)

> 400 queries?  Are you launching 400 separate backends to do that?

Well yes. That's the common problem with php and persistent connections.

> Some sort of connection pooling seems like a good idea, if you don't
> have it in place already.

pg_pool for example? I'm planning to give it a try.

>             regards, tom lane


     |\__/|
     ( oo )    Kari Lavikka -  - (050) 380 3808
__ooO(  )Ooo_______ _____ ___ _ _  _   _    _      _                  _
       ""



pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: Finding bottleneck
From: Tom Lane
Date:
Subject: Re: Why hash join instead of nested loop?