AP FAQ

FAQ about columnar storage

What is columnar storage?

Columnar storage is a data storage format in which static data is stored in columnar format on disk and modified data in memory is stored in row format. This format balances scanning performance and transaction processing capabilities.

For analytical queries, columnar storage significantly improves query performance. It is an essential feature for OceanBase Database to support HTAP. In classic AP databases, columnar data is typically static and difficult to update in place. However, in OceanBase Database's LSM Tree architecture, SSTables are static and naturally suitable for columnar storage. MemTables are dynamic and still stored in row format, which does not affect transaction processing. This allows us to balance the performance of TP and AP queries.

What is a columnar index?

OceanBase Database also supports the concept of a columnar index, which is different from "creating an index on a columnar table." A columnar index refers to an index table stored in columnar format.

For example, if we have a rowstore table t6 and want to sum the values in column c3 with the best performance, we can create a columnar index on c3:

create table t6(
   c1 TINYINT,
   c2 SMALLINT,
   c3 MEDIUMINT
);

create /*+ parallel(2) */ index idx1 on t6(c3) with column group (each column);

In addition, we support other index creation methods:

• Redundant rowstore data in the index.

  create index idx1 on t1(c2) storing(c1) with column group(all columns, each column);
  alter table t1 add index idx1 (c2) storing(c1) with column group(all columns, each column);
  

• Pure columnar data in the index.

  create index idx1 on t1(c2) storing(c1) with column group(each column);
  alter table t1 add index idx1 (c2) storing(c1) with column group(each column);
  

The STORING clause in database indexes is used to store additional non-index column data in the index. This can optimize specific queries by avoiding table lookups and reducing the cost of index sorting. When a query only needs to access columns stored in the index and does not require a table lookup for the original row, query efficiency is significantly improved.

What is the difference between a columnar table and a columnar replica?

A columnar table is a table where all partitions (both leaders and followers) are stored in columnar format, and OLAP queries can be strongly read. A columnar replica, on the other hand, is a read-only replica where the leader is stored in columnar format, while the followers are stored in row format. OLAP queries on a columnar replica can only be weakly read.

How do I create a columnar table?

First, create a hybrid row-column table.

• For a non-partitioned table:

create table t1(
   c1 TINYINT,
   c2 SMALLINT,
   c3 MEDIUMINT
) with column group (all columns, each column);

• For a partitioned table:

create table t2(
   pk int,
   c1 int,
   c2 int,
   primary key (pk)
)
partition by hash(pk) partitions 4
with column group (all columns, each column);

When creating a hybrid row-column table, the with column group (all columns, each column) syntax is always used. The meaning of this syntax is as follows:

• all columns: All columns are aggregated into a single group, treated as a wide column, and stored row by row. This is essentially the same as traditional rowstore storage.
• each column: Each column in the table is stored in columnar format.

When both all columns and each column are specified, it means that a columnar table is created by default, with rowstore data redundantly stored. Each replica stores two baseline datasets. However, it is worth noting that, regardless of the number of baseline datasets, the incremental data in the memtable and the dump is still shared.

Next, create a pure columnar table.

• For a non-partitioned table:

create table t3(
   c1 TINYINT,
   c2 SMALLINT,
   c3 MEDIUMINT
) with column group (each column);

• For a partitioned table:

create table t4(
   pk1 int,
   pk2 int,
   c1 int,
   c2 int,
   primary key (pk1, pk2)
)
partition by hash(pk1) partitions 4
with column group (each column);

For the t4 table, a columnar index is created for each of the columns pk1, pk2, c1, and c2, and a rowstore index is also created for the combination of pk1 and pk2.

How do I make the tables created by a tenant default to columnar tables?

This is simple. Just set a tenant-level parameter:

alter system set default_table_store_format = "column";

Similarly, you can set the default table storage format to rowstore or to both rowstore and columnar:

alter system set default_table_store_format = "row";   //Rowstore
alter system set default_table_store_format = "compound"; //Both rowstore and columnar

What are the recommended configurations for columnar storage?

# Set the collation to utf8mb4_bin, which can improve performance by 15%.
set global collation_connection = utf8mb4_bin;
set global collation_server = utf8mb4_bin;

