pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql background writer and checkpointer memory utilization in Linux server
7+ messages / 3 participants
[nested] [flat]
* Postgresql background writer and checkpointer memory utilization in Linux server
@ 2020-08-10 10:15 Mohammed Afsar <vmdapsar@gmail.com>
2020-08-10 11:51 ` Postgresql background writer and checkpointer memory utilization in Linux server Mohammed Afsar <vmdapsar@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Mohammed Afsar @ 2020-08-10 10:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Dear Team,
How to reduce to take less memory checkpointer and background writer with
faster database performance?
This two components parameters disable is recommended on production
environments?
How to reduce pg_dum memory utilization and pg_dump is going take reserved
database memory or differently it will take(including shared buffer memory
or).
Server/database current configuration:
RAM:128gb
CPU:24
Database size:5TB
Max_wal_size=7Gb
Checkpoint_completion_target=0.9
Wal_buffers=-1
Shared_buffers=32GB
Temp_buffers=8mb
Work_mem=5GB
Effective io concurrency=300
Max worker process=24
Max parallel worker per gather =12
Max parallel worker =24
Regards,
Mohammed Afsar
Phone:+91 9676536225
^ permalink raw reply [nested|flat] 7+ messages in thread
* Postgresql background writer and checkpointer memory utilization in Linux server
2020-08-10 10:15 Postgresql background writer and checkpointer memory utilization in Linux server Mohammed Afsar <vmdapsar@gmail.com>
@ 2020-08-10 11:51 ` Mohammed Afsar <vmdapsar@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Mohammed Afsar @ 2020-08-10 11:51 UTC (permalink / raw)
To: pgsql-admin
> Dear Team,
>
> How to reduce to take less memory checkpointer and background writer with
> faster database performance?
>
> This two components parameters disable is recommended on production
> environments?
>
> How to reduce pg_dum memory utilization and pg_dump is going take reserved
> database memory or differently it will take(including shared buffer memory
> or).
>
> Server/database current configuration:
> RAM:128gb
> CPU:24
> Database size:5TB
> Max_wal_size=7Gb
> Checkpoint_completion_target=0.9
> Wal_buffers=-1
> Shared_buffers=32GB
> Temp_buffers=8mb
> Work_mem=5GB
> Effective io concurrency=300
> Max worker process=24
> Max parallel worker per gather =12
> Max parallel worker =24
>
> Regards,
> Mohammed Afsar
> Phone:+91 9676536225
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Postgresql background writer and checkpointer memory utilization in Linux server
@ 2020-08-11 00:31 Mohammed Afsar <mohammed.afsar7799@gmail.com>
2020-08-11 15:28 ` Re: Postgresql background writer and checkpointer memory utilization in Linux server Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Mohammed Afsar @ 2020-08-11 00:31 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; pgsql-admin
Dear Team,
How to reduce to take less memory checkpointer and background writer with
faster database performance?
This two components parameters disable is recommended on production
environments?
How to reduce pg_dum memory utilization and pg_dump is going take reserved
database memory or differently it will take(including shared buffer memory
or).
Server/database current configuration:
RAM:128gb
CPU:24
Database size:5TB
Max_wal_size=7Gb
Checkpoint_completion_target=0.9
Wal_buffers=-1
Shared_buffers=32GB
Temp_buffers=8mb
Work_mem=5GB
Effective io concurrency=300
Max worker process=24
Max parallel worker per gather =12
Max parallel worker =24
Regards,
Mohammed Afsar
Phone:+91 9676536225
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgresql background writer and checkpointer memory utilization in Linux server
2020-08-11 00:31 Postgresql background writer and checkpointer memory utilization in Linux server Mohammed Afsar <mohammed.afsar7799@gmail.com>
@ 2020-08-11 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-08-11 15:35 ` Re: Postgresql background writer and checkpointer memory utilization in Linux server Mohammed Afsar <mohammed.afsar7799@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2020-08-11 15:28 UTC (permalink / raw)
To: Mohammed Afsar <mohammed.afsar7799@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Mohammed Afsar <mohammed.afsar7799@gmail.com> writes:
> How to reduce to take less memory checkpointer and background writer with
> faster database performance?
What makes you think that either of them is a problem?
> This two components parameters disable is recommended on production
> environments?
There is no way to disable them, and you would not like the results if
you could.
Neither one (especially not the bgwriter) should have any significant
amount of private memory. Tools such as "top" will probably tell you
they are using 32GB plus, but that's just reflecting the fact that
they've scanned all of shared_buffers. If your system is actually
under too much memory pressure, you might want to dial shared_buffers
down to a smaller fraction of RAM ... but you've not shown us any reason
to think that you have a problem to solve, much less that that'd be a
solution.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgresql background writer and checkpointer memory utilization in Linux server
2020-08-11 00:31 Postgresql background writer and checkpointer memory utilization in Linux server Mohammed Afsar <mohammed.afsar7799@gmail.com>
2020-08-11 15:28 ` Re: Postgresql background writer and checkpointer memory utilization in Linux server Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-08-11 15:35 ` Mohammed Afsar <mohammed.afsar7799@gmail.com>
2020-08-11 15:40 ` Re: Postgresql background writer and checkpointer memory utilization in Linux server Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Mohammed Afsar @ 2020-08-11 15:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
Dear Tom,
Thanks for response,the exact issue which am facing is when there is no
transaction queries are happening in database checkpointer and background
writer is continuously utilizing memory from server.
Please guide me how fix this issue.
Regards,
Mohammed Afsar
Phone:+91 9676536225
On Tue, Aug 11, 2020, 8:58 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Mohammed Afsar <mohammed.afsar7799@gmail.com> writes:
> > How to reduce to take less memory checkpointer and background writer with
> > faster database performance?
>
> What makes you think that either of them is a problem?
>
> > This two components parameters disable is recommended on production
> > environments?
>
> There is no way to disable them, and you would not like the results if
> you could.
>
> Neither one (especially not the bgwriter) should have any significant
> amount of private memory. Tools such as "top" will probably tell you
> they are using 32GB plus, but that's just reflecting the fact that
> they've scanned all of shared_buffers. If your system is actually
> under too much memory pressure, you might want to dial shared_buffers
> down to a smaller fraction of RAM ... but you've not shown us any reason
> to think that you have a problem to solve, much less that that'd be a
> solution.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgresql background writer and checkpointer memory utilization in Linux server
2020-08-11 00:31 Postgresql background writer and checkpointer memory utilization in Linux server Mohammed Afsar <mohammed.afsar7799@gmail.com>
2020-08-11 15:28 ` Re: Postgresql background writer and checkpointer memory utilization in Linux server Tom Lane <tgl@sss.pgh.pa.us>
2020-08-11 15:35 ` Re: Postgresql background writer and checkpointer memory utilization in Linux server Mohammed Afsar <mohammed.afsar7799@gmail.com>
@ 2020-08-11 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-08-11 15:46 ` Re: Postgresql background writer and checkpointer memory utilization in Linux server Mohammed Afsar <mohammed.afsar7799@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2020-08-11 15:40 UTC (permalink / raw)
To: Mohammed Afsar <mohammed.afsar7799@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Mohammed Afsar <mohammed.afsar7799@gmail.com> writes:
> Thanks for response,the exact issue which am facing is when there is no
> transaction queries are happening in database checkpointer and background
> writer is continuously utilizing memory from server.
That sounds like complete nonsense. Those processes don't execute
queries. It's true that they'll continue to do I/O work for some
time after queries have executed in other processes, but that's normal.
I say again that you haven't offered any evidence of having a problem
to solve. If you don't want the database to be consuming any cycles
at all, then you should shut it down. But if you want it to do useful
work, it's designed to spread out that work, not necessarily to do it
all before a query returns results to the client.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgresql background writer and checkpointer memory utilization in Linux server
2020-08-11 00:31 Postgresql background writer and checkpointer memory utilization in Linux server Mohammed Afsar <mohammed.afsar7799@gmail.com>
2020-08-11 15:28 ` Re: Postgresql background writer and checkpointer memory utilization in Linux server Tom Lane <tgl@sss.pgh.pa.us>
2020-08-11 15:35 ` Re: Postgresql background writer and checkpointer memory utilization in Linux server Mohammed Afsar <mohammed.afsar7799@gmail.com>
2020-08-11 15:40 ` Re: Postgresql background writer and checkpointer memory utilization in Linux server Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-08-11 15:46 ` Mohammed Afsar <mohammed.afsar7799@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Mohammed Afsar @ 2020-08-11 15:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
Dear Tom,
This issue is occurred in postgresql 11 version only not in postgresql 9.4
and also in postgresql Linux environments I have seen when there is no
transaction running database background processes are not going to occupies
the server memory.
Regards,
Mohammed Afsar
On Tue, Aug 11, 2020, 9:10 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Mohammed Afsar <mohammed.afsar7799@gmail.com> writes:
> > Thanks for response,the exact issue which am facing is when there is no
> > transaction queries are happening in database checkpointer and background
> > writer is continuously utilizing memory from server.
>
> That sounds like complete nonsense. Those processes don't execute
> queries. It's true that they'll continue to do I/O work for some
> time after queries have executed in other processes, but that's normal.
>
> I say again that you haven't offered any evidence of having a problem
> to solve. If you don't want the database to be consuming any cycles
> at all, then you should shut it down. But if you want it to do useful
> work, it's designed to spread out that work, not necessarily to do it
> all before a query returns results to the client.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-08-11 15:46 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-10 10:15 Postgresql background writer and checkpointer memory utilization in Linux server Mohammed Afsar <vmdapsar@gmail.com>
2020-08-10 11:51 ` Mohammed Afsar <vmdapsar@gmail.com>
2020-08-11 00:31 Postgresql background writer and checkpointer memory utilization in Linux server Mohammed Afsar <mohammed.afsar7799@gmail.com>
2020-08-11 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-08-11 15:35 ` Mohammed Afsar <mohammed.afsar7799@gmail.com>
2020-08-11 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-08-11 15:46 ` Mohammed Afsar <mohammed.afsar7799@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox