CODEWITHSUNDEEP
sqlpostgresqlupgradedatabase-performance
Juan I. Morales Pestana
Juan I. Morales Pestana

Reputation: 1147

PostgreSQL decrease performance after migration

After PostgreSQL database server v9.3 migration to v9.6 I noticed a decreace in the performance of the entire system. The config parameters are the same as in v9.3 taking in to account the next parameters:

  1. shared_buffers = 10000MB
  2. work_men= 64MB
  3. maintenance_work_men = 1024MB

Also I tried to monitor some resources, and this is the result

              total        used        free      shared  buff/cache   available
Mem:            31G        385M        4.5G         10G         26G 19G
Swap:          3.0G          0B        3.0G

Also when I run some queries the server internally launches queries like these ones:

select typname from pg_type where oid=1043
set search path to public
deallocate pdo_stmt_0000000e

And then runs my query but I'm afraid that here is some impact in performance after migration. I have another 9.6 server with a fresh install no migration and it's not presenting that problem (response time). It seems to be expending too much time in those queries.

Do you have any tip or advice on how to fix this?

I did it with pg_upgrade, but I noticed that in the process some data doesn't migrate to v9.6 server. After that I did a dump/restore process and vacuum analyze.

Upvotes: 2

Views: 1592

Answers (2)

jmunsch
jmunsch

Reputation: 24099

In our case, we neglected to:

ANALYZE the database

Postgres specifically might need it after a larger migration.

For example when upgrading django 2.2 to 3.2 and all of the id field types are changed from AutoField to BigAutoField

Upvotes: 2

Martin Preiss
Martin Preiss

Reputation: 396

you could install the pg_stat_statements extension on the slow and the fast system and compare the performance of the top queries in both systems. When there are major differences for the time/execution you can check the execution plans (using explain analyze).

Sometimes new features have a major performance impact after an upgrade. If my memory serves me well the parallel sequential scan - https://blog.2ndquadrant.com/postgresql96-parallel-sequential-scan/ - has been added in 9.6. Though this is basically a great feature, there are some situations in which its use may result in a slowdown of queries. This could be a reason to set parallel_setup_cost (or other planner parameters) to a different value to avoid inefficient parallel sequential scans.

Edited later: As I see in https://www.postgresql.org/docs/9.6/release-9-6.html the parallel query execution has not been activated by default, so it's probably not the reason of the slowdown in your situation. Still I think that only an analysis of the performance of the top queries and their plans may shed light on the issue.

Upvotes: 0

Related Questions