Connect to cockroach server on roachprod

prepare.sh

#!/bin/bash

# Print each command before executing it.
set -x

# echo "current commit: $(git rev-parse HEAD)"

# Kill the running cockroach server and remove existing data.
echo "pkill -f cockroach && rm -rf /mnt/data1/cockroach/" | roachprod ssh "jane-test-$1":1

# Put the built binary to roachprod.
roachprod put "jane-test-$1" "./artifacts/cockroach-$1" cockroach

# Start the cockroach server in secure mode.
roachprod start "jane-test-$1" --secure

# Get the certificate from roachprod.
roachprod get "jane-test-$1" ./certs/ca.crt $1-ca.crt

# Change the root user's password.
echo "ALTER USER root WITH PASSWORD 'password';" | roachprod sql "jane-test-$1":1 --secure

# Get the external ip of the cockroach server.
ip=$( roachprod pgurl "jane-test-$1" --external | sed 's/.*root@\([^:]*\).*/\1/' )


# ------ add_user.sql ------
# ALTER USER root WITH PASSWORD 'password';
# SET CLUSTER SETTING server.user_login.upgrade_bcrypt_stored_passwords_to_scram.enabled = false;
# SET CLUSTER SETTING server.user_login.password_encryption = 'crdb-bcrypt';
# CREATE USER myu WITH PASSWORD 'password';
# GRANT CONNECT ON DATABASE defaultdb TO myu;

# Add new user from local.
cat add_user.sql | ./cockroach sql --url "postgres://root:password@$ip:26257?sslmode=verify-full&sslrootcert=$1-ca.crt"

# Stop printing command before execution.
set +x
echo "client latency is stored in client_latency_$1.txt"
echo "" > "client_latency_$1".txt

# Print link to db console.
echo $1 "https://$ip:26258/#/metrics/sql/cluster"


for j in {1..5}
do
	echo "------ round $j ------"
for i in {1..1000}
do
	if ! ((i % 100)); then
    echo "[$j] $1 $i times"
	fi
	# Connect to the database as the newly created user, and redirect the timer's output.
	(TIMEFORMAT=%R time ./cockroach sql --url "postgres://myu:password@$ip:26257?sslmode=verify-full&sslrootcert=$1-ca.crt" -e 'SELECT 1') >> "client_latency_$1".txt 2>&1

done
    sleep 20
done

The script can be run with ./prepare.sh pre and ./prepare.sh post, and accordingly ./artifacts/cockroach-pre and ./artifacts/cockroach-post are binary built before and after cockroachdb/cockroach@a82aa82.

Note that for line 55 above,
for both before and after cockroachdb/cockroach@a82aa82, the total time elapsed is around 500ms.