Skip to content

Instantly share code, notes, and snippets.

@kmoppel
Last active September 13, 2018 17:17
Show Gist options
  • Save kmoppel/22623a4aeee9f67445eeb7e34afdd8be to your computer and use it in GitHub Desktop.
Save kmoppel/22623a4aeee9f67445eeb7e34afdd8be to your computer and use it in GitHub Desktop.
select
test,
query::char(48),
instance,
--scale,
--clients,
round(mean_time::numeric, 4) mean_time,
round(stddev_time::numeric, 4) stddev_time,
round(((mean_time - mean_time_lag)::numeric / mean_time::numeric )*100, 1) as mean_time_diff,
round(((stddev_time - stddev_time_lag)::numeric / stddev_time::numeric )*100, 1) as stddev_time_diff
from (
select
*,
lag(mean_time) over(partition by test, scale, clients, query::char(48) order by instance desc) as mean_time_lag,
lag(stddev_time) over(partition by test, scale, clients, query::char(48) order by instance desc) as stddev_time_lag
from
my_pg_stat_statements
) a
order by test, query, scale, clients, instance desc;
#!/bin/bash
# Assuming by default that testclusters are on localhost
# Specified DB will also be used for storing pg_stat_statement snapshots from all clusters for easier analyzing
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=postgres
PGBENCH=/usr/pgsql-9.6/bin/pgbench
PSQL=/usr/pgsql-9.6/bin/psql
# Add/remove hosts as needed here
connection_str[0]='' # 1st instance doesn't need adjusting
connection_name[0]="9.6"
connection_str[1]='-p 5433'
connection_name[1]="10beta1"
TEST_MODE="analytics"
SCALE="100" # 100 = 10mio pgbench_accounts rows
TEST_DURATION=300 # 5min
LOOPS_EACH_INSTANCE=12
TEST_QUERIES=$(cat <<-HERE
SELECT sum(abalance) FROM pgbench_accounts CROSS JOIN generate_series(1, 5); \n
SELECT count(*) FROM pgbench_accounts JOIN pgbench_accounts_copy using (aid); \n
SELECT count(*) FROM (SELECT aid, bid, count(*) FROM pgbench_accounts GROUP BY CUBE (aid, bid)) a;
HERE
)
# Test conn and do pg_stat_statement and pgbench setup
for conn_str in "${connection_str[@]}" ; do
$PSQL $conn_str -qXc "select 1" &>/dev/null
if [ "$?" -ne 0 ] ; then
echo "could not connect to $conn_str, check connection params. exiting..."
exit 1
fi
$PSQL $conn_str -qXc "create extension if not exists pg_stat_statements"
echo "init scale $SCALE ..."
$PGBENCH -i -q --unlogged-tables --foreign-keys -s $SCALE
$PSQL $conn_str -qXc "drop table if exists pgbench_accounts_copy"
$PSQL $conn_str -qXc "create unlogged table if not exists pgbench_accounts_copy as select * from pgbench_accounts"
$PSQL $conn_str -qXc "create unique index if not exists pgbench_accounts_copy_aid_idx ON pgbench_accounts_copy (aid)"
$PSQL $conn_str -qXc "vacuum analyze pgbench_accounts_copy"
$PSQL $conn_str -qXc "select pg_stat_statements_reset()"
done
# Initialize the table for storing pg_stat_statement results
$PSQL -qc "create table if not exists my_pg_stat_statements as select ''::text as testset, ''::text as mode, 0 as scale, 0 as clients, now() as created_on, * from pg_stat_statements where false"
#$PSQL -qc "truncate table my_pg_stat_statements"
# Test
for loop in $(seq 1 ${LOOPS_EACH_INSTANCE}) ; do
i=0
for conn_str in "${connection_str[@]}" ; do
echo "doing loop $loop on ${connection_name[i]} - scale $scale, duration $TEST_DURATION s ..."
echo -e $TEST_QUERIES | $PGBENCH $conn_str -T $TEST_DURATION -f-
i=$((i + 1))
done
done
# Store results for this run
i=0
for conn_str in "${connection_str[@]}" ; do
echo "storing pg_stat_statements results for ${connection_name[i]}..."
$PSQL $conn_str -qXc "copy (select '${connection_name[$i]}', '$TEST_MODE', ${SCALE:-NULL}, ${clients:-1}, now(), * from pg_stat_statements where query ~ '^(INSERT|UPDATE|SELECT).*pgbench_') to stdout" \
| $PSQL -qXc "copy my_pg_stat_statements from stdin"
i=$((i + 1))
done
echo "done"
#!/bin/bash
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=postgres
PSQL=/usr/pgsql-9.6/bin/psql
PGBENCH=/usr/pgsql-9.6/bin/pgbench
# Add/remove hosts as needed here
connection_str[0]='' # 1st instance doesn't need adjusting
connection_name[0]="9.6"
connection_str[1]='-p 5433'
connection_name[1]="10beta1"
SCALES="1 10 100"
CLIENTS="1 2 4 8 16"
PGBENCH_MODES="select-only" # tpcb-like, simple-update, select-only
TEST_DURATION=100
LOOPS_EACH_INSTANCE=3
# Test conn and do pg_stat_statement and pgbench setup
for conn_str in "${connection_str[@]}" ; do
$PSQL $conn_str -qXc "select 1" &>/dev/null
if [ "$?" -ne 0 ] ; then
echo "could not connect to $conn_str, check connection params. exiting..."
exit 1
fi
$PSQL $conn_str -qXc "create extension if not exists pg_stat_statements"
$PSQL $conn_str -qXc "select pg_stat_statements_reset()"
done
# Initialize the table for storing pg_stat_statement results
$PSQL -qXc "create table if not exists my_pg_stat_statements as select ''::text as instance, ''::text as test, 0 as scale, 0 as clients, now() as created_on, * from pg_stat_statements where false"
#$PSQL -qXc "truncate table my_pg_stat_statements"
# Test
for scale in $SCALES ; do
for conn_str in "${connection_str[@]}" ; do
echo "initializing pgbench - host ${connection_name[i]}, scale $scale ..."
$PGBENCH $conn_str -i -q --unlogged-tables --foreign-keys -s $scale
done
for pgbench_mode in $PGBENCH_MODES ; do
for clients in $CLIENTS ; do
for loop in $(seq 1 $LOOPS_EACH_INSTANCE) ; do
i=0
for conn_str in "${connection_str[@]}" ; do
echo "doing loop $loop for ${connection_name[$i]}, scale $scale, clients $clients, duration $TEST_DURATION s ..."
$PGBENCH $conn_str -T $TEST_DURATION -c $clients -b $pgbench_mode
i=$((i + 1))
done
done
# Store results for this run
i=0
for conn_str in "${connection_str[@]}" ; do
echo "storing pg_stat_statements results for ${connection_name[i]}..."
$PSQL $conn_str -qXc "copy (select '${connection_name[$i]}', '$pgbench_mode', ${scale:-NULL}, ${clients:-1}, now(), * from pg_stat_statements where query ~ '^(INSERT|UPDATE|SELECT).*pgbench_' and query !~ 'ONLY') to stdout" \
| $PSQL -qXc "copy my_pg_stat_statements from stdin"
$PSQL $conn_str -qXc "select pg_stat_statements_reset()"
i=$((i + 1))
done
done
done
done
echo "done"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment