Storage Provisioning for Transaction Log Files

There is a process for estimating the necessary file system space allocation for transaction log files

Note:

You can generate a more accurate estimate of the transaction log volume within a test environment.

Consider the following when estimating the necessary file system space allocation for transaction log files:

  • TimesTen keeps enough transaction log files to support recovering from either checkpoint file. Transaction log files may accumulate depending on the settings for background checkpoint operations (defined with the CkptLogVolume and CkptFrequency first connection attributes). In addition, TimesTen preallocates space for three additional transaction log files.

  • TimesTen uses fuzzy checkpoint operations for background operations. Transaction log files can accumulate during fuzzy checkpoint operations. The amount of time taken to create a checkpoint scales with the value assigned to the PermSize connection attribute.

  • Transaction log files may accumulate between incremental backups.

  • In TimesTen, transaction log files may accumulate on the master during temporary replication outages or if the subscriber is down.

    Note:

    You can limit the number of transaction log files retained with the FAILTHRESHOLD clause on the CREATE REPLICATION and ALTER REPLICATION statements.

See CkptLogVolume, CkptFrequency, and PermSize in the Oracle TimesTen In-Memory Database Reference for more information on these connection attributes. See CREATE REPLICATION and ALTER REPLICATION in the Oracle TimesTen In-Memory Database SQL Reference for more information on these SQL statements.

The estimate of the transaction log volume depends on the peak update transaction rate and the average complexity of each transaction that modifies the database.

Consider:

  • B represents the transaction log volume in bytes per transaction.

  • C represents the number of columns updated. The minimum transaction update (which is an update of a single number column) generates 400 bytes of transaction log data. Each additional number column update generates another 250 bytes.

  • V represents the average size of larger data inserted or updated (columns of type CHAR, VARCHAR2, BINARY, VARBINARY or LOB) for each write transaction.

To estimate the transaction log volume for each transaction, use this formula:

B = 400 + ((C-1) * 250) + V

Note that the first column is just 150 bytes more than all subsequent columns, as follows:

B = (C * 250) + (400-250) + V

which simplifies to:

B =(C * 250) + 150 + V

Multiply the estimated transaction log volume value (B) by the expected peak transaction rate to find the expected peak transaction log rate.

Where:

  • L = total storage space to provision for transaction log files in bytes.

  • C = average number of columns inserted or updated per write transaction.

  • V = average bytes of large data (CHAR, VARCHAR2, BINARY, or VARBINARY columns) inserted or updated for each write transaction.

  • S = time in seconds of required transaction log retention.

  • T = peak transaction rate per second averaged over intervals of S seconds.

  • f = fraction of transactions that include insert, update, or delete operations.

  • Lastly, increase the estimated file system space by an additional 30% for contingencies.

Taking all these factors into account, estimate provisioned transaction log file space in bytes according to this formula:

L = ((C * 250) + 150 + V) * S * T * f * 1.3

For example, a workload consists of 35% update transactions. Each transaction updates four columns, including two character columns each averaging 30 bytes updated total. The workload runs at 1 million transactions per second and needs enough transaction log space to hold one hour's worth of transactions.

  • C = 4 columns

  • V = 30

  • S = 3600

  • T = 1,000,000

  • f = 35% = 0.35

Thus, you would estimate that the storage provisioning workload requires:

L = ((4 * 250) + 150 + 30) * 3,600 * 1,000,000 * 0.35 * 1.3 = 1.9 TB

Thus, provision file system space of 1.9 TB for the transaction log files.