Re: [PERFORM] A Better External Sort?

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Ron Peacetree <rjpeace(at)earthlink(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [PERFORM] A Better External Sort?
Date: 2005-10-01 21:56:07
Message-ID: 20051001215602.GG13830@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

[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(at)svana(dot)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.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2005-10-02 03:26:07 Re: [PERFORM] A Better External Sort?
Previous Message Joshua D. Drake 2005-10-01 21:54:27 8.1beta2 pg_dumpall inconsistencies

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2005-10-02 03:26:07 Re: [PERFORM] A Better External Sort?
Previous Message Tom Lane 2005-10-01 21:15:25 Re: [HACKERS] Query in SQL statement