Toad World® Forums

New feature in 6.9 - ability to adjust loaded benchmark data?

Release Notes - New in Release 6.9
Standard Benchmarks:

  • Added ability to adjust loaded benchmark data to a new scale factor without total recreate/reload.

Does any one know this new feature? Can you please specify where it is located and how to use it? It is a nice improvement to shorten the loading time.

BMF does this automatically when the create benchmark objects is executed. When it detects that the tables already exist, BMF will determine what scale factor is already loaded into the tables and then, based on the desired scale factor as set in the benchmark objects properties, either load the mssing data, if the scale factor desired is greater than the loaded data scale factor. If the loaded scale factor is larger than the desired scale factor, BMF will delete the excess data.

So no action needs to be done by the user.

What if I have used the loaded data (for example, TPC-E scale = 5) to run the multiple user workload (TPC-E transaction mix), the data is dirty already.

  1. Can I use this used dataset to add to scale = 10?

  2. Does BMF clean up those changes from the mixed workload? Or no need at all?

  3. Is the dataset created this way as clean as the dataset created from scratch? Any performance difference?

  1. Yes. BMF runs refresh functions and the existing data to get it back to a pre-test state before adding the additional scale factor data, 10 in this case.

  2. Yes, the refresh functions will cleanup those changes made by the mixed workload test.

  3. The dataset is as clean as the dataset created from scratch. There may be some number changes but nothing that will affect performance.

That is really neat. It should be included in the user guide. I do not see it there.

  1. What does refresh function do internally? Delete, reindex? Or more efficient way to clean up? How long does it take to clean up the dirty data from the mixed workload running for 2 hours?

  2. “There may be some number changes” – What are the numbers?

  3. Can I call the refresh function separately? Good to see how it works and how long it takes to reset the state.

  1. The refresh functions perform delete, updates, etc. Everything that is necessary to get the data back to the initial state. The length of time it takes really depends on the initial scale factor than the length of time the mixed test runs since it will go perform updates. deletes looking for key values so it will scan the entire table/index.

  2. An example would be quanity columns which are updated from the mixed tests. If the data from these columns are not used in any decision making of the transactions themselves then there is no performance impact not updating them back to the original values. This will save time for the refresh.

  3. You can import these refresh transactions into BMF tests using the Transaction wizard, so you can call them separately.

Just as a side note – I always break every BMF project into two parts – data load in schema #1 and run workload in schema #2. Then I hand code the run workload
portion to do a CTAS (create table as select) to first populate/refresh the data in schema #2 from data in schema #1. This approach runs circles around trying to do anything from the client/console. I can refresh a terabyte sizes project in about 15 minutes
since al the work gets done internally by the database on the server. For large projects with many runs this is the only way to work …

Bert Scalzo

Engr Tech Principal Engineer

Dell | Information Management

office
+1 469 888 5302

fax
+1 949 754 8999

cell+1 469 693 3300

Quest Software is now part of Dell

From: Kevin Dalton [mailto:bounce-Kevin_Dalton@toadworld.com]

Sent: Friday, June 07, 2013 6:09 PM

To: bmfstandards@toadworld.com

Subject: RE: [Benchmark Factory for Databases - Standard Benchmarks Discussion Forum] New feature in 6.9 - ability to adjust loaded benchmark data?

  1. The refresh functions perform delete, updates, etc. Everything that is necessary to get the data back to the initial state. The length of time it takes really depends on the initial scale
    factor than the length of time the mixed test runs since it will go perform updates. deletes looking for key values so it will scan the entire table/index.

  2. An example would be quanity columns which are updated from the mixed tests. If the data from these columns are not used in any decision making of the transactions themselves then there
    is no performance impact not updating them back to the original values. This will save time for the refresh.

  3. You can import these refresh transactions into BMF tests using the Transaction wizard, so you can call them separately.

Thank you Kevin for the clarification. That is a very useful feature.

Bert’s note is very practical as it saves a lot of time. It seems need 2x space though.

BMF is a great performance benchmarking tool and there is a lot of room to play around.