BUG #14606: Memory usage continue to increase

On Thu, Mar 30, 2017 at 2:28 PM, <sam.dai@servicemax.com> wrote:

I have a java application that uses postgresSQL 9.6.1, found the memory
usage continue to increase over time, the details is as below:
(1) At the beginning, the memory usage is Postgresql is 178.3 MB.
(2) Run performance testing(concurrent user 1800) on this application, when
active user is 1800 in performance testing, the memory usage of this
Postgresql is 1.413 GB. After the performance testing is finished, the
memory usage of this Postgresql is 344.1 MB.
(3) Wait for 8 hours and don't do anything on this application during this
time, the memory usage of this container is 360.2 MB after 8 hours. Even I
restart this application and wait for 2 hours, the memory usage of this
Postgresql is 350 MB.
(4)Run this performance testing for 20 times in 20 days, found memory usage
of this Postgresql is 3.5 GB. If restart Postgres, the memory usage will
back to about 178.3 MB.

It looks like the memory usage of Postgres increases every time we run
performance testing, not sure if it's a bug or not.

Are you keeping connections to the server alive for a long time? This
may be a sign of syscache bloat.
--
Michael

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

My application used ComboPooledDataSource to connect to Postgresql server,
the setting is as below, if this issue is syscache bloat, what is the
solution of this issue?

<bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource"

destroy-method="close">

<!-- connection properties -->

...

<!-- pool size -->

<property name="acquireIncrement" value="3" />

<property name="initialPoolSize" value="5" />

<property name="maxPoolSize" value="20" />

<property name="minPoolSize" value="5" />

<!-- trim old connections -->

<property name="maxConnectionAge" value="300" /> <!-- 5 min -->

<property name="maxIdleTime" value="180" /> <!-- 3 min -->

<property name="maxIdleTimeExcessConnections" value="60" /> <!-- 1 min -->

<property name="numHelperThreads" value="6" />

<!-- test connections -->

<property name="idleConnectionTestPeriod" value="300" /> <!-- 5 min -->

<property name="testConnectionOnCheckin" value="true" />

<property name="preferredTestQuery" value="SELECT 1" />

<!-- disable prepared statement caching -->

<property name="maxStatements" value="0" />

<property name="maxStatementsPerConnection" value="0" />

<!-- rollback uncommited changes on close -->

<property name="autoCommitOnClose" value="false" />

</bean>

Thanks,

Sam

On Thu, Mar 30, 2017 at 8:53 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:

On Thu, Mar 30, 2017 at 10:24 PM, Sam Dai <sam.dai@servicemax.com> wrote:

(please do not top-post, this breaks the thread logic)

My application used ComboPooledDataSource to connect to Postgresql server,
the setting is as below, if this issue is syscache bloat, what is the
solution of this issue?

Just a question: how are you measuring memory?

<property name="maxConnectionAge" value="300" /> <!-- 5 min -->
<property name="maxIdleTime" value="180" /> <!-- 3 min -->

Seeing that, likely this is not syscache.
--
Michael

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

I run Postgresql server in docker container, I run command "docker stats"
to measure memory , for example in the below command result, the memory
usage of docker container is 1.413 GB.

c137012d8af9 793.64% 1.413 GB / 67.55 GB 2.09%
5.927 GB / 7.249 GB 0 B / 10.32 GB 0

Thanks,

Sam

On Thu, Mar 30, 2017 at 9:32 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:

Is it possible this issue is caused by the cache or buffer of Postgresql
server? I guess when we run performance testing, will read/update
different records, Postgresql might use some new memory for cache or buffer
some records, and some these cache or buffer will not be cleaned after
performance test is finished. so the new increased memory is for cache or
buffer.
Not sure if my guess is reasonable or not.

Thanks,
Sam

On Thu, Mar 30, 2017 at 9:42 PM, Sam Dai <sam.dai@servicemax.com> wrote:

At Fri, 31 Mar 2017 00:30:31 +0800, Sam Dai <sam.dai@servicemax.com> wrote in <CAO+pR0oYSFNgx_oAYxLyNOVmh7R0oG52v3cU0Dq1mkTsQLbqGQ@mail.gmail.com>

Is it possible this issue is caused by the cache or buffer of Postgresql
server? I guess when we run performance testing, will read/update
different records, Postgresql might use some new memory for cache or buffer
some records, and some these cache or buffer will not be cleaned after
performance test is finished. so the new increased memory is for cache or
buffer.
Not sure if my guess is reasonable or not.

