Ubuntu setup
sudo apt-get update
sudo apt-get install \
git automake autoconf libtool make gcc \
libmysqlclient-dev mysql-client-5.6
git clone https://github.com/akopytov/sysbench.git
./autogen.sh
./configure
make -j8
Test variables
export test_system=<test name>
export mysql_host=<mysql host>
export mysql_user=<mysql user>
export mysql_password=<mysql password>
export test_path=~/oltp_${test_system}_1
export test_name=01_baseline
Prepare test data
sysbench/sysbench \
--mysql-host=${mysql_host} \
--mysql-user=${mysql_user} \
--mysql-password=${mysql_password} \
--mysql-db="sbtest" \
--test=sysbench/tests/db/parallel_prepare.lua \
--oltp_tables_count=100 \
--oltp-table-size=20000000 \
--rand-init=on \
--num-threads=16 \
run
Run the benchmark:
mkdir -p ${test_path}
for threads in 1 2 4 8 16 32 64 128 256 512 1024
do
sysbench/sysbench \
--mysql-host=${mysql_host} \
--mysql-user=${mysql_user} \
--mysql-password=${mysql_password} \
--mysql-db="sbtest" \
--db-ps-mode=disable \
--rand-init=on \
--test=sysbench/tests/db/oltp.lua \
--oltp-read-only=off \
--oltp_tables_count=100 \
--oltp-table-size=20000000 \
--oltp-dist-type=uniform \
--percentile=99 \
--report-interval=1 \
--max-requests=0 \
--max-time=1800 \
--num-threads=${threads} \
run
Format the results:
Capture results in CSV format
grep "^\[" ${test_path}/${test_name}_*.out \
| cut -d] -f2 \
| sed -e 's/[a-z ]*://g' -e 's/ms//' -e 's/(99%)//' -e 's/[ ]//g' \
> ${test_path}/${test_name}_all.csv
Plot the results in R
status <- NULL # or e.g. "[DRAFT]"
config <- "Amazon RDS (MySQL Multi-AZ, Aurora) vs. Google Cloud SQL Second Generation\nsysbench 0.5, 100 x 20M rows (2B rows total), 30 minutes per step"
steps <- c(1, 2, 4, 8, 16, 32, 64, 128, 256, 512)
time_per_step <- 1800
output_path <- "~/oltp_results/"
test_name <- "01_baseline"
results <- data.frame(
stringsAsFactors = FALSE,
row.names = c(
"amazon_rds_multi_az",
"amazon_rds_aurora",
"google_cloud_sql"
),
file = c(
"~/amazon_rds_multi_az_1/01_baseline_all.csv",
"~/amazon_rds_aurora_1/01_baseline_all.csv",
"~/google_cloud_sql_1/01_baseline_all.csv"
),
name = c(
"Amazon RDS MySQL Multi-AZ",
"Amazon RDS Aurora",
"Google Cloud SQL 2nd Gen."
),
color = c(
"darkgreen",
"red",
"blue"
)
)
results$data <- lapply(results$file, read.csv, header=FALSE, sep=",", col.names=c("threads", "tps", "reads", "writes", "latency", "errors", "reconnects"))
# TPS
pdf(paste(output_path, test_name, "_tps.pdf", sep=""), width=12, height=8)
plot(0, 0,
pch=".", col="white", xaxt="n", ylim=c(0,2000), xlim=c(0,length(steps)),
main=paste(status, "Transaction Rate by Concurrent Sysbench Threads", status, "\n\n"),
xlab="Concurrent Sysbench Threads",
ylab="Transaction Rate (tps)"
)
for(result in rownames(results)) {
tps <- as.data.frame(results[result,]$data)$tps
points(1:length(tps) / time_per_step, tps, pch=".", col=results[result,]$color, xaxt="n", new=FALSE)
}
title(main=paste("\n\n", config, sep=""), font.main=3, cex.main=0.7)
axis(1, 0:(length(steps)-1), steps)
legend("topleft", results$name, bg="white", col=results$color, pch=15, horiz=FALSE)
dev.off()
# Latency
pdf(paste(output_path, test_name, "_latency.pdf", sep=""), width=12, height=8)
plot(0, 0,
pch=".", col="white", xaxt="n", ylim=c(0,2000), xlim=c(0,length(steps)),
main=paste(status, "Latency by Concurrent Sysbench Threads", status, "\n\n"),
xlab="Concurrent Sysbench Threads",
ylab="Latency (ms)"
)
for(result in rownames(results)) {
latency <- as.data.frame(results[result,]$data)$latency
points(1:length(latency) / time_per_step, latency, pch=".", col=results[result,]$color, xaxt="n", new=FALSE)
}
title(main=paste("\n\n", config, sep=""), font.main=3, cex.main=0.7)
axis(1, 0:(length(steps)-1), steps)
legend("topleft", results$name, bg="white", col=results$color, pch=15, horiz=FALSE)
dev.off()
# TPS per Thread
pdf(paste(output_path, test_name, "_tps_per_thread.pdf", sep=""), width=12, height=8)
plot(0, 0,
pch=".", col="white", xaxt="n", ylim=c(0,60), xlim=c(0,length(steps)),
main=paste(status, "Transaction Rate per Thread by Concurrent Sysbench Threads", status, "\n\n"),
xlab="Concurrent Sysbench Threads",
ylab="Transactions per thread (tps/thread)"
)
for(result in rownames(results)) {
tps <- as.data.frame(results[result,]$data)$tps
threads <- as.data.frame(results[result,]$data)$threads
points(1:length(tps) / time_per_step, tps / threads, pch=".", col=results[result,]$color, xaxt="n", new=FALSE)
}
title(main=paste("\n\n", config, sep=""), font.main=3, cex.main=0.7)
axis(1, 0:(length(steps)-1), steps)
legend("topleft", results$name, bg="white", col=results$color, pch=15, horiz=FALSE)
dev.off()
Follow