Re: OSX & Performance

On Aug 29, 2005, at 1:57 PM, Tom Lane wrote:
>
> You must have CFLAGS set to empty in your build environment, because
> configure will certainly default to -O2 if not overridden. It works
> fine for me on OS X. Maybe you want to trace through the configure
> script and see why it's doing something else?
>

/me hangs head in shame.

Yes. I'd been futzing with various settings and had CFLAGS set to
empty instead of cleared out. 8.0.3 and -snapshot (8/29) both seem
to now compile with -O2

Anyway, I tried putting together a nice self-data-producing test case
but that didn't cause the bug. So I'm trying to get this dump as
small as possible (I'll email you a url later).

To tide things over, here's the gprof (and shark) output for my query
of doom.

linux box:

6.36 0.41 0.41 240694 0.00 0.00 _bt_compare
5.97 0.79 0.38 907242 0.00 0.00 AllocSetAlloc
4.55 1.07 0.29 135008 0.00 0.00 hash_any
4.16 1.34 0.27 185684 0.00 0.00
MemoryContextAllocZeroAlig
ned
3.30 1.55 0.21 39152 0.01 0.01 localsub
2.98 1.74 0.19 1213172 0.00 0.00 AllocSetFreeIndex
2.83 1.92 0.18 52695 0.00 0.00 nocachegetattr
2.75 2.10 0.17 134775 0.00 0.00 hash_search
2.51 2.25 0.16 47646 0.00 0.01
StrategyBufferLookup
2.28 2.40 0.14 71990 0.00 0.00 fmgr_isbuiltin
2.20 2.54 0.14 33209 0.00 0.00 _bt_moveright
1.88 2.66 0.12 78864 0.00 0.00 comparetup_heap
1.57 2.76 0.10 63485 0.00 0.00 SearchCatCache
1.41 2.85 0.09 39152 0.00 0.00 timesub
1.26 2.93 0.08 325246 0.00 0.00 tas
1.26 3.01 0.08 305883 0.00 0.00 AllocSetFree
1.26 3.09 0.08 162622 0.00 0.00 LWLockAcquire

and on osx: (self, total, library, func)

29.0% 29.0% postmaster _bt_checkkeys
15.6% 15.6% postmaster FunctionCall2
10.4% 10.4% libSystem.B.dylib __isnand
9.5% 9.5% postmaster timestamp_cmp_internal
9.3% 9.3% postmaster _bt_step
5.3% 5.3% postmaster timestamp_le
4.9% 4.9% postmaster _bt_next
3.6% 3.6% postmaster dyld_stub___isnand
3.1% 3.1% postmaster timestamp_gt
1.9% 1.9% postmaster int4eq
1.3% 1.3% postmaster BufferGetBlockNumber
0.6% 0.6% postmaster LWLockAcquire
0.5% 0.5% postmaster LWLockRelease
0.4% 0.4% postmaster hash_search

On my failed simulated attempt here's what things looked liek (the
data should have been relatively similar).

linux:

5.39 0.28 0.28 852086 0.00 0.00 AllocSetAlloc
4.90 0.53 0.25 130165 0.00 0.00 hash_any
4.12 0.73 0.21 214061 0.00 0.00 _bt_compare
4.12 0.94 0.21 39152 0.01 0.01 localsub
4.02 1.15 0.20 160487 0.00 0.00
MemoryContextAllocZeroAlig
ned
3.24 1.31 0.17 1157316 0.00 0.00 AllocSetFreeIndex
3.14 1.48 0.16 64375 0.00 0.00 fmgr_isbuiltin
2.55 1.60 0.13 56142 0.00 0.00 SearchCatCache
2.35 1.73 0.12 130076 0.00 0.00 hash_search
1.76 1.81 0.09 39152 0.00 0.00 timesub
1.67 1.90 0.09 221469 0.00 0.00
timestamp_cmp_internal
1.67 1.99 0.09 56069 0.00 0.00
MemoryContextCreate
1.57 2.06 0.08 145787 0.00 0.00 LWLockRelease
1.37 2.13 0.07 289119 0.00 0.00 pfree
1.37 2.21 0.07 8002 0.01 0.02
ExecMakeFunctionResult
1.37 2.27 0.07 8000 0.01 0.22 ExecInitIndexScan
1.18 2.33 0.06 291574 0.00 0.00 tas

and on osx: (which runs very fast, usually a couple hundred ms faster
than the linux box)

5.9% 5.9% postmaster LWLockAcquire
5.2% 5.2% postmaster AllocSetAlloc
4.9% 4.9% postmaster LWLockRelease
3.9% 3.9% postmaster hash_any
3.6% 3.6% postmaster _bt_compare
2.9% 2.9% postmaster hash_search
2.6% 2.6% postmaster MemoryContextAllocZeroAligned
2.6% 2.6% postmaster ExecInitExpr
2.0% 2.0% mach_kernel ml_set_interrupts_enabled
2.0% 2.0% postmaster fmgr_info_cxt_security
2.0% 2.0% postmaster AllocSetFree
1.6% 1.6% postmaster MemoryContextAlloc
1.6% 1.6% postmaster FunctionCall2
1.6% 1.6% postmaster AllocSetDelete
1.6% 1.6% libSystem.B.dylib __isnand

which to me anyway, looks like basically the same profile.
So there must be something about the exact nature of hte data that is
kicking it in the nuts.

I tried making a copy of hte table using select into, I get the same
performace. Clustered on the index.. same hting.

The table is a timestamp (no tz), 2 ints and 4 doubles. The index is
on (timestamp, int1)

As I said before, I'll send a url along to the dump once it has
dumped and I get it somewhere good (unless I get my test data
generator to invoke this problem). I could also get you access to
this machine, but be warned gprof on tiger is pretty useless from
what I've seen.

--
Jeff Trout <jeff(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/