Re: Need some help on Performance 9.0.4

On 17.10.2013 20:56, akp geek wrote:
> got the output from depesz and this the top on the system. thanks for
> the help ..

From depesz? That site works differently - you enter the explain plan
into the form, it does some formatting/highlighting and you're supposed
to submit the link to that page. E.g. http://explain.depesz.com/s/JwTB
is for your query.

However it seems you've done some anonymization on the query, e.g.
replacing the table/index/function names and string literals with some
random words.

Anyway, the plan shows the estimates are reasonably accurate, but the
index scan on romeo_four consumes ~99% of the query duration. Not sure
if it can be improved, because we don't know the original query and the
anonymization made it rather annoying to deal with.

>
> Regards
>
> load averages: 30.3, 28.8, 27.8; up
> 680+00:51:09
> 18:24:44
> 156 processes: 123 sleeping, 33 on cpu
> CPU states: 49.5% idle, 50.0% user, 0.4% kernel, 0.0% iowait, 0.0% swap
> Memory: 64G phys mem, 19G free mem, 63G total swap, 63G free swap
>
> PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
> 7965 postgres 1 20 0 3178M 3171M cpu/7 78:21 1.58% postgres
> 20638 postgres 1 20 0 3181M 3175M cpu/28 187:01 1.58% postgres
> 22819 postgres 1 30 0 3181M 3175M cpu/33 264:06 1.57% postgres
> 3789 postgres 1 20 0 3183M 3176M cpu/9 626:11 1.57% postgres
> 10375 postgres 1 30 0 3182M 3175M cpu/50 293:55 1.57% postgres
> 27335 postgres 1 30 0 3175M 3169M cpu/29 27:27 1.57% postgres
> 7959 postgres 1 30 0 3178M 3171M cpu/41 104:07 1.57% postgres
> 8156 postgres 1 30 0 3180M 3173M cpu/43 124:18 1.56% postgres
> 20640 postgres 1 0 0 3182M 3176M cpu/58 207:49 1.56% postgres
> 10584 postgres 1 30 0 3179M 3173M cpu/35 76:32 1.56% postgres
> 13984 postgres 1 30 0 3181M 3174M cpu/30 207:04 1.56% postgres
> 3575 postgres 1 20 0 3283M 3261M cpu/19 1:48 1.56% postgres
> 7964 postgres 1 40 0 3177M 3170M cpu/62 82:56 1.56% postgres
> 1299 postgres 1 40 0 3166M 3161M cpu/52 5:11 1.56% postgres
> 27692 postgres 1 0 0 3181M 3174M cpu/46 260:58 1.56% postgres

Once again, this top output is incomplete - all it lists are postgres
processes (and it's unclear what they're doing), but there are certainly
some other processes running. Moreover, those 15 processes account for
only ~25% of CPU, but there's almost 50% CPU utilized. So where's the rest?

Tomas