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

От: Andrus
Тема: Re: Hash join on int takes 8..114 seconds
Дата: ,
Msg-id: 1945D3A75A2848BB8409CB7CDA5F3665@andrusnotebook
(см: обсуждение, исходный текст)
Ответ на: Re: Hash join on int takes 8..114 seconds  (Richard Huxton)
Список: pgsql-performance

Скрыть дерево обсуждения

Hash join on int takes 8..114 seconds  ("Andrus", )
 Re: Hash join on int takes 8..114 seconds  (PFC, )
  Re: Hash join on int takes 8..114 seconds  ("Andrus", )
 Re: Hash join on int takes 8..114 seconds  (Richard Huxton, )
  Re: Hash join on int takes 8..114 seconds  ("Andrus", )
   Re: Hash join on int takes 8..114 seconds  (Richard Huxton, )
    Re: Hash join on int takes 8..114 seconds  ("Andrus", )
   Re: Hash join on int takes 8..114 seconds  (PFC, )
    Re: Hash join on int takes 8..114 seconds  ("Andrus", )
     Re: Hash join on int takes 8..114 seconds  (, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
       Re: Hash join on int takes 8..114 seconds  (Alan Hodgson, )
       Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
     Re: Hash join on int takes 8..114 seconds  (Richard Huxton, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
        Re: Hash join on int takes 8..114 seconds  ("Andrus", )
         Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera, )
          Re: Hash join on int takes 8..114 seconds  ("Andrus", )
           Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera, )
            Re: Hash join on int takes 8..114 seconds  ("Andrus", )
             Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera, )
           Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (Scott Carey, )
     Re: Hash join on int takes 8..114 seconds  (PFC, )
      Re: Hash join on int takes 8..114 seconds  (Tom Lane, )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
        Re: Hash join on int takes 8..114 seconds  ("Andrus", )
         Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
          Re: Hash join on int takes 8..114 seconds  ("Andrus", )
           Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
         Re: Hash join on int takes 8..114 seconds  (Scott Carey, )
          Re: Hash join on int takes 8..114 seconds  ("Andrus", )
           Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
            Re: Hash join on int takes 8..114 seconds  ("Andrus", )
             Re: Hash join on int takes 8..114 seconds  (, )
              Re: Hash join on int takes 8..114 seconds  ("Andrus", )

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 по дате сообщения:

От: Alvaro Herrera
Дата:
Сообщение: Re: Hash join on int takes 8..114 seconds
От: Tom Lane
Дата:
Сообщение: Re: Hash join on int takes 8..114 seconds