May
14
Written by:
Bert
Thursday, May 14, 2009 5:56 AM
The title says it all – there is no such thing. This is the second benchmarking myth or “urban legend” that needs exposed. However this is the most prevalent frustration or exasperation that people attempting database benchmarks experience – and experience the hard way (i.e. their efforts majorly or totally fail to meet their initial expectations).
There are many reasons for this benchmarking failure – first and foremost is that people fail to allocate proper time for benchmarking. You cannot expect to complete the task in two weeks if you’re new to benchmarking, over-rely on tools to do all the “heavy lifting”, and when it might take days to even weeks to load the required workload test data.
It’s this second issue – over reliance on tools to do it all – that sinks most benchmarking efforts. Tools are a great thing. As I sit here typing in Microsoft Word, this task is much easier than in the past using manual type-writers. But I still have to think of the content. Yes MS Word offers a spell checker, but it can miss an obvious mistake such as “The dog eight my homework.” So MS Word makes the task easier – but the bulk of the work still falls on the writer’s shoulders. And to reiterate last week’s myth using this example, I do not ask MS Word for the document properties and then focus on word count (i.e. TPS) to measure a document’s success. It’s how it reads that matters 
Benchmarking and benchmarking tools work exactly the same way. Let’s examine what steps a successful database benchmark effort must perform – and must perform entirely and correctly in order to potentially meet expected results:
- Configure and optimize the server hardware
- Install and optimize the server operating system
- Install and optimize the server RDBMS (e.g. Oracle, SQL Server, etc)
- Create and optimize the “TEST” database
- Create database user/schema for benchmark objects (e.g. tables, indexes, etc)
- Create optimized benchmark database objects for desired benchmark scale
- Load the benchmark database objects to their initial or pre-workload state
- Run the benchmark workload as per the spec that defines transaction nature
- Monitor database performance under workload, diagnose and then optimize
- Repeat steps 6, 7, 8 and 9 until satisfied (i.e. results approximate expectations)
There are probably more steps, but I tried to keep it very simple. Plus the math is easy for ten steps. You should ONLY rely on database benchmarking tools for Step 8 – “running” the workload as per spec. That means that pre-execution and post-execution steps are for you to decide and optimize.
Many people eventually “get” the fact that benchmarking tools cannot monitor and then optimize their database. So they’ll eventually utilize tools like Quest Software’s Spotlight for Oracle and Spotlight for RAC to address this need. And while they’re not happy, they begrudgingly accept the fact that running a workload does not also mean optimizing it.
But most people just seem to feel that the benchmarking tool should automatically create fully optimized database objects to contain the data the workload operates upon. On first thought that seems fairly reasonable. But after a little reflection, how can a benchmarking tool “magically” know how many “spindles” that you have and how they are allocated or grouped. In other words, how can a benchmarking tool “guess” as how to partition your database objects? Benchmarking tools can simply issue stock and/or generic “CREATE” commands. The DBA must manually optimize all benchmarking database objects based upon their particular hardware configuration.
So in Benchmark Factory terms, I always run a benchmark in two steps (i.e. two separate BMF projects). Step one simply creates stock or generic database objects which are to be loaded and then used to load optimized database objects upon which the actual workload is performed. You can consider these initial database objects and their data as a “staging” area. Yes – that require extra storage, but disk space is cheap and your time is not.
The second step is a BMF project that does the following:
- Drop the BMF user/schema (not the staging area!!!)
- Create the BMF user/schema
- Create and load the optimized database objects
- Copy all the data from staging to BMF schema/user
- Uses CREATE AS SELECT – very efficient and fast
- Uses hints for APPEND and PARALLEL – even faster
- Collect database statistics for data dictionary and BMF schema
- Take a database performance metric snapshot (e.g. STATS PACK)
- Perform the standard workload
- Take a database performance metric snapshot (e.g. STATS PACK)
So that’s how I recommend successfully handling steps 6 and 7 for creating and loading database objects that make effective and efficient usage of your disk storage subsystem, which should lead to generally achieving or exceeding your benchmarking performance expectations. Failure to work this way will almost always lead to quite unhappy results.