pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPage File Size Reached Critical Threshold PostgreSQL V13
5+ messages / 2 participants
[nested] [flat]
* Page File Size Reached Critical Threshold PostgreSQL V13
@ 2021-06-10 00:45 Haseeb Khan <khanhaseeb92@gmail.com>
2021-06-10 04:28 ` Re: Page File Size Reached Critical Threshold PostgreSQL V13 Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 5+ messages in thread
From: Haseeb Khan @ 2021-06-10 00:45 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hello Everyone,
I trust that everyone is Keep doing very Well !
We have installed PostgreSQL V13 on window’s server 2016, where we kept the Ram of the Server is 32 GB and disk size is 270 GB.Later we faced some performance issues regarding the database, after deep dive into it we came up and increased the Shared buffer size to 16 Gb. After the changed I am not sure we are facing that Page file Size reached to critical threshold. Currently the Page File size is 9504MB.
Highly Appreciated, if you guys could recommend/suggest any solution / idea.
Br,
Haseeb Ahmad=
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Page File Size Reached Critical Threshold PostgreSQL V13
2021-06-10 00:45 Page File Size Reached Critical Threshold PostgreSQL V13 Haseeb Khan <khanhaseeb92@gmail.com>
@ 2021-06-10 04:28 ` Justin Pryzby <pryzby@telsasoft.com>
2021-06-10 06:41 ` Re: Page File Size Reached Critical Threshold PostgreSQL V13 Haseeb Khan <khanhaseeb92@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Justin Pryzby @ 2021-06-10 04:28 UTC (permalink / raw)
To: Haseeb Khan <khanhaseeb92@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, Jun 10, 2021 at 05:45:45AM +0500, Haseeb Khan wrote:
> We have installed PostgreSQL V13 on window’s server 2016, where we kept the Ram of the Server is 32 GB and disk size is 270 GB.Later we faced some performance issues regarding the database, after deep dive into it we came up and increased the Shared buffer size to 16 Gb. After the changed I am not sure we are facing that Page file Size reached to critical threshold. Currently the Page File size is 9504MB.
Hi,
How large is your DB ? (Or the "active set" of the DB, if parts of it are
accessed infrequently).
What was the original performance issue that led you to increase shared_buffers ?
You've set shared_buffers to half of your RAM, which may be a "worst case"
setting, since everything that's read into shared_buffers must first be read
into the OS cache. So it may be that many blocks are cached twice, rather than
relying on a smaller shared_buffers only for the "hottest" blocks, and the
larger OS cache for everything else.
There are exceptions to the guideline - for example, if your DB is 23 GB in
size, it might make sense to have the entire thing in 24GB OF shared_buffers.
But most DB don't need to fit in shared_buffers, and you shouldn't make that a
goal, unless you can measure a performance benefit.
--
Justin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Page File Size Reached Critical Threshold PostgreSQL V13
2021-06-10 00:45 Page File Size Reached Critical Threshold PostgreSQL V13 Haseeb Khan <khanhaseeb92@gmail.com>
2021-06-10 04:28 ` Re: Page File Size Reached Critical Threshold PostgreSQL V13 Justin Pryzby <pryzby@telsasoft.com>
@ 2021-06-10 06:41 ` Haseeb Khan <khanhaseeb92@gmail.com>
2021-06-10 08:55 ` Re: Page File Size Reached Critical Threshold PostgreSQL V13 Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 5+ messages in thread
From: Haseeb Khan @ 2021-06-10 06:41 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
Hi Justin,
You mean, So should I request for to increase the System Ram from 32 Gb to 64 Gb and keep the same parameter setting.Is it ?
Br,
Haseeb Ahmad
>
> On 10-Jun-2021, at 9:28 AM, Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> On Thu, Jun 10, 2021 at 05:45:45AM +0500, Haseeb Khan wrote:
>> We have installed PostgreSQL V13 on window’s server 2016, where we kept the Ram of the Server is 32 GB and disk size is 270 GB.Later we faced some performance issues regarding the database, after deep dive into it we came up and increased the Shared buffer size to 16 Gb. After the changed I am not sure we are facing that Page file Size reached to critical threshold. Currently the Page File size is 9504MB.
>
> Hi,
>
> How large is your DB ? (Or the "active set" of the DB, if parts of it are
> accessed infrequently).
>
> What was the original performance issue that led you to increase shared_buffers ?
>
> You've set shared_buffers to half of your RAM, which may be a "worst case"
> setting, since everything that's read into shared_buffers must first be read
> into the OS cache. So it may be that many blocks are cached twice, rather than
> relying on a smaller shared_buffers only for the "hottest" blocks, and the
> larger OS cache for everything else.
>
> There are exceptions to the guideline - for example, if your DB is 23 GB in
> size, it might make sense to have the entire thing in 24GB OF shared_buffers.
> But most DB don't need to fit in shared_buffers, and you shouldn't make that a
> goal, unless you can measure a performance benefit.
>
> --
> Justin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Page File Size Reached Critical Threshold PostgreSQL V13
2021-06-10 00:45 Page File Size Reached Critical Threshold PostgreSQL V13 Haseeb Khan <khanhaseeb92@gmail.com>
2021-06-10 04:28 ` Re: Page File Size Reached Critical Threshold PostgreSQL V13 Justin Pryzby <pryzby@telsasoft.com>
2021-06-10 06:41 ` Re: Page File Size Reached Critical Threshold PostgreSQL V13 Haseeb Khan <khanhaseeb92@gmail.com>
@ 2021-06-10 08:55 ` Justin Pryzby <pryzby@telsasoft.com>
2021-06-10 14:33 ` Re: Page File Size Reached Critical Threshold PostgreSQL V13 Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 5+ messages in thread
From: Justin Pryzby @ 2021-06-10 08:55 UTC (permalink / raw)
To: Haseeb Khan <khanhaseeb92@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, Jun 10, 2021 at 11:41:09AM +0500, Haseeb Khan wrote:
> You mean, So should I request for to increase the System Ram from 32 Gb to 64 Gb and keep the same parameter setting.Is it ?
No - I don't know how large your DB is, or the other question that I asked.
So I can't possibly make a suggestion to add RAM.
But I do know that "half" is the worst possible setting for many databases.
I suggest to provide some more information, and we can try to suggest a better
configuration.
https://wiki.postgresql.org/wiki/Slow_Query_Questions
On 10-Jun-2021, at 9:28 AM, Justin Pryzby <pryzby@telsasoft.com> wrote:
> > On Thu, Jun 10, 2021 at 05:45:45AM +0500, Haseeb Khan wrote:
> >> We have installed PostgreSQL V13 on window’s server 2016, where we kept the Ram of the Server is 32 GB and disk size is 270 GB.Later we faced some performance issues regarding the database, after deep dive into it we came up and increased the Shared buffer size to 16 Gb. After the changed I am not sure we are facing that Page file Size reached to critical threshold. Currently the Page File size is 9504MB.
> >
> > How large is your DB ? (Or the "active set" of the DB, if parts of it are
> > accessed infrequently).
> >
> > What was the original performance issue that led you to increase shared_buffers ?
> >
> > You've set shared_buffers to half of your RAM, which may be a "worst case"
> > setting, since everything that's read into shared_buffers must first be read
> > into the OS cache. So it may be that many blocks are cached twice, rather than
> > relying on a smaller shared_buffers only for the "hottest" blocks, and the
> > larger OS cache for everything else.
> >
> > There are exceptions to the guideline - for example, if your DB is 23 GB in
> > size, it might make sense to have the entire thing in 24GB OF shared_buffers.
> > But most DB don't need to fit in shared_buffers, and you shouldn't make that a
> > goal, unless you can measure a performance benefit.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Page File Size Reached Critical Threshold PostgreSQL V13
2021-06-10 00:45 Page File Size Reached Critical Threshold PostgreSQL V13 Haseeb Khan <khanhaseeb92@gmail.com>
2021-06-10 04:28 ` Re: Page File Size Reached Critical Threshold PostgreSQL V13 Justin Pryzby <pryzby@telsasoft.com>
2021-06-10 06:41 ` Re: Page File Size Reached Critical Threshold PostgreSQL V13 Haseeb Khan <khanhaseeb92@gmail.com>
2021-06-10 08:55 ` Re: Page File Size Reached Critical Threshold PostgreSQL V13 Justin Pryzby <pryzby@telsasoft.com>
@ 2021-06-10 14:33 ` Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 5+ messages in thread
From: Justin Pryzby @ 2021-06-10 14:33 UTC (permalink / raw)
To: Haseeb Khan <khanhaseeb92@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Please work through the Slow Query wiki page and try to provide as much
information as possible. It's too hard to try to help if each communication
includes only a fraction of the requested information.
On Thu, Jun 10, 2021 at 04:33:51PM +0500, Haseeb Khan wrote:
> PFB the query and there are many other queries like these
>
> select
> pd.gender,
> count(1) as dispensation_counts
> from cdss_wasfaty.dispensation_fact df
> inner join cdss_wasfaty.patient_dim pd
> on df.patient_key = pd.patient_key
> inner join cdss_wasfaty.date_dim date_dim
> on df.dispensation_date_key = date_dim.date_key
> and date_dim.year IN (2020)
> group by pd.gender
>
> On Thu, Jun 10, 2021 at 2:48 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> > Can you give an example of a query that performed poorly?
> >
> > Send the query, and its explain (analyze,buffers,settings) for the query,
> > and schema for the relevant queries.
> >
> > > > https://wiki.postgresql.org/wiki/Slow_Query_Questions
--
Justin Pryzby
System Administrator
Telsasoft
+1-952-707-8581
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-06-10 14:33 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-10 00:45 Page File Size Reached Critical Threshold PostgreSQL V13 Haseeb Khan <khanhaseeb92@gmail.com>
2021-06-10 04:28 ` Justin Pryzby <pryzby@telsasoft.com>
2021-06-10 06:41 ` Haseeb Khan <khanhaseeb92@gmail.com>
2021-06-10 08:55 ` Justin Pryzby <pryzby@telsasoft.com>
2021-06-10 14:33 ` Justin Pryzby <pryzby@telsasoft.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