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.gi7...@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 <jkocen...@mediamath.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:mmonc...@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.steh...@gmail.com> > wrote: > > 2015-10-21 21:32 GMT+02:00 Jamie Koceniak <jkocen...@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 >
