performance degredation after upgrade from 9.6 to 12

Поиск
Список
Период
Сортировка
От Mariel Cherkassky
Тема performance degredation after upgrade from 9.6 to 12
Дата
Msg-id CA+t6e1n1oO2pJLJqPYtt_hZiQhWakF54D9ifXm4403ij3G9LLw@mail.gmail.com
обсуждение исходный текст
Ответы Re: performance degredation after upgrade from 9.6 to 12  (Thomas Poty <thomas.poty@gmail.com>)
Re: performance degredation after upgrade from 9.6 to 12  (Thomas Poty <thomas.poty@gmail.com>)
Re: performance degredation after upgrade from 9.6 to 12  (Jeff Janes <jeff.janes@gmail.com>)
Re: performance degredation after upgrade from 9.6 to 12  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-admin
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.

В списке pgsql-admin по дате отправления:

Предыдущее
От: swadesh mondal
Дата:
Сообщение: Re: Trouble setting up Streaming Replication with Barman
Следующее
От: Thomas Poty
Дата:
Сообщение: Re: performance degredation after upgrade from 9.6 to 12