Re: Debugging a backend stuck consuming CPU

"ktm(at)rice(dot)edu" <ktm(at)rice(dot)edu> writes:
> The stack trace just appeared to be what I would expect while a 'DISCARD ALL'
> command was being run:

> #0 0x000000000073bc7c in MemoryContextSetParent ()
> #1 0x000000000073bde3 in MemoryContextDelete ()
> #2 0x000000000054e3a9 in DropAllPreparedStatements ()
> #3 0x00000000005365f3 in DiscardCommand ()

Hmm, what it seems from these traces is that you've got a whole heck of
a lot of prepared statements.

> The backend does have a very large memory footprint (12GB).

Um.

The most likely explanation is that you are hitting O(N^2) behavior as
a consequence of MemoryContextSetParent being O(N) in the number of
sibling contexts of the context to be deleted. We fixed that for 9.6
(commit 25c539233044c235e97fd7c9dc600fb5f08fe065) but there's no easy
solution in older branches, short of not using so many prepared
statements. I'm a bit surprised that you could have gotten up to 12GB
worth of prepared statements in an application that sends DISCARD ALL
periodically.

regards, tom lane