Re: performance degredation after upgrade from 9.6 to 12

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello, 
did you run ananlyze on your db? 

Le dim. 24 nov. 2019 à 13:53, Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> a écrit :
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.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux