Thread: performance degredation after upgrade from 9.6 to 12
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
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
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
SELECT 10000
Time: 35.099 ms
postgres=# select count(*) from test1;
count
-------
10000
(1 row)
Time: 4.819 ms
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
SELECT 10000
Time: 19.962 ms
postgres=# select count(*) from test1;
count
-------
10000
(1 row)
Time: 1.541 ms
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.
Hello,
did you run ananlyze on your db?
Le dim. 24 nov. 2019 à 13:53, Mariel Cherkassky <mariel.cherkassky@gmail.com> 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 ram8 cpuregular 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 = 16116MBin 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_prI 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 mspostgres=# select count(*) from test1;
count
-------
10000
(1 row)
Time: 4.819 ms9.6v :postgres=# create table test1 as select generate_series(1,10000);
SELECT 10000
Time: 19.962 mspostgres=# select count(*) from test1;
count
-------
10000
(1 row)
Time: 1.541 msAny 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.
Hello,
did you run ananlyze on your db?
Le dim. 24 nov. 2019 à 13:53, Mariel Cherkassky <mariel.cherkassky@gmail.com> 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 ram8 cpuregular 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 = 16116MBin 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_prI 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 mspostgres=# select count(*) from test1;
count
-------
10000
(1 row)
Time: 4.819 ms9.6v :postgres=# create table test1 as select generate_series(1,10000);
SELECT 10000
Time: 19.962 mspostgres=# select count(*) from test1;
count
-------
10000
(1 row)
Time: 1.541 msAny 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.
On Sun, Nov 24, 2019 at 7:53 AM Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
The second machine is a clone of the first one + db upgrade to 12 beta 3 (Yes I'm aware 12.1 was released).
So then fix it. Why spend time investigating obsolete software? Was 12Beta3 compiled with --enable-cassert?
Cheers,
Jeff
On Sun, Nov 24, 2019 at 7:53 AM Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
The second machine is a clone of the first one + db upgrade to 12 beta 3 (Yes I'm aware 12.1 was released).
So then fix it. Why spend time investigating obsolete software? Was 12Beta3 compiled with --enable-cassert?
Cheers,
Jeff