With the advent of the e-commerce insurrection, much focus has been directed towards the web-based technology that supports the Internet, such as Java, HTTP, Web Services, XML, etc. The common thread to all of the new technologies is their dependency upon relational databases to provide data essential to their business paradigm. Database platforms such as Oracle9i, IBM DB2, Microsoft SQL Server and Sybase Adaptive Server Enterprisetherefore provide the core foundation upon which business decisions are made and revenue is produced.
Whether implementing an on-line transaction processing system (OLTP) or decision support system (DSS) within a standard client/server application or distributed web-based application, the requirements are the same:
Database performance tuning is the iterative process of analyzing the ramifications of hardware and/or software configuration changes with the intent of increasing application performance while minimizing costs.
RTTS has successfully assisted in many database performance tuning engagements. Armed with a proven testing methodology and test automation best practices, RTTS has provided an integral solution for resolving many issues associated with the relational database management system (RDBMS) and operating system kernel parameters. These include:
• providing an inventory of slow or inefficient database queries
• determining the proper size of connection pools to support the arrival rate of SQL requests
• discovering the inability of a RDBMS to scale on a multiprocessor database server (RS/6000 SMP)
• establishing the best configuration sizes for data and procedure caches
ascertaining the best hardware platform to implement
• discerning the most efficient auditing scheme that would prevent deadlocks, while maintaining history of the business processes
• validating the correct indexes to employ, such as clustered indexes versus non-clustered indexes
The database performance tuning realm also extends to web clients, such as application servers, and fat clients (i.e. Powerbuilder, Visual Basic, C++). RTTS has pinpointed issues relating to the manner in which data is requested and client/server communication is enabled.
How does RTTS solve the problem?
Regardless of the RDBMS that is implemented, RTTS has a solution for tuning database servers and their clients. Although the configurable parameter terminology differs by platform, the same performance tuning concepts apply to all database server vendors.
1. Determine the level of tuning - Component-level tuning or system-level tuning? Do you want to tune the database server as an isolated component or as part of a larger application?
2. Understand the end-user community - Gather metrics regarding the manner in which the database will be accessed. What SQL queries will be executed? What business transactions will be executed? How often are transactions executed?
3. Gather performance requirements - Determining the exit criteria for tuning needs to be established in order to know when sufficient testing has occurred.
4. Automate test scripts - Create automated test scripts that issue the necessary SQL queries, updates and deletes. Generate automated test scripts that emulate the business scenarios.
5. Execute & analyze tests - Run the planned tests and collect metrics, such as response times, transaction volumes, operating system statistics, database server statistics.
6. Application Profilers - Implement ancillary tools to profile transaction characteristics. Determine the network characteristics of a transaction, such as bandwidth utilization and conversational chattiness. Ascertain the CPU utilization on the database server and client, memory utilization, query compilation and execution times.
The Solution
As a result, database server capacity and scalability is increased by addressing:
Deliverables
At the conclusion of the project, RTTS provides an Executive Summary report illustrating performance of your application and/or database server as quantified by response times, throughput, application and communication errors, system resources and capacity, as related to the particular database server tuning parameters.
The engagement will also provide a suite of automated test scripts that can be used for future testing and tuning endeavors along with a set of best practices for approaching database server performance tuning.
Whether implementing an on-line transaction processing system (OLTP) or decision support system (DSS) within a standard client/server application or distributed web-based application, the requirements are the same:
1. retrieve the data as quick as possible,
2. support hundreds or thousands of end-users, and
3. keep hardware and software maintenance costs at a minimum.
2. support hundreds or thousands of end-users, and
3. keep hardware and software maintenance costs at a minimum.
Database performance tuning is the iterative process of analyzing the ramifications of hardware and/or software configuration changes with the intent of increasing application performance while minimizing costs.
RTTS has successfully assisted in many database performance tuning engagements. Armed with a proven testing methodology and test automation best practices, RTTS has provided an integral solution for resolving many issues associated with the relational database management system (RDBMS) and operating system kernel parameters. These include:
• providing an inventory of slow or inefficient database queries
• determining the proper size of connection pools to support the arrival rate of SQL requests
• discovering the inability of a RDBMS to scale on a multiprocessor database server (RS/6000 SMP)
• establishing the best configuration sizes for data and procedure caches
ascertaining the best hardware platform to implement
• discerning the most efficient auditing scheme that would prevent deadlocks, while maintaining history of the business processes
• validating the correct indexes to employ, such as clustered indexes versus non-clustered indexes
The database performance tuning realm also extends to web clients, such as application servers, and fat clients (i.e. Powerbuilder, Visual Basic, C++). RTTS has pinpointed issues relating to the manner in which data is requested and client/server communication is enabled.
How does RTTS solve the problem?
Regardless of the RDBMS that is implemented, RTTS has a solution for tuning database servers and their clients. Although the configurable parameter terminology differs by platform, the same performance tuning concepts apply to all database server vendors.
1. Determine the level of tuning - Component-level tuning or system-level tuning? Do you want to tune the database server as an isolated component or as part of a larger application?
2. Understand the end-user community - Gather metrics regarding the manner in which the database will be accessed. What SQL queries will be executed? What business transactions will be executed? How often are transactions executed?
3. Gather performance requirements - Determining the exit criteria for tuning needs to be established in order to know when sufficient testing has occurred.
4. Automate test scripts - Create automated test scripts that issue the necessary SQL queries, updates and deletes. Generate automated test scripts that emulate the business scenarios.
5. Execute & analyze tests - Run the planned tests and collect metrics, such as response times, transaction volumes, operating system statistics, database server statistics.
6. Application Profilers - Implement ancillary tools to profile transaction characteristics. Determine the network characteristics of a transaction, such as bandwidth utilization and conversational chattiness. Ascertain the CPU utilization on the database server and client, memory utilization, query compilation and execution times.
The Solution
As a result, database server capacity and scalability is increased by addressing:
• the use of a small packet size between the client and the server
• chatty conversation over high latency network links
• large amounts of unused data returned to the client
• redundant database queries
• additional tuning methods
• chatty conversation over high latency network links
• large amounts of unused data returned to the client
• redundant database queries
• additional tuning methods
Deliverables
At the conclusion of the project, RTTS provides an Executive Summary report illustrating performance of your application and/or database server as quantified by response times, throughput, application and communication errors, system resources and capacity, as related to the particular database server tuning parameters.
The engagement will also provide a suite of automated test scripts that can be used for future testing and tuning endeavors along with a set of best practices for approaching database server performance tuning.
0 comments:
Post a Comment