Personal blog of Yzmir Ramirez

In this blog post, I’ll discuss changes I’ve made to the

tpcc-mysql

 benchmark tool. These changes make it less random and support multi-schema.

This post might only be interesting to performance researchers. The

tpcc-mysql

 benchmark to is what I use to test different hardware (as an example, see my previous post: https://www.percona.com/blog/2016/07/26/testing-samsung-storage-in-tpcc-mysql-benchmark-percona-server/).

The first change is support for multiple schemas, rather than just one schema. Supporting only one schema creates too much internal locking in MySQL on the same rows or the same index. Locking is fine if we want to compare different MySQL server versions. But it limits comparing different hardware or Linux kernels. In this case, we want to push MySQL as much as possible to load the underlying components. One solution is to partition several tables, But since MySQL still does not support Foreign Keys over partitioning tables, we would need to remove Foreign Key as well. A better solution is using multiple schemas (which is sort of like artificial partitioning). I’ve implemented this updated in the latest code of

tpcc-mysql

: https://github.com/Percona-Lab/tpcc-mysql.

The second change I proposed is replacing fully random text fields with generated text, something similar to what is used in the TPC-H benchmark. The problem with fully random strings is that they take a majority of the space in

tpcc-mysql

 schemas, but they are aren’t at all compressible. This makes it is hard to use

tpcc-mysql

 to compare compression methods in InnoDB (as well as different compression algorithms). This implementation is available in a different branch for now: https://github.com/Percona-Lab/tpcc-mysql/tree/less_random.

If you are using

tpcc-mysql

, please test these changes.

This blog post will detail the results of Samsung storage in

tpcc-mysql

 benchmark using Percona Server.

I had an opportunity to test different Samsung storage devices under tpcc-mysql benchmark powered by Percona Server 5.7. You can find a summary with details here https://github.com/Percona-Lab-results/201607-tpcc-samsung-storage/blob/master/summary-tpcc-samsung.md

I have in my possession:

• Samsung 850 Pro, 2TB: This is a SATA device and is positioned as consumer-oriented, something that you would use in a high-end user desktop. As of this post, I estimate the price of this device as around $430/TB.
• Samsung SM863, 1.92TB: this device is also a SATA, and positioned for a server usage. The current price is about $600/TB. 
• Samsung PM1725, 800GB: This is an NVMe device, in a 2.5″ form factor, but it requires a connection to a PCIe slot, which I had to allocate in my server. The device is high-end, oriented for server-side and demanding workloads. The current price is about $1300/TB.

I am going to use 1000 warehouses in the 

tpcc-mysql

 benchmarks, which corresponds roughly to a data size of 100GB.

This benchmark varies the

innodb_buffer_pool_size

 from 5GB to 115GB. With 5GB buffer pool size only a very small portion of data fits into memory, which results in intensive foreground IO reads and intensive background IO writes. With 115GB almost all data fits into memory, which results in very small (or almost zero) IO reads and moderate background IO writes.

All buffer pool sizes in the middle of the interval correspond to resulting IO reads and writes. For example, we can see the read to write ratio on the chart below (received for the PM1725 device) with different buffer pool sizes:

We can see that for the 5GB buffer pool size we have 56000 read IOPs operations and 32000 write IOPs. For 115GB, the reads are minimal at about 300 IOPS and the background writes are at the 20000 IOPs level. Reads gradually decline with the increasing buffer pool size.

The charts are generated with the Percona Monitoring and Management tools.

Results

Let’s review the results. The first chart shows measurements taken every one second, allowing us to see the trends and stalls.

If we take averages, the results are:

In table form (the results are in new order transactions per minute (NOTPM)):

bp, GB	pm1725	sam850	sam863	pm1725 / sam863	pm1725 / sam850
5	42427.57	1931.54	14709.69	2.88	21.97
15	78991.67	2750.85	31655.18	2.50	28.72
25	108077.56	5156.72	56777.82	1.90	20.96
35	122582.17	8986.15	93828.48	1.31	13.64
45	127828.82	12136.51	123979.99	1.03	10.53
55	130724.59	19547.81	127971.30	1.02	6.69
65	131901.38	27653.94	131020.07	1.01	4.77
75	133184.70	38210.94	131410.40	1.01	3.49
85	133058.50	39669.90	131657.16	1.01	3.35
95	133553.49	39519.18	132882.29	1.01	3.38
105	134021.26	39631.03	132126.29	1.01	3.38
115	134037.09	39469.34	132683.55	1.01	3.40

Conclusion

The Samsung 850 obviously can’t keep with the more advanced SM863 and PM1725. The PM1725 shows a greater benefit with smaller buffer pool sizes. In cases using large amounts of memory, there is practically no difference with SM863. The reason is that with big buffer pool sizes, MySQL does not push IO subsystem much to use all the PM1725 performance.

For the reference, my.cnf file is

[mysqld]
datadir=/var/lib/mysql
socket=/tmp/mysql.sock
ssl=0
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# general
thread_cache_size=2000
table_open_cache = 200000
table_open_cache_instances=64
back_log=1500
query_cache_type=0
max_connections=4000
# files
innodb_file_per_table
innodb_log_file_size=15G
innodb_log_files_in_group=2
innodb_open_files=4000
innodb_io_capacity=10000
loose-innodb_io_capacity_max=12000
innodb_lru_scan_depth=1024
innodb_page_cleaners=32
# buffers
innodb_buffer_pool_size= 200G
innodb_buffer_pool_instances=8
innodb_log_buffer_size=64M
# tune
innodb_doublewrite= 1
innodb_support_xa=0
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit= 1
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_max_dirty_pages_pct=90
join_buffer_size=32K
sort_buffer_size=32K
innodb_use_native_aio=0
innodb_stats_persistent = 1
# perf special
innodb_adaptive_flushing = 1
innodb_flush_neighbors = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 8
innodb_purge_threads=4
innodb_adaptive_hash_index=0
innodb_change_buffering=none
loose-innodb-log_checksum-algorithm=crc32
loose-innodb-checksum-algorithm=strict_crc32
loose-innodb_sched_priority_cleaner=39
loose-metadata_locks_hash_instances=256

Lots of times we could see different benchmarks performed by tpcc-mysql. So today I want to tell you about how to use tpcc-mysql and how to build graphs with gnuplot in a few easy steps.

As an example I’ll compare Percona Server 5.5 (latest version: 5.5.31) performance by changing InnoDB buffer pool size: innodb_buffer_pool_size = 256M / innodb_buffer_pool_size = 768M on my old test machine

System Info

• CPU: Intel(R) Pentium(R) 4 CPU 1.80GHz
• MemTotal: 1543732 kB
• OS: LinuxMint 15 (based on Ubuntu 13.04)

Files
You can find the source code of all files at the end of this post

Installation

• Install latest Percona Server
• Install tpcc-mysql


sudo apt-get install bzr
bzr branch lp:~percona-dev/perconatools/tpcc-mysql
make all

In this case it’s installed to ~/tpcc-mysql/ directory

• Install gnuplot


sudo apt-get install gnuplot


DB Config
First test will be running with innodb_buffer_pool_size = 256M option enabled and second one with innodb_buffer_pool_size = 768M

Test for innodb_buffer_pool_size = 256M

Create DB
Assuming that Percona Server 5.5.31 installed and configured

cd ~/tpcc-mysql
mysql -u root -p -e "CREATE DATABASE tpcc1000;"
mysql -u root -p tpcc1000 < create_table.sql
mysql -u root -p tpcc1000 < add_fkey_idx.sql


Load Data

./tpcc_load 127.0.0.1 tpcc1000 root "root-password" 20


Where:

• Host: 127.0.0.1
• DB: tpcc1000
• User: root
• Password: root-password
• Warehouse: 20

...DATA LOADING COMPLETED SUCCESSFULLY.

In this case DB size is 1.9GB

Run tpcc-mysql test

./tpcc_start -h127.0.0.1 -dtpcc1000 -uroot -p -w20 -c16 -r10 -l1200 > ~/tpcc-output-ps-55-bpool-256.log


Where:

• Host: 127.0.0.1
• DB: tpcc1000
• User: root
• Warehouse: 20
• Connection: 16
• Rampup time: 10 (sec)
• Measure: 1200 (sec)

The most interesting part in the output is:

MEASURING START.

10, 25(17):9.005|9.221, 21(0):1.866|1.869, 3(0):0.647|0.840, 1(0):0.000|10.614, 2(2):19.999|29.490
20, 22(14):9.419|9.555, 26(0):1.591|1.593, 2(0):0.593|0.788, 4(0):10.453|10.688, 3(3):19.999|22.962
30, 41(32):8.703|9.057, 32(0):1.615|1.662, 3(0):0.588|0.777, 2(0):9.530|10.495, 3(2):19.999|22.983

The first two values are “time range” and “transactions”, so you can read it as:

0-10 sec, 25 transactions
10-20 sec, 22 transactions
20-30 sec, 41 transactions


Test for innodb_buffer_pool_size = 768M

Repeat following steps for innodb_buffer_pool_size = 768M (change it in my.cnf) and get results:

• DB Config
• Create DB
• Load Data
• Run tpcc-mysql test


./tpcc_start -h127.0.0.1 -dtpcc1000 -uroot -p -w20 -c16 -r10 -l1200 > ~/tpcc-output-ps-55-bpool-768.log


There are 2 files: tpcc-output-ps-55-bpool-256.log and tpcc-output-ps-55-bpool-768.log which have benchmarking results for both tests.

Generate data file for each test

./tpcc-output-analyze.sh ~/tpcc-output-ps-55-bpool-256.log > tpcc-256-data.txt
./tpcc-output-analyze.sh ~/tpcc-output-ps-55-bpool-768.log > tpcc-768-data.txt


Merge data files

paste tpcc-256-data.txt tpcc-768-data.txt > tpcc-graph-data.txt


Build graph

./tpcc-graph-build.sh tpcc-graph-data.txt tpcc-graph.jpg


In this case tpcc-graph-data.txt is a filename of source datafile and tpcc-graph.jpg filename of graph which will be generated

Graph ready: tpcc-graph.jpg

Note: “using 3:4 … with lines axes x1y1″ in tpcc-graph-build.sh means that columns number 3 and 4 in datafile will be used for as axises x and y accordingly while building second line

File listing

tpcc-output-analyze.sh (I got it there and a bit modified)

TIMESLOT=1

if [ -n “$2” ]
then
TIMESLOT=$2
echo “Defined $2″
fi

cat $1 | grep -v HY000 | grep -v payment | grep -v neword | awk -v timeslot=$TIMESLOT ‘ BEGIN { FS=”[,():]“; s=0; cntr=0; aggr=0 } /MEASURING START/ { s=1} /STOPPING THREADS/ {s=0} /0/ { if (s==1) { cntr++; aggr+=$2; } if ( cntr==timeslot ) { printf (“%d %3d\n”,$1,(aggr/’$TIMESLOT’)) ; cntr=0; aggr=0 } } ‘

tpcc-graph-build.sh

#!/bin/bash

### goto user homedir and remove previous file
rm -f ‘$2′

gnuplot << EOP

### set data source file
datafile = ‘$1′

### set graph type and size
set terminal jpeg size 640,480

### set titles
set grid x y
set xlabel “Time (sec)”
set ylabel “Transactions”

### set output filename
set output ‘$2′

### build graph
# plot datafile with lines
plot datafile title “PS 5.5.1, buffer pool: 256M” with lines, \
datafile using 3:4 title “PS 5.5.1, buffer pool: 768M” with lines axes x1y1

EOP

The post tpcc-mysql: Simple usage steps and how to build graphs with gnuplot appeared first on MySQL Performance Blog.