1 / 31

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

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 mjafar@mail.wtamu.edu

cadee
Download Presentation

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

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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 mjafar@mail.wtamu.edu randerson@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 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

  8. The Test Environment

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

  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. Environment Preparation

  14. Test Workflow

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

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

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

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

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

  20. Issues related to testing multi-tier web-based Applications

  21. Issues related to testing multi-tier web-based Applications

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

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

  24. Appliances Level SolutionsAvailable Test Summary Results (time in ms)

  25. Appliances Level SolutionsAvailable Test Real-Time Results (time in ms)

  26. Some Test ResultsTransaction Response Time (ms)

  27. Some Test ResultsTransaction Success Rate(ms)

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

  29. Questions? Thank You

More Related