set global ob_query_timeout= 10000000000;
set global ob_trx_timeout= 100000000000;
set global ob_sql_work_area_percentage=30;
set global max_allowed_packet=67108864;
# Recommended value is 10 times the number of CPU cores.
set global parallel_servers_target=1000;
set global parallel_degree_policy = auto;
set global parallel_min_scan_time_threshold = 10;
# Limit the maximum degree of parallelism (DOP) when parallel_degree_policy = auto.
# A large DOP can cause performance issues. The recommended value is cpu_count * 2.
set global parallel_degree_limit = 0;

alter system set compaction_low_thread_score = cpu_count;
alter system set compaction_mid_thread_score = cpu_count;
alter system set default_table_store_format = "column";

How do I determine whether a query uses columnar storage?

When a query scans a rowstore table, the EXPLAIN output shows TABLE FULL SCAN. When a query scans a columnar table, the EXPLAIN output shows COLUMN TABLE FULL SCAN. For example, consider the following query on the t5 table:

create table t5(
   c1 TINYINT,
   c2 SMALLINT,
   c3 MEDIUMINT,
   c4 INT,
   c5 INT,
   PRIMARY KEY(c1, c2)
) with column group(all columns, each column);

OceanBase(admin@test)>explain select c1,c2 from t5;
+------------------------------------------------------------------------+
| Query Plan                                                             |
+------------------------------------------------------------------------+
| ===============================================                        |
| |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                        |
| -----------------------------------------------                        |
| |0 |TABLE FULL SCAN|t5  |1       |3           |                        |
| ===============================================                        |
| Outputs & filters:                                                     |
| -------------------------------------                                  |
|   0 - output([t5.c1], [t5.c2]), filter(nil), rowset=16                 |
|       access([t5.c1], [t5.c2]), partitions(p0)                         |
|       is_index_back=false, is_global_index=false,                      |
|       range_key([t5.c1], [t5.c2]), range(MIN,MIN ; MAX,MAX)always true |
+------------------------------------------------------------------------+
11 rows in set (0.011 sec)

OceanBase(admin@test)>explain select c1 from t5;
+------------------------------------------------------------------------+
| Query Plan                                                             |
+------------------------------------------------------------------------+
| ======================================================                 |
| |ID|OPERATOR              |NAME|EST.ROWS|EST.TIME(us)|                 |
| ------------------------------------------------------                 |
| |0 |COLUMN TABLE FULL SCAN|t5  |1       |3           |                 |
| ======================================================                 |
| Outputs & filters:                                                     |
| -------------------------------------                                  |
|   0 - output([t5.c1]), filter(nil), rowset=16                          |
|       access([t5.c1]), partitions(p0)                                  |
|       is_index_back=false, is_global_index=false,                      |
|       range_key([t5.c1], [t5.c2]), range(MIN,MIN ; MAX,MAX)always true |
+------------------------------------------------------------------------+
11 rows in set (0.003 sec)

Can I add or drop columns in a columnar table?

• Yes, you can add and drop columns.

• You can increase or decrease the maximum length of a VARCHAR column.

• Columnar tables support various offline DDL operations, similar to rowstore tables.

For more information about modifying columnar tables, see Convert a table from rowstore to columnar storage (MySQL-compatible mode) and Convert a table from rowstore to columnar storage (Oracle-compatible mode).

What are the characteristics of queries on a columnar table?

• In a hybrid row-column table, range scans default to columnar storage, while point get queries fall back to rowstore storage.

• In a pure columnar table, all queries use columnar storage.

Does columnar storage support transactions, and are there any size limitations?

Yes, columnar storage supports transactions, and there are no size limitations on transactions. It also provides high consistency.

What are the characteristics of log synchronization, backup, and restore for columnstore tables?

There are no special characteristics. The synchronization and backup of columnstore tables are the same as those of rowstore tables. The synchronized logs are in rowstore mode.

Can I convert a rowstore table to a columnstore table by using DDL operations?

Yes. You can convert a rowstore table to a columnstore table by adding a column group or dropping a column group. Syntax examples are as follows:

create table t1( pk1 int, c2 int, primary key (pk1));

alter table t1 add column group(all columns, each column);
alter table t1 drop column group(all columns, each column);

alter table t1 add column group(each column);
alter table t1 drop column group(each column);

Can I store multiple columns in a column group?

In OceanBase Database V4.3.0, you can store either all columns in a column group or each column in a separate column group. You cannot store only some columns in a column group.

Does OceanBase Database support updates for columnstore tables? What is the structure of MemTable?

