Thread: [BUGS] BUG #14726: Memory consumption of PreparedStatement

The following bug has been logged on the website:

Bug reference:      14726
Logged by:          Daniel Migowski
Email address:      dmigowski@ikoffice.de
PostgreSQL version: 9.5.7
Operating system:   Debian Linux 8.6
Description:

Hello,

This is more a feature request than a bug, but I beliebe it should be placed
on the dev list anyway. I was researching cases for OOMs on our servers
yesterday, and noticed that the server side prepared statements can be real
nasty.

Due to a misconfiguration we had all our statements name-prepared on the
server side, so the query plans became stored. We maxed the number to 256
per connection, and didn't think about it anymore.

Now, there was this loop we created where we called something like

"select * from vw_largebeast where id=n"

without using prepared statement parameters, but the driver created server
side prepared statements anyway. 

The loop contained about 1000 entries, but after about 200 entries the
memory of the server was complety exhausted (small 8GB VM with 6GB free at
time of start).  

I concluded that the query plan of the statement, which I uploaded to despez
would result in 30MB of memory footprint!

https://explain.depesz.com/s/gN2

Besides that fact that I should have developed that better, I have some
questions now:

* How can I determine the memory footprint of prepared statements?
* Wouldn't it be useful if we could give a memory limit for prepared
statements on the server, so that PostgreSQL automatically evicts them if
more are prepared, maybe by using an LRU list? 

PostgreSQL could automatically replan them when they get used again, I
think. Currently we have no way to determine how much memory really is used
in PreparedStatements. Althought the JDBC driver we use does its own memory
based limitation, it is only based on query length, and a "select * from
vw_FatMamma" doesn't honor the real complexity. 



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

dmigowski@ikoffice.de writes:
> I concluded that the query plan of the statement, which I uploaded to despez
> would result in 30MB of memory footprint!
> https://explain.depesz.com/s/gN2

Really?  There's 132 plan nodes in that plan.  I could believe that we're
eating several KB per plan node, but not 220KB per node.

> * How can I determine the memory footprint of prepared statements?

It's not something we track specifically.

> * Wouldn't it be useful if we could give a memory limit for prepared
> statements on the server, so that PostgreSQL automatically evicts them if
> more are prepared, maybe by using an LRU list? 

I'd be against that, as it pretty much would destroy the point of having
prepared statements at all --- if the server forgets them at random, or
even just has to re-prepare them unexpectedly, it's not holding up its
end of the contract.  It's the application's job to use that resource
in a prudent manner, just like any other SQL resource.

> PostgreSQL could automatically replan them when they get used again, I
> think.

This view seems like a poster child for why that would be a bad idea
--- that query has to take a mighty long time to plan.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs