[BUGS] BUG #14726: Memory consumption of PreparedStatement
От | dmigowski@ikoffice.de |
---|---|
Тема | [BUGS] BUG #14726: Memory consumption of PreparedStatement |
Дата | |
Msg-id | 20170702090956.1469.41812@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: [BUGS] BUG #14726: Memory consumption of PreparedStatement
|
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: