Testing functionalities and options for a database can be challenging at times, as a live production environment might be required. As I was looking for different options, I was directed by Derek Downey to this post in the Percona blog.
The blog discussed an interesting and fun tool from Percona, tpcc-mysql. I was interested in testing the tool so I decided to play around with it in an AWS EC2 server.
In this post I will expand on the Percona blog post, since the tool lacks documentation, as well as explain how I used it to create a MySQL Benchmark in AWS.
There are various reasons why tpcc-mysql could be a good option to use for a benchmarking project. The following points highlights most of them:
- Mimics a full DB structure of a real warehouse.
- Simulates a real life load on the server.
- Options and flexibility.
- Very light footprint on the system.
- No documentation.
Getting the Server Started
You’ll probably need to launch a new EC2 server from the AWS Console, or use an existing one that you already have up an running. Either way, you had better save the current state of your database. Luckily, AWS EBS offers really good and convenient solution to achieve this.
It is possible to create and manage sanpshots of EBS volumes in the AWS Dashboard with some very basic steps. I personally prefer to setup the MySQL base and data directories together in a different volume from from the root volume. This allows me to swap between different versions and data-sets without having to reconfigure my tools every time I load a snapshot.
Setting up the Benchmark
Once you have taken your snapshot and configured you MySQL, move on to setup. First we’ll need to setup the prerequisites.
tpcc-mysql uses mysql_config is part of the libmysqlclient_dev package. We also need Bazaar. So we’ll go ahead and install that:
sudo apt-get install libmysqlclient_dev sudo apt-get install bzr
Install & Compile spcc-mysql
Use following commands to download the tpcc-mysql source code and compile it:
bzr branch lp:~percona-dev/perconatools/tpcc-mysql cd tpcc-mysql/src make all
Prepare the Database & Create Required Tables
Once the the tpcc-mysql has been compiled, we will need to prepare the database for the benchmark. This will consist of running a few scripts to create the required database, tables, and generate random data to use during the testing process.
Following these steps will create the database and tables made for us, they are all part of the tpcc-mysql package:
cd ~/tpcc-mysql # 1. Create Database to be load data in mysql -u root -p -e "CREATE DATABASE tpcc1000;" # 2. Create the required table definitions mysql -u root -p tpcc1000 < create_table.sql # 3. Add foreign keys and indexes mysql -u root -p tpcc1000 < add_fkey_idx.sql
The following tables are created from the previous step:
$ mysql -u root -p tpcc1000 -e "SHOW TABLES;" Enter password:
+--------------------+ | Tables_in_tpcc1000 | +--------------------+ | customer | | district | | history | | item | | new_orders | | order_line | | orders | | stock | | warehouse | +--------------------+
As you can see, tpcc-mysql mimics a warehouse’s database that tracks clients, items, orders, stock, … etc
Prepare the Database & Create Required Tables
The last step remaining before we can start our test is to populate some data into the tables. For that, tpcc-mysql has a script, tpcc_load, that does the job.
The tpcc_load script generates random dummy data in the tables created in the previous steps. The script also have a parameter that allows to specify how many warehouses you want to simulate.
The script usage is as follow:
tpcc_load [server] [DB] [user] [pass] [warehouse]
In our example, we’ll use the following:
./tpcc-mysql/tpcc_load 127.0.0.1 tpcc1000 root "$pw" 2
Beginning the Benchmarking Process
This would be a good time to take a snapshot of your server/dataset, so you can come back to it. Also, before we get started, let’s get familiar with the script we need to use for starting the benchmarking process, tpcc_start.
The script will start creating transactions that would execute various statements like SELECT, UPDATE, DELETE, and INSERT. The script will also be generating a detailed output of the progress and a summary in the end. You can redirect this output to a file to run some analysis, compare it later on, or use it to run an analysis.
The script comes with various parameters to give you flexibility to configure it as you desire:
tpcc_start -h[server] -P[port] -d[DB] -u[mysql_user] -p[mysql_password] -w[# of warehouses] -c[# of connections] -r[warmup_time] -l[running_time]
Now let’s get to the fun part!
We’ll be using the following command will start a simulation of warehouse transactions, and record the output in the file tpcc-output-01.log
./tpcc_start -h127.0.0.1 -dtpcc1000 -uroot -p -w2 -c16 -r10 -l1200 > ~/tpcc-output-01.log
Analyzing the Output
tpcc-mysql comes with different scripts that could be used for analysis. Check the tpcc-mysql/scripts folder. Example of some scripts are:
$ ls ~/tpcc-mysql/scripts/
analyze_min.sh analyze.sh anal.full.sh analyze_modified.sh ... ...
Visual Analysis of the Output
We can always take these tests a step further in many different directions. Since plotted data is a lot of fun, why not do a quick experiment with it?
The same blog post I used as my reference for this post also has a modified version of analyze.sh script that comes with tpcc-mysql. The script is named tpcc-output-analyze.sh. What this script does is that it extracts the time and # of transactions for each time block in a format that gnuplot can read for plotting the data. So let’s use the script on the output file:
./tpcc-output-analyze.sh tpcc-logs/tpcc-output-01.log tpcc-analyzed/time_tr_data_01.txt
To install gnuplot you simply run:
sudo apt-get install gnuplot
Then, we can create the plot using the tpcc-graph-build.sh script (from here as well) as follows:
./tpcc-graph-build.sh tpcc-analyzed/time_tr_data_01.txt tpcc-graphs/graph01.jpg
And this generated the following plot for me:
I hope this was helpful. As you can see, there is a lot of potential of things that can be done using tpcc-mysql. If there is anything that you come up with or experiment with, I would love to hear it from you.