v20.1.1 Regression: Grant on all tables is Very Slow #50165
Description
Describe the problem
Please describe the issue you observed, and any steps we can take to reproduce it:
When running a statement like: grant all on * to "test"; it takes nearly 1 minute to execute on a cluster with 6 nodes.
To Reproduce
I wrote a script to test this use case. It spins up 9 nodes, creates 100 tables, and then runs a grant command. If you run it with 19.1.1 or 19.2.2 the grant takes about 400ms. On 20.1.1 it takes 4 seconds.
To use, save the script somewhere then call it with bash /tmp/script.sh 20.1.1 (or 19.2.2 to see the difference.)
#!/usr/bin/env bash
set -e -o pipefail -u
version="${1:-20.1.1}"
dir=$( mktemp -d )
cd "$dir"
function cockroach(){
if ! [[ -x /tmp/cockroach-v$version.darwin-10.9-amd64/cockroach ]]; then
(
cd /tmp/;
curl https://binaries.cockroachdb.com/cockroach-v$version.darwin-10.9-amd64.tgz | tar -xz
)
fi
command /tmp/cockroach-v$version.darwin-10.9-amd64/cockroach "${@}"
}
cockroach version
num_nodes=9
num_tables=100
startsqlport=26257
starthttpport=8080
join=""
for (( i=0; i<9; i++ )); do
join="$join,localhost:$(( startsqlport + i ))"
done
join="${join#,}"
pids=()
for (( i=0; i<num_nodes; i++ )); do
cockroach start \
--insecure \
--store="node$i" \
--listen-addr=localhost:$(( $startsqlport + $i )) \
--http-addr=localhost:$(( $starthttpport + $i )) \
--join="$join" \
--background
done
until cockroach init --insecure --host localhost:$startsqlport || echo "select 1;" | cockroach sql --insecure --host localhost:$startsqlport; do
sleep 1
done
command=()
command+=( "create user if not exists test;" )
for (( i=0; i<num_tables; i++ )); do
command+=( "create table if not exists test$i ();" )
done
command+=( "grant all on * to test;" )
printf "%s\n" "${command[@]}" | cockroach sql --insecure --host localhost:$startsqlport
for (( i=0; i<num_nodes; i++ )); do
args=()
if [[ $version == 20.1.1 ]]; then
args+=( "--drain-wait" "5s" )
fi
cockroach quit "${args[@]}" --insecure --host localhost:$(( $startsqlport + $i )) &
done
wait
rm -r "$dir"Expected behavior
Grants should happen quickly.
Environment:
- CockroachDB version: 20.1.1
- Server OS: Linux running in GKE across 2 or 3 regions.
- The test script (above) I run on MacOS 10.14.5