Overview

Before operating on a project with Altibase, the configuration of the server must be determined. This document explains how to plan the CPU, memory, disk, and network capacity required for a server configuration.

This document was prepared based on the version below.

• Altibase 5.5.1 or later

CPU capacity planning

CPU capacity planning is generally performed based on TPC-C. This section describes the necessary items and factors to be applied to Altibase and provides examples of capacity planning.

TPC-C

Visit http://www.tpc.org and there is a measurement result called tmpC provided for each device. This is a number that is measured based on the throughput of a specific transaction (NEW_Order) in an environment where various transactions occur, and CPU capacity is calculated based on this.

This document also assumes that the CPU capacity is calculated based on this. For more detailed information on TPC-C, please visit the http://www.tpc.org/ website.

CPU capacity planning criteria

If it is difficult to build a TPC-C execution environment, the CPU capacity can be calculated instead of the TPC-C result by applying the correction below.

Applying the correction means applying the expected weight for each situation to the required tmpC value.

For each correction item, refer to the table below.

• If Altibase replication is not used, the 'replication correction' item is not applied.
• Unless only the memory DB is used, the "Memory DB correction" item is not applied.

Transactions per minute

The number of transactions per minute should be calculated after calculating the number of concurrent users first as follows.

Basic tpmC correction

The tmpC provided on the TPC homepage is measured in the optimal environment and is different from the actual operating environment.

Therefore, it is necessary to correct the tmpC value measured in the experimental environment to fix the real environment. This is called "basic tmpC correction" and a fixed value of 5 is applied.

Peak-time load correction

Since the system is generally subjected to a heavy load of about 20~50% during the peak time than usual, a weight of 1.2~1.5 is applied in consideration of this.

Database size correction

Apply it by considering the total database size and the table with the most records.

If it is difficult to derive an accurate value, the general value of 1.7 is applied because weighting cannot be applied.

Application structure correction

Application structure correction is a correction for performance differences depending on the structure of the application and the required response time

It is as follows, and does not apply when the expected response time is more than 5.

Application load correction

Application load correction is a correction that takes into account when batch operations and others are simultaneously performed during peak-time of online operations. 

If additional operations (backup, reporting, statistics, etc) are required to be processed in addition to the specified operation, the required processing power must be corrected accordingly.

The applied value is as follows, and normally, 1.7 is applied.

System margin correction

This is a correction to ensure system stability in case of an unexpected increase in operation. The value is fixed at 1.3.

System target utilization correction

Generally, when designing the database system, it is designed to use 100% CPU usage, but for the stable operation, the CPU is not used 100% in an actual operating environment.

System target utilization correction is set based on the actual operating environment, In most cases, the maximum CPU usage is 70%, so the value of 0.7 is applied.

Replication correction

Applied when Altibase replication is used.

The default value of 1.1. is applied as a correction for handling Altibase replication loads.

Memory DB correction

This does not apply when both disk DB and memory DB are used or only disk DB is used.

When only memory DB is used, it performs 3 times faster than disk DB, so 1/3 = 0.3 is applied.

CPU Capacity calculation example

CPU capacity can be calculated as follows by applying the above correction factor items.

Memory capacity calculation

The following three items should be considered when calculating the memory capacity.

1. Memory DB capacity
2. Size of buffer area for disk DB
3. Memory for session query statement execution

Memory DB storage space 

Tables created in memory tablespaces are allocated pages in units of 32KB. One page is again divided into units called slots according to the length of the record in the table.

Therefore, one page in units of 32KB is divided into several slots according to the length of the record of the created table.

The exact size or number of slots in a page can be checked in the Altibase V$MEMTBL_INFO performance view.

Memory DB capacity calculation

The memory DB of Altibase is a form in which all data and indexes reside in memory.

Therefore, it is calculated based on the memory table schema to be created in the operating environment and the expected number of records.

It can be calculated as follows. The cord header length differs depending on the Altibase version, so be cautious when calculating the capacity.

• The index of the memory table is calculated in the form of (number of records * 8 bytes) because only pointer information pointing to a location in real memory is managed.
  Depending on the number of memory indexes, it can be calculated using the formula '8 bytes * number of records * number of indexes'.
• The total sum of each expected size of the table calculated by the above calculation formula can be calculated as the capacity of the memory DB. In addition, the capacity of the memory DB may be calculated in consideration of a margin ratio of 30% to 50% from the calculated capacity.
  (The required margin is further explained in the "Considering margin factor" section below.)
• To find out the exact length of a record after creating a table, it can be clearly found by looking up MEM_SLOT_SIZE of the table in V$MEMTBL_INFO.
• In the case of a volatile tablespace, if there is actual data, the memory area is used, so it must be included in the capacity calculation based on the above criteria.
  (All data in tables created in the volatile tablespace will be lost when the Altibase server is restarted. Transactions for tables in volatile tablespaces are provided as tablespace concept used for fast processing without recording a physical transaction log.)

Size of disk DB buffer

There is no fixed formula for calculating the size of the disk DB buffer.

However, 10% or more of the size of the disk DB that is expected to be accessed frequently among all disk DBs is recommended.

