BFScripts to pass bind variables to your SQL scalability test

Quest Software

Sujets

• Jobaria
• Transaction informatique
• Testudo
• Server Message Block
• Exécution par éléphant
• Amitabh Bachchan

Informations

• Publié par: Zeyt
• Nombre de lectures: 127
• Langue: English

Extrait

BFScripts to pass bind variables to your SQL scalability test Amit Parikh May 12, 2008

Quite often, applications and databases are load tested to ensure that they are meeting the needs of the business they support. While using the Toad Development Suite for Oracle, I am able to take advantage of Benchmark Factory, which offers capabilities to create and conduct a variety of database load testing projects.

One of the challenges testers face is automating the input of bind variables into their load test, allowing them to execute the transactions in the manner intended by application usage, but without the need to intervene by manually entering bind values.

Below I will explain how I created and executed a load scenario using the BF Scripting Wizard. The load scenario is comprised of one database power user which needs to generate a report for their constituents. The user will populate a temp table with the data that is needed and then subsequently run a query against that temp table to retrieve the data they for the report. The goal is to execute this user with concurrent load by increasing the number of virtual users executing this transaction so that I can identify any degradation that may occur using the “Run Reports” found in Benchmark Factory.

Overview of Benchmark Factory Benchmark Factory is a database performance and code scalability testing tool that simulates users and transactions on the database and replays production workload in nonproduction environments. This enables developers, DBAs, and QA teams to validate that their databases will scale as user load increases, application changes are made, and platform changes are implemented. Benchmark Factory is available for Oracle, SQL Server, DB2, Sybase, MySQL, and other databases via ODBC connectivity.

Benchmark Factory places stress on a database system, which is typically hard to achieve in a standard testing environment. A system typically breaks under extreme load. By identifying system capacity and performance bottlenecks before they occur, Benchmark Factory facilitates proactive testing, which in turn reduces downtime, development costs, and potential loss of revenue. Benchmark Factory allows you to: Determine system throughput and capacity for database systems Simulate thousands of concurrent users with a minimal amount of hardware Find applications that do not scale well with an increase in the number of users Find breaking points, weak links, or bottlenecks of a system

Quantify application or server performance All test results are collected and stored in the repository for data analysis and reporting. Benchmark Factory collects a vast amount of statistics, including overall server throughput (measured in transactions per second, bytes transferred, etc.) and detailed transaction statistics by individual workstation producing a load. You use these statistics to measure, analyze, and predict the capacity of a system. Building the Jobs and User Scenarios

Once Benchmark Factory is launched, you can “rightclick” on the project name and choose to create a new job, for the purposes of this scenario, let’sthis“Job 1”. Within thejob, “rightclick” again and choose to create “new load scenario” where you specifically choose to “Test SQLfor Scalability”

From here, the User Scenario Wizard appears allowing the creation of the user you want to put under a load test.

My user scenario will contain two SQL Statements:  One SQL statement to build a temp table off of a query.  One SQL statement to query the temp table

Controlling the amount of load placed on the database In the figure below, you will see how BMF allows you to control the number of executions or execution time for the given query, and in the second tab, you can apply the appropriate amount of virtual users that you want to conduct this test under. I have chosen to start with 1 user, incrementing up to 10 users concurrently over a span of time.

The third tab mentioned above contains options that allow you to store the results of the load test once it has been completed. All of the test results postexecution are stored in a repository that can be used to create run reports that outline the load test and point out where degradation may be affecting a particular user load level.

Making use of Bind Variable using the BFScripts Now that we have our Job and User Scenario created with the appropriate amount of user load, we need to address how we are going to populate these transactions with the required bind variable it will prompt for. Benchmark Factory provides a scripting capability as a means of inserting builtin functions for use in scripts. In BFScripts, a function is a formula that takes one or more values (arguments), performs an operation, and returns a value that simulates realworld user activity. Functions are used alone or as building blocks in creating complex user activity. Randomized data is important when attempting to simulate realworld user activity because data that is random prevents a server from using data stored in its cache. One of the functions is called “$BFFileArray” which selects and returns a single item from a commadelimited file. The item returned depends on the mode selected. The syntax of the statement is also slightly different for each mode. Adding the use of $BFFileArray to the execution of the “insert into customer_temp” sql statement, can be done by clickingon the button on the upper right of the “Prepare SQL” frame. From there, choose the category “File Access” and select “$BFFileArray”

And then proceed to fully qualify the data file name as well as the retrieval mode (see below). In my scenario, I chose to read through the delimited file sequentially.

Here is a screenshot of the data file I used for this example. These are the sample rows from the ow_customers table that is being referenced here.

You can test the query using the “green play button” that appears above the sql statement itself. Depending on the “retrieval mode”, Benchmark Factory will select a value from the delimited file you pointed against to execute the query that prompts for a bind variable. A quick save of the project “Demo” and the job is read to be submitted for a load test.

Executing the Job and using the Run Reports Now I’m ready to submit my job and see how it performs under a load test. Benchmark Factory gives me the option of running this immediately, or scheduling the task to run at specified time. I can also add “realtime counters” to the load test and add server specific metrics to the run Reports if need be.

Depending on how many virtual users that is required, an agent may or may not be required to execute the transaction(s) in the load test. An agent is required when 20 or more virtual users are submitted in the load test. Below is a view of the transactions per second as the number of virtual users rose from 1 to 10. Although this is a very small number of virtual users, this can be scaled upward to show where performance begins to degrade, and that can point you to SQL tuning or server configuration opportunity.

Conclusion Benchmark Factory has a complete reference on BFScripts and how to use them. Just open the Benchmark Factory Help and navigate to the book called “BFScripts”where there are several different categories of usage that will enhance the database load testing effort. Each is equipped with a brief description and an example of how the script can be used. Overall, I was able to generate the load I need for this particular test, and was successful in using BFScripts to populate that load test with bind variables. This saved me time and effort in what would have been a manual task. To learn more about Benchmark Factory and other Quest Software solutions, please visit the following pages: Benchmark Factory Home Page:http://www.quest.com/benchmarkfactory/ Toad World Home Page:www.toadworld.com Quest Software:www.quest.com

About the Author Amit Parikh is a manager of solutions architects based out of the North American HQ in Aliso Viejo, CA. He has been with Quest since 1999 working in Applications and Database Management product areas and has over a decade of experience working with RDBMS related technologies.