Hello
I think the most important question for this topic is performance penalty.
It was a long story, first test on my desktop was too volatile. I setup
separate PC with DB only and test few cases.
PC spec: 2-core Intel Core 2 Duo E6550, 4GB ram, mechanical HDD
All tests on top 7dedfd22b79822b7f4210e6255b672ea82db6678 commit, build via
./configure --prefix=/home/melkij/tmp/ --enable-tap-tests
DB settings:
listen_addresses = '*'
log_line_prefix = '%m %p %u@%d from %h [vxid:%v txid:%x] [%i] '
lc_messages = 'C'
shared_buffers = 512MB
pgbench runned from different host, in same L2 network.
Database was generated by: pgbench -s 10 -i -h hostname postgres
After database start I run:
create extension if not exists pg_prewarm;
select count(*), sum(pg_prewarm) from pg_tables join
pg_prewarm(tablename::regclass) on true where schemaname= 'public';
select count(*), sum(pg_prewarm) from pg_indexes join
pg_prewarm(indexname::regclass) on true where schemaname= 'public';
So all data was in buffers.
Load generated by command: pgbench --builtin=select-only --time=300 -n -c 10 -h
hostname postgres -M (vary)
Tests are:
head_no_pgss - unpatched version, empty shared_preload_libraries
head_track_none - unpatched version with:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 5000
pg_stat_statements.track = none
pg_stat_statements.save = off
pg_stat_statements.track_utility = off
head_track_top - the same but with pg_stat_statements.track=top
5-times runned in every mode -M: simple, extended, prepared
patch_not_loaded - build with latest published patches, empty
shared_preload_libraries
patch_track_none - patched build with
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 5000
pg_stat_statements.track = none
pg_stat_statements.save = off
pg_stat_statements.track_utility = off
pg_stat_statements.track_planning = off
patch_track_top - the same but with pg_stat_statements.track=top
patch_track_planning - with:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 5000
pg_stat_statements.track = top
pg_stat_statements.save = off
pg_stat_statements.track_utility = off
pg_stat_statements.track_planning = on
10-times runned in every mode -M: simple, extended, prepared
Results:
test | mode | average_tps | degradation_perc
----------------------+----------+-------------+------------------
head_no_pgss | extended | 13816 | 1.000
patch_not_loaded | extended | 13755 | 0.996
head_track_none | extended | 13607 | 0.985
patch_track_none | extended | 13560 | 0.981
head_track_top | extended | 13277 | 0.961
patch_track_top | extended | 13189 | 0.955
patch_track_planning | extended | 12983 | 0.940
head_no_pgss | prepared | 29101 | 1.000
head_track_none | prepared | 28510 | 0.980
patch_track_none | prepared | 28481 | 0.979
patch_not_loaded | prepared | 28382 | 0.975
patch_track_planning | prepared | 28046 | 0.964
head_track_top | prepared | 28035 | 0.963
patch_track_top | prepared | 27973 | 0.961
head_no_pgss | simple | 16733 | 1.000
patch_not_loaded | simple | 16552 | 0.989
head_track_none | simple | 16452 | 0.983
patch_track_none | simple | 16365 | 0.978
head_track_top | simple | 15867 | 0.948
patch_track_top | simple | 15820 | 0.945
patch_track_planning | simple | 15739 | 0.941
So I found slight slowdown with track_planning = off compared to HEAD. Possibly
just at the level of measurement error. I think this is ok.
track_planning = on also has no dramatic impact. In my opinion proposed design
with pgss_store call is acceptable.
regards, Sergei
