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 an 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 to much time in those queries.

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

Thanks in advance!

  • What method did you use to migrate: pg_upgrade, or a dump/reload? Did you do a vacuum analyze afterwards? – jjanes Jan 14 '19 at 23:55
  • 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 – Juan I. Morales Pestana Jan 15 '19 at 0:01
  • 1
    What data didn't migrate? I wonder if that would be a known limitation, or a bug. Did you do the dump/restore into the same server you had just upgraded? If so, I wouldn't do that, if you are going to restore do it into a fresh instance. I think the next step would be to pick a query which got slower, and do a "EXPLAIN (ANALYZE)" on it under both systems. – jjanes Jan 15 '19 at 16:29

Your Answer

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

Browse other questions tagged or ask your own question.