In OceanBase Database, you can perform add, delete, and update operations in memory. The data is stored in MemTable in rowstore mode. The baseline data is stored in columnstore mode on disks. When you query a column, the system reads the rowstore data in MemTable and the columnstore data on disks, and returns the result. This means that OceanBase Database supports strong-consistency read for columnstore tables without data delay. The data written to MemTable can be dumped. The dumped data is stored in rowstore mode. After major compaction, the rowstore data and baseline columnstore data are merged to form new baseline columnstore data.

What are the characteristics of queries on columnstore tables?

In a redundant rowstore table, columnstore tables support range scans in columnstore mode by default. Point get queries still use rowstore mode.

In a pure columnstore table, all queries use columnstore mode.

Can I create an index on a column in a columnstore table?

Yes. OceanBase Database does not distinguish between creating an index on a column in a columnstore table and creating an index on a column in a rowstore table. The index structures are the same.

You can create an index on one or more columns in a columnstore table to construct a covering index for point queries or to sort specific columns for better sorting performance.

What is the maximum number of columns that can be stored in a columnstore table?

A columnstore table can store up to 4096 columns.

What are the considerations for using columnstore tables?

First: After you import data in batches, we recommend that you perform major compaction to improve the read performance. After you import data, you can trigger major compaction in the tenant. Then, execute alter system major freeze; in the tenant and select STATUS from CDB_OB_MAJOR_COMPACTION where TENANT_ID = tenant ID; in the sys tenant to check whether major compaction is completed. When STATUS changes to IDLE, major compaction is completed.

Second: After major compaction, we recommend that you collect statistics. You can collect statistics as follows:

• In a business tenant, you can collect statistics for all tables at one time by using 16 threads for concurrent collection:

  CALL DBMS_STATS.GATHER_SCHEMA_STATS ('db', granularity=>'auto', degree=>16);
  

• You can view the statistics collection progress in the GV$OB_OPT_STAT_GATHER_MONITOR view.

Third: You can use full direct load to import data in batches. The tables that use full direct load do not need to perform major compaction to achieve the best columnstore scan performance. The supported full direct load tools include obloader and the native load data command.

Fourth: In scenarios other than large wide tables, you can achieve performance similar to that of columnstore tables without using columnstore tables. This is because of the row-column hybrid storage architecture at the microblock level in OceanBase Database V4.3.0 (do not be surprised by this).

Fifth: The performance of cold runs and hot runs is different for tables with a large amount of data.

Sixth: The optimizer automatically selects whether to use rowstore or columnstore mode for accessing column data based on cost estimation.

Seventh: The major compaction speed of columnstore tables slows down.

What are the characteristics of log synchronization, backup, and restore for columnstore tables?

There are no special characteristics. The synchronization and backup of columnstore tables are the same as those of rowstore tables. The synchronized logs are in rowstore mode.

FAQ about data import and migration

What is bypass import? How do I perform bypass import?

Bypass import is a method of data import that can accelerate data import and data query. For large tables, we recommend that you use bypass import. Currently, the load data command and the insert into select statement support bypass import. For more information about how to use bypass import, see Overview.

Can I use Flink CDC to synchronize data from other databases to OceanBase Database?

Yes. For more information, see Use Flink CDC to synchronize data from a MySQL database to OceanBase Database.

Can I use Flink Connector to access OceanBase Database?

Yes. For more information, see https://github.com/oceanbase/flink-connector-oceanbase.

FAQ about performance tuning

What are other methods to further improve AP query performance?

According to some practical experiences, if there are no special sorting requirements, do not use the utf8mb4 character set when creating a table. Instead, use the binary character set to improve performance. For example:

create table t5(c1 TINYINT, c2 VARVHAR(50)) CHARSET=binary with column group (each column);

If users or businesses can accept it, specify the utf8mb4_bin character set when creating a table for a MySQL-compatible tenant. The syntax is as follows: CHARSET = utf8mb4 collate=utf8mb4_bin

Additionally, increasing the IOPS of a unit can accelerate direct load.

What are the features of the columnar optimizer?

Compared with the row-based optimizer, the columnar optimizer has the following features:

• The optimizer can autonomously choose to store data in rows or columns.
• You can use hints to control whether to store data in rows or columns at the table level.
• The optimizer supports cost estimation for columnar storage.
• The optimizer supports late materialization for columnar storage.