Optimization of Insert Into Select

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: insert into select优化

| username: zhanggame1

[Test Environment for TiDB] Testing
[TiDB Version] 7.1
[Reproduction Path] What operations were performed to encounter the issue
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
Testing Situation
create table t2 like t1;
insert into t2 select * from t1;
t1 has about 4 million rows of data, consuming more than 3G of memory.
We have a lot of such data to migrate, and currently, the memory consumption is very high. Besides adding where conditions to migrate in batches, is there any other good solution?

| username: tony5413 | Original post link

Have you tried the import/export tool?

| username: xingzhenxiang | Original post link

Try dumpling+lighting

| username: redgame | Original post link

Using dumpling would be better.

| username: 孤君888 | Original post link

Would importing and exporting be faster? For example, tools like mysqldumper.

| username: zhanggame1 | Original post link

When importing and exporting, is it better to change the table name using parameters or by modifying the SQL file? Modifying the file can be cumbersome when dealing with large amounts of data.

| username: xfworld | Original post link

It is best to write in batches by segmenting the primary key. Using limit for pagination is also very suboptimal.

| username: 有猫万事足 | Original post link

After exporting from db1.t1, import it into db2.t1, which is a table with the same name in another database. Create a new database named db2. After the import is complete, rename it.

rename table db2.t1 to db1.t2;

Something like this will work. You don’t need to change a bunch of table names, just change the database name.

| username: linnana | Original post link

Using export and import tools, the memory consumption of the TiDB server should decrease somewhat.

| username: 人如其名 | Original post link

Refer to the non-transactional statement processing method: BATCH | PingCAP 文档中心

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.