Jump to content
Sign in to follow this  

postgrsql server slows down as cache gets consumed

Recommended Posts

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
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
Thank you

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this