Re: Finding bottleneck

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 - tuner@bdb.fi - (050) 380 3808
__ooO(  )Ooo_______ _____ ___ _ _  _   _    _      _                  _
       ""