System Data Capacity Estimation Guide for Altibase Operations#
Overview#
This guide explains, with examples, how to estimate the memory and disk capacity required to configure an Altibase server.
This guide is based on Altibase 7.1 and later.
Info
For errors and improvements related to this document, please contact the technical support portal or technical support center.
- Technical support portal: http://support.altibase.com/en
- Technical support center: +82-2-2082-1114
Info
This document is provided for informational purposes only and is subject to change without prior notice. It may contain errors, and Altibase makes no express or implied warranties of any kind, including but not limited to warranties of merchantability or fitness for a particular purpose.
The development, release, and timing of any features or functionality described for Altibase products in this document are at the sole discretion of Altibase.
Altibase may hold patents, trademarks, copyrights, or other intellectual property rights related to the contents of this document.
Memory Capacity Estimation#
Memory capacity is the sum of the memory database size, the disk database buffer size, and the memory required for query execution.
Size of the Memory Database#
Altibase's memory database loads and manages all data and indexes in memory. The memory database size for a production environment is therefore estimated from the schemas of the memory tables to be created and their expected number of records.
A table created in a memory tablespace is allocated pages in 32 KB units, and it divides each page into multiple slots according to the record length.
The size of a memory table is calculated from the following values.
| Item | Description |
|---|---|
| Record length | Sum of the lengths of all columns in the table |
| Record header length | 32 bytes |
| Expected number of records | Expected number of records for the retention period |
| Index pointer length | 8 bytes |
For a table with a record length of 500 bytes that keeps 10 million records for one year, the calculation is as follows. A correction factor of 1.1 per year is applied to account for business growth.
| Item | Formula | Calculation | Estimated size |
|---|---|---|---|
| Table | (Record length + Record header length) × Expected number of records | (500 + 32) × 10,000,000 | 5,073.5 MB |
| Index | Index pointer length × Expected number of records × Number of indexes | 8 × 10,000,000 × 1 | 76.3 MB |
| Table (correction factor applied) | Estimated table size × Correction factor | 5,073.5 MB × 1.1 | 5,580.9 MB |
| Index (correction factor applied) | Estimated index size × Correction factor | 76.3 MB × 1.1 | 83.9 MB |
An index on a memory table manages only pointers to the memory locations of records, so it is calculated as 8 bytes per record.
To get the exact record length, create the table and query the sum of SIZE for its columns in the SYS_COLUMNS_ meta table.
The sum of the estimated sizes of all tables is the memory database capacity. In practice, add 30-50% free space to this value. Memory for Query Execution explains why the free space is needed.
Include volatile tablespaces in the estimate on the same basis. A volatile tablespace does not write logs and loses all data when the server restarts, but it uses memory while it holds data.
Buffer Size of the Disk Database#
The appropriate buffer size of the disk database depends on the data access pattern of the workload, so it is generally recommended to set it to at least 10% of the data expected to be accessed frequently. For example, if the frequently accessed data is 100 GB, set the buffer size to 10 GB or more.
Memory for Query Execution#
When estimating memory capacity, consider not only the data but also the area that stores query information and execution plans, the temporary memory space for operations, and the free space for MVCC. The general formulas for each area are as follows.
| Item | General formula |
|---|---|
| Query information memory area | Number of queries × 1 MB |
| Temporary memory area for operations | Total size of all memory tables × 0.1 |
| Free space for MVCC | Total size of all memory tables × 0.35 |
Query Information and Execution Plans#
Altibase establishes an optimal execution plan when it executes a query, and this process has a large impact on query performance. To avoid the cost of re-creating the execution plan for the same query every time, it stores the information and execution plans of executed queries in memory and reuses them.
Query information and execution plans are stored in the SQL Plan Cache, which all sessions share, and in memory areas allocated per session. The SQL Plan Cache does not grow beyond the size set by the SQL_PLAN_CACHE_SIZE property, so execution plans that do not fit in the cache are stored in the memory area allocated to the session. Therefore, memory capacity estimation must also account for the memory allocated to sessions.
Temporary Memory Space for Operations#
When a query that includes GROUP BY, aggregate functions, and so on runs on the memory database, intermediate result sets are stored in a separate memory space while the source data is processed into the query result. The temporary memory space is the space used for this purpose. The disk database uses the temporary tablespace by default, but hints and similar methods can make it use memory for better performance.
The usage of the temporary memory space varies greatly with the query type, the data size, and the number of concurrent queries, so it cannot be calculated exactly. However, allocated space is reused continuously, so a reasonable estimate does not greatly affect the overall memory capacity.
Free Space for MVCC#
Altibase controls concurrency with Multi-Version Concurrency Control (MVCC) to reduce contention between update and read transactions and improve performance. For this purpose, it temporarily stores the previous versions of changed records in the table. Therefore, memory capacity estimation must also account for the free space for MVCC.
Memory Capacity Estimation Example#
This example estimates the capacity of a memory table that grows by 1 million records per year and is kept for 10 years.
CREATE TABLE memory_size (
c1 CHAR(20),
c2 INTEGER,
c3 CHAR(30),
c4 VARCHAR(200),
c5 DATE,
c6 NUMERIC(20, 8),
PRIMARY KEY (c1)
) TABLESPACE SYS_TBS_MEM_DATA;
CREATE UNIQUE INDEX idx_1 ON memory_size (c1, c2);
Check the record length of this table with the following SQL statement.
SELECT CEIL(SUM(b.SIZE) / 8) * 8
FROM SYSTEM_.SYS_TABLES_ a, SYSTEM_.SYS_COLUMNS_ b
WHERE a.TABLE_ID = b.TABLE_ID
AND a.TABLE_NAME = 'MEMORY_SIZE';
CEIL(SUM(B.SIZE)/8)*8
-------------------------
288
1 row selected.
Memory records are aligned and managed in 8-byte units, so a size that is not a multiple of 8 is rounded up to the next multiple of 8. Therefore, even if the sum of the column lengths is 282 bytes, the record length is estimated as 288 bytes.
With the record length, the data and index capacity can be calculated as follows. This table has a primary key index and the idx_1 index, so the number of indexes is 2, and a business growth correction factor of 1.1 is applied.
| Item | Calculation | 1 year | 10 years | Correction factor applied |
|---|---|---|---|---|
| Data | (288 + 32) × 1,000,000 | 305.2 MB | 3,051.8 MB | 3,356.9 MB |
| Index | 8 × 2 × 1,000,000 | 15.3 MB | 152.6 MB | 167.8 MB |
| Total | 320.4 MB | 3,204.4 MB | 3,524.8 MB |
Once the data and index capacity of all memory tables is known, the total memory capacity can be calculated as follows.
| Item | Basis (example) | Capacity |
|---|---|---|
| Memory database | Sum of all memory table sizes | 20 GB |
| Disk database buffer | Estimated from the disk database size | 5 GB |
| Query information memory area | 1,000 queries × 1 MB | 1 GB |
| Temporary memory area for operations | 20 GB × 0.1 | 2 GB |
| Free space for MVCC | 20 GB × 0.35 | 7 GB |
| Estimated memory capacity | 20 + 5 + 1 + 2 + 7 | 35 GB |
This value covers only the memory required by Altibase. If applications also run on the same server, estimate the memory they require separately.
Disk Capacity Estimation#
Disk capacity is the sum of the space required for checkpoint image files, disk database data files, undo and temporary tablespaces, online log files, and backup files.
Checkpoint Image Files#
To recover data changed in memory even after a failure, Altibase periodically saves the contents of the memory database to data files on disk (hereafter, checkpoint image files). This process is called checkpointing, and the size of the checkpoint image files equals the size of the memory database.
Altibase performs ping-pong checkpointing, so by default it keeps two sets of checkpoint image files for each memory tablespace. The checkpoint image files therefore require disk space twice the memory database capacity. A database whose checkpoint scale is set to single keeps only one set of checkpoint image files. For details, see Checkpointing Memory Databases.
| Item | Capacity |
|---|---|
| Estimated memory database size | 20 GB |
| Checkpoint image file size | 20 GB × 2 = 40 GB |
Data Files of the Disk Database#
Unlike the memory database, the disk database is estimated in page units, because the space available in each page is limited by the PCTFREE and PCTUSED attributes.
The page size of the disk database is 8,192 bytes, and the space left after subtracting the space reserved by PCTFREE and the page header and footer is the space available for records. Unlike the memory database, the disk database must also include the length of the columns that make up each index. The header lengths of records and indexes are as follows.
| Item | Length |
|---|---|
| Record header length | 34 bytes + slot directory 2 bytes + 1 byte of length information per column |
| Index header length | 8 bytes + slot directory 2 bytes |
A record has 1 byte of length information per column, and a column of 250 bytes or longer has 3 bytes. The slot directory is an area for quickly locating the offset of a record in a page, and it takes 2 bytes per record.
For a disk table with a record length of 288 bytes, made up of 6 columns shorter than 250 bytes, that keeps 1 million records for one year, the estimated one-year capacity of the table is calculated as follows. PCTFREE is set to its default of 10%.
| Item | Calculation | Result |
|---|---|---|
| Page size | 8,192 bytes | |
| PCTFREE 10% applied | 8,192 − (8,192 × 0.1) | 7,373 bytes |
| Fixed page header | Used for page management | 80 bytes |
| Fixed page footer | Used for page consistency checks | 16 bytes |
| Space available for records | 7,373 − (80 + 16) | 7,277 bytes |
| Record header length | 34 + 2 + (6 × 1) | 42 bytes |
| Records per page | 7,277 / (288 + 42), rounded down | 22 |
| Required pages | 1,000,000 / 22, rounded up | 45,455 |
| Estimated one-year capacity | 45,455 × 8,192 bytes | About 355 MB |
The number of records per page gives the number of pages required for the expected records. Because the disk database uses space in 8,192-byte pages, multiply the number of pages by 8,192 bytes to get the capacity.
Indexes are estimated the same way. Use the sum of the key column lengths as the record length and the index header length in the table above as the header length.
After calculating the capacity of all disk tables and indexes, add 30-50% free space to the total to get the final capacity.
| Item | Basis (example) | Capacity |
|---|---|---|
| Disk database | Sum of all disk table sizes | 500 GB |
| Free space | 500 GB × 0.3 | 150 GB |
| Estimated disk capacity | 500 + 150 | 650 GB |
Undo and Temporary Tablespaces#
Undo Tablespace#
The disk database stores copies of data before changes in the undo tablespace until the transaction ends. Its usage therefore depends on query processing and is hard to predict. For example, a transaction that updates a 1 TB table at once requires 1 TB of undo tablespace.
Excluding such worst cases, about 30% of the largest table is generally estimated as the undo tablespace capacity.
Temporary Tablespace#
The usage of the temporary tablespace also depends on the query type and is hard to predict. As with the undo tablespace, about 30% of the largest table is estimated as the temporary tablespace capacity.
Estimate enough space for both the undo and temporary tablespaces so that they do not run out during operation.
Online Log Files#
Altibase records the transaction changes required for failure recovery in log files according to the WAL (Write-Ahead Logging) protocol, and these files are called online log files.
Online log files that are no longer needed are deleted automatically at checkpoint. However, if the replication sender has not yet sent some logs, or if a transaction lasts a long time because of a large change operation, the online log files cannot be deleted and are kept. Secure enough space for online log files so that a shortage of disk space does not cause a failure.
The disk space required for online log files depends on the system size, so it is hard to give a general formula. It is best to measure the amount of log files generated through performance load tests and estimate from that; if testing is difficult, at least 50 GB is recommended.
Backup Files#
This section explains only how to estimate the capacity required for backups. For backup methods and policies, see Backup and Recovery in the Altibase manual and Considerations for Altibase Backup Policy.
Estimating Online Backup Space#
Online backup requires the database to run in archive log mode, which needs the following two kinds of disk space.
| Item | Description |
|---|---|
| Archive log directory | Space for copies of online log files (archive log files) |
| Backup file storage space | Space for data files backed up by online backup and similar methods |
The backup file storage space is estimated as the sum of the estimated memory database and disk database capacity. However, the required space differs depending on whether the entire database is backed up each time or incremental or tablespace-level backups are used, so estimate it according to the backup method.
In the archive log directory, previous log files that are no longer needed for recovery after a backup completes can all be deleted. The capacity required for the archive log directory therefore depends on the backup cycle and policy.
The following example assumes a policy of one full backup per day.
| Item | Memory database | Disk database | Archive log files |
|---|---|---|---|
| Estimated capacity | 20 GB | 650 GB (including free space) | 200 GB (online log files per day) |
| Space required for backup | 20 GB | 650 GB | 200 GB |
| Space required to keep two backup sets | 20 × 2 = 40 GB | 650 × 2 = 1,300 GB | 200 × 2 = 400 GB |
The memory database keeps two sets of checkpoint image files, but only the stable set is backed up, so backup file storage space equal to the memory database capacity is sufficient. For the disk database, assuming full backup, backup file storage space equal to the disk database capacity is required.
To recover to the current point in time from a backup, all archive log files generated until the next backup must be available. Space is therefore required to store all archive log files generated during the backup cycle.
In addition, the previous backup can be deleted only after a new backup succeeds, so keeping the previous backup on disk requires twice the space needed for one backup.
Estimating iLoader Backup Space#
To also back up data at the table level, use iLoader together with online backup to download table data to text files. The backup files created this way also take up disk space and must be included in the estimate.
iLoader generally uses a delimiter of about 5 bytes per column, so delimiter space is required in addition to the table data size. For a table with a record length of 200 bytes and 10 columns that keeps 1 million records, the delimiter space and iLoader backup space are calculated as follows.
| Item | Formula | Calculation | Estimated size |
|---|---|---|---|
| Delimiter space | Delimiter length × Number of columns × Number of records | 5 × 10 × 1,000,000 | 47.7 MB |
| iLoader backup space | (Record length × Number of records) + Delimiter space | (200 × 1,000,000) + (5 × 10 × 1,000,000) | 238.4 MB |
If the backup files are kept compressed, estimate the storage space considering the compression ratio. The disk capacity estimation example below does not include iLoader backup space, so if you use this backup method, be sure to estimate it separately.
Disk Capacity Estimation Example#
The following example assumes 30% free space added to the disk database, a largest table of 100 GB, 20 GB of online log files measured by load testing, 40 GB of archive log files generated during the backup cycle, and the previous backup kept on disk.
| Item | Basis | Usage | Required disk space |
|---|---|---|---|
| Checkpoint image files | Memory database capacity × 2 | 20 GB | 20 × 2 = 40 GB |
| Data files of the disk database | Disk database capacity + free space (disk database capacity × 0.3) | 500 GB | 500 + (500 × 0.3) = 650 GB |
| Undo and temporary tablespaces | Largest table size × 0.3 (30% margin) × 2 (undo and temporary each) | 100 GB | 100 × 0.3 × 2 = 60 GB |
| Online log files | Depends on system size | 20 GB | 20 GB |
| Backup files (archive log files) | Online log files generated during the backup cycle × 2 | 40 GB | 40 × 2 = 80 GB |
| Backup files (data file backup) | (One set of checkpoint image files + disk database capacity) × 2 | 20 + 650 = 670 GB | 670 × 2 = 1,340 GB |
| Total | 2,190 GB |