As many of you are aware Quest provides a tool known as Benchmark Factory, BMF for short. BMF is a load generation tool that also allows you to install small databases for testing. The tools out of the box can provide TPC-B, TPC-D, TPC-H, AS3AP and many other standard benchmarks as well as allowing goal and scalability tests from your own code, either manually entered or scanned from text or, in the case of Oracle, from active database memory. BMF does all this for several databases such as Oracle, SQL Server, DB2 and MySQL with no additional licenses required.

 

In the last year and a half I have been involved with 2-3 clients who wanted to do large scale testing with the BMF suite, like between 2000-20000 users and hundreds of gigabyte databases. Usually the conference call begins with “BMF doesn’t work” and goes down hill from there. However, BMF does work, but there are certain things that need to be addressed before BMF is used.

 

The first thing to remember about BMF is that it is a load generation tool providing the capability to run benchmarks and other tests against a standard benchmark database or in the case of application specific code, against your own database. As a courtesy we provide the capability to generate the basic database structures and build small test environments for the standard benchmarks. BMF is not a benchmark artificially intelligent facilitator (BAiRF for short.) BMF cannot analyze your entire system including the disk setup and tell you how to reconfigure your disk array, partition the specific size of database tables and indexes and load a terabyte database in under 3 hours. Maybe someday we will come up with BARiF but for now we have BMF.
 

The next thing to remember is that BMF is not an analytical tool that will look at your database setup and make recommendations as to optimizing the parameters or configuration for a benchmark.  Perhaps when we build BAiRF we can add in that functionality; however, now you must use Performance Analysis or Grid Control (with the appropriately licensed tuning and diagnostic packs) to give you tuning recommendations. Many database and perhaps system parameters will need to be adjusted to achieve a successful benchmark.  A good source for what needs tweaking is the www.tpc.org website in the full disclosure documents. In a full disclosure document the TPC-C author needs to completely describe their system setup, including initialization parameters for Oracle, how they loaded the data, and, how their disk subsystem was organized.
 

Another item to remember is that BMF is not an IO subsystem analyzer that can tell you if you have configured your RAID setup with enough disks, have enough host bus adapters or have utilized the proper form of RAID, all of this falls on your shoulders (at least until BAiRF).  In order to get a good benchmark result you have to provide high IO bandwidth with maximum concurrent access.  This is only achieved through large numbers of disks in a RAID10 configuration, in most cases. For example, in order to get decent TPC-H values (the data warehouse/DSS benchmark) the normal ratio of disk capacity to database size is 20-30 to 1, so if you want to run a test against a 1 terabyte database, you have better provided 20-30 terabytes of storage. This 20-30 to 1 ratio is based on the study of many TPC-H full disclosure documents available from www.tpc.org. This amount of disk space is needed not because of some need to pay large power bills, but because of the needed disk IO requirements of the TPC-H. There is a direct correlation between the number of disks and the success of the TPC-H. Of course having said this, you may notice some recent benchmarks where the disk capacity to database size ratio is much smaller. These are using advanced caching and in-memory database systems as well as solid state disks. A single 2 terabyte solid state disk array can provide 100,000 IO/sec and fits in a 4U rack space.
 

Finally, all that BMF provides for database benchmark physical build and load is the basic structures and capability to load a small database data set. In order to achieve good results with any of the large scale benchmarks you must add indexes, use partitioning and use advanced loading techniques such as external parallel tables with create-table-as-select (CTAS). I suggest if you need to build large datasets for a large scale test you use the dbgen tool which is available as a free download from, you guessed it, www.tpc.org. I also suggest you spend some time burning the midnight oil studying a few of the full disclosure reports, particularly the appendices where they describe in great and gory detail how they set up the database, system and disk arrays for their test.  Why invent the wheel?
 

So, what can BMF do for you out of the box? It can install and run small standard benchmarks (however, you may have to add a stray index or two) and can be used to perform scalability tests on code as well as goal tests. The BMF tools can harvest code from text, trace files or active memory and provide the needed scripts to provide for random input of any of the needed variables. I myself have used BMF to test the applicability of partitioning, update on commit materialized views for testing, scalability tests using code from a users application to test new hardware, determine what additional hardware would be needed to support a larger user base and database data set and run TPC-C and TPC-D against solid state arrays in both single and RAC systems. I have also used BMF for harvesting code from single trace files, multiple trace files, active memory and text files as well as done manual entry.

 

As long as you understand the limitations of BMF, what you want to test and the benchmark or test you are doing, BMF can provide great benefits to your organization. On the other hand if you refuse to do the homework needed to properly layout your disk array and setup your database for the test you wish to run, then you will be faced with great frustrations. So unless you want to wait for BAiRF, just use BMF but do your homework and you should find it satisfactory.