Re: Recursive query performance issue

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
 67103 postgres  20   0 65.7g  81m  56m R    97  0.0   2:01.89 postgres: user1 db 0.0.0.4(46337) SELECT
 82527 postgres  20   0 65.7g  25m  20m R    93  0.0   0:02.35 postgres: user1 db 0.0.0.2(52490) SELECT
 82559 postgres  20   0 65.7g  25m  20m R    93  0.0   0:02.17 postgres: user1 db 0.0.0.2(52496) SELECT
 82728 postgres  20   0 65.7g  80m  76m R    93  0.0   0:00.60 postgres: user1 db 0.0.0.6(60957) SELECT
 65588 postgres  20   0 65.7g  76m  56m R    89  0.0   2:12.27 postgres: user1 db 0.0.0.6(57195) SELECT
 80594 postgres  20   0 65.7g  34m  28m R    89  0.0   0:22.81 postgres: user1 db 0.0.0.2(52071) SELECT
 25176 postgres  20   0 65.7g  74m  57m R    85  0.0   7:24.42 postgres: user1 db 0.0.0.2(39410) SELECT
 82182 postgres  20   0 65.7g 513m 502m R    85  0.0   0:04.85 postgres: user1 db 0.0.0.4(49789) SELECT
 82034 postgres  20   0 65.7g 523m 510m R    81  0.0   0:05.79 postgres: user1 db 0.0.0.3(44683) SELECT
 82439 postgres  20   0 65.7g 262m 258m R    81  0.0   0:02.64 postgres: user1 db 0.0.0.6(60887) SELECT
 82624 postgres  20   0 65.7g 148m 143m R    81  0.0   0:01.20 postgres: user1 db 0.0.0.4(49888) SELECT
 82637 postgres  20   0 65.7g 139m 134m R    81  0.0   0:01.17 postgres: user1 db 0.0.0.3(44805) SELECT
 82669 postgres  20   0 65.7g 119m 114m R    81  0.0   0:00.97 postgres: user1 db 0.0.0.6(60939) SELECT
 82723 postgres  20   0 65.7g  79m  75m R    81  0.0   0:00.56 postgres: user1 db 0.0.0.4(49907) SELECT
 29160 postgres  20   0 65.7g  79m  54m R    77  0.0   6:52.13 postgres: user1 db 0.0.0.6(48802) SELECT
 51095 postgres  20   0 65.7g  81m  57m R    77  0.0   4:01.51 postgres: user1 db 0.0.0.4(42914) SELECT
 81833 postgres  20   0 65.7g 528m 515m R    77  0.0   0:07.23 postgres: user1 db 0.0.0.3(44644) SELECT
 81978 postgres  20   0 65.7g 528m 515m R    77  0.0   0:06.05 postgres: user1 db 0.0.0.2(52364) SELECT
 82099 postgres  20   0 65.7g 523m 510m R    77  0.0   0:05.18 postgres: user1 db 0.0.0.3(44692) SELECT
 82111 postgres  20   0 65.7g 523m 510m R    77  0.0   0:05.14 postgres: user1 db 0.0.0.4(49773) SELECT
 82242 postgres  20   0 65.7g 433m 429m R    77  0.0   0:04.27 postgres: user1 db 0.0.0.2(52428) SELECT
 82292 postgres  20   0 65.7g 407m 402m R    77  0.0   0:04.10 postgres: user1 db 0.0.0.2(52440) SELECT
 82408 postgres  20   0 65.7g 292m 288m R    77  0.0   0:02.98 postgres: user1 db 0.0.0.4(49835) SELECT
 82542 postgres  20   0 65.7g 207m 202m R    77  0.0   0:01.98 postgres: user1 db 0.0.0.4(49868) SELECT
 63638 postgres  20   0 65.7g  80m  56m R    73  0.0   2:30.10 postgres: user1 db 0.0.0.2(48699) SELECT
 71572 postgres  20   0 65.7g  80m  56m R    73  0.0   1:31.13 postgres: user1 db 0.0.0.2(50223) SELECT
 80580 postgres  20   0 65.7g  34m  28m R    73  0.0   0:22.93 postgres: user1 db 0.0.0.2(52065) SELECT
 81650 postgres  20   0 65.8g 622m 555m R    73  0.0   0:08.84 postgres: user1 db 0.0.0.2(52290) SELECT
 81728 postgres  20   0 65.7g 523m 510m R    73  0.0   0:08.28 postgres: user1 db 0.0.0.4(49684) SELECT
 81942 postgres  20   0 65.7g 528m 515m R    73  0.0   0:06.46 postgres: user1 db 0.0.0.2(52355) SELECT
 81958 postgres  20   0 65.7g 528m 514m R    73  0.0   0:06.48 postgres: user1 db 0.0.0.4(49744) SELECT
 81980 postgres  20   0 65.7g 528m 515m R    73  0.0   0:06.02 postgres: user1 db 0.0.0.3(44671) SELECT
 82007 postgres  20   0 65.7g 523m 510m R    73  0.0   0:06.27 postgres: user1 db 0.0.0.3(44676) SELECT
 82374 postgres  20   0 65.7g 367m 362m R    73  0.0   0:03.48 postgres: user1 db 0.0.0.6(60873) SELECT
 82385 postgres  20   0 65.7g 310m 306m R    73  0.0   0:03.03 postgres: user1 db 0.0.0.6(60876) SELECT
 82520 postgres  20   0 65.7g 220m 215m R    73  0.0   0:02.00 postgres: user1 db 0.0.0.3(44785) SELECT
 82676 postgres  20   0 65.7g 116m 111m R    73  0.0   0:00.90 postgres: user1 db 0.0.0.2(52531) SELECT
 18471 postgres  20   0 65.7g  73m  56m R    69  0.0   8:14.08 postgres: user1 db 0.0.0.6(46144) SELECT
 43890 postgres  20   0 65.7g  76m  56m R    69  0.0   5:04.46 postgres: user1 db 0.0.0.3(36697) SELECT
 46130 postgres  20   0 65.7g  70m  57m R    69  0.0   4:46.56 postgres: user1 db 0.0.0.4(41871) SELECT
 55604 postgres  20   0 65.7g  81m  57m R    69  0.0   3:27.67 postgres: user1 db 0.0.0.3(39292) SELECT
 59139 postgres  20   0 65.7g  81m  57m R    69  0.0   3:01.18 postgres: user1 db 0.0.0.2(47670) SELECT
 63523 postgres  20   0 65.7g  80m  56m R    69  0.0   2:28.04 postgres: user1 db 0.0.0.2(48680) SELECT
 81707 postgres  20   0 65.7g 528m 515m S    69  0.0   0:08.44 postgres: user1 db 0.0.0.6(60737) SELECT
 81830 postgres  20   0 65.7g 523m 510m R    69  0.0   0:07.60 postgres: user1 db 0.0.0.4(49707) SELECT
 81932 postgres  20   0 65.7g 528m 515m R    69  0.0   0:06.65 postgres: user1 db 0.0.0.2(52352) SELECT
 81950 postgres  20   0 65.7g 528m 515m R    69  0.0   0:05.92 postgres: user1 db 0.0.0.6(60783) SELECT
 81973 postgres  20   0 65.7g 522m 510m R    69  0.0   0:06.18 postgres: user1 db 0.0.0.6(60789) SELECT
 82193 postgres  20   0 65.7g 487m 479m R    69  0.0   0:04.61 postgres: user1 db 0.0.0.2(52415) SELECT
 82358 postgres  20   0 65.7g 299m 295m R    69  0.0   0:03.11 postgres: user1 db 0.0.0.2(52453) SELECT
 82372 postgres  20   0 65.7g 318m 313m R    69  0.0   0:03.22 postgres: user1 db 0.0.0.4(49827) SELECT
 82381 postgres  20   0 65.7g 331m 326m R    69  0.0   0:03.30 postgres: user1 db 0.0.0.3(44757) SELECT
 82404 postgres  20   0 65.7g 294m 289m R    69  0.0   0:02.86 postgres: user1 db 0.0.0.3(44761) SELECT
 82415 postgres  20   0 65.7g 270m 266m R    69  0.0   0:02.80 postgres: user1 db 0.0.0.3(44767) SELECT
 82521 postgres  20   0 65.7g 209m 205m R    69  0.0   0:02.00 postgres: user1 db 0.0.0.3(44786) SELECT
 82526 postgres  20   0 65.7g  35m  29m R    69  0.0   0:01.20 postgres: user1 db 0.0.0.6(60906) SELECT
 82550 postgres  20   0 65.7g 188m 184m R    69  0.0   0:01.72 postgres: user1 db 0.0.0.4(49870) SELECT
 82587 postgres  20   0 65.7g 183m 178m R    69  0.0   0:01.64 postgres: user1 db 0.0.0.4(49882) SELECT
 82683 postgres  20   0 65.7g  97m  93m R    69  0.0   0:00.77 postgres: user1 db 0.0.0.4(49899) SELECT
 82685 postgres  20   0 65.7g 103m  99m R    69  0.0   0:00.84 postgres: user1 db 0.0.0.2(52532) SELECT
 82687 postgres  20   0 65.7g 109m 104m R    69  0.0   0:00.85 postgres: user1 db 0.0.0.3(44809) SELECT
 82712 postgres  20   0 65.7g  68m  64m R    69  0.0   0:00.55 postgres: user1 db 0.0.0.3(44814) SELECT
 82715 postgres  20   0 65.7g  75m  70m R    69  0.0   0:00.58 postgres: user1 db 0.0.0.4(49905) SELECT
 19548 postgres  20   0 65.7g  79m  56m R    65  0.0   8:02.44 postgres: user1 db 0.0.0.2(37887) SELECT
 36714 postgres  20   0 65.7g  80m  56m R    65  0.0   5:56.08 postgres: user1 db 0.0.0.3(35177) SELECT
 43599 postgres  20   0 65.7g  80m  56m R    65  0.0   5:05.03 postgres: user1 db 0.0.0.3(36638) SELECT

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@gmail.com] 
Sent: Wednesday, October 21, 2015 12:50 PM
To: Pavel Stehule
Cc: Jamie Koceniak; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Recursive query performance issue

On Wed, Oct 21, 2015 at 2:45 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2015-10-21 21:32 GMT+02:00 Jamie Koceniak <jkoceniak@mediamath.com>:
>>
>> adama_prod=# SHOW shared_buffers;
>>
>> shared_buffers
>>
>> ----------------
>>
>> 64GB
>
>
> can you try to increase shared buffers to 200GB and decrease effective 
> cache size to 180GB? If it is possibly - I am not sure, if this 
> setting is good fro production usage, but the result can be 
> interesting for bottleneck identification.

we need to see a snapshot from
*) top
*) perf top

merlin