Kevin Dalton's Blog

BFScripts, Part 3

Kevin Dalton — Tue, 09 Mar 2010 15:59:00 GMT

In the final part of this series on BFScripts, we will look at the different ways you can use BFScripts to your benefit, including when performing user load tests and when connecting to a database.

The most common use of BFScripts is to randomize SQL statements when simulating large numbers of users. As I mentioned in the first part of this blog series, , you will yield unrealistic results by submitting the same SQL from each virtual user. Doing so will cause the database server to cache the SQL results, thus increasing performance, and the real world is never that predictable.

A better way to perform this test involves randomizing the SQL transactions using BFScripts for a bind parameter value, as seen above. Using the same SQL statement above, you can convert the BFScript to a bind parameter value, as seen below.

Another little known, but useful, way to use BFScripts is in the connection profile to change database logon credentials. For example, you can use BFScripts to find Oracle profile user names and passwords, as seen in the screenshot below.

You can find more details on how to do this at the Benchmark Factory community:

BFScripts can also be used in the command line for an external file you want to execute before or after an iteration. Using BF Scripts here allow you to gather information such as current userload, to external scripts. This will help you as you gather more information about performance, create data files, and so on. As you can see below, we are calling a batch file to create a data file that will be used to read real data inside executed SQL statements.

Yet another way to BF Scripts is when customizing your Benchmark Factory job by adding Execute File steps. These steps can be used to restore a database before a test run and when changing the database configuration. There is a video located on the BMF community showing how to add BFScripts to customize a BMF job.

Do you use BFScripts to control connecting to the database server or for executing external commands? Is there anywhere else you would like to see BFScripts available? I’d love to hear your comments!

BFScripts, Part 2

Kevin Dalton — Tue, 23 Feb 2010 14:28:00 GMT

In this blog, part 2 of a three part series, I will show you some ways BMF helps you in creating your BFScripts.

BFScripts, Part 1

Kevin Dalton — Fri, 01 May 2009 18:22:00 GMT

One question that has been coming up lately is how I can randomize my SQL so that not every user is submitting the same statements. This is very important in creating a realistic workload to correctly perform a database performance test. Without this step the database will either pull data from cache or there will be a larger than normal amount of constraints/conflicts, such as updating the same row in a table.

Fortunately, Benchmark Factory has a very handy scripting language called BFScripts. Although it is more of a macro language than a scripting one, it does provide several easy to use scripts to randomize data such as numbers, characters, dates, etc.

So, in this first of a three-part blog, I will give you an introduction to BFScripting and its common uses.

Let’s say that you have a simple SQL statement as shown below;

SELECT CustomerID, FirstName, LastName
FROM Customers
WHERE LastName like ‘SM%’

As you can see, we are searching for customers whose last name starts with the letters SM. When I run this SQL with thousdands of users, it would not be realistic since all users would not be looking for the same characters. This is where BFScripts can be used. All BFScripts start with $BF and can perform a large number of randomization functions such as numbers, characters, dates, etc. In this case we want to randomly select two characters, so we can use the $BFRandStr function like:

SELECT CustomerID, FirstName, LastName
FROM Customers
WHERE LastName like ‘$BFRandStr(2,ALPHA_UPPER)%’

And when this is executed the entire $BFRandStr function will be replaced with two random uppercase alphanumeric characters like:

SELECT CustomerID, FirstName, LastName
FROM Customers
WHERE LastName like ‘BP%’

Now the above is just a simple example. You can also use BFScripts within BFScripts to create the output you need. So let’s say we have a similar select but you would like to use your own data, such as:

SELECT CustomerID
FROM Customers
WHERE LastName = ‘x’
AND FirstName = ‘y’

You can create a text file with your own data:

"Jones","Kevin"
"Smith","Steve"
"Cool","Joe"

And for this example we will call it mydata.txt. Also, we would like virtual user one to use the first row of data, virtual user 2 to use the second row and so on. You can use a combination of the $BFFileArray and $BFCurrentUser to accomplish this:

SELECT CustomerID
FROM Customers
WHERE LastName = ‘$BFFileArray(“c:\mydata.txt”, SELECTION, 1, $BFUserIID())’
AND FirstName = ‘$BFFileArray(“c:\mydata.txt”, SELECTION, 2, $BFUserIID())’

When the $BFFileArray script is being evaluated, the BMF script engine sees the $BFUserID() and evaluates it to be the virtual user number. This number is then used by the $BFFileArray to select the row number, so virtual user 1 will use row 1, virtual user 2 will use row 2 and so on.

There are several BFScripts that are available to use, so please refer to the BMF help file for more information and examples on how to use the scripts.

What level of experience do you have with BFScripts? Do you have a favorite BFScript function? Is there a function you would like to see? I’d love to hear your comments!

Benchmarking and Latency

Kevin Dalton — Thu, 12 Mar 2009 16:41:00 GMT

Welcome to my first blog! I am very excited about this opportunity to relay information about benchmarking/performance testing and, in particular, Benchmark Factory for Databases (BMF), but where to begin? For my first blog, I’ll start on the topic of latency.

One key thing to understand when doing testing is the characteristics of the workload, and a big piece of the workload is how fast the transactions are submitted to the system under test - or what is commonly called latency. The two most common latency types are ‘Keying’ and ‘Think’ time.

Keying is a delay before the SQL is sent to the database server and is used to simulate a real user keying in a request before submitting it. A good example of this is doing a user typing in a search for customers who live in a particular state. Think time is the delay applied after a SQL request has been processed and results returned to simulate the user thinking about what to do next. Figure 1 shows this relationship between keying and think time with the SQL execution.

Figure 1 – Keying and Think time relationship

There is another latency type called ‘Interarrival’ time and it is a dynamic think time used to set the rate that requests are sent to the server. So if you want to make sure that requests are sent to the server at a rate of 1 per every 60 seconds, you would set the interarrival rate to 60 seconds. The dynamic delay is then calculated to see how much time is left, after the time for the SQL request, to guarantee that 1 minute would elapse before the next SQL is requested. Using our example of an interarrival rate of 60 seconds from above, if the request takes 23 seconds then calculated wait time would be 60 seconds – 23 seconds = 37 seconds, as seen in Figure 2 below.

Figure 2 – Interarrival Time

So if the next SQL execution would take only 4 seconds to execute, then the interarrival delay would be 56 seconds.

Why would you use the different latency models? Keying and Think time are best used if you want to have a workload that simulates real world users vs. submitting the SQL as fast as possible. Modeling your workload to simulate real world users allows for easier result interpretation. So if your results say that your server maximum throughput is at 1000 users, you know that 1000 real users are the most your server can handle. If you decide not to use real world modeling, then you need to figure out how many real users your “fast” users equate to.

Interarrival time is commonly used if you want to guarantee the timing relationship between the executing SQL. The majority of the time this is used in a playback/replay workload where the user wants to playback the captured workload with the same timing relationships.

What are your experiences with workload testing and latency? Which types of latency do you most often test for? I’d love to hear your comments!