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 ?