Date: Sat, 1 Oct 2005 23:56:07 +0200
From: Martijn van Oosterhout <kleptog@svana.org>
To: Ron Peacetree <rjpeace@earthlink.net>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] A Better External Sort?
Message-ID: <20051001215602.GG13830@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
References: 
 <18329659.1128188552696.JavaMail.root@elwamui-polski.atl.sa.earthlink.net>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="QWpDgw58+k1mSFBj"
Content-Disposition: inline
In-Reply-To: 
 <18329659.1128188552696.JavaMail.root@elwamui-polski.atl.sa.earthlink.net>
User-Agent: Mutt/1.3.28i


--QWpDgw58+k1mSFBj
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

[removed -performance, not subscribed]

On Sat, Oct 01, 2005 at 01:42:32PM -0400, Ron Peacetree wrote:
> You have not said anything about what HW, OS version, and pg version
> used here, but even at that can't you see that something Smells Wrong?

Somewhat old machine running 7.3 on Linux 2.4. Not exactly speed
daemons but it's still true that the whole process would be CPU bound
*even* if the O/S could idle while it's waiting. PostgreSQL used a
*whole CPU* which is its limit. My point is that trying to reduce I/O
by increasing CPU usage is not going to be benficial, we need CPU usage
down also.

Anyway, to bring some real info I just profiled PostgreSQL 8.1beta
doing an index create on a 2960296 row table (3 columns, table size
317MB).

The number 1 bottleneck with 41% of user time is comparetup_index. It
was called 95,369,361 times (about 2*ln(N)*N). It used 3 tapes. Another
15% of time went to tuplesort_heap_siftup.

The thing is, I can't see anything in comparetup_index() that could
take much time. The actual comparisons are accounted elsewhere
(inlineApplySortFunction) which amounted to <10% of total time. Since
nocache_index_getattr doesn't feature I can't imagine index_getattr
being a big bottleneck. Any ideas what's going on here?

Other interesting features:
- ~4 memory allocations per tuple, nearly all of which were explicitly
freed
- Things I though would be expensive, like: heapgettup and
myFunctionCall2 didn't really count for much.

Have a nice weekend,

  %   cumulative   self              self     total          =20
 time   seconds   seconds    calls   s/call   s/call  name   =20
 43.63    277.81   277.81 95370055     0.00     0.00  comparetup_index
 16.24    381.24   103.43  5920592     0.00     0.00  tuplesort_heap_siftup
  3.76    405.17    23.93 95370055     0.00     0.00  inlineApplySortFuncti=
on
  3.18    425.42    20.26 95370056     0.00     0.00  btint4cmp
  2.82    443.37    17.95 11856219     0.00     0.00  AllocSetAlloc
  2.52    459.44    16.07 95370055     0.00     0.00  myFunctionCall2
  1.71    470.35    10.91  2960305     0.00     0.00  heapgettup
  1.26    478.38     8.03 11841204     0.00     0.00  GetMemoryChunkSpace
  1.14    485.67     7.29  5920592     0.00     0.00  tuplesort_heap_insert
  1.11    492.71     7.04  2960310     0.00     0.00  index_form_tuple
  1.09    499.67     6.96 11855105     0.00     0.00  AllocSetFree
  0.97    505.83     6.17 23711355     0.00     0.00  AllocSetFreeIndex
  0.84    511.19     5.36  5920596     0.00     0.00  LogicalTapeWrite
  0.84    516.51     5.33  2960314     0.00     0.00  slot_deform_tuple
--=20
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

--QWpDgw58+k1mSFBj
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDPwXxIB7bNG8LQkwRAmA3AJ0SJrScvMeXv99OsRZivZcxojUfhgCgiLzQ
adITHNOK9MrxSou0G5gNFko=
=PdSb
-----END PGP SIGNATURE-----

--QWpDgw58+k1mSFBj--