Welcome!

Machine Learning Authors: Yeshim Deniz, Zakia Bouachraoui, Liz McMillan, Pat Romanski, Elizabeth White

Related Topics: Java IoT, Microservices Expo, Machine Learning , Agile Computing, Cloud Security, @DXWorldExpo

Java IoT: Article

Data Access Analysis Tips to Boost Performance

When database log files don’t help

If transaction times jump from less than one second to more than 60 seconds after a software upgrade, it is evident that there is a problem that needs investigation. This scenario was experienced by a customer with its time-tracking application. Our customer identified this performance regression while executing extensive performance tests prior to its production upgrade. The company's "traditional" approach of analyzing these types of problems was analyzing application and database log files as well as infrastructure metrics such as CPU, Memory and Network Throughput. Our customer couldn't find a single slow database query, nor were there any indications of an error in the log files. The infrastructure also seemed to be fine because the application, the database server and the network didn't show any bottlenecks. Analyzing the end-to-end transaction, such as the click on search, however, revealed a problem that turned out to be a "data-driven" performance issue.

Problem: Loading and Processing Large Result Sets
The following screenshot shows parts of the PurePath highlighting where most of the time is spent within the transaction:

60s is consumed by loadDataStore which iterates through a large ResultSet

The SQL Statement to retrieve time-tracking data executed in about 840ms. This doesn't really indicate a hotspot for this transaction. loadDataStore however iterates through the ResultSet of this query. Looking at the PurePath details of that method execution shows that the continuous fetches of the next result sets is the real hotspot of this transaction. Most of the time is spent in I/O to retrieve the next batch of table rows and process this data.

Root Cause: Too Much Data for Test User
After consulting with the third-party vendor of this application our customer came to the conclusion that the user account used in its load testing script had elevated privileges. When accessing a particular web page in the application this user was able to see time-tracking data from all other users in the system. The implementation of this web page was such that it processed all data accessible by the user - instead of just accessing the data needed for display, e.g, for the first page of results.

Solution: "Real" User Account for Testing
Instead of using the special privileged user account the test scripts were modified to use a user account with "normal" privileges - this also reflected the real-life scenario. This reduced the transaction response time significantly - down from 61 seconds to 73 milliseconds - as shown in the following PurePath:

Standard Users process the same request in 73ms instead of 61s

Conclusion
Unsurprisingly, the amount of data that is returned by a SQL statement can have significant impact on transaction performance. There are, however, several lessons learned:

  1. SQL Log Files didn't show this as a problem as they only reported the SQL Execution Time but not the individual Fetch and Processing times
  2. Testing needs to be done with real user accounts and not test, dummy or special privileged accounts
  3. Optimize access to large result sets by only processing what is really needed for display

If you are interested in more typical problem patterns check out the Top 8 Application Performance Landmines.

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.


CloudEXPO Stories
Your job is mostly boring. Many of the IT operations tasks you perform on a day-to-day basis are repetitive and dull. Utilizing automation can improve your work life, automating away the drudgery and embracing the passion for technology that got you started in the first place. In this presentation, I'll talk about what automation is, and how to approach implementing it in the context of IT Operations. Ned will discuss keys to success in the long term and include practical real-world examples. Get started on automating your way to a brighter future!
The challenges of aggregating data from consumer-oriented devices, such as wearable technologies and smart thermostats, are fairly well-understood. However, there are a new set of challenges for IoT devices that generate megabytes or gigabytes of data per second. Certainly, the infrastructure will have to change, as those volumes of data will likely overwhelm the available bandwidth for aggregating the data into a central repository. Ochandarena discusses a whole new way to think about your next-gen applications and how to address the challenges of building applications that harness all data types and sources.
DXWorldEXPO LLC announced today that Big Data Federation to Exhibit at the 22nd International CloudEXPO, colocated with DevOpsSUMMIT and DXWorldEXPO, November 12-13, 2018 in New York City. Big Data Federation, Inc. develops and applies artificial intelligence to predict financial and economic events that matter. The company uncovers patterns and precise drivers of performance and outcomes with the aid of machine-learning algorithms, big data, and fundamental analysis. Their products are deployed by some of the world's largest financial institutions. The company develops and applies innovative machine-learning technologies to big data to predict financial, economic, and world events. The team is a group of passionate technologists, mathematicians, data scientists and programmers in Silicon Valley with over 100 patents to their names. Big Data Federation was incorporated in 2015 and is ...
Dynatrace is an application performance management software company with products for the information technology departments and digital business owners of medium and large businesses. Building the Future of Monitoring with Artificial Intelligence. Today we can collect lots and lots of performance data. We build beautiful dashboards and even have fancy query languages to access and transform the data. Still performance data is a secret language only a couple of people understand. The more business becomes digital the more stakeholders are interested in this data including how it relates to business. Some of these people have never used a monitoring tool before. They have a question on their mind like "How is my application doing" but no idea how to get a proper answer.
All in Mobile is a place where we continually maximize their impact by fostering understanding, empathy, insights, creativity and joy. They believe that a truly useful and desirable mobile app doesn't need the brightest idea or the most advanced technology. A great product begins with understanding people. It's easy to think that customers will love your app, but can you justify it? They make sure your final app is something that users truly want and need. The only way to do this is by researching target group and involving users in the designing process.