Обсуждение: UPGRADE TO PG11 CAUSED DEGREDATION IN PERFORMANCE
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
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..
Hi
ne 18. 8. 2019 v 8:57 odesílatel Mariel Cherkassky <mariel.cherkassky@gmail.com> napsal:
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_statements2.run the applicative flow3.collect everything from pg_stat_statementsI 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.
first, did you run VACUUM ANALYZE after upgrade?
Regards
Pavel
my env settings :60GB RAM16CPUregular HDpostgresql.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 = 30259MBThe 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..
On Sun, Aug 18, 2019 at 1:57 AM Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote: > > 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_statementsand sumed it up. Since you have performance data, do you see any trends? For example, is it generalized performance issues or are there specific queries that have degraded? We would need more specific detail before being able to give better advice on how to fix performance issue. merlin