Postgres connection growing memory usage over time! This right after the connections in the pool are closed and opened again. - Mailing list pgsql-bugs
Hi,
We use postgres in a multitude of systems for various solutions that we offer.
We have been using the native JSON, JSONB Features in postgres more and more in the past year.
Recently, in a rewrite of one of our key applications we noticed a behavior whe JSON Payloads and issues with Out of Memory issues and heavy swap usage.
What we have discovered is if you leave a connection open and reuse that connection while resulting in JSON connection memory usage skyrockets over time.
We use connections from a pool manager of sorts. The tooling we use allows us to create a connection and cache a pointer to it for reference.
When we leave a connection open for regular row results we never see this issue, but if we result in JSON this occurs in all situations.
This is the configuration of one system that I have been working on to isolate this problem and attempt to resolve it.
PostgreSQL 13.3 (Ubuntu 13.3-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
8 CPU
40 GiB Memory
20 MB Work Memory
1 GB Maint Work Memory
3 GB Shared Buffers.
Live connections 31
Max Connections 100
This is the query that we are running that creates the issue.
<SQLText>
Select json_agg(row_to_json(t))::text from ( Select id , auditpkey from auditrepo.atnaexport
where 1 = 1
and (processed = false or processed is null)
order by id asc limit 100) t
</SQLText>
var result = dbConn1.executeCachedQuery(sql.toString());
Those two values are BigInt, ID is a Primary Key for the table and auditpkey is a primary key from another table.
This graph shows what happens over time when the connection is left alive for the read of JSON, the end of this graph at 1:00 shows a change
that I implemented to close a connection after 1000 statements are processed to the DB. In this use case there are 8 connections in the pool.
These 8 connections make approximately 8,000 transactions at the DB in 14 minutes or so.
The JSON query above was isolated to a single connection to trace the issue better.
You can see in the image below that there is one connection that is growing memory usage over time and the rest are not.
This image shows the memory growing on that single connection that the JSON is processed and the other connections that the memory used is stable and not growing. This is just before the connections are closed and opened again in the pool.
We are using:
Java: Eclipse Adoptium, OpenJDK Runtime Environment, 11.0.14.1+1
JDBC postgresql-42.2.19Thank you,
Vice President of Engineering Services
Zen’s Gemini Integration-as-a-Service Platform is now officially HITRUST CSF r2 Certified!
Zen Healthcare IT is incredibly proud to announce that our Gemini Integration-as-a-Service Platform and HIE 3rd Party hosting services are HITRUST CSF Risk-based, 2-year certified! This includes Gemini modules such as our Stargate IHE Gateway and Gravity FHIR Aggregator. For the fastest path to interoperability without sacrificing security – contact the Zen Team.
Attachment
pgsql-bugs by date: