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

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

From

Raghavendra Rao J S V

Date:

22 August 2018, 19:43:06

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.

Regards,
Raghavendra Rao J S V

Attachment

image.png

Re: "checkpointer process" is consuming more memory. How to controlit?

From

Adrian Klaver

Date:

22 August 2018, 19:54:44

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

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

From

Avinash Kumar

Date:

22 August 2018, 19:56:43

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.

--
Regards,
Raghavendra Rao J S V

9000799060

Attachment

image.png

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

From

Raghavendra Rao J S V

Date:

22 August 2018, 20:02:36

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');

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.

--
Regards,
Raghavendra Rao J S V

--
9000799060

Regards,
Raghavendra Rao J S V
Mobile- 8861161425

Attachment

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

From

Avi Vallarapu

Date:

22 August 2018, 20:14:48

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:

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');

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.

--
Regards,
Raghavendra Rao J S V

--
9000799060

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

Attachment

Re: "checkpointer process" is consuming more memory. How to controlit?

From

Maksim Milyutin

Date:

22 August 2018, 20:43:30

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

Re: "checkpointer process" is consuming more memory. How to controlit?

From

Andres Freund

Date:

23 August 2018, 19:46:28

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