Data Loading Stuck #129
Comments
|
Hello,
It seems that the data stream (MySQL -> nmig - > PG COPY) is still running.
"my_table" is quite large ))
Any chance you can give it more time to see when/if it will complete?
…On Wed, Jul 31, 2024 at 4:16 PM perror ***@***.***> wrote:
I noticed yesterday that the logs show --[NMIG DataWriter] Loading the
data into "public"."my_table" table..., but it has remained at this message
until now. I'm unsure what is happening.
System Configuration
- Running in K8S without any CPU or memory limits set.Running in K8S
without any CPU or memory limits set
Configuration Information
{
"source": {
"host": "mysql",
"port": 3306,
"database": "test",
"charset": "utf8mb4",
"supportBigNumbers": true,
"user": "xxx",
"password": "xxxx"
},
"target": {
"host": "postgres",
"port": 5432,
"database": "test",
"charset": "UTF8",
"user": "xxxx",
"password": "xxxx",
"ssl": {
"require": true,
"rejectUnauthorized": false
}
},
"max_each_db_connection_pool_size": 100,
"number_of_simultaneously_running_loader_processes": "8",
"loader_max_old_space_size": "16384",
"streams_high_water_mark": 16384,
"encoding": "utf8",
"schema": "public",
"migrate_only_data": false,
"delimiter": ",",
"enable_extra_config": false
}
Logs
NMIG - the database migration tool
Copyright (C) 2016 - present, Anatoly Khaytovich ***@***.***>
--[boot] Configuration has been just loaded.
--[boot] NMIG is restarting after some failure.
--[boot] Consider checking log files at the end of migration.
--[MigrationStateManager::createStateLogsTable] table "public"."state_logs_publicsubtest" is created...
--[DataPoolManager::createDataPoolTable] table "public"."data_pool_publicsubtest" is created...
--[TableProcessor::createTable] Currently creating table: `my_table`
--[StructureLoader::default] Source DB structure is loaded...
--[StructureLoader::default] Tables to migrate: 1
--[StructureLoader::default] Views to migrate: 0
--[TableProcessor::createTable] Table "public"."my_table" is created...
--[DataPoolManager::readDataPool] Data-Pool is loaded...
--[NMIG data transfer] "public"."my_table" DATA TRANSFER IN PROGRESS...
--[NMIG data transfer] TIME REQUIRED FOR TRANSFER DEPENDS ON AMOUNT OF DATA...
--[NMIG loadData] Loading the data into "public"."my_table" table...
--[NMIG DataWriter] Loading the data into "public"."my_table" table...
The my_table contains a total of 202,488,638 rows, with a data size of
approximately 396.05 GB.
When I executed SHOW PROCESSLIST in MySQL, I found no active processes.
In Postgres, querying SELECT * FROM pg_stat_activity WHERE state NOT
ILIKE 'idle%' showed only one SQL statement running: COPY
"public"."chain_extrinsics" (xxxxx,xxx,xxxx,xxx,xxxx,xx) FROM STDIN WITH
(FORMAT csv, DELIMITER ',', ENCODING 'UTF8');
Here is the process information from the pod:
PID PPID USER STAT VSZ %VSZ CPU %CPU COMMAND
1 0 root S 1209m 2% 6 0% /app/app -c /conf/config.json
73 62 root S 735m 1% 8 0% /usr/local/bin/node /app/nmig/dist/src/DataWriter.js --max-old-space-size=16384
62 30 root S 731m 1% 10 0% /usr/local/bin/node --max-old-space-size=16384 /app/nmig/dist/src/DataReader.js
30 16 root S 684m 1% 11 0% node dist/src/Main.js --conf-dir=/data/migrate/config --logs-dir=/data/migrate/logs
16 1 root S 681m 1% 8 0% npm start --conf-dir=/data/migrate/config --logs-dir=/data/migrate/logs
51 30 root S 677m 1% 12 0% /usr/local/bin/node /app/nmig/dist/src/LogsProcessor.js
—
Reply to this email directly, view it on GitHub
<#129>, or unsubscribe
<https://github.com/notifications/unsubscribe-auth/ADHR7N6DISCKFCT3AQVKZDDZPDPRPAVCNFSM6AAAAABLYO2VSSVHI2DSMVQWIX3LMV43ASLTON2WKOZSGQ2DAMBTG44TSNI>
.
You are receiving this because you are subscribed to this thread.Message
ID: ***@***.***>
|
|
Today I deleted the remaining files and database tables left after the last run, but when running again, it still got stuck at the same place. 
CPU usage: Memory usage: Network: It seems that there is no more any activity after running for about 40 minutes. |
|
I added some logs and found that it was indeed stuck: |
|
Any chance, that the "my_table" contains any kind of BLOB columns? |
|
@perrornet I had the same issue. It seems like there was a partial migration that happened and was subsequently stopped. I deleted the database and schema and re ran the script and it worked again. (I did a whole investigation into ignoring tables, increasing pool connection but nothing work). |
This solution does not work for me. I have modified the nmig code and no longer use the 'copy' command to import data. Instead, I am using bulk insert SQL. The migration has now been completed successfully. |
Yes, one of the fields in "my_table" is of type longblob. |
|
@perrornet Could you please share your version with better logging and inserts? I faced the same issue - if the target db is not empty, then data migration stuck in ~50% of runs |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I noticed yesterday that the logs show --[NMIG DataWriter] Loading the data into "public"."my_table" table..., but it has remained at this message until now. I'm unsure what is happening.
System Configuration
Configuration Information
Logs
The
my_tablecontains a total of 202,488,638 rows, with a data size of approximately 396.05 GB.When I executed
SHOW PROCESSLISTin MySQL, I found no active processes. In Postgres, queryingSELECT * FROM pg_stat_activity WHERE state NOT ILIKE 'idle%' showed only one SQL statement running: COPY "public"."chain_extrinsics" (xxxxx,xxx,xxxx,xxx,xxxx,xx) FROM STDIN WITH (FORMAT csv, DELIMITER ',', ENCODING 'UTF8');Here is the process information from the pod:
The text was updated successfully, but these errors were encountered: