Setup Partition Size

Table of Contents

Machbase has two types of files based on time: data file and index file. These file units are called partitions. The entire performance of the system is affected by the size of these partitions.

The size of each partition will be set by the unique formula, or the user can set it when creating a table. For data partition, the size of each column will be created on a time basis, and for index partition, partitions against indexes are created consecutively based on time.

  • Setup Data Partition - determined when creating tables
  • Setup Index Partition - determined when creating indexes

Setup Data Partition

It is efficient to set the size of partitions respectably based on the size of each column as partitions of a log table is created based on the number of columns. The efficiency of system I/O is increased when the size of a particular file that was managed by the operating system is adequate.

If the number of column records is fixed in one partition, it would be inefficient as it is setting one size for different file sizes, for example, 2 bytes of SHORT and 512 bytes of VARCHAR types can be about dozens of kilo bytes to over hundreds of mega bytes. Therefore, Machbase sets the size of a data partition to 4 mega bytes, which is an optimized number, by default.

Structure of Data Partition

One data partition is composed of multiple pages and it decides the size of the partition file according to the size and number of these pages.

The size of partition file = the number of pages the size of page
The size of file = the size of one column
the number of column that will be in one page

It also can set properties for each column as below.

PART_PAGE_COUNT: Number of pages that can be included in one partition.
PAGE_VALUE_COUNT: The number of column values that can be included in a single page.

Table. Default size of properties based on data types

Type of Data PART_PAGE_COUNT PAGE_VALUE_COUNT
short 32 65536
integer 32 32768
long 32 16384
datetime 32 16384
float 32 32768
double 32 16384
ipv4 32 16384
ipv6 32 16384
varchar(N) 32 3276 ~ 65536
binary, text 32 3276

Settings of Partition Property

The partition size of the column is specified when creating a table. Once it is set, it cannot be changed. If you want to change the value, you can do it by using "drop column" or "add column", but the existing data will be lost.

CREATE TABLE table_name ( column_name type property ( PART_PAGE_COUNT = value1, PAGE_VALUE_COUNT = value2 ));

Refer to the statement above for specifying the properties. If the values of the properties are not specified, default values will be used. However, if 0 or negative number is used, an error occurs.

Check Partition Size

You can check property values of designated partitions in each column via M$SYS_COLUMNS.

SELECT NAME, PART_PAGE_COUNT, PAGE_VALUE_COUNT from m$sys_columns;

NAME PART_PAGE_COUNT PAGE_VALUE_COUNT
-------------------------------------------------------------------------------
ID 32 32768
_RID 0 0
_ARRIVAL_TIME 32 16384
DT 32 16384
_RID 0 0
C2 32 32768
C3 32 32768
ellipsis...

Setup Index Partition

The index partition is created via CREATE INDEX syntax and is keyword or bitmap index file for a specified column. The index partition is based on the number of records that can be inserted in a single partition.

The key value and structure for index are stored in the index file. The index files are stored in memory at once. Thus, it might cause memory shortages when too large size of values are set. If a small size of value is set, it might slow down the performance due to the frequent access to index partitions.

Settings of Partition Property

Index partition property is specified when creating an index with CREATE INDEX syntax. By default, it is set to 100,000 cases. If you need to change this value, it can only be changed via "drop index" or "create index". So be careful when setting the initial value.

CREATE INDEX index_name ON table_name(column_name) PART_VALUE_COUNT = value;

Confirmation of Partition Properties

You can check the value of a partition property that is set to each index via M$SYS_INDEXES.

mach> SELECT NAME, PART_VALUE_COUNT from m$sys_indexes;
NAME PART_VALUE_COUNT
--------------------------------------------------------------
IDX1 100000
IDX2 100000
[2] row(s) selected.

results matching ""

    No results matching ""