Re: [HACKERS] Postgres Speed or lack thereof

This from January 24, 1999. Is it still relivant, and something that
should be on the TODO list?

> 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
>
>

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026