Обсуждение: Constantly increasing per connection memory usage on pg 9.2
Dear Community, we've recently moved one of our production databases from a 8.3 based server to a 9.2 installation. Our usage patterns remained the same, however, we noticed that the swap space in the server started to decrease with time. We have roughly 16 connections to this database in an almost constant basis, pooled by a java connection pooler on the app side. This connections never expire (nor did they do with the 8.3 db). However, as you can see on this memory usage chart http://i.imgur.com/2sRRj1J.png it shows a rolleccoaster like pattern. When we restart our application, the connections are closed and the memory is freed. Our work_mem is 2MB, so it does not warrant the large memory allocation. Also I am completely sure that this is the pg connection process that's eating up the memory. I've been digging the net for info for days, but this is the first time I haven't found anything event remotely resembling our scenario. The exact version number is: psql (EnterpriseDB) 9.2.1.3 Does anybody have any ideas what causes this? regards Eliott
On Sat, Dec 7, 2013 at 1:10 AM, Eliott <eliott100@gmail.com> wrote:
Dear Community,
we've recently moved one of our production databases from a 8.3 based server to a 9.2 installation. Our usage patterns remained the same, however, we noticed that the swap space in the server started to decrease with time.
We have roughly 16 connections to this database in an almost constant basis, pooled by a java connection pooler on the app side. This connections never expire (nor did they do with the 8.3 db).
However, as you can see on this memory usage chart http://i.imgur.com/2sRRj1J.png it shows a rolleccoaster like pattern. When we restart our application, the connections are closed and the memory is freed.
Our work_mem is 2MB, so it does not warrant the large memory allocation. Also I am completely sure that this is the pg connection process that's eating up the memory.
Is it the process on the client side or the server side that is using the memory?
How many tables are in the database? Every additional table touched will pull in some more metadata, so over the course of a long lived connection in the pool, it could grow a lot if you have a lot of tables.
Cheers,
Jeff
Hi Jeff!
No, the process that seems to grow indefinitely is postgres process created for each active connection. The app side is fine.
Altogether we have 50 tables, only two are in the 200 mb range, the rest is <50mb. While we query one of the big tables constantly (once every min) but each connection process growing to 100mb in memory footprint is something we haven't experienced with the 8.3.
Of course one solution would be to configure the connection pooler to expire the connections from time to time, but I am still not convinced that what we see is normal.
regards
Eliott
On 2013.12.07. 21:53, Jeff Janes wrote:
No, the process that seems to grow indefinitely is postgres process created for each active connection. The app side is fine.
Altogether we have 50 tables, only two are in the 200 mb range, the rest is <50mb. While we query one of the big tables constantly (once every min) but each connection process growing to 100mb in memory footprint is something we haven't experienced with the 8.3.
Of course one solution would be to configure the connection pooler to expire the connections from time to time, but I am still not convinced that what we see is normal.
regards
Eliott
On 2013.12.07. 21:53, Jeff Janes wrote:
On Sat, Dec 7, 2013 at 1:10 AM, Eliott <eliott100@gmail.com> wrote:Dear Community,
we've recently moved one of our production databases from a 8.3 based server to a 9.2 installation. Our usage patterns remained the same, however, we noticed that the swap space in the server started to decrease with time.
We have roughly 16 connections to this database in an almost constant basis, pooled by a java connection pooler on the app side. This connections never expire (nor did they do with the 8.3 db).
However, as you can see on this memory usage chart http://i.imgur.com/2sRRj1J.png it shows a rolleccoaster like pattern. When we restart our application, the connections are closed and the memory is freed.
Our work_mem is 2MB, so it does not warrant the large memory allocation. Also I am completely sure that this is the pg connection process that's eating up the memory.Is it the process on the client side or the server side that is using the memory?How many tables are in the database? Every additional table touched will pull in some more metadata, so over the course of a long lived connection in the pool, it could grow a lot if you have a lot of tables.Cheers,Jeff
Hi!
I'd like to revisit this issue. What we have noticed that the amount of memory increase does not really depend on the number of large queries, but the number of queries in general.
So, can anybody confirm that is is normal for a 9.2 based server connection to take up 10 times the connection specific memory that 8.3 used to need? It seems like that difference is in the (RES-SHR) value. For 8.2 it is just a few megs, however, in case of 9.2 the non shared resident portion is 60, sometimes maybe even 100 mb per connection.
regards
eliott
On 2013.12.07. 21:53, Jeff Janes wrote:
I'd like to revisit this issue. What we have noticed that the amount of memory increase does not really depend on the number of large queries, but the number of queries in general.
So, can anybody confirm that is is normal for a 9.2 based server connection to take up 10 times the connection specific memory that 8.3 used to need? It seems like that difference is in the (RES-SHR) value. For 8.2 it is just a few megs, however, in case of 9.2 the non shared resident portion is 60, sometimes maybe even 100 mb per connection.
regards
eliott
On 2013.12.07. 21:53, Jeff Janes wrote:
On Sat, Dec 7, 2013 at 1:10 AM, Eliott <eliott100@gmail.com> wrote:Dear Community,
we've recently moved one of our production databases from a 8.3 based server to a 9.2 installation. Our usage patterns remained the same, however, we noticed that the swap space in the server started to decrease with time.
We have roughly 16 connections to this database in an almost constant basis, pooled by a java connection pooler on the app side. This connections never expire (nor did they do with the 8.3 db).
However, as you can see on this memory usage chart http://i.imgur.com/2sRRj1J.png it shows a rolleccoaster like pattern. When we restart our application, the connections are closed and the memory is freed.
Our work_mem is 2MB, so it does not warrant the large memory allocation. Also I am completely sure that this is the pg connection process that's eating up the memory.Is it the process on the client side or the server side that is using the memory?How many tables are in the database? Every additional table touched will pull in some more metadata, so over the course of a long lived connection in the pool, it could grow a lot if you have a lot of tables.Cheers,Jeff
Eliott <eliott100@gmail.com> writes:
> So, can anybody confirm that is is normal for a 9.2 based server
> connection to take up 10 times the connection specific memory that 8.3
> used to need?
I'm sure there's been some bloat over time, but I can't offhand think of
a reason for it to be 10X more than 8.3 was. You might try attaching to a
backend with gdb and doing "call MemoryContextStats(TopMemoryContext)"
periodically to try to identify where the memory is going. (The output
from that will go to postmaster stderr, so make sure your logging
configuration will capture that.)
regards, tom lane