slide1 l.
Skip this Video
Loading SlideShow in 5 Seconds..
Teaching Scalability Issues in Database Application Development ISECON-2006 #2124 9:00-9:20 A.M. Fri. Nov 3 rd 2006 PowerPoint Presentation
Download Presentation
Teaching Scalability Issues in Database Application Development ISECON-2006 #2124 9:00-9:20 A.M. Fri. Nov 3 rd 2006

Loading in 2 Seconds...

play fullscreen
1 / 31

Teaching Scalability Issues in Database Application Development ISECON-2006 #2124 9:00-9:20 A.M. Fri. Nov 3 rd 2006 - PowerPoint PPT Presentation

  • Uploaded on

Teaching Scalability Issues in Database Application Development ISECON-2006 #2124 9:00-9:20 A.M. Fri. Nov 3 rd 2006. Russell Anderson Musa Jafar Amjad Abdullat Network Security and Software Testing (SoNST) Lab CIS Dept West Texas A&M University

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Teaching Scalability Issues in Database Application Development ISECON-2006 #2124 9:00-9:20 A.M. Fri. Nov 3 rd 2006' - cadee

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Teaching Scalability Issues inDatabase Application DevelopmentISECON-2006 #2124 9:00-9:20 A.M. Fri. Nov 3rd 2006

Russell Anderson

Musa Jafar

Amjad Abdullat

Network Security and Software Testing (SoNST) Lab

CIS Dept

West Texas A&M University

  • Most Information Technology degree programs require a database applications development course
  • Students are required to architect, design and build a complete database application
  • Scalability and performance issues are discussed
  • Providing students with hands-on experience in design for scalability and performance is challenging and difficult.
issues in providing a real world experience
Issues in Providing a “Real World” Experience
  • Database must be larger than what students can be expected to manually load.
    • When testing with a small database, response time tests yield grossly optimistic results because the entire database is cached.
  • Must be able to test with hundreds (or even thousands) of concurrent users. Typically a student may be the only person testing their application (single user) or they may have a small group.
the objective
The Objective

Information Technology students should gain insight into realistic scenarios of the behavior of their database applications

Students should understand issues related to Performance, Scalability and Configuration when building software applications

Build an environment that is sustainable and reusable across semesters.

application level vs appliances level solutions
Application Level VS Appliances Level Solutions
  • Application Level Test Environment:
    • Concurrent tests launched from multiple lab workstations
    • The machines may be on different sub-networks.
    • Requires set of test tools, drivers, monitors, and elegant scripting.
  • Appliance Level Test Environment:
    • Utilizes special purpose hardware – the appliance.
    • Appliance has its own proprietary stacks, drivers and configuration front-end to simulate:
      • client terminals, sub-networks
      • Transaction test data.
application level solutions
Application Level Solutions
  • Hard to simulate realistic usage patterns
    • Test load type (TPS, Concurrent Transactions, concurrent users, etc.)
    • Transactions configurations (Number, timeout, etc.)
  • Hard to simulate network traffic usage patterns
    • Different networks and sub networks
    • Traffic bandwidth patterns (Bandwidth, error rates, etc.)
  • Hard to implement centralized dynamic monitoring, configuration and management of test environment resources
    • Need to distribute Network Analysis tools, Sniffers, tcpdump parsers across a wide variety of nodes.
  • Too much scripting and driver emulations
    • Test Initiation
    • Test Monitoring
    • Data Gathering and Analysis
  • Hard to have the identical hardware, software architecture, configuration for terminal and network equipment (TPC Requirement).
appliance level solutions
Appliance Level Solutions
  • General purpose and supports multiple protocols
  • Configurable load generators and test environment
  • Specialized and configurable TCP/IP stacks that are capable of generating realistic traffic and simulating 1000’s of randomly generated IP and MAC addresses.
  • No usage of terminal Equipment to simulate usage patterns: “Equipment used in measuring results is calibrated according to established quality standards” which is a TPC guideline.
  • Centralized dynamic monitoring, configuration and management of test environment resources
  • Not Flexible: Bound by the Vendor specifications of product capabilities
the test environment avalanche 220ee from spirent communication
The Test EnvironmentAvalanche-220EE from Spirent Communication

Avalanche-220EE is a Load generator and capacity assessment environment. Avalanche is designed to generate large quantities of realistic network traffic simulating different clients from different subnets.

Avalanche simplifies the stress-testing process of a networked environment by providing very powerful infrastructure and a user interface to configure different types of test configuration scenarios (concurrent users, transactions per second, different subnets traffic, TCP parameters, load configurations, etc.) and to gather, analyze and compare the different test results.

Avalanche is ideal for HTTP, HTTPS, FTP, SMTP, POP3, Telnet, RTSP, etc. applications performance testing.

the business and application environment
The Business and Application Environment

A Veterinary Clinic Application

  • The Business
    • Manage client and animal records,
    • Track clinic visits, payments, services rendered
    • Manage Inventory
  • The Application: Web-based 3-tier
    • User-tier: Web browser
    • Middle-tier: A jsp/servlet server for processing (Tomcat)
    • Data-tier: Oracle Relational DBMS for data management
    • JDBC for connectivity between the business and data tiers.
the transactions chosen for tests
The Transactions Chosen for Tests
  • Record Client Payments

2 tables read

inserts into 2 tables

  • Retrieve Animal Visit History

5 tables read

  • Retrieve Client and Animal Information

2 tables read

  • Save Visit Information

1 table read

inserts into 5 tables

updates of 1 table

student interactions
Student Interactions
  • Students can configure different testing patterns
    • TPS
    • Concurrent Users
    • Test Duration
  • Students can visually monitor database server activity while running tests
  • At the end of each test, student gets:
    • a run-time progress spreadsheet
      • sampled at 4 seconds intervals
    • summary data spreadsheet of the test run
    • a pcap file (network activity log) for further analysis
  • Each student application was given three 80 second test runs:
    • 5 TPS, 25 TPS, 50 TPS
  • Statistics Available in Output Spreadsheets
    • Transactions Attempted
      • percent successful and unsuccessful
    • Minimum Response Time
    • Maximum Response Time
    • Average Response Time
observed application errors discovered in search of network log
Observed Application Errors(discovered in search of network log)
  • Oracle Internal Errors
    • ORA-01000: maximum open cursors exceeded
    • ORA-00060: deadlock detected while waiting for resource
    • ORA-00018: maximum number of sessions exceeded
    • ORA-00604: error occurred at recursive SQL level 1
    • ORA-00001: unique constraint violated
  • HTTP 5XX: Internal Server error
    • TNS-12560: protocol adapter error
    • TNS-00530: Protocol adapter error
    • ORA-00018: maximum number of sessions exceeded
what do students learn
What Do Students Learn
  • Allowing the DBMS to retrieve data from multiple tables via multi-table selects (joins) is much more efficient than retrieving the data programmatically one table at a time.
  • Response time is very much affected by lock waits.
  • Deadlock does happen.
  • You can improve on the default DBMS configuration.
  • See the “hockey stick” effect in response time.
issues related to testing multi tier web based applications
Issues related to testing multi-tier web-based Applications
  • Misleading Response time to the client driver.
    • Architecturally, Web browser applications only have knowledge of their http server, the handler of the service is not known to the client application.
    • During testing, client drivers (the appliance) have no knowledge processing done by each tier (Chain of Responsibility).
    • Returned performance metrics may be misleading and needs to be analyzed for sudden improvement in performance.
    • Web server may return response quickly to the client because of connection resets
      • Average Response Time of the 115 Simulated users improvement in performance
        • HTTP 500: Internal Server error
        • ORA-00018: maximum number of sessions exceeded
issues related to testing multi tier web based applications24
Issues related to testing multi-tier web-based Applications
  • How to Propagate DBMS errors to client emulator driver:deadlocks, exceeding resources limit, etc. i.e. SQLException Errors
    • Solution: Propagate ORA and ORA TNS errors by forcing a 5XX HTTP error through code.

catch (java.sql.SQLException e){ ……..

SessionMaster.giveBackConnection(session.getId()) response.sendError(510, e.getMessage()); ……..


    • Comment: May be the HTTP protocol needs to be enhanced to return a marked-up error message that can be interpreted based on generic schema definitions
other performance measures that can be monitored and analyzed in real time
Other Performance Measures that can be monitored and analyzed In real time
  • Connection Properties
    • Sync/Ack time
    • Time till first byte
    • Incoming/Outgoing traffic into the test device
    • Current/Attempted/Established Connections
  • Transaction Parameters
    • Attempted
    • successful
    • unsuccessful
    • Aborted
  • Estimated Server Processing time
  • TPC BENCHMARK™ C, Standard Specification Revision 5.6, Transaction Processing Performance Council
  • Spirent Communication: Avalanche User Guide
  • Oracle Documentation
  • Apache Tomcat documentation
  • rfc2616 – Hypertext Transmission Protocol HTTP/1.1


Thank You