NL1dkVTd

1#!/bin/bash
2
3# Assuming by default that testclusters are on localhost
4# Specified DB will also be used for storing pg_stat_statement snapshots from all clusters for easier analyzing
5export PGHOST=/var/run/postgresql
6export PGPORT=5432
7export PGUSER=postgres
8export PGDATABASE=postgres
9
10PGBENCH=/usr/lib/postgresql/11/bin/pgbench
11PSQL=/usr/lib/postgresql/11/bin/psql
12
13# Add/remove hosts as needed here
14connection_str[0]=''		# 1st instance doesn't need adjusting
15connection_name[0]="10"
16connection_str[1]='-p 5433'
17connection_name[1]="11rc1"
18
19TEST_MODE="analytics"
20SCALE="100"	# 100 = 10mio pgbench_accounts rows
21TEST_DURATION=600	# 10min
22LOOPS_EACH_INSTANCE=36
23DO_PGBENCH_INIT=1
24
25TEST_QUERIES=$(cat <<-HERE
26SELECT sum(abalance) FROM pgbench_accounts CROSS JOIN generate_series(1, 5) where aid % 2 = 0; \n
27SELECT count(*) FROM pgbench_accounts JOIN pgbench_accounts_copy using (aid) where aid % 2 = 0; \n
28SELECT count(*) FROM (SELECT aid, bid, count(*) FROM pgbench_accounts where aid % 2 = 0 GROUP BY CUBE (aid, bid)) a;\n
29SELECT COUNT(DISTINCT aid) FROM pgbench_accounts where aid % 2 = 0;
30HERE
31)
32
33
34# Test conn and do pg_stat_statement and pgbench setup
35for conn_str in "${connection_str[@]}" ; do
36
37	$PSQL $conn_str -qXc "select 1" &>/dev/null
38	if [ "$?" -ne 0 ] ; then
39		echo "could not connect to $conn_str, check connection params. exiting..."
40		exit 1
41	fi
42
43	if [ "$DO_PGBENCH_INIT" -gt 0 ] ; then
44
45	echo "init scale $SCALE ..."
46	$PGBENCH "$conn_str" -i -q --unlogged-tables --foreign-keys -s $SCALE
47	$PSQL $conn_str -qXc "drop table if exists pgbench_accounts_copy"
48	$PSQL $conn_str -qXc "create unlogged table if not exists pgbench_accounts_copy as select * from pgbench_accounts"
49	$PSQL $conn_str -qXc "create unique index if not exists pgbench_accounts_copy_aid_idx ON pgbench_accounts_copy (aid)"
50	$PSQL $conn_str -qXc "vacuum analyze pgbench_accounts_copy"
51
52	fi
53	
54	$PSQL $conn_str -qXc "checkpoint"
55	$PSQL $conn_str -qXc "create extension if not exists pg_stat_statements"
56	$PSQL $conn_str -qXc "select pg_stat_statements_reset()"		
57done
58
59
60# Initialize the table for storing pg_stat_statement results
61$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"
62#$PSQL -qc "truncate table my_pg_stat_statements"
63
64
65# Test
66for loop in $(seq 1 ${LOOPS_EACH_INSTANCE}) ; do
67	i=0
68	for conn_str in "${connection_str[@]}" ; do
69		echo "doing loop $loop on ${connection_name[i]} - scale $scale, duration $TEST_DURATION s ..."
70		echo -e $TEST_QUERIES |  $PGBENCH $conn_str -T $TEST_DURATION -f-
71		i=$((i + 1))
72	done
73done
74
75# Store results for this run
76i=0
77for conn_str in "${connection_str[@]}" ; do
78	echo "storing pg_stat_statements results for ${connection_name[i]}..."
79	$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" \
80	| $PSQL -qXc "copy my_pg_stat_statements from stdin"
81	i=$((i + 1))
82done
83
84echo "done"