Project Notes


Project: WebBench
Author: JBuilder Team
Company: Inprise
Description: WebBench creates a set of large sample tables in a JDataStore or other database, runs transactions on multiple concurrent connections, and reports on throughput.  It can be used to evaluate a database's performance.

Introduction
Setup
Running WebBench:  JDataStore
Running WebBench:  other databases
Interpreting benchmark results
Organization of WebBench's code
 

Introduction
WebBench serves several purposes:

Although most of this document discusses using WebBench transactions with JDataStore, WebBench can be run against other database servers as well.

WebBench generates sample data for an order-entry application and runs sample transactions. Through the application's UI or code you can set parameters such as table size, number of connections, type of transactions (read-write, read-only or both), and the duration of the test. When you select Run, the requested connections to the database are established and begin running transactions. After it has run for the requested time, WebBench shuts down the connections and reports the number of transactions executed.

Unlike most sample applications, WebBench features industrial-strength - and industrial-sized - tables. Generating the database takes five to ten minutes, and the files occupy about 150 megabytes (this figure is for a JDataStore and its log files). The data itself isn't very interesting - sometimes just strings of random characters. However, the data is realistic where it counts: in the mix of datatypes used, the relationships between tables, the index defined, and the values in key columns. As a result, this data can be used to simulate realistic production transactions and to investigate how throughput varies with table size, number of concurrent users, transaction type, and other variables.

WebBench's UI isn't dramatic - just the necessary menus and status reporting. You may want to use the JDataStore Explorer along with WebBench to see what's happening inside a store file.. If you do, remember that WebBench and JDataStore Explorer can't open a store file at the same time. Only open the store in the JDataStore Explorer between WebBench operations, and close it again before the next WebBench run. If you run read/write transactions, you'll see that new records are added at the end of the ORDERS and ORDER_LINE tables (check the values in the timestamp column to see when they were added). Several other tables are updated. Study the SQL statements in ReadWriteTx.java for details.

Setup
Add the javax/sql package to WebBench:  WebBench uses DataExpress's JdbcConnectionPool and JdbcConnectionFactory components to implement connection pooling.  These components use classes in the Java 2 Enterprise Edition's javax/sql package that are not distributed with JBuilder.  There are two ways you can get them:

In either case, define a JB library pointing to this jar and add it to the WebBench project.  One way to do this is: Start your database:  If you plan to use WebBench to test the performance of a database other than JDataStore, make sure that your database server is running.  Also start any necessary connection software; for instance, if using InterBase, be sure InterServer is running.

Running WebBench:  JDataStore
Follow these steps to create the database and populate it with data

  1. Select Bench | Options. Use theFilename and File Path options to specify the name and location of the sample JDataStore files  that will be created.  If you accept the defaults, webbench.jds will be created in the .../samples/JDataStore/WebBench subdirectory of your JBuilder directory.  By default, the store's log files will go in the same directory.  You can direct them to another location by setting the LogA Directory option.  For now, accept the default values for the other options.
  2. Chose Bench | Create Database. The data store is created, but so far there's nothing in it.
  3. Bench | Load Data is now available. Select it. WebBench will create the sample tables and fill them with data. The tables are large, so this may take ten minutes or more. When the status-line message reports "Data load complete", you can explore the newly-created tables using JDataStore Explorer if you want. Close the data store in the Explorer when you're done.
Now you're ready to run some benchmark tests:
  1. Optionally, you can select Bench | Options and set the following options.  To simplify your first run, you can let them take their default values.  Later, you can return to this dialog and see how changing values here affects JDataStore's performance.
  2. Select Bench | Run and set these parameters:
  3. Click the Run button. The threads you requested are created and begin running transactions. When the time you selected elapses, WebBench reports the number of transactions executed and the number of transactions per second.
  4. Optionally, you can use JDataStore Explorer to verify the integrity of the store file after running the benchmark.
Here are a few other WebBench operations that you might find useful: Running WebBench:  other databases
WebBench does most of its database work through SQL statements that should run on any database server. However, some operations, such as establishing the connection to the server and creating and dropping databases, are done differently by each server.  WebBench code to perform these operations is separated out into classes called "drivers".  In addition to its default driver for JDataStore, WebBench includes drivers for Cloudscape, InterBase (via InterClient), Oracle, and PointBase.  (Don't confuse these driver classes with JDBC drivers.  To connect to another database, you'll need to provide a JDBC driver and, of course, the database software itself.).

A WebBench run against another database only differs from a run against a JDataStore in a few details:

To write a driver for a database that WebBench doesn't support, implement the Driver interface.  Be sure that your database supports the fairly standard SQL that WebBench uses. The SQL feature that's most likely to be problematic is the CURRENT_TIMESTAMP keyword, which not every server supports.

Interpreting benchmark results
By itself, the number of transactions WebBench can execute per second means very little because it will vary widely depending on the machine you run WebBench on. It's the comparisons between runs that are interesting. You might look at:

It's important to realize that although WebBench is useful for some comparisons, it doesn't represent how databases are often used.  WebBench threads hammer on the database steadily, which is useful for testing how a database handles a heavy load, but is very different from having human users who may spend a long time working on their selected data before saving any changes back to the database. Four or five WebBench threads might simulate the activity of dozens or hundreds of actual users.  In WebBench, connection pooling is costly; a thread can keep a connection busy so much of the time that it's more efficient to give each thread its own connection.  In more typical use, however, each user needs a connection only a small fraction of the time, so pooling is effective.

