Re: Consecutive Query Executions with Increasing Execution Time

Hi,

On 2019-12-16 17:48:16 -0500, Tom Lane wrote:
> Hmm, that's an interesting thought. The OP did say the CPU type,
> but according to Intel's spec page for it [1] the difference between
> base and turbo frequency is only 4.0 vs 4.2 GHz, which doesn't seem
> like enough to explain the results ... unless you suppose it actually
> throttled to below base freq, which surely shouldn't happen that fast.
> Might be worth watching the CPU frequency while doing the test though.

FWIW, it takes about 3s for my laptop CPU to throttle way below
non-turbo when I put it under strenuous load. Obviously that's a laptop,
and caused by a firmware bug leading to fans not spinning fast enough
automatically. But it'd not take that much for insufficient cooling to
cause problems in a desktop either. Been there, done that.

But: I don't see that causing a 10x slowdown as reported in the first
mail in this thread.

I think we need a system-wide perf profile during a few initial "good"
runs and then later from a few "really bad" runs. For that you'd have to
make sure you compiled postgres with debug symbols (--enable-debug to
configure), and then run something like
perf record -o fast.data --call-graph dwarf -a sleep 3
while running repeated "fast" queries and then
perf record -o slow.data --call-graph dwarf -a sleep 3

and then show us the results of something like
perf report -i fast.data -g folded --percent-limit 1 > fast.txt
perf report -i slow.data -g folded --percent-limit 1 > slow.txt

and also, if your perf is new enough:
perf diff fast.data slow.data > diff.txt

- Andres