Speeding up large transfers

• What DBConvert does on its own
• Read the copy plan in the log
• Worker threads
• Give tables a primary key
• Copy only the rows you need
• Track changes instead of comparing

What DBConvert does on its own

• Reads stream. Rows arrive from the source as the copy goes, so a large table does not have to fit in memory.
• Large tables are cut into pieces. The size comes from the source server's statistics. A table under 64 MB is read in one piece. A larger one is cut into pieces of about 256 MB, at least four per thread, so the threads finish together.
• Rows are written in batches. A batch ends at 1 MB of values or 1000 rows, whichever comes first.

PostgreSQL, SQL Server, MySQL and SQLite sources report their size. Other sources do not, so their pieces are sized from the declared column widths and each piece is read whole.

Read the copy plan in the log

Before a large table is copied, the log on the Execution step says how. For example:

Writer batches: up to 1 MB of values or 1000 rows a statement, fewer where the destination limits the values one statement takes.
Table 'payment' plan: about 820 MB by the server's statistics; 16 pieces of about 103000 rows, each a range of the key; rows streamed from the source; 4 threads.

Check three things in that line:

• each a range of the key is the fast way to cut a table. by its position in the table is slower: see Give tables a primary key.
• rows streamed from the source keeps memory low. buffered whole in the client means the source did not report its size.
• the number of threads, after the licence and the destination limits.

Worker threads

On the Execution step, set Worker threads. The licence limits and when to lower the number are in Limit parallel work.

Give tables a primary key

With a primary key or a unique key, a table is cut into ranges of that key, and each thread reads only its own range. Without one, each piece is found by its position, and every piece after the first reads the rows before it.

Synchronization also matches rows by key. A table without one cannot be synchronized reliably.

Copy only the rows you need

If a table holds a long history and the job needs only recent rows, set a row filter on the table. See Conditional migration and data filters.

Track changes instead of comparing

For repeated synchronization of a large database, use Tracking changes. Each run then visits only the rows that changed since the last one, instead of reading both databases in full. See Trigger-based synchronization.