Click here to close now.

SYS-CON MEDIA Authors: Pat Romanski, Elizabeth White, Liz McMillan, Carmen Gonzalez, AppDynamics Blog

Related Topics: Big Data Journal, Java, Microservices Journal, Linux, AJAX & REA, Web 2.0

Big Data Journal: 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.


Latest Stories
For better or worse, DevOps has gone mainstream. All doubt was removed when IBM and HP threw up their respective DevOps microsites. Where are we on the hype cycle? It's hard to say for sure but there's a feeling we're heading for the "Peak of Inflated Expectations." What does this mean for the enterprise? Should they avoid DevOps? Definitely not. Should they be cautious though? Absolutely. The truth is that DevOps and the enterprise are at best strange bedfellows. The movement has its roots in t...
Wearable devices have come of age. The primary applications of wearables so far have been "the Quantified Self" or the tracking of one's fitness and health status. We propose the evolution of wearables into social and emotional communication devices. Our BE(tm) sensor uses light to visualize the skin conductance response. Our sensors are very inexpensive and can be massively distributed to audiences or groups of any size, in order to gauge reactions to performances, video, or any kind of present...
The true value of the Internet of Things (IoT) lies not just in the data, but through the services that protect the data, perform the analysis and present findings in a usable way. With many IoT elements rooted in traditional IT components, Big Data and IoT isn’t just a play for enterprise. In fact, the IoT presents SMBs with the prospect of launching entirely new activities and exploring innovative areas. CompTIA research identifies several areas where IoT is expected to have the greatest impac...
There is little doubt that Big Data solutions will have an increasing role in the Enterprise IT mainstream over time. 8th International Big Data Expo, co-located with 17th International Cloud Expo - to be held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA - has announced its Call for Papers is open. As advanced data storage, access and analytics technologies aimed at handling high-volume and/or fast moving data all move center stage, aided by the cloud computing bo...
The 4th International Internet of @ThingsExpo, co-located with the 17th International Cloud Expo - to be held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA - announces that its Call for Papers is open. The Internet of Things (IoT) is the biggest idea since the creation of the Worldwide Web more than 20 years ago.
Every day we read jaw-dropping stats on the explosion of data. We allocate significant resources to harness and better understand it. We build businesses around it. But we’ve only just begun. For big payoffs in Big Data, CIOs are turning to cognitive computing. Cognitive computing’s ability to securely extract insights, understand natural language, and get smarter each time it’s used is the next, logical step for Big Data.
There's no doubt that the Internet of Things is driving the next wave of innovation. Google has spent billions over the past few months vacuuming up companies that specialize in smart appliances and machine learning. Already, Philips light bulbs, Audi automobiles, and Samsung washers and dryers can communicate with and be controlled from mobile devices. To take advantage of the opportunities the Internet of Things brings to your business, you'll want to start preparing now.
DevOps Summit, taking place Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 17th Cloud Expo 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 to wait for long developmen...
In a world of ever-accelerating business cycles and fast-changing client expectations, the cloud increasingly serves as a growth engine and a path to new business models. Dynamic clouds enable businesses to continuously reinvent themselves, adapting their business processes, their service and software delivery and their operations to achieve speed-to-market and quick response to customer feedback. As the cloud evolves, the industry has multiple competing cloud technologies, offering on-premises ...
The 5th International DevOps Summit, co-located with 17th International Cloud Expo – being held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA – announces that its Call for Papers is open. Born out of proven success in agile development, cloud computing, and process automation, DevOps is a macro trend you cannot afford to miss. From showcase success stories from early adopters and web-scale businesses, DevOps is expanding to organizations of all sizes, including the...
Over the years, a variety of methodologies have emerged in order to overcome the challenges related to project constraints. The successful use of each methodology seems highly context-dependent. However, communication seems to be the common denominator of the many challenges that project management methodologies intend to resolve. In this respect, Information and Communication Technologies (ICTs) can be viewed as powerful tools for managing projects. Few research papers have focused on the way...
As the world moves from DevOps to NoOps, application deployment to the cloud ought to become a lot simpler. However, applications have been architected with a much tighter coupling than it needs to be which makes deployment in different environments and migration between them harder. The microservices architecture, which is the basis of many new age distributed systems such as OpenStack, Netflix and so on is at the heart of CloudFoundry – a complete developer-oriented Platform as a Service (PaaS...
In high-production environments where release cycles are measured in hours or minutes — not days or weeks — there's little room for mistakes and no room for confusion. Everyone has to understand what's happening, in real time, and have the means to do whatever is necessary to keep applications up and running optimally. DevOps is a high-stakes world, but done well, it delivers the agility and performance to significantly impact business competitiveness.
17th Cloud Expo, taking place Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Meanwhile, 94% of enterprises a...
Hybrid IT is an approach to delivering IT services that matches business requirements and application needs with different IT deployment modalities. In their session at 16th Cloud Expo, Jeff Katzen, Director of the Cloud Practice at CenturyLink, and Gary Sloper, Area Vice President, Sales Engineering and Operations, at CenturyLink, will go into more depth around those different modalities and how customers have made decisions to choose between them. They will talk to some of the challenges t...