Changing Postgres 9.6 to behave more like 9.5 I don't hold myself out as being as a professional database administrator, however I recently upgraded a non-trivial size database (about 100 gigs on disk) from Postgres 9.5 to 9.6.

The database was handling OK under load in Postgres 9.5, however it becomes unusably slow in 9.6.   The only thing that changed was the version of Postgres (which was upgraded using the pg_upgrade tool) - same Postgresql.conf file.

We have run a VACUUM ANALYZE, and this does not seem to have made any difference.

When I analyze a typical query which was taking > 5 seconds under load, it executes in 50ms.  When the system is under load, uptime (on an 8 core cpu) reports values of > 30 and VMSTAT shows all available CPU is being used (with no swap and negligible disk IO)

On the advice of "experts" the Postgres configuration (in 9.5) was tuned with the following options - which are in the 9.6 config as well -

autovacuum_analyze_scale_factor = 0.05
autovacuum_analyze_threshold = 100
autovacuum_max_workers = 10
autovacuum_naptime = 5min
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_threshold = 100
vacuum_cost_limit = 800

bgwriter_delay = 100ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4

checkpoint_timeout = 15min
checkpoint_warning = 5min
cpu_tuple_cost = 0.03
effective_cache_size = 50GB
maintenance_work_mem = 1GB
shared_buffers = 8GB
wal_buffers = 64MB
work_mem = 60MB


Are there any settings I can change to make Postgres 9.6 behave more like 9.5, so that I can work out what is causing the issue?

Does anyone have an insight into what might be causing Postgres to eat up all the available CPU ?

How can I go about debugging/getting a grip on the problem in a production environment ?
The issue we were having turned out to be Transparent Huge pages.  I disabled this in the kernel and on Postgres, and things went back to how they were prior to the upgrade to Postgres 9.6