Re: Hash join on int takes 8..114 seconds - Mailing list pgsql-performance

From Andrus
Subject Re: Hash join on int takes 8..114 seconds
Date
Msg-id 1945D3A75A2848BB8409CB7CDA5F3665@andrusnotebook
Whole thread Raw
In response to Re: Hash join on int takes 8..114 seconds  (Richard Huxton <dev@archonet.com>)
List 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.


pgsql-performance by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: Performance and IN clauses
Next
From: "Andrus"
Date:
Subject: Re: Hash join on int takes 8..114 seconds