performance degredation after upgrade from 9.6 to 12

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org, pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: performance degredation after upgrade from 9.6 to 12
Date: 2019-11-24 12:53:19
Message-ID: CA+t6e1n1oO2pJLJqPYtt_hZiQhWakF54D9ifXm4403ij3G9LLw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-performance

Hey all,
I'm testing performance of two identical machines one in 9.6 and the second
one is in 12. The second machine is a clone of the first one + db upgrade
to 12 beta 3 (Yes I'm aware 12.1 was released).

machine stats :
32gb ram
8 cpu
regular hd (not ssd)

my postgresql.confg settings:

max_wal_size = 2GB
min_wal_size = 1GB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 30min
log_checkpoints = on
log_lock_waits = on
log_temp_files = 1024
log_min_duration_statement = 1000
log_autovacuum_min_duration = 5000
autovacuum_max_workers = 4
autovacuum_vacuum_cost_limit = 800
autovacuum_vacuum_cost_delay = 10ms
standard_conforming_strings = off
max_locks_per_transaction = 5000
max_connections = 500
log_line_prefix = '%t %d %p '
random_page_cost = 2.0
deadlock_timeout = 5s
shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 32764
maintenance_work_mem = 250MB
work_mem = 32MB
shared_buffers = 8058MB
effective_cache_size = 16116MB

in 12v I also added the following settings :
log_directory = 'pg_log'
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
max_worker_processes = 8 # (change requires restart)
max_parallel_workers_per_gather = 4 # taken from max_parallel_workers
max_parallel_workers = 8 # maximum number of max_worker_pr

I tested a few applications flows and I saw that the 9.6 version is faster.
I also did a few simple tests (enabled \timing) :

12v :
postgres=# create table test1 as select generate_series(1,10000);
SELECT 10000
Time: 35.099 ms

postgres=# select count(*) from test1;
count
-------
10000
(1 row)

Time: 4.819 ms

9.6v :
postgres=# create table test1 as select generate_series(1,10000);
SELECT 10000
Time: 19.962 ms

postgres=# select count(*) from test1;
count
-------
10000
(1 row)

Time: 1.541 ms

Any idea what can cause it ? What can I check?
This degredation is visible in many queries that we use ..

After the upgrade to 12v version I run analyze on all tables..

Thanks.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Thomas Poty 2019-11-24 13:15:16 Re: performance degredation after upgrade from 9.6 to 12
Previous Message swadesh mondal 2019-11-23 23:21:01 Re: Trouble setting up Streaming Replication with Barman

Browse pgsql-performance by date

  From Date Subject
Next Message Thomas Poty 2019-11-24 13:15:16 Re: performance degredation after upgrade from 9.6 to 12
Previous Message Tomas Vondra 2019-11-22 19:36:02 Re: Postgresql planning time too high