MOT Usage

Using MOT tables is quite simple and is described in the few short sections below.

MogDB enables an application to use of MOT tables and standard disk-based tables. You can use MOT tables for your most active, high-contention and throughput-sensitive application tables or you can use MOT tables for all your application's tables.

The following commands describe how to create MOT tables and how to convert existing disk-based tables into MOT tables in order to accelerate an application's database-related performance. MOT is especially beneficial when applied to tables that have proven to be bottlenecks.

The following is a simple overview of the tasks related to working with MOT tables -

• Granting User Permissions
• Creating/Dropping an MOT Table
• Creating an Index for an MOT Table
• Converting a Disk Table into an MOT Table
• Query Native Compilation
• Retrying an Aborted Transaction
• MOT External Support Tools
• MOT SQL Coverage and Limitations

Granting User Permissions

The following describes how to assign a database user permission to access the MOT storage engine. This is performed only once per database user, and is usually done during the initial configuration phase.

NOTE: The granting of user permissions is required because MOT is integrated into the MogDB database by using and extending the Foreign Data Wrapper (FDW) mechanism, which requires granting user access permissions.

To enable a specific user to create and access MOT tables (DDL, DML, SELECT) -

Run the following statement only once -

GRANT USAGE ON FOREIGN SERVER mot_server TO <user>;

All keywords are not case sensitive.

Creating/Dropping an MOT Table

Creating a Memory Optimized Table (MOT) is very simple. Only the create and drop table statements in MOT differ from the statements for disk-based tables in MogDB. The syntax of all other commands for SELECT, DML and DDL are the same for MOT tables as for MogDB disk-based tables.

• To create an MOT table -

  create FOREIGN table test(x int) [server mot_server];

• Always use the FOREIGN keyword to refer to MOT tables.

• The [server mot_server] part is optional when creating an MOT table because MOT is an integrated engine, not a separate server.

• The above is an extremely simple example creating a table named test with a single integer column named x. In the next section (Creating an Index) a more realistic example is provided.

• MOT tables cannot be created if incremental checkpoint is enabled in postgresql.conf. So please set enable_incremental_checkpoint to off before creating the MOT.

• To drop an MOT table named test -

  drop FOREIGN table test;

For a description of the limitations of supported features for MOT tables, such as data types, see the MOT SQL Coverage and Limitations section.

Creating an Index for an MOT Table

Standard PostgreSQL create and drop index statements are supported.

For example -

create index  text_index1 on test(x) ;

The following is a complete example of creating an index for the ORDER table in a TPC-C workload -

create FOREIGN table bmsql_oorder (
  o_w_id       integer      not null,
  o_d_id       integer      not null,
  o_id         integer      not null,
  o_c_id       integer not null,
  o_carrier_id integer,
  o_ol_cnt     integer,
  o_all_local  integer,
  o_entry_d    timestamp,
  primarykey (o_w_id, o_d_id, o_id)
);

create index  bmsql_oorder_index1 on bmsql_oorder(o_w_id, o_d_id, o_c_id, o_id) ;

NOTE: There is no need to specify the FOREIGN keyword before the MOT table name, because it is only created for create and drop table commands.

For MOT index limitations, see the Index subsection under the SQL Coverage and Limitations section.

Converting a Disk Table into an MOT Table

The direct conversion of disk tables into MOT tables is not yet possible, meaning that no ALTER TABLE statement yet exists that converts a disk-based table into an MOT table.

The following describes how to manually perform a few steps in order to convert a disk-based table into an MOT table, as well as how the gs_dump tool is used to export data and the gs_restore tool is used to import data.

Prerequisite Check

Check that the schema of the disk table to be converted into an MOT table contains all required columns.

Check whether the schema contains any unsupported column data types, as described in the Unsupported Data Types section.

If a specific column is not supported, then it is recommended to first create a secondary disk table with an updated schema. This schema is the same as the original table, except that all the unsupported types have been converted into supported types.

Afterwards, use the following script to export this secondary disk table and then import it into an MOT table.

Converting

To covert a disk-based table into an MOT table, perform the following -

