[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Memory settings
From: Hans Schou <hans.schou () gmail ! com>
Date: 2019-06-30 6:04:44
Message-ID: CAApBw35G+RPnkYXKzdN4grptG0b8o-StguUk-qY87VcGB0gfag () mail ! gmail ! com
[Download RAW message or body]
Try run postgresqltuner.pl as suggested on
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and also
look at the other info there.
After running a few days with live data run cache_hit_ratio.sql by Melvin
Davidson:
SELECT pg_stat_database.datname,
pg_stat_database.blks_read,
pg_stat_database.blks_hit,
round((pg_stat_database.blks_hit::double precision
/ (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+1)::double precision * 100::double precision)::numeric,
2) AS cachehitratio
FROM pg_stat_database
WHERE pg_stat_database.datname !~ '^(template(0|1)|postgres)$'::text
ORDER BY round((pg_stat_database.blks_hit::double precision
/ (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+ 1)::double precision * 100::double
precision)::numeric, 2) DESC;
The real question is: Is your system slow?
On Sun, Jun 30, 2019 at 5:14 AM Daulat Ram <Daulat.Ram@exponential.com>
wrote:
> Hi team,
>
>
>
> Can you please suggest what will be the suitable memory settings for
> Postgresql11 if we have 80gb RAM, 16 CPU's and OS Linux.
>
>
>
> If we set 25 % of total RAM then shared_buffers value will be 20GB. Will
> it be useful or we can set it any random vale like 8g or 12gb.
>
>
>
> According to https://pgtune.leopard.in.ua/#/
>
> below are the suggested memory values for 80gb RAM and 16 CPU. I assume
> the values preferred for effective_cache_size = 60GB and shared_buffers =
> 20GB are too large.
>
>
>
> max_connections = 500
>
> shared_buffers = 20GB
>
> effective_cache_size = 60GB
>
> maintenance_work_mem = 2GB
>
> checkpoint_completion_target = 0.7
>
> wal_buffers = 16MB
>
> default_statistics_target = 100
>
> random_page_cost = 1.1
>
> effective_io_concurrency = 300
>
> work_mem = 6553kB
>
> min_wal_size = 1GB
>
> max_wal_size = 2GB
>
> max_worker_processes = 16
>
> max_parallel_workers_per_gather = 8
>
> max_parallel_workers = 16
>
>
>
> Please give your suggestions.
>
>
>
> Regards,
>
> Daulat
>
>
>
[Attachment #3 (text/html)]
<div dir="ltr"><div><br></div><div>Try run <a \
href="http://postgresqltuner.pl">postgresqltuner.pl</a> as suggested on <a \
href="https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server">https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server</a> \
and also look at the other info there.</div><div><br></div><div>After running a few \
days with live data run cache_hit_ratio.sql by Melvin Davidson:</div><div><span \
style="font-family:courier new,monospace">SELECT pg_stat_database.datname, <br> \
pg_stat_database.blks_read,<br> pg_stat_database.blks_hit, <br> \
round((pg_stat_database.blks_hit::double precision <br> / \
(pg_stat_database.blks_read <br> + pg_stat_database.blks_hit \
<br> +1)::double precision * 100::double \
precision)::numeric, 2) AS cachehitratio<br> FROM pg_stat_database<br> WHERE \
pg_stat_database.datname !~ '^(template(0|1)|postgres)$'::text<br> ORDER BY \
round((pg_stat_database.blks_hit::double precision <br> / \
(pg_stat_database.blks_read <br> + \
pg_stat_database.blks_hit <br> + 1)::double precision * \
100::double precision)::numeric, 2) DESC;</span></div><div><br></div><div>The real \
question is: Is your system slow?<br></div><div><br></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Jun 30, 2019 at 5:14 AM \
Daulat Ram <<a href="mailto:Daulat.Ram@exponential.com">Daulat.Ram@exponential.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div lang="EN-US">
<div class="gmail-m_1690306996347007353WordSection1">
<p class="MsoNormal">Hi team,<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Can you please suggest what will be the suitable memory \
settings for Postgresql11 if we have 80gb RAM, 16 CPU's and OS \
Linux.<u></u><u></u></p> <p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">If we set 25 % of total RAM then shared_buffers value will be \
20GB. Will it be useful or we can set it any random vale like 8g or 12gb. \
<u></u><u></u></p> <p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">According to <a href="https://pgtune.leopard.in.ua/#/" \
target="_blank">https://pgtune.leopard.in.ua/#/</a><u></u><u></u></p> <p \
class="MsoNormal">below are the suggested memory values for 80gb RAM and 16 CPU. \
I assume the values preferred for effective_cache_size = 60GB and shared_buffers = \
20GB are too large.<u></u><u></u></p> <p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">max_connections = 500<u></u><u></u></p>
<p class="MsoNormal">shared_buffers = 20GB<u></u><u></u></p>
<p class="MsoNormal">effective_cache_size = 60GB<u></u><u></u></p>
<p class="MsoNormal">maintenance_work_mem = 2GB<u></u><u></u></p>
<p class="MsoNormal">checkpoint_completion_target = 0.7<u></u><u></u></p>
<p class="MsoNormal">wal_buffers = 16MB<u></u><u></u></p>
<p class="MsoNormal">default_statistics_target = 100<u></u><u></u></p>
<p class="MsoNormal">random_page_cost = 1.1<u></u><u></u></p>
<p class="MsoNormal">effective_io_concurrency = 300<u></u><u></u></p>
<p class="MsoNormal">work_mem = 6553kB<u></u><u></u></p>
<p class="MsoNormal">min_wal_size = 1GB<u></u><u></u></p>
<p class="MsoNormal">max_wal_size = 2GB<u></u><u></u></p>
<p class="MsoNormal">max_worker_processes = 16<u></u><u></u></p>
<p class="MsoNormal">max_parallel_workers_per_gather = 8<u></u><u></u></p>
<p class="MsoNormal">max_parallel_workers = 16<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Please give your suggestions.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Regards,<u></u><u></u></p>
<p class="MsoNormal">Daulat<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic