Re: Finding bottleneck

From: Kari Lavikka
Subject: Re: Finding bottleneck
Date: ,
Msg-id: Pine.HPX.4.62.0508081444470.3361@purple.bdb.fi
(view: Whole thread, Raw)
In response to: Re: Finding bottleneck  ("Luke Lonergan")
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", )

Hi!

Oprofile looks quite interesting. I'm not very familiar with postgresql
internals, but here's some report output:

CPU: AMD64 processors, speed 2190.23 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Cycles outside of halt state) with a unit
mask of 0x00 (No unit mask) count 100000
samples  %        symbol name
13513390 16.0074  AtEOXact_CatCache
4492257   5.3213  StrategyGetBuffer
2279285   2.6999  AllocSetAlloc
2121509   2.5130  LWLockAcquire
2023574   2.3970  hash_seq_search
1971358   2.3352  nocachegetattr
1837168   2.1762  GetSnapshotData
1793693   2.1247  SearchCatCache
1777385   2.1054  hash_search
1460804   1.7304  ExecMakeFunctionResultNoSets
1360930   1.6121  _bt_compare
1344604   1.5928  yyparse
1318407   1.5617  LWLockRelease
1290814   1.5290  FunctionCall2
1137544   1.3475  ExecEvalVar
1102236   1.3057  hash_any
912677    1.0811  OpernameGetCandidates
877993    1.0400  ReadBufferInternal
783908    0.9286  TransactionIdPrecedes
772886    0.9155  MemoryContextAllocZeroAligned
679768    0.8052  StrategyBufferLookup
609339    0.7218  equal
600584    0.7114  PGSemaphoreLock

And btw, I tried to strace lingering queries under different loads. When
number of concurrent queries increases, lseek and read syscalls stay
within quite constant limits but number of semop calls quadruples.

Are there some buffer locking issues?

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

On Thu, 28 Jul 2005, Luke Lonergan wrote:

> On 7/28/05 2:21 AM, "Kari Lavikka" <> wrote:
>
> There's a new profiling tool called oprofile:
>
> http://oprofile.sourceforge.net/download/
>
> that can be run without instrumenting the binaries beforehand.  To actually
> find out what the code is doing during these stalls, oprofile can show you
> in which routines the CPU is spending time when you start/stop the
> profiling.
>
> As an alternative to the "guess->change parameters->repeat" approach, this
> is the most direct way to find the exact nature of the problem.
>
> - Luke
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


pgsql-performance by date:

From: Kari Lavikka
Date:
Subject: Re: Finding bottleneck
From: Tom Lane
Date:
Subject: Re: Finding bottleneck