For example, if the size of frequently access data among the disk is estimated to be 100GB, set the buffer size to 10GB or more, which is 10%.

Considerations for free memory

In addition to the data, the memory size of the following areas must be considered when calculating the memory capacity.

1. Query information and execution plan
2. Temporary memory space for operations
3. Separate memory margin rate by MVCC technique.

Query information and execution plan

When executing a query, Altibase performs with an execution plan establishment procedure so that the query can be executed with an internally optimized execution plan. This is very important in the performance of query processing.

However, in order to reduce the cost of establishing an execution plan for the same query each time, Altibase stores and manages query information and execution plan for queries that have already been executed internally. For this, a session memory area is required.

There are two main types of session memory areas in Altibase, the SQL_CACHE area commonly used for all sessions and the memory area allocated for each session.

Since the size of SQL_CACHE does not increase dynamically, when it is not possible to store all the execution plans for a query, it is operated by storing it in the memory area allocated to the session.

Therefore, when calculating the memory capacity, the memory space to be allocated to the session must also be considered.

Temporary memory space

When a query including group by, aggregation, etc. is executed in the memory DB, separate storage space for storing the intermediate result set is required to process the query result requested by the user from the source data. The temporary memory space refers to a separate storage space used at this time.

(In the case of a disk DB, a temporary tablespace for the disk is used by default, but the memory area can be used by using hints to improve performance.)

Consequently, it is unable to calculate this area explicitly arithmetically. This is because the degree of increase in the temporary memory space to be used is very flexible depending on the type of query, and concurrent queries.

However, since it is continuously reused after allocation, it does not significantly affect the total memory calculation if properly calculated.

Separate margin rate by MVCC technique

Altibase uses a concurrency control technique called MVCC to maximize performance by minimizing contention between two transactions when a retrieve transaction occurs while a change transaction is in progress.

In addition, to use this MVCC technique, a previous image of versioned data is temporarily stored in the table. (For more detailed information on MVCC, please refer to the "MVCC Guide".)

Therefore, when calculating the memory capacity, the margin for MVCC must also be considered.

Example of memory capacity calculation

Assuming that the memory table is stored for 10 years in the form of 1 million increments per year, the capacity can be calculated as follows.

In the above table, the user can check the length of the record with the following SQL statement.

Altibase aligns 8 bytes for memory resource management. That is, a size that is not divided by a multiple of 8 is managed by a larger multiple of 8.

Therefore, if the record length is 282 bytes, 288 bytes must be calculated as the length of the record according to the align rule.

If the length of the record is identified as above, it can be calculated using the following table. Separately calculate Altibase version 5, Altibase version 6, and Altibase version 7.

When input data is created for each table as above, it is able to calculate the capacity for data and indexes.

In addition, when the capacity of data and indexes is calculated, the total required memory capacity can be calculated as follows.

This memory capacity calculation is purely based on the capacity of Altibase. If planning to run a user application in the same server, the user must perform a separate capacity calculation for that part.

Disk capacity calculation

These four items should be considered for the disk capacity.

1. Estimated space of memory DB
2. Transaction log file space
3. Estimated space of disk DB
4. Disk DB backup space

Estimated space of memory DB

Altibase periodically stores the contents of the memory DB as a physical data file so that the changed data in the memory can be restored again even in the event of a sudden failure.

This process is called a checkpoint, and the size of the data file created in this process is created by the size of the memory DB. (Please refer to the "Altibase Checkpoint Guide" for detailed checkpoint procedures.)

At this time, the created files are created as two files, one pair each, so it requires twice the capacity of the memory DB in terms of disk capacity.

Transaction log file

Altibase performs transaction logging in order to perform a recovery scheme based on the WAL (Write-Ahead Logging) protocol.

During the log recording process performed at this time, storage space is required for the physical file, and the recorded files are called online log files.

Online log files are automatically deleted when a checkpoint occurs, but in some cases, they can be kept without being deleted. This is the case in the following cases.

• In the case of maintaining the log that the Sender should send in a replication
• When a transaction is held for a long time due to the occurrence of a large number of changes.

It is recommended to secure as much space as possible because it is necessary to prevent the shortage of failure of disk capacity due to the continuous maintenance of online log files.

However, it is not recommended the disk space for online log files by calculation formula.

Since it is flexible according to the size of the system, it is recommended to calculate it based on data such as the number of log files created with a performance load test.

If it is difficult to perform such a test, at least 20GB of disk space is recommended.

Estimated space of disk DB

For disk DB, unlike the method for calculating the capacity of memory DB, it is recommended to calculate the page unit. This is because, depending on the properties of PCTFREE and PCTUSED for each page, there is a limitation on the storage space.

The size of an Altibase page is 8,192 bytes. Here, the space on the page calculated for the PCTFREE setting for each page is calculated as the space available for the record.

The header length of record and index is as follows. Unlike the memory DB, in the disk DB, the length of the column configured in the index must be considered in the capacity calculation.

