Welcome!

@DXWorldExpo Authors: Yeshim Deniz, Pat Romanski, Liz McMillan, Elizabeth White, Zakia Bouachraoui

Related Topics: @DXWorldExpo, Java IoT, Microservices Expo, Linux Containers, Machine Learning , Agile Computing

@DXWorldExpo: Article

Software Quality Metrics for Your Continuous Delivery Pipeline | Part 2

The metrics around database access

No matter how often you deploy your application or how sophisticated your delivery pipeline is, you always need to know the quality status of the software you are building. That can only be done if you measure it; but measure what exactly? In Part 1 we introduced the Concept of Quality Metrics in CD (Continuous Delivery) by looking at the metric # of Requests per End User Action. In Part 2 we will focus on metrics around database access.

You need to be aware of bad database access patterns right when they get introduced in your code. Whether the reason is incorrectly configured O/R (Object Relational) Mappers such as Hibernate, TopLink or JDO, or because of bad coding. Finding these problems immediately by looking at the right metrics will make it is easier for developers to fix the problem, which will reduce test cycles and give operations more confidence that a new deployment will not blow their current database server.

Examples of Bad Database Access Patterns
The following example is taken from a web application that displays an account report with 25 items per page that contains the names and high-level statuses of these accounts. The developers decided to go with Hibernate in order to access these account objects stored in the database. A closer look at the generated SQL queries when generating this report reveals that Hibernate not only loads the account objects that match the search query, but actually loads all account objects including all referenced objects in a related history table. These history objects are not needed at all to generate the report. The way Hibernate is used by the application (premature loading of all objects and referenced objects) results in more than 4000 SQL executions contributing 6s to the total page load time:

The way Hibernate is used by the application results in 4k+ individual SQL Statements, returning much more data than is actually needed for the report

If you want to learn more about database access problems check out load balancers cause database locks, when it is really the database to blame or the "Understanding Hibernate" Series: Part I - Session Cache, Part II - Query Cache and Part III - Second Level Cache.

Metric: Total Number of SQL Statements per Transaction
The first metric you want to take a closer look at is the total number of SQL executions per transaction. If you want to go a step further you can even monitor SELECT, INSERT, DELETE and UPDATE statements separately.

If you're always aware how many database statements are executed for your individual transactions (Login, Search, Checkout) and you monitor this along the delivery pipeline for every build, you will immediately see how the newly added functionality impacts the load on your database. The following screenshot shows a way to track this number across builds and across your different deployment stages. In this scenario, Developers extended the search feature in Build #3 by making an additional call to a 3rd party recommendation service. Build #3 suddenly shows a huge spike in SQL queries in the Load Stage and Production. Why is that?

A new call to an external third-party service introduced with Build 3 has major impacts on the load (capacity stage) and production environment when this new feature has to deal with real production data

What can we learn from these metrics above?

  • Commit stage: The executed Unit Tests in that stage didn't catch the problem because the call to the third-party service was mocked and therefore no actual DB calls were executed by that service.
  • Acceptance stage: A change in behavior was detected due to the additional call to the recommendation service which executed 2 additional SQLs. This could already be a warning sign but will probably still stay unnoticed.
  • Capacity stage: a 200x increase in DB calls must stop this deployment so that it never reaches Live. The reason why we have 200x and not only two should also trigger the integration tests to be executed against more than sample database content.
  • Production: 4k SQLs instead of just two is a huge impact on production. This again can be explained because the production database has "real life" data and the database access pattern of this third-party service queries every piece of data. If this really makes it into production and we monitor this data down to the transaction level, it is easy to pull this change back and engineering can immediately start working on the problematic area. Most important, however, is that this build never makes it into production because the problem was found in testing already!

How to Measure on Dev Workstations
Developers can look at this data by either turning on certain SQL Logging options of the frameworks that they are using, e.g., Hibernate (see stackoverflow discussion). They can profile their code using the profiler that comes with their IDE or use tools such as the Development Edition of dynaTrace (or the 15 Days Free Trial) to see all database calls made by their own code or the code that they are calling. The following shows a screenshot taken from Database Access Patterns Gone Wild and shows which data can be analyzed on a local machine:

Developers can analyze which SQL statements are executed by their own code or third-party frameworks they use. In this case it was code executed by Telerik to populate .NET control data.

For more measurement tips, and for further insight, click here for the full article

More Stories By Andreas Grabner

Andreas Grabner has been helping companies improve their application performance for 15+ years. He is a regular contributor within Web Performance and DevOps communities and a prolific speaker at user groups and conferences around the world. Reach him at @grabnerandi

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


DXWorldEXPO Digital Transformation Stories
Today, we have more data to manage than ever. We also have better algorithms that help us access our data faster. Cloud is the driving force behind many of the data warehouse advancements we have enjoyed in recent years. But what are the best practices for storing data in the cloud for machine learning and data science applications?
DevOpsSummit New York 2018, colocated with CloudEXPO | DXWorldEXPO New York 2018 will be held November 11-13, 2018, in New York City. Digital Transformation (DX) is a major focus with the introduction of DXWorldEXPO within the program. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term.
Bill Schmarzo, author of "Big Data: Understanding How Data Powers Big Business" and "Big Data MBA: Driving Business Strategies with Data Science" is responsible for guiding the technology strategy within Hitachi Vantara for IoT and Analytics. Bill brings a balanced business-technology approach that focuses on business outcomes to drive data, analytics and technology decisions that underpin an organization's digital transformation strategy.
@DevOpsSummit at Cloud Expo, taking place November 12-13 in New York City, NY, is co-located with 22nd international CloudEXPO | first international DXWorldEXPO and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time t...
Headquartered in Plainsboro, NJ, Synametrics Technologies has provided IT professionals and computer systems developers since 1997. Based on the success of their initial product offerings (WinSQL and DeltaCopy), the company continues to create and hone innovative products that help its customers get more from their computer applications, databases and infrastructure. To date, over one million users around the world have chosen Synametrics solutions to help power their accelerated business or per...
DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City and will bring together Cloud Computing, FinTech and Blockchain, Digital Transformation, Big Data, Internet of Things, DevOps, AI, Machine Learning and WebRTC to one location.
When talking IoT we often focus on the devices, the sensors, the hardware itself. The new smart appliances, the new smart or self-driving cars (which are amalgamations of many ‘things'). When we are looking at the world of IoT, we should take a step back, look at the big picture. What value are these devices providing. IoT is not about the devices, its about the data consumed and generated. The devices are tools, mechanisms, conduits. This paper discusses the considerations when dealing with the...
Charles Araujo is an industry analyst, internationally recognized authority on the Digital Enterprise and author of The Quantum Age of IT: Why Everything You Know About IT is About to Change. As Principal Analyst with Intellyx, he writes, speaks and advises organizations on how to navigate through this time of disruption. He is also the founder of The Institute for Digital Transformation and a sought after keynote speaker. He has been a regular contributor to both InformationWeek and CIO Insight...
For years the world's most security-focused and distributed organizations - banks, military/defense agencies, global enterprises - have sought to adopt cloud technologies that can reduce costs, future-proof against data growth, and improve user productivity. The challenges of cloud transformation for these kinds of secure organizations have centered around data security, migration from legacy systems, and performance. In our presentation, we will discuss the notion that cloud computing, properl...
CloudEXPO New York 2018, colocated with DXWorldEXPO New York 2018 will be held November 11-13, 2018, in New York City and will bring together Cloud Computing, FinTech and Blockchain, Digital Transformation, Big Data, Internet of Things, DevOps, AI, Machine Learning and WebRTC to one location.