Teaching Scalability Issues in Database Application Development ISECON2006

1
Teaching Scalability Issues inDatabase
Application DevelopmentISECON-2006 2124
900-920 A.M. Fri. Nov 3rd 2006

• Russell Anderson
• Musa Jafar
• Amjad Abdullat
• Network Security and Software Testing (SoNST) Lab
• CIS Dept
• West Texas AM University
• mjafar_at_mail.wtamu.edu
• randerson_at_mail.wtamu.edu

2
Background

• 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.

3
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.

4
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.

5
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.

6
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).

7
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 1000s 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

8
The Test Environment
9
The Test Environment Avalanche-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.

10
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.

11
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

12
The Database Schema of Records per Table
13
(No Transcript)
14
(No Transcript)
15
Environment Preparation
16
Test Workflow
17
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

18
Results

• 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

19
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

20
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.

21
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

22
Issues related to testing multi-tier web-based
Applications
23
Issues related to testing multi-tier web-based
Applications
24
Issues related to testing multi-tier web-based
Applications

• How to Propagate DBMS errors to client emulator
  driverdeadlocks, 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

25
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

26
Appliances Level SolutionsAvailable Test Summary
Results (time in ms)
27
Appliances Level SolutionsAvailable Test
Real-Time Results (time in ms)
28
Some Test ResultsTransaction Response Time (ms)
29
Some Test ResultsTransaction Success Rate (ms)
30
References

• TPC BENCHMARK C, Standard Specification Revision
  5.6, Transaction Processing Performance Council
  www.tpc.org.
• Spirent Communication Avalanche User Guide
• Oracle Documentation
• Apache Tomcat documentation
• rfc2616 Hypertext Transmission Protocol HTTP/1.1

31
Questions?

• Thank You