Re: Finding bottleneck

From: Merlin Moncure
Subject: Re: Finding bottleneck
Date: ,
Msg-id: 6EE64EF3AB31D5448D0007DD34EEB3417DD15D@Herge.rcsinc.local
(view: Whole thread, Raw)
In response to: Finding bottleneck  (Kari Lavikka)
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", )

> Bill of Materials Traversal ( ~ 62k records).
>
>              ISAM*      pg 8.0     pg 8.1 devel   delta 8.0->8.1
> running time 63 sec     90 secs    71 secs        21%
> cpu load     17%        45%        32%            29%
> loadsecs**   10.71      40.5       22.72          44%
> recs/sec     984        688        873
> recs/loadsec 5882       1530       2728
>
> *ISAM is an anonymous commercial ISAM library in an optimized server
> architecture (pg smokes the non-optimized flat file version).
> **Loadsecs being seconds of CPU at 100% load.

One thing that might interest you is that the penalty in 8.1 for
stats_command_string=true in this type of access pattern is very high: I
was experimenting to see if the new cpu efficiency gave me enough of a
budget to start using this.  This more than doubled the cpu load to
around 70% with a runtime of 82 seconds.  This is actually worse than
8.0 :(.

This *might* be a somewhat win32 specific issue.  I've had issues with
the stats collector before.  Anyways, the feature is a frill so it's not
a big deal.

Merlin




pgsql-performance by date:

From: Josh Berkus
Date:
Subject: Re: MemoryContextSwitchTo during table scan?
From: Tom Lane
Date:
Subject: Re: complex query performance assistance request