CTI3561-Lab2.1-HammerDB-William-Feller
docx
keyboard_arrow_up
School
Full Sail University *
*We aren’t endorsed by this school
Course
3561
Subject
Information Systems
Date
Dec 6, 2023
Type
docx
Pages
11
Uploaded by HighnessThunderSeahorse21
Lab Assignment 2.1 – HammerDB
This lab will focus on database performance. There are many factors that impact the performance
of an application. One of the most important factors is backend performance. In most cases the backend is some kind of database. There are many types of databases, database servers, schemas,
queries, server hardware, and components that may affect database performances. How does one determine which of these areas is the cause of database “slowness”? To understand database performance the database needs to be tested. This lab will allow students to test database functions by creating a uniform database and table across systems with uniform queries. By eliminating factors like schema, tables, and queries one can analyze the database performance based on external factors like server hardware and components. Objective:
The student will learn how to utilize HammerDB. This tool will create a standard table in the database and utilize a test query to put stress on the database. The results are measured in transactions per minute. The more transactions per minute, the better. By evaluating the theoretical maximum transactions per minute in a database design you can plan for growth and threshold for alerting. Task 1: Download HammerDB and install on your Windows 10 instance
This task will have you download HammerDB on your Windows 10 instance. Remember that each VM should have 2 NICs, one on the HTTP tier and the other on the DB tier. 1.
Download HammerDB for Windows 64-bit by going to this link:
a.
https://github.com/TPC-Council/HammerDB/releases/tag/v2.23
b.
Download Release 2.23 for Windows 64-bit installer (works best for this lab)
c.
Once the download is complete, run the installer. 2.
Install HammerDB
a.
Follow the steps through the installer leaving the defaults i.
Choose English ii.
Choose to Install HammerDB on the Computer iii.
Select Next to start the Install Wizard iv.
Leave the default location for the install and click next v.
Start Copying files, click next vi.
Once the copy is complete check the box to launch HammerDB Deliverable:
No deliverable for this task.
Task 2: Configure HammerDB to install a database and run a database test
There are two database instances you will test with HammerDB. For the first test we will focus on the MySQL server. HammerDB will create a test database and queries for that database. Once executed it will run a series of queries to place load on the database server and determine its transactions per minute. Again, the higher the transactions per minute the better the database server is performing. 1.
In HammerDB, Under Benchmark, double click on “MySQL” 2.
Benchmark options should open, select MySQL and TPC-C 3.
You will get a confirmation confirming the benchmark, click OK 4.
Under Benchmark expand MySQL, then TPC-C, Schema Build, then double click on Options 5.
Enter the server information for the MySQL server and WolfCMS database a. MySQL Host: 10.10.3.32
b. MySQL Port: 3306
c. MySQL User: wolf
d. MySQL Password: password
e. MySQL Database: wolfcms
f. Leave all else the default and click OK
6.
Under Schema Build double click on Build
7.
You will get a prompt to Create Schema, click Yes
This will create the test schema for use. Check the middle pane in HammerDB to verify the task completed. If you see a red X
check the IP, username, and password settings under Options and try again. You may also get an error saying, “Table ‘customer’ already exists”
if so continue. This means the schema was already created in your database for HammerDB. If the rest of the steps are unresponsive, click the “red light” (Destroy users) and continue on. 8.
Expand Driver Script and double click on Load
This will update the driver script with the options created for your MySQL database. 9.
Expand Virtual User and double click on Create This will create a virtual user connected to the database to run the queries on the test schema. For now, we will leave this as the default of one. However, much like you learned in Lab 1.2 regarding threads in jMeter, the more virtual users you add the longer the test will take to complete, and the more load gets placed on the server.
10.
Under Virtual User double click Run. At this point you should see script moving quickly across the virtual user screen. Let this test continue running as you move to the next step. It will eventually stop; however, you can always stop it by click the traffic light button on the menu bar of HammerDB. 11.
Next to the Virtual User Output tab in HammerDB click on the Transaction Counter tab. If it is greyed out, click the Pen icon above the
tab and it should change to a Red Light and allow you to see the transaction counter. This re-enables the transaction counter. This will show you a chart with samples for the transactions per minute. Take a screenshot while the test is running. The test will eventually complete itself, however, if it continues running past 15 minutes stop the test using the traffic light button in the menu. If you must restart the test and the transaction counter isn’t functioning, click
the menu button three to the right of the traffic light button (It looks like a Pen). This will re-enable tracking on the transaction counter. Deliverable:
Save your transaction counter screenshot and place it in your deliverable document for upload in to FSO
Task 3: Repeat tests for MariaDB and with Virtual User changes to MariaDB & MySQL
Now that you have completed one test you should be able to complete tests on the MariaDB databases by repeating the steps from Task 2 and
by changing the IP information in Options. We will also repeat tests for both MariaDB and MySQL with an increased Virtual User count. We want to determine how each database performs as more users are added. Does the transactions per minute climb uniformly with each new user or does the transactions per user per minute decrease? During the tests you will also want to evaluate the CPU load placed on the server as you run the tests:
During the test log into the Database Server
At the CLI, type top to see statistics of the load that the server is
under.
Pay attention to the CPU utilization when you go from one to eight users. What happens?
1.
Run Tests with 1 Virtual User in Maria DB
a.
In HammerDB under Benchmark -> Schema Build click Options b.
Change the IP to the MariaDB IP address – 10.10.3.31
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
c.
All other options can remain the same, Click OK d.
Follow the rest of the steps just as they were listed in Task 2 to start the test. e.
Capture a screen shot of the transactions per minute in the transaction counter tab while the test is running. 2.
Run Tests with 8 Virtual Users in Maria DB a.
In Benchmark under MySQL -> TPC-C -> Virtual User -> Options change Virtual User to 8 and click OK b.
Follow the rest of the steps just as they were listed in Task 2 to start the test. c.
Capture a screen shot of the transactions per minute in the transaction counter tab while the test is running. 3.
Run Tests with 8 Virtual Users in MySQL DB a.
In HammerDB under Benchmark -> Schema Build click Options b.
Change the IP to the MySQL IP address – 10.10.3.32 c.
All other options can remain the same, Click OK d.
In Benchmark under MySQL -> TPC-C -> Virtual User -> Options change Virtual User to 8 and click OK e.
Follow the rest of the steps just as they were listed in Task 2 to start the test.
f.
Capture a screen shot of the transactions per minute in the transaction counter tab while the test is running.
Deliverable:
Save your transaction counter screenshots for MariaDB – 1 Virtual User, MariaDB – 8 Virtual Users, and MySQL – 8 Virtual Users and place them in your deliverable document with the MySQL – 1 user screenshot from Task 2 for upload in to FSO
Lab Write up
Completely answer the below questions using at least one full sentence. Use your screenshots as data references:
1.
What was the transactions per minute for 1 Virtual User in MySQL?
1 virtual user was a rate of 66024 transactions per minute
2.
What was the transactions per minute for 8 Virtual User in MySQL?
8 virtual users was a rate of 179580 transactions per minute
3.
What was the transactions per minute for 1 Virtual User in MariaDB?
1 virtual user was a rate of 62304 transactions per minute
4.
What was the transactions per minute for 8 Virtual User in MariaDB?
8 virtual users was a rate of 140196 transactions per minute
5.
On average how many transactions per minute did each user get in MySQL when 8 virtual users ran concurrently?
On average, each user in MySQL experienced approximately 22,447.5 transactions per minute when 8 virtual users ran concurrently.
6.
On average how many transactions per minute did each user get in MariaDB when 8 virtual users ran concurrently?
On average, each user in MariaDB experienced approximately 17,524.5 transactions per minute when 8 virtual users ran concurrently.
7.
Did MySQL have more transactions per minute per user with 1 user or 8 users? What was the difference in transactions per minute per user?
In MySQL, the transactions per minute per user were higher with 1 user compared to 8 users, with a difference of 43,576.5 transactions per minute per user.
8.
Did MariaDB have more transactions per minute per user with 1 user or 8 users? What was the difference in transactions per minute?
In MariaDB, the transactions per minute per user were higher with 1 user compared to 8 users, with a difference of 44,779.5 transactions per minute per user.
9.
Based on the findings which database server (MariaDB or MySQL) would you recommend for applications with a need for more transactions per minute? Why?
Based on the findings, MariaDB is recommended for applications with a need for more transactions per minute per user, as it consistently showed higher transaction rates per user compared to MySQL, whether with 1 or 8 users running concurrently.
10. Stop all instances of servers and HammerDB. Increase the CPUs on the MariaDB server to 2 CPUs and increase the RAM to 1024 MB. Run the test with 8 concurrent users. Did the Transactions per minute increase, decrease or stay the same? What was the TPM and the average TPM per user? Using these results, what happened to the CPU usage on the server? Include a screenshot for this answer.
This question does not seem possible with the current iteration of proxmox, I am not able to change anything on the mariadb server. However, the breakdown is below without the data.
Transactions per minute (TPM) and Average TPM per user:
After increasing the CPUs to 2 and RAM to 1024 MB, re-run the test with 8 concurrent users and record the TPM.
Calculate TPM and average TPM per user using the formula:
TPM with 8 users = Transactions per minute with 8 Virtual Users
Average TPM per user = TPM with 8 users / 8
Impact on CPU Usage:
Monitor the CPU usage before and after the change.
Compare the CPU usage under the new configuration (2 CPUs) with the previous configuration.
Analyze the CPU usage trends and differences to determine the impact of the changes on CPU utilization.
Generally, adding more CPU and RAM can potentially improve performance by allowing the server to handle more concurrent users and transactions. However, the actual impact on TPM and average TPM per user will depend on various factors including the efficiency of the database design, the workload, and the nature of the transactions being performed.
It's essential to monitor the system during and after the test to observe any changes in performance metrics like TPM and CPU usage accurately.
Deliverable:
Answer the above questions and place them in your deliverable document for upload in to FSO
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Deliverable:
1.
Gather the deliverables listed for each task into a single PDF document.
2.
Name the PDF CTI3561-Lab2.1-Firstname-lastname.pdf where first name is your first name and last name is your last name
3.
Upload the PDF to FSO in the appropriate area for Lab 2.1
Grading Rubric:
Build a Hammer DB Test Plan – 10 points
o
Build a test plan in HammerDB to test MySQL with 1 virtual user.
o
Take a screenshot of the Transaction counter
Repeat the Test Plan on MariaDB for 1 virtual user then for 8 virtual users on MySQL and MariaDB – 30 points
o
Take screenshots of the Transaction Counter for each test as part of your deliverables in FSO
o
Take a screenshot of each test for a total of three more screenshots
Lab Write-Up – 50 points
o
Answer the questions with at least one sentence (
and an additional screenshot for Question 10
)
o
Answers should completely address the question.
Follow Instructions – 10 points
o
Follow the instructions as writing in the lab document.