Re: [HACKERS] Postgres Speed or lack thereof

Tom Lane wrote:
>
> Having an idle hour this evening, I thought it'd be interesting to build
> a backend with profiling enabled, so as to confirm or deny the above
> guess. It seems that indeed a lot of time is being wasted, but where
> it's being wasted might surprise you!
...
> In other words, we're spending a third of our time mallocing and freeing
> memory. A tad high, what?
>
> Actually, it's worse than that, because AllocSetAlloc,
> PortalHeapMemoryAlloc, AllocSetFree, and all of the OrderedElemXXX
> routines represent our own bookkeeping layer atop malloc/free.
> That's another 18.66 seconds spent in these top routines, which means
> that we are real close to expending half the backend's runtime on
> memory bookkeeping. This needs work.
>

Yes, it's suprizing!

I added some debug code to palloc/pfree and it shows that for
INSERT:

1. 80% of allocations are made for <= 32 bytes.
2. pfree is used for 25% of them only (others are freed
after statement/transaction is done).

Note that our mmgr adds 16 bytes to each allocation
(+ some bytes in malloc) - a great overhead, yes?

I added code to allocate a few big (16K-64K) blocks
of memory for these small allocations to speed up
palloc by skiping AllocSetAlloc/malloc. New code
don't free allocated memory (to make bookkeeping fast)
but keeping in mind 2. above and memory overhead
it seems as appropriate thing to do. These code also
speed up freeing when statement/transaction is done,
because of only a few blocks have to be freed now.

I did 5000 INSERTS (into tables with 3 ints and 33 ints)
with BEGIN/END, -F and -B 512 (I run postgres directly,
without postmaster). User times:

old new
-----------------------------------------
table with 3 ints 9.7 sec 7.6 sec
table with 33 ints 59.5 sec 39.9 sec

So, new code 20%-30% faster. Process sizes are the same.

Tom, could you run new code under profiling?

There are still some things to do:

1. SELECT/UPDATE/DELETE often palloc/pfree tuples
(sizes are > 32 bytes), but pfree now requires
additional lookup to see is memory allocated by
AllocSetAlloc or new code. We can avoid this.

2. Index scans palloc/pfree IndexResult for each
tuple returned by index. This one was annoying me
for long time. IndexResult should be part of
IndexScan structure...

3. psort uses leftist structure (16 bytes) when
disk is used for sorting. Obviously, big block
allocation should be used by lselect code itself.

4. Actually, new mode shouldn't be used by Executor
in some cases.

I'll address this in a few days...

BTW, look at memutils.h: new code is like "tunable"
mode described there.

>
> In other words, essentially *all* of the CPU time spent in
> CommitTransaction is spent freeing memory. That's probably why
> ganging the transactions doesn't help --- it's the same number of
> memory blocks getting allocated and freed.

It shows that we should get rid of system malloc/free and do
all things in mmgr itself - this would allow us much faster
free memory contexts at statement/transaction end.

Vadim