Manage Large Data Import in TiDB

When managing large-scale databases, efficient and reliable data import is essential especially during initial setup or migration from legacy systems. TiDB Lightning is a high-performance data import tool designed for the TiDB distributed SQL database.This guide covers everything you need to get started with TiDB Lightning.
 

What is TiDB Lightning?
TiDB Lightning is a powerful tool within the TiDB ecosystem designed to facilitate fast and efficient data import into TiDB clusters. It is particularly useful for large-scale data migration tasks, offering high performance and scalability. 
 

Purpose

The primary purpose of TiDB Lightning is to import data at a large scale, often used for initial data import into TiDB clusters. It is capable of processing data at a high speed, which is crucial for handling large datasets efficiently. The tool ensures data integrity through mechanisms like global checksums and metadata coordination, which are essential for maintaining data accuracy during the import process.

TiDB Lightning supports the following file formats:

• Files exported by Dumpling.
• CSV files.
• Apache Parquet files generated by Amazon Aurora, Apache Hive, or Snowflake.

TiDB Lightning can read data from the following sources:

• Local
• Amazon S3
• Google Cloud Storage

TiDB lightning import modes 

TiDB Lightning supports two import modes, configured by backend. The import mode determines the way data is imported into TiDB.

Physical Import Mode: 

TiDB Lightning first encodes data into key-value pairs and stores them in a local temporary directory, then uploads these key-value pairs to each TiKV node, and finally calls the TiKV Ingest interface to insert data into TiKV's RocksDB. If you need to perform initial import, consider the physical import mode, which has higher import speed. The backend for the physical import mode is local.

Logical Import Mode: 

TiDB Lightning first encodes the data into SQL statements and then runs these SQL statements directly for data import. If the cluster to be imported is in production, or if the target table to be imported already contains data, use the logical import mode. The backend for the logical import mode is tidb.

TiDB Lightning Architecture

Use TiDB Lightning

Prepare the source data

Use Dumpling to export the data from MySQL/TiDB.

Run TiDB Lightning

Set the Config

Create the configuration file tidb-lightning.toml based on your cluster information:

[lightning]
# Logging
level = "info"
file = “tidb-lightning.log”

[tikv-importer]
# Uses the Local-backend
backend = "local"
# Sets the directory for temporarily storing the sorted key-value pairs.
# The target directory must be empty.
sorted-kv-dir = “/data/tidb-tmp/”

[mydumper]
# Local source data directory
data-source-dir = “/data/dump/”

# Configures the wildcard rule. By default, all tables in the mysql, sys, INFORMATION_SCHEMA, PERFORMANCE_SCHEMA, METRICS_SCHEMA, and INSPECTION_SCHEMA system databases are filtered.
# If this item is not configured, the "cannot find schema" error occurs when system tables are imported.
filter = ['*.*', '!mysql.*', '!sys.*', '!INFORMATION_SCHEMA.*', '!PERFORMANCE_SCHEMA.*', '!METRICS_SCHEMA.*', '!INSPECTION_SCHEMA.*']
[tidb]
# Information of the target cluster
host = "172.17.0.6"
port = 4000
user = "root"
password = ""
# Table schema information is fetched from TiDB via this status-port.
status-port = 10080
# The PD address of the cluster
pd-addr = “172.17.0.5:2379”

Start the lightning

tiup tidb-lightning -config lightning.toml > lightning.out 

If the data was imported successfully it will show the output tidb lightning exit successfully

Limitations

Physical Import Mode Limitations

• During the import process, the target TiDB cluster becomes unavailable for other operations. This mode is designed for initializing empty.
• If your TiDB cluster has a latency-sensitive application and a low concurrency, it is strongly recommended that you do not use the physical import mode to import data into the cluster. This mode might have a significant impact on the online application.
• No DDL or DML operations should be performed on the target tables during the import process, as this can lead to failures or data inconsistencies. Additionally, it is not recommended to perform read operations during the import.
• The import process can significantly impact the performance of the cluster, and it is advised not to use multiple TiDB Lightning instances to import data to the same cluster unless using parallel import features.

Logical Import Mode Limitations
When using multiple TiDB Lightning instances, it is important not to mix the back ends (i.e., do not use both physical and logical import modes simultaneously on the same cluster).