1. Suspend application activity.
2. Use gs_dump tool to dump the table’s data into a physical file on disk. Make sure to use the data only.
3. Rename your original disk-based table.
4. Create an MOT table with the same table name and schema. Make sure to use the create FOREIGN keyword to specify that it will be an MOT table.
5. Use gs_restore to load/restore data from the disk file into the database table.
6. Visually/manually verify that all the original data was imported correctly into the new MOT table. An example is provided below.
7. Resume application activity.

IMPORTANT Note - In this way, since the table name remains the same, application queries and relevant database stored-procedures will be able to access the new MOT table seamlessly without code changes. Please note that MOT does not currently support cross-engine multi-table queries (such as by using Join, Union and sub-query) and cross-engine multi-table transactions. Therefore, if an original table is accessed somewhere in a multi-table query, stored procedure or transaction, you must either convert all related disk-tables into MOT tables or alter the relevant code in the application or the database.

Conversion Example

Let's say that you have a database name benchmarksql and a table named customer (which is a disk-based table) to be migrated it into an MOT table.

To migrate the customer table into an MOT table, perform the following -

1. Check your source table column types. Verify that all types are supported by MOT, refer to section Unsupported Data Types.

   benchmarksql-# \d+ customer
                          Table "public.customer"
    Column |  Type   | Modifiers | Storage | Stats target | Description
   --------+---------+-----------+---------+--------------+-------------
    x      | integer |           | plain   |              |
    y      | integer |           | plain   |              |
   Has OIDs: no
   Options: orientation=row, compression=no

2. Check your source table data.

   benchmarksql=# select * from customer;
    x | y
   ---+---
    1 | 2
    3 | 4
   (2 rows)

3. Dump table data only by using gs_dump.

   $ gs_dump -Fc benchmarksql -a --table customer -f customer.dump
   gs_dump[port='15500'][benchmarksql][2020-06-04 16:45:38]: dump database benchmarksql successfully
   gs_dump[port='15500'][benchmarksql][2020-06-04 16:45:38]: total time: 332  ms

4. Rename the source table name.

   benchmarksql=# alter table customer rename to customer_bk;
   ALTER TABLE

5. Create the MOT table to be exactly the same as the source table.

   benchmarksql=# create foreign table customer (x int, y int);
   CREATE FOREIGN TABLE
   benchmarksql=# select * from customer;
    x | y
   ---+---
   (0 rows)

6. Import the source dump data into the new MOT table.

   $ gs_restore -C -d benchmarksql customer.dump
   restore operation successful
   total time: 24  ms
   Check that the data was imported successfully.
   benchmarksql=# select * from customer;
    x | y
   ---+---
    1 | 2
    3 | 4
   (2 rows)
   
   benchmarksql=# \d
                                   List of relations
    Schema |    Name     |     Type      | Owner  |             Storage
   --------+-------------+---------------+--------+----------------------------------
    public | customer    | foreign table | aharon |
    public | customer_bk | table         | aharon | {orientation=row,compression=no}
   (2 rows)

Query Native Compilation

An additional feature of MOT is the ability to prepare and parse pre-compiled full queries in a native format (using a PREPARE statement) before they are needed for execution.

This native format can later be executed (using an EXECUTE command) more efficiently. This type of execution is much quicker because the native format bypasses multiple database processing layers during execution and thus enables better performance.

This division of labor avoids repetitive parse analysis operations. In this way, queries and transaction statements are executed in an interactive manner. This feature is sometimes called Just-In-Time (JIT) query compilation.

Query Compilation - PREPARE Statement

To use MOT’s native query compilation, call the PREPARE client statement before the query is executed. This instructs MOT to pre-compile the query and/or to pre-load previously pre-compiled code from a cache.

The following is an example of PREPARE syntax in SQL -

PREPARE name [ ( data_type [, ...] ) ] AS statement

PREPARE creates a prepared statement in the database server, which is a server-side object that can be used to optimize performance.

Execute Command

When an EXECUTE command is subsequently issued, the prepared statement is parsed, analyzed, rewritten and executed. This division of labor avoids repetitive parse analysis operations, while enabling the execution plan to depend on specific provided setting values.

The following is an example of how to invoke a PREPARE and then an EXECUTE statement in a Java application.

conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);

// Example 1: PREPARE without bind settings
String query = "SELECT * FROM getusers";
PreparedStatement prepStmt1 = conn.prepareStatement(query);
ResultSet rs1 = pstatement.executeQuery())
while (rs1.next()) {…}

// Example 2: PREPARE with bind settings
String sqlStmt = "SELECT * FROM employees where first_name=? and last_name like ?";
PreparedStatement prepStmt2 = conn.prepareStatement(sqlStmt);
prepStmt2.setString(1, "Mark"); // first name "Mark"
prepStmt2.setString(2, "%n%"); // last name contains a letter "n"
ResultSet rs2 = prepStmt2.executeQuery())
while (rs2.next()) {…}

The following describes the supported and unsupported features of MOT compilation.

Supported Queries for Lite Execution

The following query types are suitable for lite execution -

• Simple point queries -
  • SELECT (including SELECT for UPDATE)
  • UPDATE
  • DELETE
• INSERT query
• Range UPDATE queries that refer to a full prefix of the primary key
• Range SELECT queries that refer to a full prefix of the primary key
• JOIN queries where one or both parts collapse to a point query
• JOIN queries that refer to a full prefix of the primary key in each joined table

Unsupported Queries for Lite Execution

Any special query attribute disqualifies it for Lite Execution. In particular, if any of the following conditions apply, then the query is declared as unsuitable for Lite Execution. You may refer to the Unsupported Queries for Native Compilation and Lite Execution section for more information.

It is important to emphasize that in case a query statement does not fit

native compilation and lite execution, no error is reported to the client and the query will still be executed in a normal and standard manner.

For more information about MOT native compilation capabilities, see either the section about Query Native Compilation or a more detailed information in the Query Native Compilation (JIT) section.

Retrying an Aborted Transaction

In Optimistic Concurrency Control (OCC) (such as the one used by MOT) during a transaction (using any isolation level) no locks are placed on a record until the COMMIT phase. This is a powerful advantage that significantly increases performance. Its drawback is that an update may fail if another session attempts to update the same record. This results in an entire transaction that must be aborted. These so called Update Conflicts are detected by MOT at the commit time by a version checking mechanism.

NOTE: A similar abort happens on engines using pessimistic concurrency control, such as standard PG and the MogDB disk-based tables, when SERIALIZABLE or REPEATABLE-READ isolation level are used.

Such update conflicts are quite rare in common OLTP scenarios and are especially rare in our experience with MOT. However, because there is still a chance that they may happen, developers should consider resolving this issue using transaction retry code.

The following describes how to retry a table command after multiple sessions attempt to update the same table simultaneously. You may refer to the OCC vs 2PL Differences by Example section for more detailed information. The following example is taken from TPC-C payment transaction.

int commitAborts = 0;

while (commitAborts < RETRY_LIMIT) {

    try {
        stmt =db.stmtPaymentUpdateDistrict;
        stmt.setDouble(1, 100);
        stmt.setInt(2, 1);
        stmt.setInt(3, 1);
        stmt.executeUpdate();

        db.commit();

        break;
    }
    catch (SQLException se) {
        if(se != null && se.getMessage().contains("could not serialize access due to concurrent update")) {
            log.error("commmit abort = " + se.getMessage());
            commitAborts++;
            continue;
        }else {
            db.rollback();
        }

        break;
    }
}

MOT External Support Tools

The following external MogDB tools have been modified in order to support MOT. Make sure to use the most recent version of each. An overview describing MOT-related usage is provided below. For a full description of these tools and their usage, refer to the MogDB Tools Reference document.

gs_ctl (Full and Incremental)

This tool is used to create a standby server from a primary server, as well as to synchronize a server with another copy of the same server after their timelines have diverged.

At the end of the operation, the latest MOT checkpoint is fetched by the tool, taking into consideration the checkpoint_dir configuration setting value.

The checkpoint is fetched from the source server's checkpoint_dir to the destination server's checkpoint_dir.

Currently, MOT does not support an incremental checkpoint. Therefore, the gs_ctl incremental build does not work in an incremental manner for MOT, but rather in FULL mode. The Postgres (disk-tables) incremental build can still be done incrementally.

gs_basebackup

gs_basebackup is used to prepare base backups of a running server, without affecting other database clients.

The MOT checkpoint is fetched at the end of the operation as well. However, the checkpoint's location is taken from checkpoint_dir in the source server and is transferred to the data directory of the source in order to back it up correctly.

gs_dump

gs_dump is used to export the database schema and data to a file. It also supports MOT tables.

gs_restore

gs_restore is used to import the database schema and data from a file. It also supports MOT tables.

MOT SQL Coverage and Limitations

MOT design enables almost complete coverage of SQL and future feature sets. For example, standard Postgres SQL is mostly supported, as well common database features, such as stored procedures and user defined functions.

The following describes the various types of SQL coverages and limitations -

Unsupported Features

The following features are not supported by MOT -

• Engine Interop - No cross-engine (Disk+MOT) queries, views or transactions. Planned for 2021.
• MVCC, Isolation - No snapshot/serializable isolation. Planned for 2021.
• Native Compilation (JIT) - Limited SQL coverage. Also, JIT compilation of stored procedures is not supported.
• LOCAL memory is limited to 1 GB. A transaction can only change data of less than 1 GB.
• Capacity (Data+Index) is limited to available memory. Anti-caching + Data Tiering will be available in the future.
• No full-text search index.
• Do not support Logical copy.

In addition, the following are detailed lists of various general limitations of MOT tables, MOT indexes, Query and DML syntax and the features and limitations of Query Native Compilation.

MOT Table Limitations

The following lists the functionality limitations of MOT tables -

• Partition by range
• AES encryption
• Stream operations
• User-defined types
• Sub-transactions
• DML triggers
• DDL triggers

Unsupported Table DDLs

• Alter table
• Create table, like including
• Create table as select
• Partition by range
• Create table with no-logging clause
• DEFERRABLE primary key
• Reindex
• Tablespace
• Create schema with subcommands

Unsupported Data Types

• UUID
• User-Defined Type (UDF)
• Array data type
• NVARCHAR2(n)
• Clob
• Name
• Blob
• Raw
• Path
• Circle
• Reltime
• Bit varying(10)
• Tsvector
• Tsquery
• JSON
• Box
• Text
• Line
• Point
• LSEG
• POLYGON
• INET
• CIDR
• MACADDR
• Smalldatetime
• BYTEA
• Bit
• Varbit
• OID
• Money
• Any unlimited varchar/character varying
• HSTORE

UnsupportedIndex DDLs and Index

• Create index on decimal/numeric

• Create index on nullable columns

• Create index, index per table > 9

• Create index on key size > 256

  The key size includes the column size in bytes + a column additional size, which is an overhead required to maintain the index. The below table lists the column additional size for different column types.

  Additionally, in case of non-unique indexes an extra 8 bytes is required.

  Thus, the following pseudo code calculates the key size:

  keySize =0;
  
  for each (column in index){
        keySize += (columnSize + columnAddSize);
  }
  if (index is non_unique) {
        keySize += 8;
  }

  Column Type	Column Size	Column Additional Size
  varchar	N	4
  tinyint	1	1
  smallint	2	1
  int	4	1
  bigint	8	1
  float	4	2
  float8	8	3

  Types that are not specified in above table, the column additional size is zero (for instance timestamp).

Unsupported DMLs

• Merge into
• Select into
• Lock table
• Copy from table
• Upsert

Unsupported Queries for Native Compilation and Lite Execution

• The query refers to more than two tables
• The query has any one of the following attributes -
  • Aggregation on non-primitive types
  • Window functions
  • Sub-query sub-links
  • Distinct-ON modifier (distinct clause is from DISTINCT ON)
  • Recursive (WITH RECURSIVE was specified)
  • Modifying CTE (has INSERT/UPDATE/DELETE in WITH)

In addition, the following clauses disqualify a query from lite execution -

• Returning list
• Group By clause
• Grouping sets
• Having clause
• Windows clause
• Distinct clause
• Sort clause that does not conform to native index order
• Set operations
• Constraint dependencies