Scaling for Multiple CPUs

Run the Demo Applications as a Prototype

Performance impact: Variable

One way to determine the approximate scaling you can expect from TimesTen is to run one of the scalable demo applications, such as tptbm, on your system.

The tptbm application implements a multi-user throughput benchmark. It enables you to control how it runs, including options to vary the number of processes that run TimesTen operations and the transaction mix of SELECTs, UPDATEs, and INSERTs, for example. Run tptbm -help to see the full list of options.

By default the demo runs one operation per transaction. You can specify more operations per transaction to better model your application. Larger transactions may scale better or worse, depending on the application profile.

Run multi-processor versions of the demo to evaluate how your application can be expected to perform on systems that have multiple CPUs. If the demo scales well but your application scales poorly, you might try simplifying your application to see where the issue is. Some users comment out the TimesTen calls and find they still have bad scaling due to issues in the application.

You may also find, for example, that some simulated application data is not being generated properly, so that all the operations are accessing the same few rows. That type of localized access greatly inhibits scalability if the accesses involve changes to the data.

See the Quick Start home page at installation_dir/quickstart.html for additional information about tptbm and other demo applications. Go to the ODBC link under Sample Programs.

Limit Database-Intensive Connections Per CPU

Performance impact: Variable

Check the lock.timeouts or lock.locks_granted.wait fields in the SYS.SYSTEMSTATS table. If they have high values, this may indicate undue contention, which can lead to poor scaling.

Because TimesTen is quite CPU-intensive, optimal scaling is achieved by having at most one database-intensive connection per CPU. If you have a 4-CPU system or a 2-CPU system with hyperthreading, then a 4-processor application runs well, but an 8-processor application does not perform well. The contention between the active threads is too high. The only exception to this rule is when many transactions are committed durably. In this case, the connections are not very CPU-intensive because of the increase in I/O operations to the file system, and so the system can support many more concurrent connections.

Use Read Operations When Available

Performance impact: Variable

Read operations scale better than write operations. Make sure that the read and write balance reflects the real-life workload of your application.

Limit Prepares, Re-prepares, and Connects

Performance impact: Variable

Prepares are resource-intensive operations that do not scale well compared to SQL execution operations. Ensure that you use parameterized SQL statements wherever possible and always pre-prepare statements that run more than once.

The stmt.prepares.count column of the SYS.SYSTEMSTATS table indicates how often statements were prepared directly by an application. If the stmt.prepares.count column has a high value, ensure the following:

Your application uses parametrized statements.
Your application only prepares any given SQL statement once and not for every single execution.
Your application does not connect and disconnect frequently from the database. If that is the case, consider using long active connections.

Connect and disconnect operations are resource-intensive operations that do not scale well. Ensure that your application minimizes the number of connect and disconnect operations. Use long active connections with parameterized and re-prepared SQL statements to improve performance.

If your application cannot avoid frequent connect and disconnect operations or you are unable to modify the application to use long-lived connections, consider using connection pooling. Connection pooling can reduce the frequency of connect and disconnect operations and improve the utilization of prepared statements compared to the case where an application frequently connects and disconnects from the database.

Enable Indexes to be Rebuilt in Parallel During Recovery

Performance impact: Variable

On multi-processor systems, set RecoveryThreads to minimum(number of CPUs available, number of indexes) to enable indexes to be rebuilt in parallel if recovery is necessary.

If a rebuild is necessary, progress can be viewed in the user log. Setting RecoveryThreads to a number larger than the number of CPUs available can cause recovery to take longer than if it were single-threaded.

Use Private Commands

Performance impact: Variable

When multiple connections run the same command, they access common command structures controlled by a single command lock. To avoid sharing their commands and possibly placing contention on the lock, you can set PrivateCommands=1.

The use of private commands increases the amount of temporary space used, but provides better scaling at the cost.

See PrivateCommands in the Oracle TimesTen In-Memory Database Reference.