java - Transforming Huge Database table to Another Table -

java - Transforming Huge Database table to Another Table -

we migrating lot of info our old database new database.

presently, both of them actively[writing/reading] used based on flows/activity migration in progress.

here's real challenge me now. have old database table which's still getting used now.

atleast 100,000 new records getting inserted every day , need move 6 months old info [01-01-2014] new database.

we using mysql in both places. new table old table need logical-transformation of info carried through java-jdbc, table structures , functionality changed.

what ideal steps need follow , taken care transformation quick possible without fail.

as per calculation, migrating @ to the lowest degree 20,000,000 records.

some info:

presently index exists on id column. creation_date exists without index how fetch efficiently these records, improve query fetch?? efficient way transform using multi-threading efficient way write info new database

i expect able alter both databases. have written info gets changed while migration processed, shouldn't relevant how long takes migration needs run more 1 time anyway. has cope changing datasets, don't bother performance, bother correctness.

start adding indexed boolean column old database mark migrated data, don't need migrate more once. if info in old database gets updated (and not inserted) while migration in process utilize timestamp column instead. mark moment in time been shifted lastly , moment been edited lastly - way can see needs transformed , has been.

select info in chunks older newer. 1 chunk per thread (so first thread takes week 1, sec takes week 2 etc.). if table has no insertation date field utilize else split - id ranges, first letter of name, have you. create sure sorting column has index on it!

then process info in form:

open transaction on both dbs. select chunk of info old one transform info create batch insertion on new side (row - per row) create batch mark transformed on old side execute both batches commit both transactions if both batches executed correctly.

most bottleneck here java <-> sql server network connection using sufficiently big connection pool of import (at to the lowest degree amount of weeks since start +2 recommend).

after have transformed info 1 time need re-run quite maintain info date. collisions you.

if can cross-database selection old database insertion trigger combined cross-database view save lot of pain , soften transformation.

java mysql sql performance jdbc