Re: Memory usage per session

On Fri, Jul 8, 2016 at 11:26 AM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

>
>
> On Fri, Jul 8, 2016 at 11:49 AM, <AMatveev(at)bitec(dot)ru> wrote:
>
>> Hi
>>
>>
>> >> Oracle: about 5M
>> >> postgreSql: about 160М
>>
>>
>>
>> >The almost session memory is used for catalog caches. So you should to
>> have big catalog and long living sessions.
>>
>> >What do you do exactly?
>>
>> I've generate test code that emulates instruction tree size for our
>> production code.
>> This test shows:
>> -What is the size of instruction tree for our typical BP
>> it's greater than 300M for each session
>> -How often do PostgreSql parse the text
>> When postgres clean cache, so much often
>>
>> So Oracle is much better in this case.
>> It's very difficult really estimate in such case, to buy Oracle or to by
>> hardware.
>>
>>
>
> *My questions:*>What is the actual O/S that PostgreSQL is installed on?
> >How much total memory is on the server?
> >I would be very curious about the values you have specified in
> postgresql.conf?
> > Also, what is the exact version of PostgreSQL you are using?
> >What is the total time to complete the test for all 3 DB's?
> >The best I can tell is that with all the unknowns, you are comparing
> apples to oranges.
>
> *Your answers:*
> >There is real problem for us.
> >The PL/pgSQL interpreter parses the function's source text and produces
> an internal binary instruction tree the first time the function is called
> (within each session)
>
>
> *Your answer is jibberish and has nothing to do with my questions.*
>
> *Have you even tuned the postgresql.conf?*
> *You cannot fairly compare PostgreSQL with any other database unless you
> first tune it's postgres.conf.*
>
> *Melvin Davidson*
>

I think the "problem" that he is having is fixable only by changing how
PostgreSQL itself works. His problem is a PL/pgSQL function which is 11K
lines in length. When invoked, this function is "compiled" into a large
tokenized parse tree. This parse tree is only usable in the session which
invoked the the function. Apparently this parse tree takes a lot of memory.
And "n" concurrent users of this, highly used, function will therefore
require "n" times as much memory because the parse tree is _not_
shareable. This is explained in:
​​
https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

​In previous posts, he implied that he is running on some version of
Windows by referencing the VC compiler. I am _guessing_ that the other DBs
mentioned: MSSQL and Oracle implement their server side programming
differently so that it takes less memory. Perhaps by allowing the "compiled
program" to be shared between session.

--
"Pessimism is a admirable quality in an engineer. Pessimistic people check
their work three times, because they're sure that something won't be right.
Optimistic people check once, trust in Solis-de to keep the ship safe, then
blow everyone up."
"I think you're mistaking the word optimistic for inept."
"They've got a similar ring to my ear."

From "Star Nomad" by Lindsay Buroker:

Maranatha! <><
John McKown