Re: Prepared transaction releasing locks before deregistering its GID

[Michael Paquier]

On Mon, Feb 25, 2019 at 02:28:23PM +0900, Michael Paquier wrote:
> Done.  I have spent some time today looking at the performance of the
> patch, designing a worst-case scenario to see how much bloat this adds
> in COMMIT PREPARED by running across many sessions 2PC transactions
> taking SHARE locks across many tables, as done in the script attached.

And of course I forgot the script, which is now attached.
--
Michael

#!/bin/bash

NUM_SESSIONS=100
NUM_TABLES=300
RUNTIME=30

# Create utility function for creating multiple tables and
# be able to lock them.
psql <<EOF
CREATE OR REPLACE FUNCTION create_tables(num_tables int)
  RETURNS VOID AS
  \$func\$
  BEGIN
  FOR i IN 1..num_tables LOOP
    EXECUTE format('
      CREATE TABLE IF NOT EXISTS %I (id int)', 't_' || i);
  END LOOP;
END
\$func\$ LANGUAGE plpgsql;

-- Lock many tables at once
CREATE OR REPLACE FUNCTION lock_tables(num_tables int)
  RETURNS VOID AS
  \$func\$
  BEGIN
  FOR i IN 1..num_tables LOOP
    EXECUTE format('LOCK t_' || i || ' IN SHARE MODE');
  END LOOP;
END
\$func\$ LANGUAGE plpgsql;
SELECT create_tables($NUM_TABLES);
EOF

# Create set of pgbench scripts
for i in `seq 1 $NUM_SESSIONS`; do
        cat <<EOF > bench_script_$i.sql
BEGIN;
SELECT lock_tables($NUM_TABLES);
PREPARE TRANSACTION 't_$i';
COMMIT PREPARED 't_$i';
EOF
done

# And do the runs with their results in dedicated log files
for i in `seq 1 $NUM_SESSIONS`; do
        pgbench -T $RUNTIME -n -f bench_script_$i.sql > bench_log_$i.txt &
done

signature.asc
Description: PGP signature