Re: Hash join on int takes 8..114 seconds

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: "Richard Huxton" <dev(at)archonet(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Hash join on int takes 8..114 seconds
Date: 2008-11-21 13:49:24
Message-ID: 1945D3A75A2848BB8409CB7CDA5F3665@andrusnotebook
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Richard,

> In addition to "top" below, you'll probably find "vmstat 5" useful.

Thank you.
During this query run (65 sec), vmstat 5 shows big values in bi,cs and wa
columns:

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id
wa
1 1 88 51444 0 1854404 0 0 17 6 15 13 5 1 83
10
0 1 88 52140 0 1854304 0 0 3626 95 562 784 15 38 0
47
0 1 92 51608 0 1855668 0 0 14116 103 1382 2294 4 8 0
88
0 1 92 51620 0 1857256 0 1 15258 31 1210 1975 4 8 0
88
0 2 92 50448 0 1859188 0 0 13118 19 1227 1982 3 7 0
90
0 1 92 51272 0 1859088 0 0 7691 53 828 1284 14 4 0
82
0 1 92 52472 0 1858792 0 0 10691 9 758 968 3 7 0
89
0 2 92 51240 0 1858596 0 0 8204 7407 717 1063 2 5 0
93
0 1 92 51648 0 1860388 0 0 20622 121 1118 2229 12 9 0
79
2 1 92 50564 0 1861396 0 0 20994 3277 969 1681 15 8 0
76
1 0 92 52180 0 1860192 0 0 18542 36 802 1276 36 12 0
51
0 0 92 91872 0 1820948 0 1 15285 47 588 774 9 12 32
47
0 0 92 91904 0 1820948 0 0 0 4 251 18 0 0
100 0
0 0 92 92044 0 1820948 0 0 0 0 250 17 0 0
100 0
0 0 92 91668 0 1821156 0 0 27 93 272 66 5 0 92
3
0 0 92 91668 0 1821156 0 0 0 64 260 38 0 0
100 0
0 0 92 91636 0 1821156 0 0 0 226 277 71 0 0
100 0
0 0 92 91676 0 1821156 0 0 0 26 255 23 0 0
100 0

> Here you're stuck waiting for disks (91.0% wa). Check out vmstat and
> iostat to see what's happening.

typing iostat returns

bash: iostat: command not found

It seems that this is not installed in this gentoo.

Andrus.

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Andrus 2008-11-21 15:12:27 Re: Hash join on int takes 8..114 seconds
Previous Message Tomas Vondra 2008-11-21 01:09:36 Re: Performance and IN clauses