{{ vExpose }}.Blog

Tag: sql

  • Database Performance Benchmark using HammerDB

    Database Performance Benchmark using HammerDB

    I have been using HammerDB for database performance bench-marking . This is very useful tool if you’re getting into any proof of concepts (POC) or testing of new database infrastructure stack before production roll-out.

    In this blog post we will discuss around using HammerDB to generate OLTP (TPC-C workload) on Microsoft SQL database. In my example I had Microsoft SQL and HammerDB server created on AWS EC2 instances. But from HammerDB perspective it doesn’t matter as far as it can communicate to database instance.

    We will follow 5 simple steps to run synthetic workload on MS SQL database. So let’s get started.

    Step 1 – Installing the HammerDB Application

    Before you get started make sure that –

    • HammerDB and MS SQL server are on same network/VLAN
    • MS SQL server is installed and running
    • Login to MS SQL console and create new SQL database. Make sure that new database is created on the target storage disks. If you create database in C drive, then you might not get expected performance due to disk bottleneck.
    Microsoft SQL database console
    • Download HammerDB installer (Click image below)
    HammerDB download link
    • Once HammerDB installer is downloaded, go ahead and install the same. Below are the links for installation steps.

    At this point you should have

    • MS SQL server ready with test database created on desired target storage disks
    • HammerDB installed and running

    Step 2 – Configuration of Schema Build

    We will need to created OLTP workload schema as per TPC-C specifications. Follow below steps for building OLTP schema on target database

    • Open the HammerDB console
    HammerDB – Console
    • Under Benchmark navigation page, double-click on SQL Server. In Benchmark Options pop-up window select SQL Server and TPC-C options. Then click Ok. Click Ok once again to confirm the selection.
    HammerDB – Selecting SQL database and TPC-C workload
    • Under Benchmark navigation now you can see SQL Server selected with TPC-C options.
    HammerDB – SQL server and TPC-C benchmark
    • Expand TPC-C under SQL Server and then expand Schema Build
    HammerDB – TPC-C Schema Build
    • Double-click on Options under Schema Build. Pop-up window of TPC-C Build Options will open. In this window enter below details, and then click Ok
      • SQL Server – IP address or hostname of SQL server. Keep (local) if HammerDB is installed on the SQL server.
      • Authentication – Use Windows Authentication if you want logged-in user credentials to be used for SQL server, else select SQL authentication and enter credentials.
      • SQL Server Database – Name of the database which we had created in Step 1
      • Number of Warehouses – Enter the value to number of warehouses you have chosen for your testing. Preferably select number of warehouses equal to number of cores on the server
      • Virtual Users to Build Schema – Set this value equal to number of warehouses.
    HammerDB – TPC-C Build Options
    • In the Benchmark pane double-click on Build
    HammerDB – TPC-C Schema Build
    • Click Yes to confirm creating schema in the target SQL database.
    • HammerDB will now start creating virtual user threads and create schema in the target database. This process takes some time. You can monitor the status in top-right corner of HammerDB console. (TPC-C creation in top-right)
    HammerDB – TPC-C Schema Creation in process
    • Once completed you can see that the status is changed to Complete
    HammerDB – TPC-C Schema Creation Completed

    Step 3 – Configure Driver Script

    Follow below steps to configure HammerDB driver script

    • Expand the Driver Script from Benchmark navigation pane and double-click on Options
    HammerDB – Driver Script
    • In the TPC-C Driver Options pop-up window enter below details, and then click Ok
      • SQL Server – Keep it same as Step 2
      • Authentication – Keep it same as Step 2
      • Total Transactions Per User – Keep default value. This value will set the number of transactions each virtual user will process before logging off
      • TPC-C Driver Script – Keep this option as Timed Driver Script. This will run the workload for finite time as specified in Minutes for Test Duration
      • Minutes of Rampup Time – The rampup time defines the time in minutes for the monitoring virtual user to wait for the virtual users running the workload to connect to the database. 2 minutes in my case. You can increase this number if you’ve higher number of virtual users.
      • Minutes for Test Duration – The Minutes for Test Duration is shown as duration in the Driver Script. This does not include rampup time.
      • Use All Warehouses – Keep this option checked.
    HammerDB – Driver Script Options
    • In the Benchmark navigation double-click on Load (under Driver Script). You need to Load the Driver Script every time you make changes to Driver Script Options.
    HammerDB – Driver Script Load

    Step 4 – Create Virtual User

    Once HammerDB driver script is loaded, follow below steps to create virtual user.

    • Expand Virtual User from Benchmark pane
    HammerDB – Virtual User
    • Double-click on Options. In the pop-up window enter below details, and then click Ok.
      • Virtual Users – Keep number of users same as Step 2
      • Keep Other inputs as default
    HammerDB – Virtual User Options
    • In the Benchmark navigation pane double-click on Create under Virtual User. This will create virtual users and keep them idle.
    HammerDB – Virtual User Create

    At this point we are ready to start OLTP workload on target database.

    Step 5 – Run HammerDB OLTP (TPC-C) Workload

    Follow below steps to run the OLTP workload and monitor the TPMs.

    • In Benchmark pane double-click on Run (Under Virtual User).
    HammerDB – Run OLTP (TPC-C) workload
    • Now Virtual Users will start logging into the target database and begin running their workload. You can monitor the status under Virtual User 1-MONITOR
    HammerDB – Starting OLTP (TPC-C) Workload – In Progress
    • While workload is running you can monitor the real-time TPM (Transactions Per Minute) by clicking on Transaction Counter
    HammerDB – Transaction Counter
    • Once workload is completed you can see the status in top-right corner as well as under Virtual User 1-MONITOR
    HammerDB – Starting OLTP (TPC-C) Workload – Completed
    • Once the workload is completed note down TEST RESULT under Virtual User 1-MONITOR
    HammerDB – OLTP (TPC-C) TEST RESULT

    You can run multiple tests and take average across them for realistic performance numbers.

    Apart from MS SQL (used in this blog post) HammerDB supports running OLTP (TPC-C) and OLAP (TPC-H) on Oracle, IBM DB2, MySQL, PostgreSQL, MariaDB and Redis.

    I hope this helps everyone.

  • Microsoft SQL DWH using DellEMC iCDM

    Recently I got engaged with one of our customer who was trying to implement data warehouse using the Microsoft SQL 2017. But they were not able to implement the same into the production environment even after successful POC and testing. When we got engaged with customer DBA teams we used DellEMC’s iCDM functionality (with XtremIO X2 system) in some unique way to help them not just solve their problem but exceeding business SLA expectations. This blog covers the details around problem and solution along with learning.

    Problem Statement

    Customer has several mission-critical MS SQL 2016 databases which are running in 24×7 operation. These databases are supporting their business critical OLTP application.

    Customer’s planned DWH approach

    Business teams were pushing application and DBA teams to create singular reporting platform which will create reporting on data from multiple MSSQL source databases.

    For this project DBA teams built MS SQL 2017 platform which will consolidate data from 3 source MS SQL databases. They created the test setup for feature testing before production roll-out, which worked as per their expectations. But when they implemented this feature in production DBA team realized that this is generating massive load on their source databases and causing performance issues for their business applications, which was completely unacceptable to business teams.

    This forced DBA teams to stop fetching data from the network and restore the data from last backup instead. This was used as workaround because this meant that data in reporting server was more than 30 hours old, which was making reporting irrelevant.

    DellEMC Solution

    When DellEMC team got involved we started with collecting DB environment data collection and business expectation understanding. Once we understood the pain points and business requirement we mentioned to DBAs that traditional way of data management techniques won’t help them. We also mentioned that since they are exiting DellEMC customer they already have the solution with them, which they never implemented.

    Every DellEMC All Flash storage solutions includes bundled copy data management software called Appsync.

    On high level AppSync allows application and database administrators to create application workflows. These workflows allows them to create on demand or scheduled protection or re-purpose copies of databases. We also mentioned that using this software you can create your copies of source MS SQL 2016 DB copies and mount it to target MS SQL 2017 DB server, scheduled or on-demand. These workflows are based on DellEMC storage in back-end and hence can be executed during even the busiest times.

    As usual customer DBA teams didn’t believe us and that’s where it comes to next section of this blog – Demonstration! 🙂

    Demonstration of the Appsync

    To demonstrate the functionality of DellEMC iCDM we installed the Appsync server on of the Windows server VM in customer environment. Below is the high level architecture

    DellEMC’s proposed solution approach for DWH project

    Once Appsync was installed we configured the same to communicate with source and target SQL servers and also DellEMC XtremIO storage. This allows Appsync to discover running databases and create the end to end database mapping.

    Post initial configuration we created the SQL re-purpose copy schedule to create the MS SQL database copy every 6 hours. This schedule was then applied to all the 3 source databases.

    Once the copy of multiple source database was created we used Appsync to mount the same into target MSSQL 2017 server. In our testing we mounted 3 source copies on single target MSSQL 2017 instance. Entire mounting operation completed within couple of minutes. This process allowed customer to save multiple days and help achieve business SLA.

    Apart from solving their most critical problem in hand this solution helped them solve couple of major production issues

    • Database RPO – We reduced production DB RPO to 2 hours instead of 30+ hours
    • Agent-free backup – iCDM helped customer to mount DB production copy on off-host backup server. This helped them completely eliminate backup load from production server.

    If you’re facing similar challenges in your DB environment then highly recommended to use iCDM approach instead of traditional methods.

    I have created another blog post to elaborate more on iCDM and advantages of the same. You can go through the same using below link.

    Databases and Integrated Copy Data Management (iCDM) with DellEMC

    Below are few reference documents for further reading on iCDM

    Dell EMC AppSync Datasheet

    Best Practices for running SQL Server on DellEMC XtremIO X2