Re: Recursive query performance issue

Had the issue again today.

Here is vmstat :
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
24 0 0 1591718656 605656 499370336 0 0 0 371 0 0 7 1 93 0
25 0 0 1591701376 605656 499371936 0 0 0 600 13975 20168 20 1 79 0
26 0 0 1591654784 605656 499372064 0 0 0 5892 12725 14627 20 1 79 0
25 0 0 1591614336 605656 499372128 0 0 0 600 11665 12642 21 1 78 0
27 0 0 1591549952 605656 499372192 0 0 0 408 16939 23387 23 1 76 0
29 0 0 1591675392 605656 499372288 0 0 0 836 15380 22564 23 1 76 0
27 0 0 1591608704 605656 499372352 0 0 0 456 17593 27955 23 1 76 0
34 0 0 1591524608 605656 499372480 0 0 0 5904 18963 30915 23 1 75 0
23 0 0 1591632384 605656 499372576 0 0 0 704 18190 31002 22 1 77 0
25 0 0 1591551360 605656 499372640 0 0 0 944 12532 14095 21 1 78 0
24 0 0 1591613568 605656 499372704 0 0 0 416 11183 12553 20 1 79 0
23 0 0 1591531520 605656 499372768 0 0 0 400 12648 15540 19 1 80 0
22 0 0 1591510528 605656 499372800 0 0 0 6024 14670 21993 19 1 80 0
31 0 0 1591388800 605656 499372896 0 0 0 472 20605 28242 20 1 79 0

We have a 120 CPU server :)

processor : 119
vendor_id : GenuineIntel
cpu family : 6
model : 62
model name : Intel(R) Xeon(R) CPU E7-4880 v2 @ 2.50GHz

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
Sent: Monday, October 26, 2015 8:04 AM
To: Jamie Koceniak
Cc: Pavel Stehule; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Recursive query performance issue

On Fri, Oct 23, 2015 at 12:45 PM, Jamie Koceniak <jkoceniak(at)mediamath(dot)com> wrote:
> Hi,
>
> We just had the performance problem again today.
> Here is some of the top output. Unfortunately, we don't have perf top installed.
>
> top - 16:22:16 up 29 days, 13:00, 2 users, load average: 164.63, 158.62, 148.52
> Tasks: 1369 total, 181 running, 1188 sleeping, 0 stopped, 0 zombie
> %Cpu(s): 6.2 us, 0.7 sy, 0.0 ni, 93.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
> MiB Mem: 2068265 total, 433141 used, 1635124 free, 586 buffers
> MiB Swap: 7812 total, 0 used, 7812 free, 412641 cached
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 81745 postgres 20 0 65.7g 51m 34m R 101 0.0 0:09.20 postgres: user1 db 0.0.0.2(52307) SELECT
> 81782 postgres 20 0 65.7g 51m 34m R 101 0.0 0:08.50 postgres: user1 db 0.0.0.3(44630) SELECT
> 81797 postgres 20 0 65.7g 51m 34m R 101 0.0 0:08.03 postgres: user1 db 0.0.0.6(60752) SELECT
<snip>

ok, this rules out iowait.

load is 160+. system is reporting 6.2%user, 93.1%idle, 0 iowait.
This is very odd.
*) how many processors do you have?
*) Can we have more details about the hardware platform?
*) Is this system virtualized? If so, what solution?

we need a perf top and a capture of 'vmstat 1' for context switches

merlin