Re: Recursive query performance issue - Mailing list pgsql-performance

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@gmail.com] 
Sent: Monday, October 26, 2015 8:04 AM
To: Jamie Koceniak
Cc: Pavel Stehule; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Recursive query performance issue

On Fri, Oct 23, 2015 at 12:45 PM, Jamie Koceniak <jkoceniak@mediamath.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