Re: Need some help on Performance 9.0.4

got the output from depesz and this the top on the system. thanks for the
help ..

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

Hash Join (cost=343.930..63924.990 rows=1272 width=33) (actual
time=1110.544..10759.808 rows=729 loops=1)
Hash Cond: ((golf_papa.tango)::text = (romeo_quebec.tango)::text)
-> Hash Join (cost=342.550..63896.580 rows=1272 width=19) (actual
time=1110.409..10755.737 rows=729 loops=1)
Hash Cond: (golf_papa.bravo = india_quebec.bravo)
-> Hash Join (cost=243.350..63779.560 rows=2975 width=26)
(actual time=1109.516..10752.258 rows=731 loops=1)
Hash Cond: (golf_papa.india_golf = uniform_papa.india_golf)
-> Nested Loop (cost=0.000..63369.970 rows=36399
width=33) (actual time=0.196..10737.785 rows=11204 loops=1)
-> Nested Loop (cost=0.000..1639.760 rows=2751
width=15) (actual time=0.093..26.857 rows=3702 loops=1)
Join Filter: (yankee.xray = charlie.xray)
-> Seq Scan on delta charlie
(cost=0.000..1.040 rows=1 width=14) (actual time=0.042..0.049 rows=1
loops=1)
Filter: (romeo_lima = 10::numeric)
-> Seq Scan on victor yankee
(cost=0.000..1471.100 rows=13410 width=15) (actual time=0.015..17.272
rows=13410 loops=1)
-> Index Scan using romeo_four on foxtrot
golf_papa (cost=0.000..22.190 rows=20 width=34) (actual
time=0.686..2.889 rows=3 loops=3702)
Index Cond: (golf_papa.golf_foxtrot =
yankee.lima_echo)
Filter: (((golf_papa.tango)::text = ANY
('six_tango'::text[])) OR (((golf_papa.tango)::text = 'alpha'::text)
AND (lima_sierra((golf_papa.whiskey_foxtrot)::time
-> Hash (cost=240.310..240.310 rows=243 width=7)
(actual time=4.068..4.068 rows=243 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on six_november uniform_papa
(cost=0.000..240.310 rows=243 width=7) (actual time=0.044..3.817
rows=243 loops=1)
Filter: ((whiskey_whiskey)::text = 'echo'::text)
-> Hash (cost=98.640..98.640 rows=45 width=29) (actual
time=0.675..0.675 rows=45 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 3kB
-> Nested Loop (cost=0.000..98.640 rows=45 width=29)
(actual time=0.154..0.597 rows=45 loops=1)
-> Index Scan using whiskey_five on four
two_yankee (cost=0.000..43.040 rows=45 width=22) (actual
time=0.074..0.148 rows=45 loops=1)
Index Cond: (lima_echo = 10::numeric)
-> Index Scan using oscar on two_golf
india_quebec (cost=0.000..1.220 rows=1 width=7) (actual
time=0.007..0.008 rows=1 loops=45)
Index Cond: (india_quebec.bravo = two_yankee.bravo)
-> Hash (cost=1.170..1.170 rows=17 width=19) (actual
time=0.026..0.026 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on uniform_victor romeo_quebec
(cost=0.000..1.170 rows=17 width=19) (actual time=0.004..0.012 rows=17
loops=1)

On Sun, Oct 13, 2013 at 8:17 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:

> Hi,
>
> On 13.10.2013 17:43, akp geek wrote:
> > Sorry all not posting clear. I posted our postresql.conf file. What
> > we are having is very slow response from the database.
> >
> > would like to get some feedback about postgresql.conf file
> > parameters that I posted are causing the issue for performance of the
> > DB.
> >
> > Our database is about 60GB and performance is very bad.
>
> The postgresql.conf seems fine - at least I haven't noticed anything
> obviously wrong there. But we know almost nothing about your HW (not
> even what kind of CPU / how many cores, etc.) or how you're using it.
>
> For example you haven't even posted a complete top output, so we have no
> idea what else is running on the box. OTOH the box seems to be rather
> idle - there's plenty of CPU, RAM and the disks are rather idle.
>
> Why exactly do you think it's the database that's slow?
>
> We can't really help you unless you show us what exactly you consider
> slow. The best thing you can probably do is to show us a slow query
> along with it's EXPLAIN ANALYZE output (use explain.depesz.com to post
> it).
>
> And it'd be nice to know the history around this - was it fast before,
> did it get slow suddenly or over a longer period of time?
>
> regards
> Tomas
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>