"checkpointer process" is consuming more memory. How to control it?

On 08/22/2018 06:43 AM, Raghavendra Rao J S V wrote:

Hi All,

We have a database cluster as "db1_data". Under this cluster we have two
databases. one is *db1 *and other is *qovr*. I surprised to see as
"checkpointer process" is consuming 8.73GB of memory(RSS value as
9158892). Why "checkpointer process" is consuming this much amount of
memory and how to limit the usage of the "checkpointer process" memory.

Take a look at:

https://www.postgresql.org/docs/10/static/wal-configuration.html

image.png

--
Regards,
Raghavendra Rao J S V

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Raghavendra,

Please let us know your PostgreSQL version.

Also, what are the values set to the following parameters ?

select name, setting from pg_settings where name IN
('checkpoint_segments','checkpoint_timeout','max_wal_size','min_wal_size','checkpoint_completion_target','wal_keep_segments');

On Wed, Aug 22, 2018 at 10:43 AM, Raghavendra Rao J S V <
raghavendrajsv@gmail.com> wrote:

Hi All,

We have a database cluster as "db1_data". Under this cluster we have two
databases. one is *db1 *and other is *qovr*. I surprised to see as
"checkpointer process" is consuming 8.73GB of memory(RSS value as 9158892).
Why "checkpointer process" is consuming this much amount of memory and how
to limit the usage of the "checkpointer process" memory.

[image: image.png]

--
Regards,
Raghavendra Rao J S V

--
9000799060

We are using PostgreSQL 9.2 version.

As you requested below you can find the checkpoint parameter values. In
pg_xlog directory , I can see each wal file size is 16mb.

select name, setting from pg_settings where name IN ('checkpoint_segments','
checkpoint_timeout','max_wal_size','min_wal_size','
checkpoint_completion_target','wal_keep_segments');

[image: image.png]

On Wed, 22 Aug 2018 at 19:26, Avinash Kumar <avinash.vallarapu@gmail.com>
wrote:

Hi Raghavendra,

Please let us know your PostgreSQL version.

Also, what are the values set to the following parameters ?

select name, setting from pg_settings where name IN
('checkpoint_segments','checkpoint_timeout','max_wal_size','min_wal_size','checkpoint_completion_target','wal_keep_segments');

On Wed, Aug 22, 2018 at 10:43 AM, Raghavendra Rao J S V <
raghavendrajsv@gmail.com> wrote:

Hi All,

We have a database cluster as "db1_data". Under this cluster we have two
databases. one is *db1 *and other is *qovr*. I surprised to see as
"checkpointer process" is consuming 8.73GB of memory(RSS value as 9158892).
Why "checkpointer process" is consuming this much amount of memory and how
to limit the usage of the "checkpointer process" memory.

[image: image.png]

--
Regards,
Raghavendra Rao J S V

--
9000799060

--
Regards,
Raghavendra Rao J S V
Mobile- 8861161425

Seams like you are with the default or fairly less checkpoint_* settings.

You need to look at several patterns to understand the best values for
these settings.

For now, i can say 300 seconds for checkpoint_timeout could be fairly very
less. Try increasing that with a reload.

See this =>
https://www.percona.com/blog/2018/06/15/tuning-postgresql-for-sysbench-tpcc/

Please do note that delaying checkpointing could also increase the time for
crash recovery/shutdown, etc.........

--
Thanks & Regards,
Avinash Vallarapu (Avi).
PostgreSQL Support Tech Lead.
+1-902-221-5976

On Wed, Aug 22, 2018 at 11:02 AM, Raghavendra Rao J S V <
raghavendrajsv@gmail.com> wrote:

22.08.2018 16:43, Raghavendra Rao J S V wrote:

We have a database cluster as "db1_data". Under this cluster we have
two databases. one is *db1 *and other is *qovr*. I surprised to see as
"checkpointer process" is consuming 8.73GB of memory(RSS value as
9158892). Why "checkpointer process" is consuming this much amount of
memory and how to limit the usage of the "checkpointer process" memory.

RSS value is not reasonable to determine memory leaks because it takes
into account shared segments (e.g. from shared buffer cache). As a
long-lived process checkpointer process tries to flush and as a
consequence to touch each buffer cell therefore its RSS approaches to
local allocated memory plus shared_buffers.

If you want to know the real local memory consumption you may to use
python utility *smem* to see unshared local memory size.

--
Regards,
Maksim Milyutin

On 2018-08-22 11:14:48 -0300, Avi Vallarapu wrote:

Seams like you are with the default or fairly less checkpoint_* settings.

You need to look at several patterns to understand the best values for
these settings.

For now, i can say 300 seconds for checkpoint_timeout could be fairly very
less. Try increasing that with a reload.

See this =>
https://www.percona.com/blog/2018/06/15/tuning-postgresql-for-sysbench-tpcc/

Please do note that delaying checkpointing could also increase the time for
crash recovery/shutdown, etc.........

This seems to have not that much to do with the problem the OP has, no?

I suggest just using pmap -d $process_id_of_checkpointer
that'll likely show that it doesn't actually use much memory, but that
the RSS originates from the shared memory it has touched.

- Andres