pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrus <kobruleht2@hot.ee>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hash join on int takes 8..114 seconds
Date: Thu, 20 Nov 2008 13:14:34 +0200
Message-ID: <0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook> (raw)
In-Reply-To: <49253FE1.3070706@archonet.com>
References: <gg1sv3$27fr$1@news.hub.org>
	<49253FE1.3070706@archonet.com>

Richard,

> At a quick glance, the plans look the same to me. The overall costs are
> certainly identical. That means whatever is affecting the query times it
> isn't the query plan.
>
> So - what other activity is happening on this machine? Either other
> queries are taking up noticeable resources, or some other process is (it
> might be disk activity from checkpointing, logging some other 
> application).

Thank you.
This is dedicated server running only PostgreSql which serves approx 6 point 
of sales at this time.

Maybe those other clients make queries which invalidate lot of data loaded 
into server cache.
In next time server must read it again from disk which causes those 
perfomance differences.

top output is currently:

top - 13:13:10 up 22 days, 18:25,  1 user,  load average: 0.19, 0.12, 0.19
Tasks:  53 total,   2 running,  51 sleeping,   0 stopped,   0 zombie
Cpu(s): 13.7% us,  2.0% sy,  0.0% ni, 78.3% id,  6.0% wa,  0.0% hi,  0.0% si
Mem:   2075828k total,  2022808k used,    53020k free,        0k buffers
Swap:  3911816k total,       88k used,  3911728k free,  1908536k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 5382 postgres  15   0  144m  43m  40m S 15.0  2.2   0:00.45 postmaster
 5358 postgres  15   0  152m  87m  75m S  0.3  4.3   0:00.97 postmaster
    1 root      16   0  1480  508  444 S  0.0  0.0   0:01.35 init
    2 root      34  19     0    0    0 S  0.0  0.0   0:00.01 ksoftirqd/0
    3 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 events/0
    4 root      10  -5     0    0    0 S  0.0  0.0   0:00.42 khelper
    5 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kthread
    7 root      10  -5     0    0    0 S  0.0  0.0   2:03.91 kblockd/0
    8 root      20  -5     0    0    0 S  0.0  0.0   0:00.00 kacpid
  115 root      13  -5     0    0    0 S  0.0  0.0   0:00.00 aio/0
  114 root      15   0     0    0    0 S  0.0  0.0   8:49.67 kswapd0
  116 root      10  -5     0    0    0 S  0.0  0.0   0:10.32 xfslogd/0
  117 root      10  -5     0    0    0 S  0.0  0.0   0:39.96 xfsdatad/0
  706 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kseriod
  723 root      13  -5     0    0    0 S  0.0  0.0   0:00.00 kpsmoused
  738 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 ata/0
  740 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 scsi_eh_0
  741 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 scsi_eh_1
  742 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 scsi_eh_2
  743 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 scsi_eh_3
  762 root      10  -5     0    0    0 S  0.0  0.0   0:17.54 xfsbufd
  763 root      10  -5     0    0    0 S  0.0  0.0   0:00.68 xfssyncd
  963 root      16  -4  1712  528  336 S  0.0  0.0   0:00.24 udevd
 6677 root      15   0  1728  572  400 S  0.0  0.0   0:04.99 syslog-ng
 7128 postgres  16   0  140m  10m 9900 S  0.0  0.5   0:05.60 postmaster

in few seconds later:

top - 13:14:01 up 22 days, 18:26,  1 user,  load average: 1.72, 0.53, 0.32
Tasks:  52 total,   2 running,  50 sleeping,   0 stopped,   0 zombie
Cpu(s):  5.3% us,  3.0% sy,  0.0% ni,  0.0% id, 91.0% wa,  0.0% hi,  0.7% si
Mem:   2075828k total,  2022692k used,    53136k free,        0k buffers
Swap:  3911816k total,       88k used,  3911728k free,  1905028k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 1179 postgres  18   0  155m 136m 122m D  6.7  6.7   1:32.52 postmaster
 4748 postgres  15   0  145m 126m 122m D  1.3  6.2   0:14.38 postmaster
 5358 postgres  16   0  160m  98m  81m D  0.7  4.9   0:01.21 postmaster
    1 root      16   0  1480  508  444 S  0.0  0.0   0:01.35 init
    2 root      34  19     0    0    0 S  0.0  0.0   0:00.01 ksoftirqd/0
    3 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 events/0
    4 root      10  -5     0    0    0 S  0.0  0.0   0:00.42 khelper
    5 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kthread
    7 root      10  -5     0    0    0 S  0.0  0.0   2:03.97 kblockd/0
    8 root      20  -5     0    0    0 S  0.0  0.0   0:00.00 kacpid
  115 root      13  -5     0    0    0 S  0.0  0.0   0:00.00 aio/0
  114 root      15   0     0    0    0 S  0.0  0.0   8:49.79 kswapd0
  116 root      10  -5     0    0    0 S  0.0  0.0   0:10.32 xfslogd/0
  117 root      10  -5     0    0    0 S  0.0  0.0   0:39.96 xfsdatad/0
  706 root      10  -5     0    0    0 S  0.0  0.0   0:00.00 kseriod
  723 root      13  -5     0    0    0 S  0.0  0.0   0:00.00 kpsmoused
  738 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 ata/0
  740 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 scsi_eh_0
  741 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 scsi_eh_1
  742 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 scsi_eh_2
  743 root      11  -5     0    0    0 S  0.0  0.0   0:00.00 scsi_eh_3
  762 root      10  -5     0    0    0 S  0.0  0.0   0:17.54 xfsbufd
  763 root      10  -5     0    0    0 S  0.0  0.0   0:00.68 xfssyncd
  963 root      16  -4  1712  528  336 S  0.0  0.0   0:00.24 udevd
 6677 root      15   0  1728  572  400 S  0.0  0.0   0:04.99 syslog-ng


Andrus. 




view thread (41+ messages)  latest in thread

Message-ID: <0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook>
Permalink:  ../0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook/
Also on:    postgresql.org/message-id/0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: kobruleht2@hot.ee, dev@archonet.com
  Subject: Re: Hash join on int takes 8..114 seconds
  In-Reply-To: <0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook>
  References: <gg1sv3$27fr$1@news.hub.org> <49253FE1.3070706@archonet.com> <0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox