FastTransfer Configuration

MigratorXpress uses FastTransfer for high-performance data transfer between databases. This page explains how to configure FastTransfer for optimal performance.

Overview

FastTransfer is a highly optimized streaming data transfer tool written in C#. It reads data from the source database and writes directly to the target database without storing data in memory, enabling transfers of tables with billions of rows.

Key Features

• Streaming Architecture: Data flows directly from source to target
• No Memory Saturation: Handles tables of any size without memory constraints
• Parallel Processing: Multiple parallel streams for large tables
• Database-Specific Optimization: Uses native features for each database type

Configuration Parameters

FastTransfer Directory

Specify the path to FastTransfer binaries:

--fasttransfer_dir_path ./FastTransfer_linux-x64_v0.12.4/

Platform-specific paths:

Parallel Degree (--fasttransfer_p)

Controls parallelism within large tables:

--fasttransfer_p 4  # Transfer large tables using 4 parallel streams

Guidelines:

• Small tables (< 250M rows): Use 1 (default)
• Large tables (> 250M rows): Use 2-8
• Very large tables (> 1B rows): Use 8-16

Considerations:

• More isn’t always better - test with your workload
• Consider available CPU cores on both source and target
• Monitor database load on both ends
• Network bandwidth limits

Large Table Threshold (--ft_large_table_th)

Row count threshold that triggers parallel transfer methods:

--ft_large_table_th 100000000  # Tables with >= 100M rows use parallel methods

Default: 250,000,000 rows

Behavior:

• Tables below threshold: Standard sequential transfer
• Tables at or above threshold (with fasttransfer_p > 1): Database-specific parallel method

Table-Level Parallelism (--n_jobs)

Controls how many tables transfer simultaneously:

--n_jobs 4  # Transfer 4 tables in parallel

Optimal Settings:

# Balanced configuration for medium server
--n_jobs 4 --fasttransfer_p 2

# High-performance configuration for large server
--n_jobs 8 --fasttransfer_p 4

# Conservative configuration
--n_jobs 2 --fasttransfer_p 1

Parallel Transfer Methods

FastTransfer automatically selects the best method based on source database type:

Method Selection Logic

IF row_count >= ft_large_table_th AND fasttransfer_p > 1:
    PostgreSQL → Ctid method
    Oracle → Rowid method
    Netezza → NZDataSlice method
    SQL Server → Standard method
ELSE:
    All databases → Standard method (no parallelism)

Performance Tuning

PostgreSQL Source

Recommended settings for large migrations:

--n_jobs 4 \
--fasttransfer_p 4 \
--ft_large_table_th 100000000

Ctid method notes:

• Works best with clustered tables
• No additional permissions required
• Automatically handles table partitioning

Oracle Source

Recommended settings for large migrations:

--n_jobs 4 \
--fasttransfer_p 4 \
--ft_large_table_th 100000000

Rowid method requirements:

• Requires SELECT_CATALOG_ROLE for optimal performance
• Falls back to standard method if role not granted

Oracle-specific options:

--p_query 6              # Inner query parallelism for profiling
--profiling_sample_pc 10  # Sample 10% of rows for profiling

Netezza Source

Recommended settings for large migrations:

--n_jobs 4 \
--fasttransfer_p 4 \
--ft_large_table_th 100000000

NZDataSlice method notes:

• Leverages Netezza’s data distribution
• Highly efficient for large tables
• No additional permissions required

SQL Server Source

Recommended settings:

--n_jobs 4 \
--fasttransfer_p 1  # Parallel methods not supported for SQL Server source

Note: SQL Server as a source uses standard transfer methods. Parallelism is achieved through --n_jobs (multiple tables simultaneously).

SQL Server Target Options

When migrating to SQL Server, additional options optimize the target:

Columnstore Indexes

--cci_threshold 1000000     # Create CCI for tables >= 1M rows
--aci_threshold 100000000   # Use archive compression for tables >= 100M rows

Behavior:

• Tables >= cci_threshold but < aci_threshold: Clustered Columnstore Index
• Tables >= aci_threshold: CCI with DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
• Tables < cci_threshold: Standard rowstore table

Foreign Key Mode

--fk_mode trusted  # Full enforcement
--fk_mode untrusted  # Enforce for new data only
--fk_mode disabled  # No enforcement (default, fastest)

Row Count Options

Compute Exact Row Counts

--compute_nbrows true  # Use COUNT(*) instead of estimates

Default: false (uses database statistics)

When to use:

• Statistics are outdated
• Need accurate threshold decisions
• Database has unreliable estimates

Trade-off: Slower discovery but accurate method selection

Transaction ID Usage

--without_xid  # Disable transaction IDs for Netezza/Oracle

