load and performance testing a database
Couple of questions for you all about load testing and performance testing a database for an enterprise scale application:
1. What sort of metrics are recorded? (standard ones like throughput, response time, etc?)
2. How are the metrics recorded? (i.e. on a "user" basis, a "connection" basis, a query level basis?
3. What tools support database load and performance testing well?
4. Shrink wrap tool, or custom made tool?
5. What tools support ODBC and JDBC and JDBC across Windows, Linux, and Solaris?
Thanks in advance for any feedback.
Re: load and performance testing a database
<<1. What sort of metrics are recorded? (standard ones like throughput, response time, etc?)>>
In your tool you collect:
* User connections
* Received Rows/Sec
* Average Rows/Response
* Processed Rows/Sec
* Avg Row Process Time: Average time in milliseconds for processing a row by an sqlexec command.
* Avg Execution Time
* Avg Preparation Time: Average time in milliseconds to execute an sqlprepare command.
Then you collect metrics on the server for:
* Database memory usage
* Database processor usage
* Database Network Usage
Depending on your database you can monitor:
* Cache usage for index, Stored procedures, data
* Table scans
* Database page usage/splits
The you monitor/measure for the occurence of
* Time outs
* Resource wait times
* Dropped connections
And never forget to analyze all your SQL on index usage before you even start to test.
<<2. How are the metrics recorded? (i.e. on a "user" basis, a "connection" basis, a query level basis?>>
Depends on your goal for your test, but typically I would go as low as the query level.
User basis would be my next step up and the complete usage (full load).
<<3. What tools support database load and performance testing well?>>
I use scripts created in Rational PerformanceStudio to emulate SQL. Most of the other load testing tools can do the same.
In purely testing the database I really do not care about the client part of the equation (yet).
<<4. Shrink wrap tool, or custom made tool?>>
Buy, unless you are in the tool smithing business.