Re: pl/pgsql function spikes CPU 100%

On Tue, 24 Apr 2007, Tom Lane wrote:

> Jeff Frost <jeff(at)frostconsultingllc(dot)com> writes:
>> Well, finally got this system upgraded to 8.1.8, but unfortunately, that did
>> not resolve this. Is there any reasonable way to see where this function is
>> spending it's time?
>
> I've grown enamored of oprofile lately --- if you're on a recent Linux
> system, try that. Solaris' DTrace is said to have equivalent
> capability. On other systems you could consider plain 'ol gprof.
>
> Any of these will require a certain amount of man-page-reading :-(

Got my oprofile man page reading done. Here's the general opreport:

CPU: P4 / Xeon with 2 hyper-threads, speed 3196.26 MHz (estimated)
Counted GLOBAL_POWER_EVENTS events (time during which processor is not
stopped)
with a unit mask of 0x01 (mandatory) count 100000
GLOBAL_POWER_E...|
samples| %|
------------------
26902279 96.3837 postgres
612276 2.1936 vmlinux
172733 0.6189 oprofiled
94914 0.3401 libc-2.3.4.so
54608 0.1956 oprofile
22887 0.0820 opreport
10811 0.0387 jbd
7792 0.0279 ext3
7673 0.0275 ld-2.3.4.so
6143 0.0220 libstdc++.so.6.0.3
4336 0.0155 e1000
3253 0.0117 ip_conntrack
2305 0.0083 ip_tables
1957 0.0070 libcrypto.so.0.9.7a
1275 0.0046 hald
1066 0.0038 uhci_hcd
859 0.0031 iptable_nat

And the postgres one:

CPU: P4 / Xeon with 2 hyper-threads, speed 3196.26 MHz (estimated)
Counted GLOBAL_POWER_EVENTS events (time during which processor is not
stopped)
with a unit mask of 0x01 (mandatory) count 100000
samples % symbol name
3894022 12.6488 LWLockAcquire
3535497 11.4842 slot_deform_tuple
3030280 9.8431 LWLockRelease
2279699 7.4050 HeapTupleSatisfiesSnapshot
1782097 5.7887 ExecMakeFunctionResultNoSets
1209262 3.9280 ExecEvalScalarVar
1128461 3.6655 heap_release_fetch
916447 2.9768 LockBuffer
898437 2.9183 _bt_checkkeys
887904 2.8841 btgettuple
728377 2.3660 slot_getattr
648121 2.1053 hash_search
608352 1.9761 _bt_restscan
574699 1.8668 index_getnext
570999 1.8547 _bt_step
517225 1.6801 FunctionCall2
501845 1.6301 _bt_next
452142 1.4687 IndexNext
426455 1.3852 PinBuffer

Is any of that helpful? This is the 2.6.9-34.ELsmp centos kernel.

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954