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:
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
Raghavendra Rao J S V
Attachment
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.--Regards,
Raghavendra Rao J S V
9000799060
Attachment
Re: "checkpointer process" is consuming more memory. How to control it?
From
Raghavendra Rao J S V
Date:
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
Raghavendra Rao J S V
Mobile- 8861161425
Attachment
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.
Please do note that delaying checkpointing could also increase the time for crash recovery/shutdown, etc.........
--
Thanks & Regards,
Avinash Vallarapu (Avi).
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--Mobile- 8861161425Regards,
Raghavendra Rao J S V
Attachment
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