Open chris-morris-h2o opened 1 year ago
I changed my interval to 5 minutes and the process has been running without hanging up for quite awhile and has found 4,271,354 unmigrated records now.
Is there a way to attempt to calculate the optimal interval for processing?
@chris-morris-h2o did you find an answer to this? This is something I'm wondering about for this migrate.sql
and also for general hypertable chunk intervals; it would be nice there were a way to estimate at runtime what would be a good batch or chunk size, as my input data can vary dramatically in density.
I'm thinking of using this migrate_to_hypertable in combination with staging tables for the process of backfilling. Rather than hammering the main hypertable with naive inserts from already sorted staging tables.
Timescale Version: 2.7.2 Postgres Version: 14.5
Issue:
I have a regular table that is ~170 gigs in size. I followed the directions in migrate.md:
CREATE TABLE sqlth_1_data_new (LIKE sqlth_1_data INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
select create_hypertable('sqlth_1_data_new', 't_stamp', chunk_time_interval => 86400000)
I then called
CALL migrate_to_hypertable('sqlth_1_data','sqlth_1_data_new');
Whenever I check the migration log it tends to stop at around ~9records not migrated and ~150,000 records migrated. This is way too small for the 170 gig table. If I stop the call and start it again, it finds the additional records (over 1 million) and begins to migrate them. I also attempted with the multithreading options:
CALL migrate_to_hypertable('sqlth_1_data','sqlth_1_data_new', '4 hours'::interval,'tagid',4,1);
CALL migrate_to_hypertable('sqlth_1_data','sqlth_1_data_new', '4 hours'::interval,'tagid',4,2);
CALL migrate_to_hypertable('sqlth_1_data','sqlth_1_data_new', '4 hours'::interval,'tagid',4,3);
CALL migrate_to_hypertable('sqlth_1_data','sqlth_1_data_new', '4 hours'::interval,'tagid',4,4);
This would migrate the same amount and hang again around 39 records left. If I stopped it and restarted it it would not find additional records unless I changed the interval. I have tried different intervals; 1, 4, 12, and 24 hour intervals. They don't stop at the same unmigrated records count. The multithreading option never seems to find additional records unlike the single threaded option.
If I call the single thread option, let it hang, stop it, call it a second time, let it run for a bit to find the unmigrated records, then stop it, and finally start the multithreaded option then the multithreaded option will start migrating the unmigrated records that the single thread initially found. However, at some point in the process (and it is different every time) the multithreaded stops working again and interrupting it and restarting it does not cause it to continue. If I call the singlethread option it will begin working again, but killing it and switching back to the multithreaded option does not cause the migration to continue.
I checked pg_stat_activity to see if I could see anything but don't really see anything that stands out. I'm not sure what the problem could be. Any ideas?
(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount,
(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE AND tgenabled = 'O') AS has_enable_triggers,
(CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned,
(SELECT count(1) FROM pg_catalog.pg_inherits WHERE inhrelid=rel.oid LIMIT 1) as is_inherits,
(SELECT count(1) FROM pg_catalog.pg_inherits WHERE inhparent=rel.oid LIMIT 1) as is_inherited
FROM pg_catalog.pg_class rel
WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = 2200::oid
AND NOT rel.relispartition
ORDER BY rel.relname;