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

From Merlin Moncure
Subject Re: Recursive query performance issue
Date
Msg-id CAHyXU0x7L1HAuhDCMWH4+Ty71Q2v6uWAUq1a7ysH764tm7NpmA@mail.gmail.com
Whole thread Raw
In response to Re: Recursive query performance issue  (Jamie Koceniak <jkoceniak@mediamath.com>)
Responses Re: Recursive query performance issue  (Jamie Koceniak <jkoceniak@mediamath.com>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Jamie Koceniak
Date:
Subject: Re: Recursive query performance issue
Next
From: David Osborne
Date:
Subject: Re: GroupAggregate and Integer Arrays