• Each column has 1 byte for length information. If the column is more than 250 bytes, it has a header of length information of 3 bytes.
• The slot directory area to quickly know the exact offset information in the page is included in the storage space and calculated.

The disk DB capacity is calculated as follows. (The index must add up the lengths of the columns that make up the key.)

If the user gets the number of records that can fit per page, the user can use that value to get the number of pages the user needs for the expected number.

However, since a page actually used is in units of 8,192 bytes, in the final calculation, the capacity is calculated by multiplying by 8,192 bytes.

In the case of an index, it is calculated by applying the length of the key column constituting the index as the length of the record.

The header can use 16 bytes of the header of the index.

When the capacity of all disk tables is calculated as above, the total is calculated and the capacity is calculated by considering the margin ratio of 30% to 50%.

Undo tablespace and temporary tablespace of Disk DB

Undo tablespace

The undo tablespace of disk DB is used as space where copies of the changed image are temporary stored until the end of the transaction.

Therefore, it is difficult to predict the amount of use because the number of use changes according to the query processing requested by the user.

For example, when a transaction that updates a 1TB table at once occurs, 1TB of undo tablespace is required.

If not considering such a case, it is generally desirable to calculate about 30% of the largest table as the capacity of the undo tablespace.

Temporary tablespace

It is difficult to predict the usage of temporary tablespace as its usage varies according to the type of query.

Therefore, just like the undo tablespace, about 30% of the table with the largest capacity should be calculated as the temporary tablespace capacity.

For the undo tablespace and the temporary tablespace, sufficient space should be calculated so that there are no operational problems.

Backup space of Disk DB

For more detailed information related to backup, please refer to the "Altibase Backup and Recovery Guide" document. This section only explains the part of calculating the capacity related to backup.

Altibase must be operated in archive log mode for backup, and two disk spaces are required for this backup.

In the case of backup space, it can be calculated as the sum of the estimated memory DB and disk DB capacity.

Since there are various types of backups such as the type of backing up the entire DB every time, the type of incremental backup, and the type of tablespace unit backup, it is recommended to calculate the appropriate space for each backup.

In the case of the archive log file directory, it is safe to delete all previous log files that are not referenced after the user completes the backup.

Therefore, it can be said to be flexible according to the backup policy because the log file capacity to be stored may differ according to the backup cycle and policy.

Let's look at the following example. In the example below, the backup policy is assumed on a daily basis.

• Memory DB is stored as one pair (two files) at the checkpoint, but only one stable file is backed up at the backup stage, so it must be considered the backup space as much as the memory DB capacity.
• If the disk DB is assumed to be a full backup policy, backup space is required as much as the expected disk DB capacity.
• Since archive log files must be stored until the next backup occurs, all archive log files created during the backup cycle must be stored since the previously backed up can be used to restore the current point in time.
• In terms of the backup cycle concept, the previous backup copy can be deleted only after the current backup is successful. If the last backup copy is kept on the disk, twice as much space for backup is required.

Considerations when using backup with iLoader

In addition to the online backup method, if it is necessary to additionally backup each table, the user can use a utility called the iLoader to back up the table as a text file.

In this case, the capacity of the data file backed up with the iLoader should be considered when calculating the disk capacity.

Generally, since a data separator of about 5 bytes should be used for each column, space obtained by the following arithmetic is required in addition to the actual expected disk DB capacity.

In other words, it means that the discriminator capacity is required in addition to the initially calculated disk DB capacity.

In addition, even when compressed and stored in a disk, the size of the space to be stored must be calculated in consideration of the compression ratio compared to the capacity calculated above.

This part is not added to the disk capacity calculation example below, so be sure to take this into account when users choose this backup method.

Example of disk capacity calculation

Network capacity calculation

The network capacity calculation part of this document only considers the replication. The user must calculate the network capacity by considering the service network, and the replication network capacity covered in this document must also be added to the total network capacity.

Packet size due to replication

In the case of sending packets in the replication, the following calculation formula is able for each transaction type.

As above, each transaction has a basic header, and the packet is sent as much as the sum of length information for each column and the length of actual data for sent data.

In a replication environment, INSERT needs to send all column data, and UPDATE only needs to send previous information and change information of the changed column data. DELETE can be processed by sending only the PK data of the data to be deleted.

Using the above calculation formula, if data input of 300 bytes in size to a replication table with 10 columns needs to be processed at 10,000 tps per second, it can be calculated as follows.

In the above environment, in order to meet user requirements, an environment that guarantees the network speed of 3.66Mbps based on the insert transaction is required.

In building the actual environment, correction factors for data increase or other factors should be applied. In terms of margin, it is recommended to build a network environment with at least a 30% increase.

As for the replication network card, a Giga-bit card is recommended, and a direct connection using a cross-cable or a private network is recommended.

However, if it is not a short distance, it is necessary to consider in advance whether the replication can be processed without delay within the available network bandwidth.

Overview

In this chapter, we have discussed how to calculate the CPU, memory, disk, and network capacity required for Altibase.

However, when estimating the capacity of a database system, there isn't much to consider for each item, so when configuring an actual system, it is desirable to find the most suitable configuration method with the review of sufficient data in advance.