{{ vExpose }}.Blog

Tag: database

  • 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

  • Databases and Integrated Copy Data Management (iCDM) with DellEMC

    Databases and Integrated Copy Data Management (iCDM) with DellEMC

    In this blog we will talk about how DellEMC can help you to store you databases smartly and efficiently.

    But firstly let’s talks about CDM and why it’s important. In the world of DBAs everyone’s always talking about performance, million IOPS and double/triple digit bandwidth with lowest possible latency. But the biggest question is whether performance is the only solution to all problems?

    For example in one of the survey members of Independent Oracle User Group (IOUG) were asked what DB activities are taking up most of the time each week?

    You can also see that performance troubleshooting is rated as second most time consuming activity, but the question is how to solve it. One of the ways people try to solve this is by upgrading their database stack. In below AWR example snippets you can see that how one of the customer has solved their performance issue by moving to DellEMC solution. Earlier they were having 92% User I/O wait time, which also means 92% of the time DB is waiting for database subsystem to respond.

    But as I have mentioned earlier upgrading the stack will always give the benefits but it does not solve all the problems. Which also means most customer apply their older process and best practices to new stack which often is the culprit of the new problems. (another blog on this later)

    But the real problem is what do you do with those databases and data? Most businesses today are trying to keep more data online. This allows them to understand the patterns and anomalies using AI/ML, which helps them taking right decisions or course correction. All this is driven using copy data, which means you need actual source production database instances to analyze, test, develop etc. Below picture is the great depiction of multiple use cases which needs source database.

    Now think of all those use cases and start thinking of the approach DBAs will take to manage the show. Usually there’s no standard way of managing this. Processes to achieve the end goal depends on the experience of the DBA and also availability of the resources.

    IDC has done some work on this problem area to understand the gravity of the situation and uncover some facts. IDC polled over 700 customers for this survey. Some of the interesting facts are –

    Tools used for producing secondary data copies

    As per the IDC survey most popular way is to rely on backup software but interestingly 53% of the respondents said that they use custom-written scripts to manage the CDM show.

    How long does it take to refresh copies?

    To which 62% of organizations, copies take ½ day or more to create (up to days). And this is very real as I have personally worked with one of my customer who used to take 2 ½ days to create single secondary copy starting weekend, on which 60 member developer team used to work.

    At which frequency are copies of dev/test or analytics refreshed?

    Copies are refreshed every few days by 32% of organizations, and weekly by 42%.

    One of the most striking detail in this IDC document is – How many Database instances do you currently have in production in your data center environment? 77% customers said that they have more that 200 database instances and 82% said that they have more than 10 copies per database.

    In simplified words this problem looks like the iceberg. People only count their production/primary database instances but secondary/re-purpose copies are usually taken for granted. If you do the quick math on IDC numbers you’ll quickly realize that this is one of the biggest operational issue in database environments.

    For Example –

    1 Production DB x 250GB DB size x 346 DB Instances = 87TB of Production data

    14 Copies of each Production DB x 250GB DB size x 346 DB Instances = 1.2PB of Copy Sprawl

    Now that we understand what copy data management issue is. Now let’s understand how DellEMC can help in simplifying the CDM problem

    Integrated Copy Data Management (iCDM)

    As per my personal experience below snippet is the best way to understand how iCDM functions. Providing self service to application/database teams with application integration. iCDM leverages advanced copy/snapshot function of the DellEMC storage system, which also means you need DellEMC storage in order to use iCDM 🙂

    To simplify things even further – in most environments you follow 3 steps process for database copy creation.

    1. Identify source database
    2. Prepare database
    3. Prepare instance

    Details for these 3 steps are as below but on high level it lacks automation, it’s (very) time consuming and most importantly it lacks automation.

    But with iCDM you can change this legacy process and adapt to something which is more modern and fits with today’s cloud native world. You can simply

    1. Install and Configure DellEMC Appsync (one time)
    2. Create and Customize workflows as per your environment
    3. Consume (repeat and scale)

    Conclusion

    DellEMC iCDM can be sumarised as

    1. Simple – Automated Copy Data Management without the need of customized scripts
    2. Intelligent – Tight integration with entire application stack – applications –> hosts –> virtualization –> DellEMC Storage with Replication
    3. Friction-less – Database and IT team are on the same page with transparent copy workflow.

    Below are the few links to the DellEMC iCDM collateral.

    Dell EMC’s integrated copy data management (iCDM) webinar

    DellEMC Appsync Datasheet

    DellEMC Appsync Software Download Link

    This blog is part of multi-blog series. Next couple of blogs I will try to explain usage of iCDM for multiple database CDM use cases.