0

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 ?

  • How is disk space? – Vérace Oct 17 '17 at 1:32
  • You need to show an explain analyze on 9.5 and 9.6 that is running differently. – Evan Carroll Oct 17 '17 at 2:11
  • @Vérace There is ample free diskspace - just under 100 gigs free. – davidgo Oct 17 '17 at 2:22
  • @EvanCarrollQWERHJKL I can't run an explain analyze on 9.5 because I no longer have an instance of it. Also, the explain analyze done on 9.6 showed a very low cost (47u) on the query which took over 5000 secs when the system was under stress. – davidgo Oct 17 '17 at 2:24
  • so show the result when the system is under stress. – Evan Carroll Oct 17 '17 at 2:26
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.