week-3

Week #3

Overview

This week, you will learn to measure the hit/miss ratio in MySQL while running the TPC-C benchmark. You will also measure the performence metrics by changing the buffer sizes. Then, you will analyze the impact of different buffer sizes on the overall performance (e.g., transaction throughput, hit ratio, etc.)

Follow the guide below. If you have any questions, don't hesitate to contact me via email (Mijin An / meeeeejin@gmail.com)

NOTE: This lab is based on the Linux environment. If you don't have a Linux machine, use VirturalBox. (Recommend Ubuntu 18.04)

Instructions

1. Start a MySQL server

1. Before starting a MySQL server, update the buffer pool size to 10% (then, 20%, 30%, 40%, 50%) of your TPC-C database size. For example, if you load 20 warehouses (e.g., about 2G database size), change the value of innodb_buffer_pool_size in my.cnf to 200M:

$ vi /path/to/my.cnf
...
innodb_buffer_pool_size=200M
...

2. Start a MySQL server:

$ ./bin/mysqld_safe --defaults-file=/path/to/my.cnf

2. Run the TPC-C benchmark

Run the benchmark by modifying the experimental parameters to match your system specifications. For example:

$ ./tpcc_start -h 127.0.0.1 -S /tmp/mysql.sock -d tpcc -u root -p "yourPassword" -w 20 -c 8 -r 10 -l 1200 | tee tpcc-result.txt

It means:

• Host: 127.0.0.1
• MySQL Socket: /tmp/mysql.sock
• DB: tpcc
• User: root
• Password: yourPassword
• Warehouse: 20
• Connection: 8
• Rampup time: 10 (sec)
• Measure: 1200 (sec)

3. Monitor the buffer hit/miss ratio of MySQL

1. While running the benchmark, collect performance metrics (e.g., I/O status, transaction throughput, hit/miss ratio) and record them in a separate file for future analysis. Refer to the performance monitoring guide and hit ratio monitoring guide

2. After the benchmark ends, shut down the MySQL server:

$ ./bin/mysqladmin -uroot -pyourPassword shutdown

4. Change the buffer size and repeat steps 1-3

Report Submission

1. Run the TPC-C benchmark on MySQL by varying the buffer size of MySQL
   • 10%, 20%, 30%, 40%, 50% of the database size
2. Observe how the performance metrics (e.g., IOPS, hit ratio, etc.) and TpmC change over time
   • Especially, monitor the buffer hit ratio at the start and end of the benchmark
3. Present the experimental results
4. Analyze the results

Organize the results into a single report and submit it. Follow the submission guide for your report.

Reference

• Build and install the source code (5.7)
• Percona-Lab/tpcc-mysql
• tpcc-mysql: Simple usage steps and how to build graphs with gnuplot