Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Testing query scalability with concurrency
# Requires : employees dataset (https://dev.mysql.com/doc/employee/en/)
# Then just create table employees_idx_on_hire_date like employees; alter table employees_idx_on_hire_date add key (hire_date);
# Pick one of these:
cat<<EOF>query.txt
select * from employees.employees_idx_on_hire_date order by hire_date desc limit 10
EOF
cat<<EOF>query.txt
select * from employees.employees order by hire_date desc limit 10
EOF
# set these:
c=10
i=1000
# run mysqlslap:
mysqlslap --csv -c $c -i $i --create-schema=employees --no-drop -q query.txt
# csv output will be:
benchmark, avg # of secs to run all queries, min # of secs to run all queries, max # of seconds to run all queries, # of clients, avg # of queries per client
# so with csv output, here's a rough way to benchmark different concurrency levels and save the output in a way that can be consumed by R or MySQL and then RMySQL
# this will be run on a 2core box so even 16 is a stretch but ...
i=100
for c in 1 2 4 8 12 16; do
for query in "select * from employees.employees_idx_on_hire_date order by hire_date desc limit 10" "select * from employees.employees order by hire_date desc limit 10"; do
echo -n "$query" # first field in the csv will be query type
mysqlslap --csv -c $c -i $i --create-schema=employees --no-drop -q "$query"
done
done | tee results.csv
# rest is not valid shell script, just saved MySQL CLI output:
mysql [localhost] {msandbox} (r_input) > show create table mysqlslap_input_20131202_2009\G
*************************** 1. row ***************************
Table: mysqlslap_input_20131202_2009
Create Table: CREATE TABLE `mysqlslap_input_20131202_2009` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`query` text,
`benchmark` tinytext,
`avg_num_secs` float DEFAULT NULL,
`min_num_secs` float DEFAULT NULL,
`max_num_secs` float DEFAULT NULL,
`num_clients` int(11) DEFAULT NULL,
`avg_query_per_client` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=latin1
mysql [localhost] {msandbox} (r_input) > load data infile '/Users/fernandoipar/percona/tmp/mysqlslap_input.csv' into table mysqlslap_input_20131202_2009 fields terminated by ',' enclosed by '"' (query,benchmark,avg_num_secs,min_num_secs,max_num_secs,num_clients,avg_query_per_client);
# also not valid shell, saved from RStudio:
> con <- dbConnect(MySQL(), user="msandbox", password="msandbox", dbname="r_input", host="127.0.0.1", port=5527)
> benchmark_results <- dbReadTable(con, "mysqlslap_input_20131202_2009")
> p <- ggplot(benchmark_results, aes(num_clients,avg_num_secs))
> p + geom_point(aes(colour = factor(query)))
# and using the same benchmark_results data, this is more what I want:
> qplot(data=benchmark_results,x=num_clients,y=avg_num_secs,color=query_type)
# and the same thing but using facets:
> qplot(data=benchmark_results,x=num_clients,y=avg_num_secs,color=query_type,facets=~query_type)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.