Leviya

I migrated a Postgres database 9.1 doing 300K transactions/hour from a server with Red Hat OS, Intel(R) Xeon(R) CPU E5-2670 0 @ 2.60GHz / 16 Core, 64 GB RAM, 240 GB x 4 Intel SSD TO Intel(R) Xeon(R) CPU E5-2680 v4 @ 2.40GHz / 56 Core, 128 GB RAM, 2TB nvme PCI SSD, RANDOM READ 450000 iops, RANDOM WRITE 56000 iops. CentOS 6.9.

Over a period of time the server slows down and the amount of data processed get reduced. If I clear the cache manually (sync; echo 3 > /proc/sys/vm/drop_caches) then the data processing resume to maximum level. Again after some time with load, the performance deteriorates in terms of an amount of data processed. The cache memory shows it has been fully consumed.

pg configuration :

datestyle = 'redwood,show_time'
db_dialect = 'redwood'
default_text_search_config = 'pg_catalog.english'
edb_dynatune = 90
edb_redwood_date = on
edb_redwood_strings = on
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen,$libdir/plugins/plugin_debugger,$libdir/plugins/plugin_spl_debugger'
timed_statistics = off
archive_command = 'rsync -a %p slave:/opt/PostgresPlus/9.1AS/wals/%f'
archive_mode = on
listen_addresses = '*'
log_destination = 'syslog'
syslog_facility = 'LOCAL0'
logging_collector = on
log_line_prefix = '%t'
max_wal_senders = 4
port = 6432
wal_keep_segments = 128
wal_level = hot_standby
temp_buffers='50MB'
constraint_exclusion = on
autovacuum = on
enable_bitmapscan = off
max_connections = 200
shared_buffers = 32GB
effective_cache_size = 96GB
work_mem = 167772kB
maintenance_work_mem = 2GB
checkpoint_segments = 64
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100