Re: Memory usage per session

[John McKown <john.archie.mckown@xxxxxxxxx>]

On Fri, Jul 8, 2016 at 11:26 AM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

On Fri, Jul 8, 2016 at 11:49 AM, <AMatveev@xxxxxxxx> 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