From f2478ab2dfe7ff45b8fb01ba016d4d0cfa8a909e Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas Date: Tue, 13 Aug 2024 13:27:03 +0300 Subject: [PATCH v2 2/6] XXX: add perf test This is not intended to be merged. But it's been useful to have this in the tree for some quick perf testing during development. To run it, I've used: (cd build-release && ninja && rm -rf tmp_install && meson test --suite setup --suite test_misc; grep TEST testrun/test_misc/000_csn_perf/log/regress_log_000_csn_perf ) It runs the other test_misc tests concurrently, but they finish a lot faster so they don't affect the results much. --- src/test/modules/test_misc/meson.build | 1 + src/test/modules/test_misc/t/000_csn_perf.pl | 139 +++++++++++++++++++ 2 files changed, 140 insertions(+) create mode 100644 src/test/modules/test_misc/t/000_csn_perf.pl diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build index 283ffa751a..e55e80af54 100644 --- a/src/test/modules/test_misc/meson.build +++ b/src/test/modules/test_misc/meson.build @@ -9,6 +9,7 @@ tests += { 'enable_injection_points': get_option('injection_points') ? 'yes' : 'no', }, 'tests': [ + 't/000_csn_perf.pl', 't/001_constraint_validation.pl', 't/002_tablespace.pl', 't/003_check_guc.pl', diff --git a/src/test/modules/test_misc/t/000_csn_perf.pl b/src/test/modules/test_misc/t/000_csn_perf.pl new file mode 100644 index 0000000000..4ad7d7e5eb --- /dev/null +++ b/src/test/modules/test_misc/t/000_csn_perf.pl @@ -0,0 +1,139 @@ + +# Copyright (c) 2021-2024, PostgreSQL Global Development Group + +# Verify that ALTER TABLE optimizes certain operations as expected + +use strict; +use warnings FATAL => 'all'; +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; +use Time::HiRes qw(time); + +# Initialize a test cluster +my $primary = PostgreSQL::Test::Cluster->new('primary'); +$primary->init(); +# Turn message level up to DEBUG1 so that we get the messages we want to see +$primary->append_conf('postgresql.conf', 'max_wal_senders = 5'); +$primary->append_conf('postgresql.conf', 'wal_level=replica'); +$primary->start; +$primary->backup('bkp'); + +my $replica = PostgreSQL::Test::Cluster->new('replica'); +$replica->init_from_backup($primary, 'bkp', has_streaming => 1); +$replica->append_conf('postgresql.conf', "shared_buffers='1 GB'"); +$replica->start; + +sub wait_catchup +{ + my ($primary, $replica) = @_; + + my $primary_lsn = + $primary->safe_psql('postgres', "SELECT pg_current_wal_lsn()"); + my $caughtup_query = + "SELECT '$primary_lsn'::pg_lsn <= pg_last_wal_replay_lsn()"; + $replica->poll_query_until('postgres', $caughtup_query) + or die "Timed out while waiting for standby to catch up"; +} + +sub repeat_and_time_sql +{ + my ($name, $node, $repeats, $sql) = @_; + + my $begin_time = time(); + + local $ENV{PGOPTIONS} = "-c max_parallel_workers_per_gather=0"; + $node->pgbench( + "--no-vacuum --client=1 --protocol=prepared --transactions=$repeats", + 0, + [qr{processed: $repeats/$repeats}], + [qr{^$}], + $name, + { + "000_csn_perf_$name" => $sql + }); + + my $end_time = time(); + my $elapsed = $end_time - $begin_time; + + pass ("TEST $name: $elapsed"); +} + +# TEST 1: A transaction is open in primary that inserted a lot of +# rows. SeqScan the table on the replica. It sees all the XIDs as not +# in-progress + +$primary->safe_psql('postgres', 'CREATE TABLE tbl(i int)'); + +my $primary_session = $primary->background_psql('postgres', on_error_die => 1); +$primary_session->query_safe("BEGIN;"); +$primary_session->query_safe("INSERT INTO tbl SELECT g FROM generate_series(1, 100000) g;"); + +# Consume one more XID, to bump up "last committed XID" +$primary->safe_psql('postgres', "select txid_current()"); + +wait_catchup($primary, $replica); + +repeat_and_time_sql("large-xact", $replica, 5000, "select count(*) from tbl"); + +$primary_session->quit; +$primary->safe_psql('postgres', "DROP TABLE tbl"); + +# TEST 2: Like 'large-xact', but with lots of subxacts + +$primary->safe_psql('postgres', 'CREATE TABLE tbl(i int)'); + +$primary_session = $primary->background_psql('postgres', on_error_die => 1); +$primary_session->query_safe("BEGIN;"); +$primary_session->query_safe(q{ +do $$ + begin + for i in 1..100000 loop + begin + insert into tbl values (i); + exception + when others then raise 'fail: %', sqlerrm; + end; + end loop; + end +$$; +}); + +# Consume one more XID, to bump up "last committed XID" +$primary->safe_psql('postgres', "select txid_current()"); + +wait_catchup($primary, $replica); + +repeat_and_time_sql("many-subxacts", $replica, 5000, "select count(*) from tbl"); + +$primary_session->quit; +$primary->safe_psql('postgres', "DROP TABLE tbl"); + + +# TEST 3: A mix of a handful of different subxids + +$primary->safe_psql('postgres', 'CREATE TABLE tbl(i int)'); + +$primary_session = $primary->background_psql('postgres', on_error_die => 1); +$primary_session->query_safe("INSERT INTO tbl SELECT g FROM generate_series(1, 100000) g;"); +$primary_session->query_safe("VACUUM FREEZE tbl;"); +$primary_session->query_safe("BEGIN;"); + +my $batches = 10; +for(my $i = 0; $i < $batches; $i++) { + $primary_session->query_safe("SAVEPOINT sp$i"); + $primary_session->query_safe("DELETE FROM tbl WHERE i % $batches = $i"); +} + +# Consume one more XID, to bump up "last committed XID" +$primary->safe_psql('postgres', "select txid_current()"); + +wait_catchup($primary, $replica); + +repeat_and_time_sql("few-subxacts", $replica, 5000, "select count(*) from tbl"); + +$primary_session->quit; +$primary->safe_psql('postgres', "DROP TABLE tbl"); + + +done_testing(); -- 2.39.2