Force Marketing

Force Marketing sees performance improvement of ETL running Windows Server on AWS.

Overview

  • County or Region: United States
  • Industry: Marketing
  • Customer Profile:
    Force Marketing is a full-service marketing firm (digital/direct/email) in the automotive industry, focused upon the ROI value of reaching customers in the automotive space. Force's Helix DNA Marketing Automation Platform is designed as an innovative marketing intelligence platform, empowering auto dealerships to streamline their marketing, improve customer retention and new customer acquisition, and track the customer journey in real-time across all channels.
  • Problem:
    Force Marketing's overnight ETL solution was exceeding the SLA and encroaching on business hours. IA was brought in to help tune and improve the performance of this ETL solution. During the project, monthly billing costs also exceeded expectations, and IA worked to solve that problem as well.
  • Solution:
    IA recommended and delivered upgrades to the infrastructure components, namely the EC2 tier, and updated storage components, particularly by changing the types of EBS volumes used by the main data warehouse server.

Case Study

  • Problem
    Force Marketing's overnight ETL solution was exceeding the SLA and encroaching on business hours. IA was brought in to help tune and improve the performance of this ETL solution. During the project, monthly billing costs also exceeded expectations, and IA worked to solve that problem as well.
  • Solution
    IA recommended and delivered upgrades to the infrastructure components, namely the EC2 tier, and updated storage components, particularly by changing the types of EBS volumes used by the main data warehouse server. Along with improving the timing of the overnight process, these changes improved Force Marketing's utilization of the Amazon Web Services infrastructure as well as educated them in various offerings available within the AWS suite of products.

    As for the cost overruns, we identified a 3rd party tool, Skeddly, as the culprit. This service was being used to manage and expire volume snapshots according to a set retention period. The service stopped following the retention setting and accumulated months' worth of snapshots instead of three days' worth, which drove up the AWS storage costs.
  • Outcome & Results
    Monthly billing was reduced by $3,000/month, which represented 40% of the cloud costs at that time. From this experience, the technical team was made well aware of the need to check on "automation" periodically, especially from 3rd party services, that may not always be working as desired.

    Moreover, the overall ETL process runtime was decreased by 50% (12hrs to 6hrs). This reduction allowed the overnight process to complete prior to the following business workday, meeting stated SLAs. Initial performance benefits were attained by upgrading the EC2 tier from r3.2xlarge to r3.4xlarge. Even though this incurred increased billing costs, it was determined that no other approach would suffice because memory and CPU limits were being reached on a nightly basis.

    Additional performance benefits were attained by re-arranging the EBS volumes and their types. This was achieved by separating data and log files, and by making greater use of SSD-based volumes. The Instance Store Volume (ephemeral) was not being used, despite its advantage as being the most performant of all options; we placed TempDB on this volume, which can be rebuilt with every server reboot and subsequent flush of the drive contents. We also used Provisioned IOPS SSD (io1) for smaller volumes and General Purpose SSD (gp2) for larger ones to manage costs. Further speed increases were achieved by increasing the size of existing volumes with spinning disks, Throughput Optimized HDD (st1) volumes--even though the additional space was not needed, the available IOPS increases with the size of the volume.

    Looking ahead, we guided Force Marketing with a strategic roadmap for future EC2 server tier upgrades to help them manage increased load over time. While optimizing code can help in these areas as well, as nightly ETL demands increase over time, the need for improvements to the architecture would inevitably become necessary.

    We called out the need for an eventual upgrade to the r3.8xlarge tier. As the organization's nightly ETL demands increased over time, we pointed out this was an inevitability. By upgrading to the next tier, the system would double the RAM, double the CPUs, and add an extra ephemeral instance storage drive. Beyond the immediate impact of additional processing power and memory, the additional ephemeral drive would allow us to stripe the 2 ephemeral drives together. This would double the I/O for the ephemeral drive, which is the volume used to store the data warehouse's TempDB; this database is used heavily in ETL and sometimes reporting. We advised Force Marketing that a bottleneck in any of these three areas--RAM, CPU, or TempDB--can be alleviated by upgrading the tier, but to be aware of the implications for increased monthly billing.
  • Software and Services
    • Microsoft SQL Server
    • AWS EC2
    • AWS VPC
    • AWS Elastic Load Balancer
  • Architectural Diagrams