RE: Memory settings

[Daulat Ram <Daulat.Ram@xxxxxxxxxxxxxxx>]

Hello Hans,

 

Thanks for your reply. Yes, we are facing performance issue.

 

Current output of query is:

 

postgres=# SELECT pg_stat_database.datname,

postgres-#        pg_stat_database.blks_read,

postgres-#        pg_stat_database.blks_hit,

postgres-#        round((pg_stat_database.blks_hit::double precision

postgres(#               / (pg_stat_database.blks_read

postgres(#                  + pg_stat_database.blks_hit

postgres(#                  +1)::double precision * 100::double precision)::numeric, 2) AS cachehitratio

postgres-#    FROM pg_stat_database

postgres-#   WHERE pg_stat_database.datname !~ '^(template(0|1)|postgres)$'::text

postgres-#   ORDER BY round((pg_stat_database.blks_hit::double precision

postgres(#                  / (pg_stat_database.blks_read

postgres(#                     + pg_stat_database.blks_hit

postgres(#                     + 1)::double precision * 100::double precision)::numeric, 2) DESC;

   datname    | blks_read | blks_hit  | cachehitratio

--------------+-----------+-----------+---------------

kbcc_eng_ret |      1192 |    269999 |         99.56

nagios       |       178 |     37185 |         99.52

kccm         |      1431 |    214501 |         99.34

kbbm         |   1944006 | 157383222 |         98.78

 

 

Thanks,

Daulat

 

From: Hans Schou <hans.schou@xxxxxxxxx>
Sent: Sunday, June 30, 2019 11:35 AM
To: Daulat Ram <Daulat.Ram@xxxxxxxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx
Subject: Re: Memory settings

 

 

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@xxxxxxxxxxxxxxx> 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