Re: Perplexing, regular decline in performance

On Tue, 25 Jun 2019 at 11:55, Benjamin Scherrey <scherrey(at)proteus-tech(dot)com>
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!