Managing-Space in Tablespaces
Managing Space in Tablespaces#
This section describes how to manage space in tablespaces.
Estimating the Size Required for the Undo Tablespace#
The undo tablespace is used for storing undo segments. Because insufficient undo tablespace can negatively affect the performance of transactions, a DBA should manage it to an appropriate size. If update transactions, especially those that execute statements for a long time, the undo segment will be relatedly extended. This can lead to a lack of space in the undo tablespace.
The user can set the undo tablespace to automatic extension mode, or set it to a fixed-size mode that estimates the approximate maximum size and specifies that estimate as to the maximum size.
Auto Extension Mode of Undo Tablespace#
It is not easy for the user to predict how much undo tablespace will be required when first running an application. In such cases, it is recommended to set the undo tablespace to automatic extension mode so that it increases automatically to the required size.
In Altibase, automatic extension mode is provided for the undo tablespace so that the size of the undo tablespace can be easily estimated in an application development environment. The undo tablespace is set to automatic extension mode by default. This can be changed using the ALTER TABLESPACE statement.
Fixed Size Mode of Undo Tablespace#
If it is desired to fix the size of the undo tablespace, the required size must be estimated. To achieve this, the user must observe and analyze patterns of space usage in TSS and undo segments while client applications are running.
The required undo table size can generally be approximated by the following formula:
Size of Undo Tablespace
= Long-Term Transaction Time(sec) *
(the number of undo pages allocated per second + the number of TSS pages allocated per second) *
page size(8KB)
In this example, if the executing time of a long-term transaction is 600 seconds (10 minutes) and 1000 undo pages and 24 TSS pages are allocated per second, then 10 x 60 x (1000 + 24) x 8K = 4800 MB, which requires about 4.7G of the undo tablespace.
However, if it is difficult to estimate the size of undo tablespace in this way, it is also acceptable to simply allocate large amounts of space, as long as disk space permits.
Undo Tablespace Extension#
If update transactions (especially long-term transactions, that is, those that take a long time to be committed) frequently occur in the system, it is possible to run out of undo tablespace. In this situation, it is necessary to increase the size of the tablespace, either by adding one or more suitably sized data file(s) or increasing the size of the file(s) in undo tablespace using the ALTER TABLESPACE statement.
Estimating the Size of Memory Tables#
Calculating the Size of Data#
The data size of the memory table can be estimated based on the data type of each column and padding for column alignment. Expressed as a mathematical formula as follows:
data size = [ ( the total estimated size plus padding for each column ) * the number of data records ]
The estimated size of each data type is shown in the following table.
[Table 6-3] Memory Table Size: Estimated Size by Data Type(P = Precision, V = Value length)
| Data Type | Estimated Column Size |
|---|---|
| INTEGER | 4 |
| SMALLINT | 2 |
| BIGINT | 8 |
| DATE | 8 |
| DOUBLE | 8 |
| CHAR | 2+P |
| VARCHAR | 22+V |
| NCHAR | 2+(P*2) - UTF8 2+(P*3) - UTF16 |
| NVARCHAR | 22+(V*2) - UTF8 22+(V*3) - UTF16 |
| BIT | 4+(P/8) |
| VARBIT | 22+(P/8) |
| BYTE | 2+P |
| VARBYTE | 22+V |
| FLOAT | 3+(P+2)/2 |
| NUMERIC | 3+(P+2)/2 |
In the above table, P (Precision) indicates the size of the column determined when the table is created. Data longer than P cannot be inserted into a column of the corresponding data type. V (Value) is the actual length of the inserted data, so V cannot be greater than P.
In fixed-length columns, such as those of type CHAR, NCHAR, BIT, etc., space equal to P is always occupied, and therefore the length of the column is fixed regardless of the actual length of the data. However, for variable-length columns, such as those of type VARCHAR, NVARCHAR, VARBIT, VARBYTE, etc., the amount of space occupied varies depending on the length of the data.
Unlike disk tables, memory tables contain padded space to increase the speed of data access. The size of this space varies according to the data type and the position of the column.
Estimating the Size of an Index#
Memory indexes are not saved in the tablespace in which data are saved. Since a pointer that points to the data storage location is saved in each bucket of a node of a memory index, the index size can be estimated based on the pointer size and the number of records currently saved in the table, regardless of the data type.
index size = (number of data records) * p ( p = Pointer Size )
In the above formula, p is the pointer size, that is, the size required to save a pointer. On a 32-bit system, this size is 4 bytes, whereas on a 64-bit system, it is 8 bytes. In this formula, the size of the index is taken as being equal to the total size of all leaf nodes (i.e., the lowest nodes on a B-Tree) of the index. In addition to leaf nodes, a B-Tree also consists of internal nodes (i.e., nodes higher than leaf nodes), but their total size is 1/128 that of the leaf nodes, which is so small that they can be safely ignored. In addition, the size of additional information used to manage the index is about 1/16 the size of the leaf nodes, which is also negligibly small.
Therefore it is acceptable to calculate the total size of the index based on the total size of all leaf nodes.
However, the value estimated using this formula can differ from the actual size of the index because it considers only the case where all buckets of all leaf nodes have key values saved therein. That is to say, if there are many empty buckets within nodes, the actual size of the index can be much greater than the estimated size. In this case, the index size can be reduced by rebuilding the index.
Example 1#
Let's try to estimate the size of the data when a table is created as shown below:
CREATE TABLE t1
(
c1 INTEGER,
c2 CHAR(1024),
c3 VARCHAR(1024)
) TABLESPACE user_data01;
In this table, column C1 and column C2 are fixed-length columns, whereas column C3 is a variable-length column. Therefore, the size of a record will vary depending on the size of column C3. If the size of one record is calculated in consideration of this, as seen below, the size of the data in table T1 equals (the length of one record * the number of records).
[record header] = 32 bytes
[column C1] = 4 bytes
[column C2] = 2+P bytes = 2+1024 bytes
[column C3] = 22+V bytes
If the length of the data in column C3 is 200 bytes
[record size] = 32 + ( 4 ) + (2+1024) + (22+200) + padding
= (1284 + padding) bytes
If the length of the data in column C3 is 500 bytes:
[record size] = 32 + ( 4 ) + (2+1024) + (22+500) + padding
= (1584 + padding) bytes
Example 2#
Let's calculate the index size of the table T1 created by the following statement, assuming that table T1 currently contains 500,000 records and the system is a 64-bit system:
CREATE TABLE t1 ( c1 INTEGER, c2 CHAR(300), c3 VARCHAR(500)) TABLESPACE user_data01;
CREATE INDEX t1_idx1 ON t1( c1, c2, c3 );
T1's index size
[index size] = 500,000 records * 8 = 3.814 Megabytes
Example 3#
Now let's calculate the data and index size of the table created by the following statement, assuming that table T1 currently contains 1,000,000 records and the system is a 64-bit system:
CREATE TABLE test001
(
c1 CHAR(8) PRIMARY KEY,
c2 CHAR(128),
n1 INTEGER,
in_date DATE
) TABLESPACE user_data01;
The size of one record and the total data size
[The size of a record] = 32[header size] + (2+8) + (2+128) + (4) + (8) = 184 bytes
[The total size of all records] = [ 184 ] * 1,000,000 records = 175.47 Megabytes
The index size
[total index size] = 8 * 1,000,000 records = 7.629 Megabytes
Note that this value is calculated only based on the size of data and leaf nodes. Thus, in reality, additional space will be used for the page header, index nodes, and memory for managing free pages.
Estimating the Size of Disk Tables#
In Altibase, the size of a disk table can be calculated on the basis of the data types and data contents, that is, it is equal to [ total length of a row in the table * number of rows ]. The following table shows the length of each data type.
[Table 6-4] Disk Table Size: Estimated Size by Data Type (P = Precision, V = Value length)
| Data Type | Estimated Column Size | ||
|---|---|---|---|
| Null | 250 bytes and below 250 bytes | Greater than 250 bytes | |
| INTEGER | 1 | 5 | X |
| SMALLINT | 1 | 3 | X |
| BIGINT | 1 | 9 | X |
| DATE | 1 | 9 | X |
| DOUBLE | 1 | 9 | X |
| CHAR | 1 | 1+P | 3+P |
| VARCHAR | 1 | 1+V | 3+V |
| NCHAR | 1 | 1+P | 3+P |
| NVARCHAR | 1 | 1+V | 3+V |
| BIT | 1 | 5+(P/8) | 7+(P/8) |
| VARBIT | 1 | 5+(V/8) | 7+(V/8) |
| BYTE | 1 | 1+P | 3+P |
| VARBYTE | 1 | 1+V | 3+V |
| FLOAT | 1 | 4+(V+2)/2 | 6+(V+2)/2 |
| NUMERIC | 1 | 4+(V+2)/2 | 6+(V+2)/2 |
In the above table, P (Precision) indicates the maximum size of the column, which is set when the table is created. Data longer than P cannot be inserted into a column of that type. Additionally, for fixed-length columns, such as those of type CHAR, NCHAR, BIT, etc., space equal to P is always occupied, and therefore the length of the column is fixed regardless of the actual length of the data.
V (Value) denotes the actual length of the inserted data, which of course cannot be greater than P. In addition, the amount of space occupied by variable-length columns, such as those of type VARCHAR, NVARCHAR, VARBIT, VARBYTE, etc., varies according to the length of the data. Therefore, the column size can vary depending on the size of the data.
Estimating Row Size#
This section describes how to calculate the row size for a table having the schema shown below:
CREATE TABLE t1
(
c1 CHAR(32),
c2 CHAR(1024),
c3 VARCHAR(512)
) TABLESPACE user_data02;
In this schema, column C1 and column C2 are fixed-length columns, whereas column C3 is a variable-length column. Therefore, the size of a row will vary depending on the size of column C3. The size of a row will also vary depending on whether any columns contain NULL values. If the size of one row is calculated in consideration of this, as seen below, the size of the data in table T1 equals (the total length of one row * the number of rows).
[Row Header] 34 bytes
[column C1] 1+P Bytes = 1+32 Bytes
[column C2] 3+P Bytes = 3+1024 Bytes
[column C3] 3+V Bytes
If the size of the data in the column C3 is 200 bytes
[Total Length of One Record] = 34 + (1+32) + (3+1024) + (3+200) = 1295 Bytes
If the size of the data in the column C3 is 500 bytes
[Total Length of One Record] = 34 + (1+32) + (3+1024) + (3+500) = 1597 Bytes
If column C2 is NULL and the size of column C3 is 300 bytes
[Total Length of One Record] = 34 + (1+32) + (1) + (3+300) = 371 Bytes
If column C3 is NULL
[Total Length of One Record] = 34 + (1+32) + (3+1024) + (0) = 1094 Bytes
If the last column is null and has no data, the last column will not be saved and reflected in size.
Estimating the Size of an Index#
In Altibase, the size of a disk index can be calculated based on the actual data types and data contents. The following table shows the length of each data type to use when calculating the index size:
[Table 6-5] Disk Index Size: Estimated Size by Data Type (P = Precision, V = Value length)
| Data Type | Size of Index Key | ||
|---|---|---|---|
| Null | 250 bytes and below 250 bytes | Greater than 250 bytes | |
| INTEGER | 4 | 4 | X |
| SMALLINT | 2 | 2 | X |
| BIGINT | 8 | 8 | X |
| DATE | 8 | 8 | X |
| DOUBLE | 8 | 8 | X |
| CHAR | 1 | 1+P | 3+P |
| VARCHAR | 1 | 1+V | 3+V |
| NCHAR | 1 | 1+P | 3+P |
| NVARCHAR | 1 | 1+V | 3+V |
| BIT | 1 | 5+(P/8) | 7+(P/8) |
| VARBIT | 1 | 5+(V/8) | 7+(V/8) |
| BYTE | 1 | 1+P | 3+P |
| VARBYTE | 1 | 1+V | 3+V |
| FLOAT | 1 | 4+(V+2)/2 | 6+(V+2)/2 |
| NUMERIC | 1 | 4+(V+2)/2 | 6+(V+2)/2 |
In the above table, P (Precision) and V (Value) respectively indicate the maximum size of the column, which is set when the table is created, and the size of the data that are actually inserted into the table.
The size of an index is calculated as follows:
[10 (header length) + (total length of key columns)] * number of records
The above formula is used to calculate the approximate size of leaf nodes (the lowest nodes on a B-Tree). In addition to leaf nodes, a B-Tree also consists of internal nodes (nodes higher than leaf nodes), but they can be safely ignored when the key column size is small.
However, if the key column size is greater than 2kB, the depth of the B*Tree increases, and thus the size of internal nodes must be included in the calculation because their size can approach 50% of the total size of leaf nodes.
The following shows how to estimate the size of index T1 for table T1, the creation statements for both of which are shown below.
CREATE TABLE t1
(
c1 INTEGER,
c2 VARCHAR(500)
) TABLESPACE user_data02;
CREATE INDEX t1_idx1 ON t1( c1, c2 );
Column C1 is always 4 bytes in size because it an integer type column. The length of column C2, which is a variable-length column, varies depending on the size of the data.
[Key Header] 10 bytes
[column C1] 4 bytes
[column C2] 1+V bytes
If the size of the data in column C2 is 50 bytes:
[Total Length] = 10 + 4 + (1+50) = 65 bytes
If the size of the data in column C2 is 500 bytes:
[Total Length] = 10 + 4 + (3+500) = 517 bytes
If column C2 is NULL:
[Total Length] = 10 + 4 + 1 = 15 bytes
Table Size Calculation Example#
The following shows how to estimate the size of the table created, as shown below, assuming that it contains 1,000,000 records. The table size consists of the total size of all the records plus the size of the index.
CREATE TABLE test001
(
c1 CHAR(8) PRIMARY KEY,
c2 CHAR(128),
n1 INTEGER,
in_date DATE
) TABLESPACE user_data02;
Row Size and Total Data Size
Row Size: 34[Header] + (1+8) + (1+130) + (1+4) + (1+8) = 188 bytes
Total Size of Data: [ 188 ] * 1,000,000 data = 179.29 Megabytes
Index Size:
Index Size for one Row: 10[Header] + (1+8)[C1] = 19 bytes
Total Index Size: 19 * 1,000,000 data = 18.12 Megabytes
Total Amount of Disk Space Occupied by TEST001:
179.29 (Data Size) + 18.12 (Index Size) = 197.41 M bytes
The above calculation takes into consideration only the size of the data. In reality, additional space is also occupied by the page header, internal nodes, space for managing segments, etc. When the space used for these purposes is also considered, the total amount of space occupied by the table is determined to be about 240 Megabytes.
Calculating Table Storage Space#
Below, table TEST001, which was used above for the estimation of table size, will be used to show how to determine the table size that is suitable for storing all of the records and indexes in the table. The following must be kept in mind when determining the suitable table size.
Consider the Relative Frequency of Transaction Types#
If a lot of update transactions are executed on the table, PCTFREE should be set to a high value for better transaction performance, and PCTUSED should be set to a low value to ensure sufficient free space for update transactions.
In contrast, if a lot of insert transactions are performed on the table but the number of update transactions is low, PCTFREE should be set to a low value, and PCTUSED should be set to a high value in order to minimize the amount of unnecessary free space.
PCTFREE#
The default value is 10. It can be set anywhere from 0 to 99 when a disk table is created. This is the percentage of free space on each page that is set aside in advance for updating existing records when saving data in tables. Therefore, supposing that PCTFREE has been set to 10 and only insert transactions occur, if the total size of the table is 100MB, the amount of space that can be used for the records and the index of the table is 90M.
PCTUSED#
The default value is 40. It can be set anywhere from 0 to 99 when a disk table is created. After the amount of free space in a particular page drops below the percentage specified in PCTFREE, no more data will be inserted into the page until the amount of used space subsequently drops below 40% (e.g. 39%) as a result of update or delete transactions. Therefore, greater amounts of free space must be allocated to tables on which updates transactions occur frequently.
[Table 6-6] Table Size Estimation based on Relative Frequency of Transactions by Type
| Circumstances | Table Size Estimation |
|---|---|
| Only SELECT transactions occur, or record size doesn't increase during UPDATE transactions | In the case where PCTFREE is set to 5 and PCTUSED is set to 90: (1) Estimated minimum table size: TEST001(Total size=215.53MB) The minimum size in which to save the table is calculated as follows: Total table size / [1-(PCTFREE / 100) = 215.53/0.95 ≒ 227MB (2) Weighted estimation: A weighting factor is taken into account in the determination of the minimum size. The weighting differs depending on the circumstances. The following is just one example of how to include weighting in the size determination. Minimum Size * [ 1- (PCTUSED / 100) ] * 2 = 227 * 0.1 * 2 ≒ 45M (3) Therefore, a table 272M in size should be created. |
| UPDATE transactions occur frequently and tend to increase the size of records | In the case where PCTFREE is set to 20 and PCTUSED is set to 40: (1) Estimated minimum table size: The minimum size required to store the TEST001 (Total Size = 213.63M) table is calculated using the following formula: Total table size / [1-(PCTFREE / 100)= 213.63/0.8 ≒ 267MB (2) Weighted estimation: A weighting factor is taken into account in the determination of the minimum size. The following is one example of how to include weighting in the size determination. Minimum Size * [ 1- (PCTUSED / 100) ] * 2 = 267 * 0.6 * 2 ≒ 320M (3) Therefore, a table approximately 587M in size should be created. |
| INSERT and UPDATE transactions occur frequently but UPDATE transactions do not increase the size of rows | PCTFREE is set to 10 and PCTUSED is set to 60. |
Note
The table size estimation method shown above should not be considered a rigid standard. It is also necessary to take into account the possibility that the amount of data will suddenly increase in the event of abnormal system operation.
Consider Suitable Backup Space#
In practice, it is rare for a tablespace to have only one table saved in it. It is more efficient to group tables according to business purposes or backup strategies and stores them collectively in a single tablespace.
In such cases, the appropriate size of a tablespace should be set in consideration of the backup time for the tablespace.
The figure below shows how tablespaces are organized in consideration of business purposes and backup strategies.
Tablespace Information#
To help manage tablespaces, Altibase provides performance views and meta tables to monitor the state of tablespaces in order to manage them.
SYSTEM_.SYS_TBS_USERS_
Also, the following performance views can be used to obtain information on the size, usage, and status of the database used by users:
V$TABLESPACES, V$DATAFILES, V$MEM_TABLESPACES