Re: Finding bottleneck

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

On Thu, 28 Jul 2005, Luke Lonergan wrote:

> On 7/28/05 2:21 AM, "Kari Lavikka" <tuner(at)bdb(dot)fi> 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
>