Re: Perplexing, regular decline in performance

[Hugh Ranalli <hugh@xxxxxxx>]

On Tue, 25 Jun 2019 at 11:55, Benjamin Scherrey <scherrey@xxxxxxxxxxxxxxxx> wrote:

Have you done a VACUUM ANALYZE FULL on your database? This needs to be done periodically to inform the server of the statistics of how the data and relations are distributed across the database. Without this bad assumptions by the planner can cause degradation of performance.

 

Autovacuum is enabled. As well, we had problems with autovacum running reliably in 8.2, so we are still running a nightly script that runs VACUUM ANALYZE on the complete database. As for VACUUM ANALYZE FULL, the database underwent a full dump and reload, which, as I understand it, would have rebuilt the indexes, followed by an ANALYZE to update the planner. So I'm not sure a VACUUM ANALYZE FULL would have much effect. I'm also not sure how it bears on the problem stated here, where the planner shouldn't even be looking at this table in the queries we are timing.

Also, if you are using the default settings in postgres.conf then understand those are established to use the absolute minimum amount of resources possible which means not taking advantage of available memory or CPUs that may be available in your environment that would make the database server more performant.

 

No, we attempted to tune these, using  https://pgtune.leopard.in.ua. The following values are from our install script (hence why they don't look exactly like their .conf versions). And, as someone else asked, transparent huge pages are enabled:

# DB Version: 11

# OS Type: linux

# DB Type: web

# Total Memory (RAM): 128 GB

# CPUs = threads per core * cores per socket * sockets

# CPUs num: 256

# Connections num: 250

# Data Storage: ssd

# Set via sysctl

# 64 GB in 4096 byte pages on our 128GB production system

shmall = 15777216

# 48 GB on our 128GB production system

shmmax = 51,539,607,552

# Huge Pages

# Set via sysctl

huge-pages-alloc = 0

shared-buffers = 32GB

work-mem = 1024kB

maintenance-work-mem = 2GB

max-stack-depth = 4MB

effective-io-concurrency = 200

max-parallel-workers-per-gather = 128

max-parallel-workers = 256

#

# postgresql-conf-archive

#

wal-buffers = 16MB

min-wal-size = 1GB

max-wal-size = 2GB

checkpoint-completion-target = 0.7

archive-mode = on

archive-timeout = 900      

#

# postgresql-conf-query

#

# 75% of production memory

effective-cache-size = 96GB

# SSD drives

random-page-cost = 1.1

default-statistics-target = 100

I'll be providing further details in reply to another message in the thread.

Thanks!