Thread: Postgresql background writer and checkpointer memory utilization in Linux server
Postgresql background writer and checkpointer memory utilization in Linux server
From
Mohammed Afsar
Date:
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
Re: Postgresql background writer and checkpointer memory utilization in Linux server
From
Tom Lane
Date:
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
Re: Postgresql background writer and checkpointer memory utilization in Linux server
From
Mohammed Afsar
Date:
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
Re: Postgresql background writer and checkpointer memory utilization in Linux server
From
Tom Lane
Date:
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
Re: Postgresql background writer and checkpointer memory utilization in Linux server
From
Mohammed Afsar
Date:
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