Re: [HACKERS] Postgres Speed or lack thereof - Mailing list pgsql-hackers

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> This from January 24, 1999.  Is it still relivant, and something that
> should be on the TODO list?

Jan's rewrite of alloc sets has probably taken care of the speed issue.
Of course we still need to look at how memory allocation is being
*used*, in order to fix the within-statement memory leak problems.
But that's not what I was complaining about here.
        regards, tom lane

>> 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@candle.pha.pa.us            |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026