If you've written a WebBench "driver" for another database server, you can compare its performance to JDataStore's. Keep in mind that it's very hard to get comparable numbers, even when both databases run on the same machine.  For instance, one database may be configured to use many more machine resources than the other.

Also keep in mind when comparing benchmark results that there is a element of randomness in the tests. Random numbers are used to determine which customer's record is selected, how many line items are inserted, whether or not a transaction is rolled back, and so on. As a result, one benchmark run might have to do more work than a second run with the same parameters. Finally, read/write transactions add records to several table, which might perturb the situation slightly.

Organization of WebBench's code
Application structure and UI: WebBenchApp and WebBenchFrame are the default JBuilder application files. WebBenchApp is the main executable file and WebBenchFrame is the UI. WebBenchFrame is designable, but because the UI is one big jTextArea, there's not much to see in the UI designer. However, you can open the menu designer and examine the menu's organization, properties, and event handlers. OptionsDialog is opened from Bench | Options and RunDialog is opened from Bench | Run.

The AppProperties class is responsible for storing and managing settings from the options dialogs. AppProperties knows how to save its data to disk (as WebBench.properties, in the java user directory) and reload it. At startup, WebBenchFrame calls AppProperties' static getAppProperties() method. This reloads previous settings, if any, and provides WebBenchFrame with any information about options that it needs. Other classes use AppProperties in the same way.

Event handlers and backup: WebBenchFrame's event handlers are where the application's UI and its database manipulation meet. The major menu commands are File | Backup, Bench | Load Data, and Bench | Run. The code in all three is organized in the same way. As an example, let's look at BackupDataItem_actionPerformed, the event handler for File | Backup. Associated with it is the class BackupThread, defined in WebBenchFrame.java. The event handler creates an instance of BackupThread and sets it running. BackupThread in turn creates a BackupData object which does the necessary work with JDataStores while BackupThread handles the status reporting in the WebBench UI.

The BackupData class is fairly simple. It gets the current store file name from AppProperties, prepends "backup" to it to name the backup, creates the backup store file in the same directory (deleting the existing file of that name, if there is one), and uses JDataStore's copyStreams() method to copy everything in the original store file to the backup.

One other small class comes into play here. A transactional JDataStore consists of a single file for data, one or more log files, and two status files. FileList is a utility class that simplifies working with this set of files. BackupData and JDataStoreDriver use FileList.

Server-specific work: Driver is an interface that encapsulates the server-specific work that WebBench needs to do. JDataStoreDriver is an implementation of Driver for JDataStore. Almost everything in WebBench that is specific to JDataStore is in JDataStoreDriver. CloudscapeDriver, InterClientDriver, OracleDriver, and PointBaseDriver are implementations of Driver for other databases.

The event handler for Bench | Create Database gets the WebBench driver name from AppProperties, gets an instance of the driver, and calls the driver's createDatabase() method. The event handler for Bench | Drop Database is analogous.

Loading tables: We've seen how WebBenchFrame's event handler for the File | Backup menu command works. Bench | Load Data is similar. The event handler creates and runs an instance of DataLoadThread, defined in WebBenchFrame.java, and sets it running. DataLoadThread handles the UI updating and creates a LoadData object to do the database work. The novelty here is that LoadData in turn instantiates another class of interest, Tx. Tx and LoadData both execute SQL statements. Tx's statements, which create tables and indexes, are hard-coded. LoadData generates data and inserts it into the tables using prepared statements and parameterized queries. LoadData intermixes calls to Tx with its own work. It stops running when it's done and thus returns control to the UI.

Running transactions: Bench | Run present the Run Options dialog. Assuming you close the dialog by clicking the Run button, the menu command's event handler starts a TxRunnerThread (as usual, this class is defined in WebBenchFrame.java), which creates and runs a TxRunner. Parameters to TxRunner's constructor tell it the number of threads to create for each type of transaction. It creates the appropriate ReadOnlyTx and/or ReadWriteTx objects, sets each as the target of a new thread, and starts the threads. Each ReadOnlyTx and ReadWriteTx instantiates a driver object and gets a connection from it.  If the driver is a JDataStoreDriver and the Use Pool options was selected, the connection comes from a pool managed by Pool. When every transaction objects is ready to run transactions, TxRunner signals them all to start, waits for the specified time, stops the threads, closes the transaction objects, and reports the results.

ReadOnlyTx and ReadWriteTx extend Tx, which contains the methods for synchronizing transaction runners that they both need. Their work is in two parts: first, they create prepared statements for all the SQL statements they will run; then, when TxRunner tells them to start, they run transactions until told to stop. Like Tx and LoadData, ReadOnlyTx and ReadWriteTx do all their work using classes from the java.sql package such as Connection, PreparedStatement, and ResultSet. Because the focus here is on database operations rather than data manipulation in the application, the DataExpress component set is not used, except for isolated use of a few classes such as DataSetException.

ReadWriteTx inserts a new order record and a random number of line items for a randomly-chosen customer. The transaction runs a total of nine different SQL statements, including queries against customer, stock, and item tables among others and updates to stock and next-ID information. Not all transactions succeed; on a random basis, some are rolled back. ReadOnlyTx executes the same queries but omits the inserts and updates.