From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Frost <jeff@frostconsultingllc.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: pl/pgsql function spikes CPU 100%
Date: Fri, 04 May 2007 00:15:20 -0400
Message-ID: <10129.1178252120@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.64.0705032021230.7932@discord.home.frostconsultingllc.com>
References: <Pine.LNX.4.64.0703160147310.11964@discord.home.frostconsultingllc.com>
<6522.1174056565@sss.pgh.pa.us>
<Pine.LNX.4.64.0703160820380.12217@discord.home.frostconsultingllc.com>
<Pine.LNX.4.64.0703160827490.12217@discord.home.frostconsultingllc.com>
<Pine.LNX.4.64.0704232306500.9190@discord.home.frostconsultingllc.com>
<24506.1177396393@sss.pgh.pa.us>
<Pine.LNX.4.64.0705032021230.7932@discord.home.frostconsultingllc.com>
Jeff Frost <jeff@frostconsultingllc.com> writes:
> Got my oprofile man page reading done. Here's the general opreport:
> ...
> And the postgres one:
> 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
It seems to basically be spending its time scanning tuples and applying
some function or other (probably a WHERE clause). The btree references
suggest that the scan is an indexscan and not a brute-force seqscan, but
still I wonder if the problem isn't that the planner has switched to some
plan much less efficient than the one it was using before. oprofile is
too low-level to give us much info about that, unfortunately.
Probably your next step should be to investigate the plans being used
for the queries in the slow functions. See the archives for hints, eg
http://archives.postgresql.org/pgsql-performance/2006-06/msg00301.php
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, jeff@frostconsultingllc.com
Subject: Re: pl/pgsql function spikes CPU 100%
In-Reply-To: <10129.1178252120@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox