Database Benchmarking Best Practices

One of the more common requests that I get from Benchmark Factory users is to provide them with a list of best practices for using Benchmark Factory and for database benchmarking in general. Even for an experienced database professional, database benchmarking can be a completely new world and it helps to have some best practices available outside of the product documentation. I have put together a list of 10 best practices to help you get started down your database benchmarking journey. These items cover the most common pitfalls that we see users fall into and hopefully they will help you avoid them. While some items specifically relate to Benchmark Factory, most of these can apply with any benchmarking tool. 

1. Test it first! Always perform a test workload capture during an off-hour or non-peak period and use a diagnostic tool (such as Spotlight) to see if any additional bottlenecks are being caused by the capture process. While we never anticipate the capture process causing significant problems, database configurations can vary greatly and it is best to test the capture process in a low risk setting before trying to capture at peak hours.
2. Avoid long running captures if possible. Capturing workloads requires significant disk storage and puts a strain on system resources. While it is technically possible to capture for long periods of time, it is recommended to try and capture workloads in small increments. For example, you may have one capture that occurs for one hour during peak production processing and you may have another capture for one hour during a slower time.
3. Understand and adhere to benchmark specifications. When using industry standard benchmark tests, it helps to have an understanding of the benchmark specification. This not only helps you to interpret the test results but also to understand why your test may not be performing as you expect. TPC-C for instance has database scale and transaction latency components as part of the specification. Without understanding the specification, it may appear as though the benchmarking tool is not operating properly when in fact it is following the specification.
4. Use a diagnostic or monitoring tool while you are testing. Benchmarking tools are primarily designed to generate the load on the database. While they will provide base metrics such as Response Time and Transactions Per Second, they often will not advise you of bottlenecks in your database and how to remedy them. Using a diagnostic or monitoring tool such as Spotlight or Foglight will give you deeper insight into how your database is performing and why it may not be performing as well as  you expected.
5. Failure can be a good thing. Benchmarking can be used to stress test your databases and uncover vulnerabilities and breaking points with your database. This is good as you can produce (and fix) these problems in a test setting rather than have them cause major problems in a production environment. 
6. Do small changes and then measure. This is not specific to benchmarking, but it is good troubleshooting technique, and will help you achieve better results. When tuning a database, it is better to make small changes and then measure how those changes affected performance. Keep a journal of test results so you can compare later. Benchmark Factory makes this easy by providing comparison reports that allow you to compare test results.
7. Use benchmarking to help your server/storage admins help you. If you are like most database administrators, you rely on server/storage administrators to provision resources for your database. In many cases, you give them the specs that you want and they give you the resources that they feel you need. But you can take this a step further and test out these resources to ensure they perform to your satisfaction before rolling them into production. Finding potential issues pre-production will help save everyone from some headaches down the road.
8. Allocate space beforehand. Be careful with tablespaces or objects that are set to autoextend. When these operations occur during testing, database performance can be significantly impacted and test results will be skewed and may not accurately reflect database performance. 
9. Feel free to customize. Benchmark Factory supports many types of pre-defined benchmark tests in addition to capture/replay capabilities but don’t let that limit you. Jobs can be customized in numerous ways to create various test scenarios. Want to test out some new storage? You can customize the  object creation process to use different tablespaces or use in memory storage. Want to see how your database will handle excessive redo log switches? You can create a custom test to do this.

 I have only listed 10 here but there are no doubt more than this and if you have some you would like to share feel free to comment here.