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?
Date: Sat, 1 Oct 2005 23:56:07 +0200
Message-ID: <20051001215602.GG13830@svana.org> (raw)
In-Reply-To: <18329659.1128188552696.JavaMail.root@elwamui-polski.atl.sa.earthlink.net>
References: <18329659.1128188552696.JavaMail.root@elwamui-polski.atl.sa.earthlink.net>
[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
time seconds seconds calls s/call s/call name
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 inlineApplySortFunction
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
--
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.
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kleptog@svana.org, rjpeace@earthlink.net, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] A Better External Sort?
In-Reply-To: <20051001215602.GG13830@svana.org>
* 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