Data Import and Export

KlustronDB supports full import of data from common database systems and streaming import of incremental data updates.

Full data import

If exporting data from PostgreSQL, you only need to perform a full dump of the data in the source database instance, and then execute KlustronDB's copy commandopen in new window to import it. If exporting data from other database systems, you need to ensure that their SQL syntax can be correctly executed by Klustron.

Import from MySQL

If the source database system is MySQL, starting from KlustronDB version 1.2, KlustronDB has supported commonly used MySQL DDL syntax and can directly process export files from tools like mysql-dump.

Special steps of KlustronDB-1.1

KlustronDB-1.1 does not support MySQL's DDL syntax, but KlustronDB provides the DDL2Kunlun tool to handle these DDL SQL statements and convert them into DDL SQL syntax that KlustronDB can execute. Therefore, it is necessary to use this tool in advance to process the SQL statements before feeding the automatically processed SQL into Klustron.

Methods to speed up full data injection

If there are a large number of data files that need to be imported, it is best to open multiple connections and process a portion of the data files in each connection, ideally importing one data file per connection. This way, you can maximize parallel imports, thereby making full use of system resources and significantly reducing the time required to import data. When performing parallel imports, if the data files are not split by table and the transaction isolation level is not READ COMMITTED (the default transaction isolation level of KlustronDB is READ COMMITTED), you need to set the transaction isolation level transaction_isolation='read committed' in the configuration file and then run the pg_ctl reload commandopen in new window to reload the configuration file.

If the data file used for importing data contains a large number of auto-commit INSERT statements, rather than inserting a large amount of data within an explicit transaction, the overhead of committing millions or even billions of transactions will place a huge load on the disk, significantly slowing down the data import speed. At this point, you can either modify the source data file to insert multiple rows per INSERT statement or within an explicit transaction to reduce the number of transaction commits, or temporarily modify the cluster node settings as described below to greatly reduce the IO overhead of transaction commits.

Temporary Speed-Up Setting for Data Injection

If you want to speed up the data import when importing the full dataset, and this database cluster will not be used for production before the import is completed, you can consider configuring the cluster's Kluscomp instances and storage shard masters as follows, and then revert to the original settings after the import is completed. This will significantly increase the import speed, but if a Kluscomp instance or shard master node fails (due to power outage, hardware or software failure, etc.) during the import, you will need to reinstall the database cluster from scratch and then import the data again.

These settings are also effective for speeding up streaming ingestion, but streaming ingestion is usually an ongoing process. At this time, the cluster may already be in use, so these speed-up setting modifications should not be made. If all the above speed-up requirements are still met during streaming ingestion, the variables can still be modified in the same way and restored after the ingestion is completed.

Klustore instance

| 变量名称                        | 灌数据前临时修改为此值  | 灌完数据后改回为此值  |
|--------------—------------------|-------------------------|-----------------------|
|  innodb_doublewrite             | 0                       | 1                     |
|  innodb_flush_log_at_trx_commit | 0                       | 1                     |
|  sync_binlog                    | 0                       | 1                     |
|  enable_fullsync                | false                   | true                  |

Streaming Import Incremental Update

For streaming data import from most database systems (such as classic databases Oracle, DB2, SQL Server, Sybase, Informix, PostgreSQL, etc.), third-party tools like OGG, DSG, etc., can be used; for streaming import from MySQL databases, for Klustron-1.1 version, the Binlog2sync tool provided by KlustronDB can be used to stream data updates; starting from KlustronDB-1.2 version, KlustronDB's CDC tool can be used for streaming import.

If you import full and incremental data from databases such as TiDB, these databases provide tools for full and incremental data export. For details, see the relevant tool documentation.