15.00 - MERGEBLOCKRATIO - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

MERGEBLOCKRATIO

When the size of a table only grows through SQL INSERT operations and bulk data loads and never becomes smaller, the average size of its data blocks varies from half their maximum up to the maximum size permitted according to the values you specify for the DATABLOCKSIZE option when you define the table (see “DATABLOCKSIZE” on page 525).

However, the majority of tables fluctuate in size throughout their lifetime because of the mix of insert, update, and delete workloads that operate on them. When you delete rows from a table, its data blocks can shrink to a size that is significantly smaller than half the defined maximum for the table. Once data blocks reach a size that is smaller than half their defined maximum, they remain small unless a roughly equal number of rows are added to the table. When a table has many such small data blocks, update and read operations against it must read a larger number of data blocks than would be necessary if those blocks were larger, and this causes the performance of updates to the table to become increasingly poor. The role of the merge block ratio is to enable the Teradata file system to merge such small data blocks into larger data blocks dynamically, but only when a full‑table modification occurs on a table. Having fewer data blocks with a larger size reduces the number of I/O operations required to read a set of rows from disk.

The MERGEBLOCKRATIO option provides a way to combine existing small data blocks into a single larger data block during full table modification operations for permanent tables and permanent journal tables. This option is not available for volatile and global temporary files. The file system uses the merge block ratio that you specify to reduce the number of data blocks within a table that would otherwise consist mainly of small data blocks. It is not possible to define a general block size that is “small” in this context. The exact block size that fits this description is subjective and can differ based on the I/O performance of different systems, different workloads, and different numbers of active users.

For tables that are frequently modified by inserting new data, the average block size varies between 50% and 100% of their maximum supported multirow block size. This maximum supported size is defined as either the table-level attribute DATABLOCKSIZE (see “DATABLOCKSIZE” on page 525) or the system-level block size for the table as defined in the DBS Control record if you have not established a value for the DATABLOCKSIZE attribute for a table. The default percentage for the merge block ratio is 60, while its maximum value is 100. whether specified using the MERGEBLOCKRATIO option for CREATE TABLE or the DBS Control parameter MergeBlockRatio (see Utilities: Volume 1 (A-K) for details).

Note that when you delete rows from a table, the blocks that previously held the deleted rows can become smaller than the sizes in the defined range, and their new sizes can vary considerably from the block size you specified when you created or last altered the table definition.

An extreme (because it is uncommon for requests to access every data block in a table) example of a block size distribution that benefits from having its data blocks merged together is a table whose blocks were created with an average size of 64 KB, but which have gradually shrunk or split over time to an average size of only 8 KB. If block splits were common for this table, its number of blocks can have increased by an order of magnitude.

If the rows in the table could be repackaged into blocks whose size were closer to the original average block size, you would probably experience improved response times for queries that read most or all of the blocks in the table because the number of logical or physical reads (or both) would then be reduced by an order of magnitude as well.

Merging blocks automatically is an enhancement of the functionality offered by the ALTER TABLE DATABLOCKSIZE option (see “ALTER TABLE (Basic Table Parameters)” on page 31). Although this option can alleviate the problem of tables overrun with small data blocks, it also suffers from several drawbacks, including the following.

  • Resolving the small data blocks problem requires a DBA to manually execute new SQL requests.
  • Resolution further requires research using the SHOWBLOCKS command of the Ferret utility (see Utilities: Volume 1 (A-K)), for example, to determine which particular tables and block sizes are causing performance problems before you can submit the SQL requests necessary to resolve the problem.
  • The operations performed by the problem resolving SQL request require an EXCLUSIVE table‑level lock, which then blocks concurrent update activity on the table.
  • If you specify the ALTER TABLE option IMMEDIATE (see “ALTER TABLE (Basic Table Parameters)” on page 31), there is no way to prohibit some data blocks in the table from becoming smaller as soon as the table is updated.
  • If you do not specify the ALTER TABLE option IMMEDIATE (see “ALTER TABLE (Basic Table Parameters)” on page 31), some data blocks in the table might take a long time to grow larger again if they are not updated for some time.
  • The merge block ratio approach has none of these drawbacks and offers the following enhanced functionality, all of which is autonomic.

  • It can run automatically without DBA intervention.
  • It does not require the analysis of block size histograms.
  • It does not require any specific AMP‑level locking.
  • It continuously searches for small data blocks to merge together even when some of those blocks are not being updated.
  • You can control the size threshold at which small data blocks are merged either by using the MERGEBLOCKRATIO option with a CREATE TABLE or ALTER TABLE request or by changing the setting of the MergeBlockRatio DBS Control parameter. Note that you can set the merge block ratio percentage as low as 1% using either the MergeBlockRatio DBS Control parameter or the MERGEBLOCKRATIO option in an ALTER TABLE or CREATE TABLE request. The default value for MERGEBLOCKRATIO is 60.

    To disable data block merging for an individual table, specify the NO MERGEBLOCKRATIO option. To disable data block merging for all tables on your system, set the DBS Control parameter DisableMergeBlocks to TRUE.

    Tables that are defined with a very small data block size do not gain a significant benefit from merging small data blocks because the sizes of most data blocks in such tables are close to the value established for a MERGEBLOCKRATIO, so there are few opportunities to merge data blocks in those tables.