Default: Transaction IDs enabled

When to use:

• Performance issues with XID
• Compatibility problems
• Data consistency not critical

Configuration Examples

Small Migration (Development)

./MigratorXpress --auth credentials.json \
  --source_db_auth_id oracle_dev \
  --source_schema_name HR \
  --target_db_auth_id postgres_dev \
  --target_schema_name hr \
  --migration_db_auth_id mig_db \
  --fasttransfer_dir_path ./FastTransfer_linux-x64/ \
  --n_jobs 2 \
  --fasttransfer_p 1 \
  --task_list translate create transfer diff

Medium Migration (Staging)

./MigratorXpress --auth credentials.json \
  --source_db_auth_id oracle_staging \
  --source_schema_name SALES \
  --target_db_auth_id mssql_staging \
  --target_schema_name sales \
  --migration_db_auth_id mig_db \
  --fasttransfer_dir_path ./FastTransfer_linux-x64/ \
  --n_jobs 4 \
  --fasttransfer_p 2 \
  --ft_large_table_th 50000000 \
  --cci_threshold 500000 \
  --task_list translate create transfer diff

Large Migration (Production)

./MigratorXpress --auth credentials.json \
  --source_db_auth_id oracle_prod \
  --source_schema_name ENTERPRISE \
  --target_db_auth_id mssql_prod \
  --target_schema_name enterprise \
  --migration_db_auth_id mig_db \
  --fasttransfer_dir_path ./FastTransfer_linux-x64/ \
  --n_jobs 8 \
  --fasttransfer_p 4 \
  --ft_large_table_th 100000000 \
  --cci_threshold 1000000 \
  --aci_threshold 100000000 \
  --compute_nbrows true \
  --task_list translate create transfer diff

Netezza to PostgreSQL

./MigratorXpress --auth credentials.json \
  --source_db_auth_id netezza_prod \
  --source_schema_name ANALYTICS \
  --target_db_auth_id postgres_prod \
  --target_schema_name analytics \
  --migration_db_auth_id mig_db \
  --fasttransfer_dir_path ./FastTransfer_linux-x64/ \
  --n_jobs 4 \
  --fasttransfer_p 4 \
  --ft_large_table_th 100000000 \
  --without_xid \
  --task_list translate create transfer diff

Monitoring Performance

Check Migration Progress

Query the migration tracking database:

-- View transfer status for current run
SELECT
    source_table,
    status,
    started_at,
    finished_at,
    DATEDIFF(SECOND, started_at, finished_at) AS duration_seconds,
    row_count
FROM migration_jobs
WHERE run_id = 'your-run-id'
ORDER BY duration_seconds DESC;

Identify Slow Tables

-- Find tables that took longest
SELECT TOP 10
    source_table,
    DATEDIFF(SECOND, started_at, finished_at) AS duration_seconds,
    row_count,
    CAST(row_count AS FLOAT) / NULLIF(DATEDIFF(SECOND, started_at, finished_at), 0) AS rows_per_second
FROM migration_jobs
WHERE run_id = 'your-run-id' AND status = 'completed'
ORDER BY duration_seconds DESC;

Optimize Slow Tables

If a table is slow:

1. Increase parallel degree: Try higher --fasttransfer_p
2. Adjust threshold: Lower --ft_large_table_th to enable parallel method
3. Check source DB: Look for locks, slow queries, resource constraints
4. Check target DB: Verify no blocking, sufficient resources
5. Network: Ensure adequate bandwidth between source and target

Troubleshooting

Transfer Fails for Large Tables

Problem: Large tables fail or hang during transfer

Solutions:

• Increase timeout settings
• Reduce --fasttransfer_p to decrease parallel load
• Check network stability
• Monitor memory usage on both source and target

Slow Transfer Performance

Problem: Transfer is slower than expected

Solutions:

• Verify parallel settings are being used (check row count vs threshold)
• Use --compute_nbrows true for accurate row counts
• Monitor network bandwidth
• Check for competing workloads on source/target
• Consider increasing --n_jobs for many small tables

Out of Memory Errors

Problem: Memory errors during transfer

Solutions:

• FastTransfer uses streaming - memory errors likely on database side
• Reduce --n_jobs to decrease concurrent connections
• Reduce --fasttransfer_p if database is memory-constrained
• Check database memory settings

Permission Errors on Oracle

Problem: Rowid method fails with permission errors

Solution:

-- Grant required role for parallel Rowid transfers
GRANT SELECT_CATALOG_ROLE TO your_username;

See Also

• CLI Reference - All command-line options
• Database Configuration - Database setup
• Examples - Real-world configurations
• Troubleshooting - Common issues