Anyway at least the amount of syscache should be revealed on SQL
interface. It is not even managed on builds without defining
CATCACHE_STATS.

For your information, the discussion about syscache bloat is
here.

/messages/by-id/214653D8DF574BFEAA6ED53E545E99E4@maumau
/messages/by-id/20161219.201505.11562604.horiguchi.kyotaro@lab.ntt.co.jp

In breaf, the bloat in the above threads is caused by accesing
many-many nonexistent tables, columns, functions or other SQL
objects. If you did something like that on a long-standing
connection, it is likely to be this issue.

If the "buffer" means shared_buffer, it cannot bloat. I'm not
sure about other kinds of memory leak but I belive there's no
known significant memory leak at present.

Thanks,
Sam

On Thu, Mar 30, 2017 at 9:42 PM, Sam Dai <sam.dai@servicemax.com> wrote:

I run Postgresql server in docker container, I run command "docker stats"
to measure memory , for example in the below command result, the memory
usage of docker container is 1.413 GB.

c137012d8af9 793.64% 1.413 GB / 67.55 GB 2.09%
5.927 GB / 7.249 GB 0 B / 10.32 GB 0

Thanks,

Sam

On Thu, Mar 30, 2017 at 9:32 PM, Michael Paquier <
michael.paquier@gmail.com> wrote:

On Thu, Mar 30, 2017 at 10:24 PM, Sam Dai <sam.dai@servicemax.com> wrote:

(please do not top-post, this breaks the thread logic)

My application used ComboPooledDataSource to connect to Postgresql

server,

the setting is as below, if this issue is syscache bloat, what is the
solution of this issue?

Just a question: how are you measuring memory?

<property name="maxConnectionAge" value="300" /> <!-- 5 min -->
<property name="maxIdleTime" value="180" /> <!-- 3 min -->

Seeing that, likely this is not syscache.

I agree with you. It's unlikely to happen in such a short time.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

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

Today I tried a performance testing again, after performance testing is
finished, found memory usage increased about 170MB, then enter the
Postgresql docker container, executed command "/usr/bin/top -bcn 1", found
there are some new idle Postgres processes as below, the memory of other
processed haven't been added, it looks like these new added idle process
caused the memory usage of Postgresql server is added. Don't know what
these idle postgres processed for, and they didn't disappear after 2 hours.

9766 postgres 10 -10 272776 9020 7236 S 0.0 0.0 0:00.00
postgres: 8wk48fzjxrrkd0hj unpyxldeefjqvm1a 10.0.16.4(39698) idle

9767 postgres 10 -10 272776 9020 7236 S 0.0 0.0 0:00.00
postgres: 8wk48fzjxrrkd0hj unpyxldeefjqvm1a 10.0.16.4(39700) idle

...

9768 postgres 10 -10 272776 9020 7236 S 0.0 0.0 0:00.00
postgres: 8wk48fzjxrrkd0hj unpyxldeefjqvm1a 10.0.16.4(39702) idle

9769 postgres 10 -10 272776 9020 7236 S 0.0 0.0 0:00.00
postgres: 8wk48fzjxrrkd0hj unpyxldeefjqvm1a 10.0.16.4(39704) idle

On Fri, Mar 31, 2017 at 11:58 AM, Kyotaro HORIGUCHI <
horiguchi.kyotaro@lab.ntt.co.jp> wrote:

Then I stopped all app instances, the idle postgres processes are gone,
when executed command "/usr/bin/top -bcn 1", the memory usage looks right,
but the result of "docker stats" seems wrong, looks like a docker issue, I
will post this issue to docker forum.

Thanks,
Sam

On Fri, Mar 31, 2017 at 3:39 PM, Sam Dai <sam.dai@servicemax.com> wrote:

On Fri, Mar 31, 2017 at 5:17 PM, Sam Dai <sam.dai@servicemax.com> wrote:

Then I stopped all app instances, the idle postgres processes are gone, when
executed command "/usr/bin/top -bcn 1", the memory usage looks right, but
the result of "docker stats" seems wrong, looks like a docker issue, I will
post this issue to docker forum.

It may be a good idea to check if this docker command is doing the
right calculation regarding the number of shared memory areas touched
by each PG process.
--
Michael

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