PostgreSQL decrease performance after migration

Question

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:

shared_buffers = 10000MB
work_men= 64MB
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.

Those statements you are seeing are not "internal" statements of the Postgres server. This looks more like statements sent from your application (or a data access framework that is used by your application) — user330315
– user330315, Commented Jan 15, 2019 at 6:40
@a_horse_with_no_name this is just doing a log in action for selecting a user permissions and other stof in order to render the main dashboard. It is making select over information_schema.coulmns table too, I'm not making those queries — Juan I. Morales Pestana
– Juan I. Morales Pestana, Commented Jan 15, 2019 at 12:11
Well, you might not be running those statement, but Postgres is not running them either. It must be something in your software stack. — user330315
– user330315, Commented Jan 15, 2019 at 12:12

jmunsch · Accepted Answer · 2021-06-04 19:08:26Z

2

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

answered Jun 4, 2021 at 19:08

jmunsch

24.3k12 gold badges102 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Preiss · Accepted Answer · 2019-01-22 15:32:45Z

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.

Collectives™ on Stack Overflow

PostgreSQL decrease performance after migration

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related