On Fri, Jul 8, 2016 at 11:26 AM, Melvin Davidson <melvin6...@gmail.com> wrote:
> > > On Fri, Jul 8, 2016 at 11:49 AM, <amatv...@bitec.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