Re: pl/pgsql function spikes CPU 100% - Mailing list pgsql-admin

On Tue, 24 Apr 2007, Tom Lane wrote:

> Jeff Frost <jeff@frostconsultingllc.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@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954