Re: Recursive query performance issue

Hi

this extremely high load looks like different issue - maybe spinlock issue
or virtual memory issue.

Probably you need some low level debug tools like perf or dtrace :(

http://www.postgresql.org/message-id/20131206095629.GI7814@awork2.anarazel.de

Has you last PostgreSQL upgrade?

result of "perf top" when this issue is active is really requested.

Regards

Pavel

2015-10-23 19:45 GMT+02:00 Jamie Koceniak <jkoceniak(at)mediamath(dot)com>:

> 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(at)gmail(dot)com]
> Sent: Wednesday, October 21, 2015 12:50 PM
> To: Pavel Stehule
> Cc: Jamie Koceniak; pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Recursive query performance issue
>
> On Wed, Oct 21, 2015 at 2:45 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > 2015-10-21 21:32 GMT+02:00 Jamie Koceniak <jkoceniak(at)mediamath(dot)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
>