Performance Testing a Greenfield Database

Question

Assuming that best practices have been followed when designing a new database, how does one go about testing the database in a way that can improve confidence in the database's ability to meet adequate performance standards, and that will suggest performance-enhancing tweaks to the database structure if they are needed?

Do I need test data? What does that look like if no usage patterns have been established for the database yet?

NOTE: Resources such as blog posts and book titles are welcome.

Answer 1

I'm in the same situation now, here's my approach (using SQL Server 2008):

Create a separate "Numbers" table with millions of rows of sample data. The table may have random strings, GUIDs, numerical values, etc. Write a procedure to insert the sample data into your schema. Use modulus (%) of a number column to simulate different UserIDs, etc.

Create another "NewData" table similar to the first table. This can be used to simulate new records being added.

Create a "TestLog" table where you can record rowcount, start time and end time for your test queries.

Write a stored procedure to simulate the workflow you expect your application to perform, using new or existing records as appropriate.

If performance seems fast, consider the probability of a cache miss! For example, if your production server has 32GB RAM, and your table is expected to be 128GB, a random row lookup is >75% likely to not be found in the buffer cache.

To simulate this, you can clear the cache before running your query:

DBCC DROPCLEANBUFFERS; (If Oracle: ALTER SYSTEM FLUSH SHARED POOL)

You may notice a 100x slowdown in performance as indexes and data pages must now be loaded from disk.

Run SET STATISTICS IO ON; to gather query statistics. Look for cases where the number of logical reads is very high (> 1000) for a query. This is usually a sign of a full table scan.

Use the standard techniques to understand your query access patterns (scans vs. seek) and tune performance.

Include Actual Execution plan, SQL Server Profiler

Answer 2

+1 birdlips, agree with the suggestions. However, database load testing can be very tricky precisely because the first and the crucial step is about predicting as best as possible the data patterns that will be encountered in the real world. This task is best done in conjunction with at least one domain expert, as it's very much to do with functional, not technical aspects of the system.

Modeling data patterns is ever so critical as most SQL execution plans are based on table "statistics", i.e. counts and ratios, which are used by modern RDBMS to calculate the optimal query execution plan. Some people have written books on the so called "query optimizers", e.g. Cost Based Oracle Fundamentals and it's quite often a challenge troubleshooting some of these issues due to a lack of documentation of how the internals work (often intentional as RDBMS vendors don't want to reveal too much about the details).

Back to your question, I suggest the following steps:

1. Give yourself a couple of days/weeks/months (depending on the size and complexity of the project) to try to define the state of a 'mature' (e.g. 2-3 year old) database, as well as some performance test cases that you would need to execute on this large dataset.
2. Build all the scripts to pump in the baseline data. You can use 3rd party tools, but I often found them lacking in functionality to do some more advanced data distributions and also often its much faster to write SQLs than to learn new tools.
3. Build/implement the performance test scenario client! This now heavily depends on what kind of an application the DB needs to support. If you have a browser-based UI there are many tools such as LoadRunner, JMeter to do end-to-end testing. For web services there's SoapSonar, SoapUI... Maybe you'll have to write a custom JDBC/ODBC/.Net client with multi-threading capabilities...
4. Test -> tune -> test -> tune...
5. When you place the system in production get ready for surprises as your prediction of data patterns will never be very accurate. This means that you (or whoever is the production DBA) may need to think on his/her feet and create some indexes on the fly or apply other tricks of the trade.

Good luck

Answer 3

You could use a tool such as RedGate's Data Generator to get a good load of test data in it to see how the schema performs under load. You're right that without knowing the usage patterns it's difficult to put together a perfect test plan but I presume you must have a rough idea as to the kind of queries that will be run against it.

Adequate performance standards are really defined by the specific client applications that will consume your database. Get a sql profiler trace going whilst the applications hit your db and you should be able to quickly spot any problem areas which may need more optimising (or even de-normalising in some cases).

Answer 4

I would do a few things:

1) simulate user/application connection to the db and test load (load testing). I would suggest connecting with many more users than are expected to actually use the system. You can have all your users log in or pick up third party software that will log in many many users and perform defined functions that you feel is an adequate test of your system.

2) insert many (possibly millions) of test records and load test again.(scalability testing). As tables grow you may find you need indexes where you didn't have them before. Or there could be problems with VIEWS or joins used through out the system.

3) Analyze the database. I am referring to the method of analyzing tables. Here is a boring page describing what it is. Also here is a link to a great article on Oracle datbase tuning. Some of which might relate to what you are doing.

4) Run queries generated by applications/users and run explain plans for them. This will, for example, tell you when you have full table scans. It will help you fix a lot of your issues.

5) Also backup and reload from these backups to show confidence in this as well.