UPGRADE TO PG11 CAUSED DEGREDATION IN PERFORMANCE

Mariel Cherkassky <[email protected]>
Newsgroups gmane.comp.db.postgresql.performance
Message-ID <CA+t6e1k4H2VB2LhGoobd91-bkHBB9=1SugzMckL3WB53oKAz_g@mail.gmail.com>
Hey,
I upgraded my pg9.6 cluster to pg11.2.
As it seems after the upgrade the duration of the same flow in my
application raised from 13 minutes to 19 minutes.

The test I did :
1.reset pg_stat_statements
2.run the applicative flow
3.collect everything from pg_stat_statements

I did this test on the env before the upgrade and after the upgrade. I took
the sum of the total_time in pg_stat_statements and sumed it up.

my env settings :
60GB RAM
16CPU
regular HD

postgresql.conf settings :
max_wal_size = 2GB
min_wal_size = 1GB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 30min
standard_conforming_strings = off
max_locks_per_transaction = 5000
max_connections = 500
random_page_cost = 4
deadlock_timeout = 5s
shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 32764
log_directory = 'pg_log'
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
max_worker_processes = 16               # (change requires restart)
max_parallel_maintenance_workers = 8   # taken from max_parallel_workers
max_parallel_workers_per_gather = 8    # taken from max_parallel_workers
max_parallel_workers = 16
maintenance_work_mem = 333MB
work_mem = 60MB
shared_buffers = 15129MB
effective_cache_size = 30259MB

The conf file was used in 9.6 (without all the new parallel settings).
Now the same queries run in both tests because it was the same flow. I will
be happy to hear if u have any ideas without involving any queries changes..
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.