From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kenneth Marshall <ktm@rice.edu>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL performance problem moving from 9.6.17 to 12.3
Date: Thu, 28 May 2020 12:42:44 -0400
Message-ID: <7007.1590684164@sss.pgh.pa.us> (raw)
In-Reply-To: <20200528155659.GI13462@aart.rice.edu>
References: <20200528155659.GI13462@aart.rice.edu>
Kenneth Marshall <ktm@rice.edu> writes:
> I have a system that was running version 9.6.17 running on a system with
> 48gb of memory and spinning disks front-ed by a HW RAID controller with
> NVRAM cache. We moved to a new box running version 12.3 on a system with
> 64gb of memory and NVME SSD drives. Here are the system config options:
> OLD:
> shared_buffers = 2048MB # min 128kB
> work_mem = 128MB # min 64kB
> maintenance_work_mem = 1024MB # min 1MB
> effective_io_concurrency = 8 # 1-1000; 0 disables prefetching
> max_parallel_workers_per_gather = 0 # taken from max_worker_processes
> effective_cache_size = 24GB
> default_statistics_target = 500 # range 1-10000
> from_collapse_limit = 30
> join_collapse_limit = 30 # 1 disables collapsing of explicit
> seq_page_cost = 1.0 # measured on an arbitrary scale
> random_page_cost = 4.0 # same scale as above
> NEW:
> shared_buffers = 12GB # min 128kB
> work_mem = 128MB # min 64kB
> maintenance_work_mem = 2GB # min 1MB
> effective_io_concurrency = 200 # 1-1000; 0 disables prefetching
> max_worker_processes = 24 # (change requires restart)
> max_parallel_workers_per_gather = 4 # taken from max_parallel_workers
> max_parallel_workers = 24 # maximum number of max_worker_processes that
> seq_page_cost = 1.0 # measured on an arbitrary scale
> random_page_cost = 1.1 # same scale as above for SSDs
> effective_cache_size = 36GB
> default_statistics_target = 500 # range 1-10000
> from_collapse_limit = 30
> join_collapse_limit = 30 # 1 disables collapsing of explicit
Maybe you should be changing fewer variables at one time ...
In particular, decreasing random_page_cost as you've done here is
going to encourage the planner to rely on nestloop-with-inner-indexscan
joins. Does undoing that change improve matters?
I personally think that v12 is way too enthusiastic about invoking
JIT compilation, too. You might want to play with the parameters
for that as well.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, ktm@rice.edu, pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL performance problem moving from 9.6.17 to 12.3
In-Reply-To: <7007.1590684164@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox