The Ultimate Guide to High Availability with MariaDB

ColumnStoreバッチInsertモード

You are viewing an old version of this article. View the current version here.

MariaDB ColumnStoreは非トランザクション型のLOAD DATA INFILEとINSERT INTO SELECT FROM SQL文に対するcpimport高速データインポートツールを備えています。この方法を使うと、これらの二つのSQL文を実行するのに比べ、明らかにパフォーマンスが向上します。この最適化方法は、SELECT対象で使用されるストレージエンジンとは独立です。

バッチInsertに対するcpimportの有効化/無効化

これらのSQL文にcpimportを使うか否かの制御については、infinidb_use_import_for_batchinsert変数が参照されます。この変数は一時的、セッションレベル、またはステートメントレベルでオンオフの制御を行うことが可能です。

バッチInsertでのcpimport仕様有無をセッションレベルで制御するには、次のコマンドを実行します。セッションが終了すると、次のセッションはデフォルト値に戻ります。

set infinidb_use_import_for_batchinsert = n
where n is:
* 0 (disabled)
* 1 (enabled)

INSERT SELECTに対するデフォルトのデリミタの設定

  • infinidb_import_for_batchinsert_delimiter変数が内部的に使用されており、非トランザクション型のINSERT INTO SELECT FROM文あMariaDB ColumnStore

Changing default delimiter for INSERT SELECT

  • The infinidb_import_for_batchinsert_delimiter variable is used internally by MariaDB ColumnStore on a non-transactional INSERT INTO SELECT FROM statement as the default delimiter passed to the cpimport tool. With a default value ascii 7, there should be no need to change this value unless your data contains ascii 7 values.

To change this variable value at the at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

set infinidb_import_for_batchinsert_delimiter = ascii_value
where ascii_value is an ascii value representation of the delimiter desired.

Note that this setting may cause issues with multi byte character set data. It is recommended to utilize UTF8 files directly with cpimport.

Version buffer file management

If the following error is received, most likely with a transaction LOAD DATA INFILE or INSERT INTO SELECT then it is recommended to break up the load into multiple smaller chunks, increase the VersionBufferFileSize setting, or consider a non transactional LOAD DATA INFILE or to use cpimport.

ERROR 1815 (HY000) at line 1 in file: 'ldi.sql': Internal error: CAL0006: IDB-2008: The version buffer overflowed. Increase VersionBufferFileSize or limit the rows to be processed.

The VersionBufferFileSize setting is updated in the ColumnStore.xml typically located under /usr/local/mariadb/columnstore/etc. This dictates the size of the version buffer file on disk which provides DML transactional consistency. The default value is '1GB' which reserves up to a 1 Gigabyte file size. Modify this on the PM1 node and restart the system if you require a larger value.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.