Search docs.molo17.com
    Troubleshooting guide Pipelines

    Speed up snapshots and explain row counts

    A snapshot that crawls at a few thousand rows per second, a progress bar that stays at 4%, a target with fewer rows than the source, or a warning that rows were skipped: they look like one problem and they are several. This guide starts from what you see, shows where the time or the rows go, and covers the cases that the guides on tuning and on row differences do not: IBM i snapshots that are interrupted, ORA-01652 on an Oracle source, a low insert count with bulk load, and the duplicate row(s) skipped warning.

    In this guide

    You will learn how to read the timings of an entity to see whether the source, the network or the target is slow, how to recognise a snapshot that a connection reset interrupts, how to compare a slow entity with a fast one, how to read a partitioned snapshot, how to fix ORA-01652: unable to extend temp segment, why a snapshot can insert fewer rows than the source holds, and what the duplicate row(s) skipped warning means when the data is correct.

    Quick information

    Goal

    Find the real limit of a slow snapshot and explain why the target shows fewer rows than the source

    Audience

    Gluesync administrators and database administrators

    Level

    Intermediate

    Time required

    About 1 hour, plus the time of the snapshot you run again

    Area

    Control Plane → pipeline → entity detail page, Fine tuning and Notifications and alerts

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You can open the entity detail page of the slow entity in the Control Plane and read its metrics;

    2. You can read the Gluesync log, see Read Gluesync logs and diagnose errors;

    3. You can run SQL on the source and on the target, for example with Query Studio;

    4. You have read Tune snapshot and CDC throughput safely. This guide does not repeat its settings (Snapshot writing concurrency, Bulk load, Parallel reads, page size, memory); it helps you to see which one applies.

    Start from the symptom: use the table below to jump to the right part.

    What you see Go to

    Rows per second is low and nothing is saturated

    Step 1, then the tuning guide

    The progress stays at the same percentage, and the log has Connection reset

    The snapshot stops at the same percentage: connection reset

    The snapshot is slow in production and fast in staging

    Step 2

    The snapshot waits a long time before the first row, with partitions

    Step 3

    ORA-01652: unable to extend temp segment on an Oracle source

    ORA-01652: unable to extend temp segment

    The target has fewer rows than the source, or shows 0 rows during a bulk snapshot

    The target has fewer rows than the source

    The Notifications list has duplicate row(s) skipped and the data looks right

    duplicate row(s) skipped, and the data is correct

    Guide steps

    Read where the time goes

    UI

    1. Start the snapshot and open the entity detail page while it runs;

    2. Read Read time, Transmission time (source to target), Write time, End-to-end time, Row count and Total data transferred (see Control Plane monitoring);

    3. Compare the times with the table below;

    4. Divide Total data transferred by Row count. That is the average size of a row.

    What you see What it means

    Read time is short, Write time is long

    The target is the limit. Write time and transmission time rise together: the source waits until the target takes the next batch

    Read time is long and Write time is short

    The source read is the limit. More writers do not help: use a partitioned read (step 3)

    Total data transferred divided by Row count is hundreds of KB per row

    The rows are very large (long columns or LOBs). A table of a few hundred thousand rows can be slower than one with millions of small rows

    All times are short, but the rate is low

    Too few parallel writes, or a small page size: see the tuning guide

    In one case on an IBM i source, the read took about 10 ms and the write to PostgreSQL about 40 ms, but the transmission time was 373 s, and 2.33 GB had moved for about 5,000 rows: the rows were large. In another case the read of a batch took 185 ms, the write 4.1 s and the transmission 16.7 s, and the limit was the write into the target IBM i.

    Expected result

    You can say which side is slow and how large a row is. Change one setting at a time on that side, as in the tuning guide.

    Do not combine Bulk load and a high Snapshot writing concurrency and expect the two gains to add up: bulk load writes in one stream. On a 124-million-row IBM i table going to PostgreSQL, a bulk snapshot took 66 hours and a multi-thread snapshot was clearly faster, while the measurements of the tuning guide, on a fast local PostgreSQL, show the opposite. Measure both on your pipeline.

    Compare a slow entity with a fast one

    UI

    Use this when a table snapshots quickly in staging and slowly in production, with no sign of saturation on either side.

    1. Open Edit and then the gear icon of the entity in each environment, select Fine tuning, and compare the values: Snapshot writing concurrency, Bulk for Snapshot, Max snapshot page size (KB). A typing slip such as 124 instead of 1024 is enough to slow a snapshot down;

    2. On the entity detail page, select Compare DDL in both environments and compare the columns of the source with the columns of the target;

    3. Look for a column that is wider on the source than on the target, for example 60 characters against 10. An application release that lengthens a column makes the two sides differ;

    4. Fix the differences in the database or in the entity, then start the snapshot again.

    Earlier 2.3 builds name the page size Max transaction message size (KB), with a default of 1024. Later builds call it Max snapshot page size (KB), with a default of 8192. If you still have 1024 and the snapshot is slow, raising it to about 10000 gave a better rate in the reported cases. A page is held several times in memory, so raise it step by step and watch the heap, as in the tuning guide.
    Expected result

    The two environments have the same settings and the same column widths, or you know the difference that explains the speed. In the case with 60 characters against 10, the snapshot of the table finished in 2 minutes after the column was aligned.

    See Fix target length and truncation errors to measure and widen a column, and Handle schema changes on a live replication for a column that changed after the entity was created.

    Read a partitioned snapshot correctly

    UI

    With Read with partition, the entity first counts the rows of every partition and only then reads. The wait before the first row is that count, not a stall. The partitions are balanced only when the pivot column is spread evenly.

    1. Open the snapshot performance settings of the entity (Logical partitioning) and select Calculate partitions;

    2. Read Count items for each partition;

    3. If one partition holds most of the rows, choose another pivot column. Gluesync splits the interval between the smallest and the largest value into equal ranges, so a column with a few very large values gives one huge partition and several empty ones. More partitions do not fix it;

    4. Check that the pivot column is not changed during the snapshot, and note whether it has an index on the source and whether it can hold NULL: these are the first questions when a partitioned snapshot is slow or unbalanced.

    Expected result

    Count items shows partitions of similar size. The snapshot starts to read after the count and every partition progresses.

    The snapshot stops at the same percentage: connection reset

    What you see. The progress of an IBM i snapshot stays at a low value (4% in the reported case, 16,731 rows of 198,308) and the log has Communication link failure with Connection reset several times a day, or The connection does not exist. The entity has no other error.

    Why it happens. The connection from Gluesync to the IBM i passes a network device that closes TCP connections that stay idle, for example a load balancer or a VPC endpoint. On such services the default idle timeout is often about 350 seconds. During a snapshot with large rows the read connection can wait longer than that while the target writes, and the device closes it.

    What to do.

    1. Ask the network team for the idle timeout of the device on the path to the IBM i. On a load balancer it is set on each listener. Raise it above the longest wait of the snapshot;

    2. Make the waits shorter: enable Bulk for Snapshot on the entity if the target supports it (on PostgreSQL it loads with COPY through a staging table), and keep Snapshot writing concurrency at 2 or 3 when the source is slower than the target;

    3. Look at the other entities of the pipeline. A connection that resets can leave changes waiting. If the retention of the entity discarded changes that had not reached the target, run a snapshot of that entity again after the connection is stable.

    Check that it is fixed. The log has no new Connection reset, and the progress goes past the percentage where it stopped. In the reported case the timeout was a parameter of each load balancer listener.

    ORA-01652: unable to extend temp segment

    What you see. An Oracle source entity fails when it starts or during the snapshot, with SQL Error: Code 1652, State 72000, Message ORA-01652: unable to extend temp segment by 128 in tablespace TEMP, and the snapshot starts again. In the reported case, on a table of more than 120 million rows, it came back about ten times in under two hours, each time after 12 to 13 minutes of reading.

    Why it happens. Oracle returns the error: the session that reads the table cannot get more space in the TEMP tablespace. The read of a partition orders the rows by primary key, and Oracle sorts them in TEMP. When the logical partitioning is badly balanced (see step 3), one partition holds almost the whole table. In the reported case the first partition held 126,027,346 rows of about 127.4 million, and the other three held 1,389,041, 790 and 361. The sort of the first partition used all of TEMP. Eight parallel reads on the same column were still unbalanced (33.9 and 92.1 million rows in two parts).

    What to do.

    1. Open the snapshot performance settings of the entity and read Count items for each partition. If they differ a lot, the partitioning is the cause;

    2. Look at how the pivot column is distributed on the source. For a numeric column, count the rows by bands of one million values:

      SELECT TRUNC(<pivot_column> / 1000000) AS band, COUNT(*) AS rows_in_band
FROM   <schema>.<table>
GROUP  BY TRUNC(<pivot_column> / 1000000)
ORDER  BY 1;

    3. Choose a pivot column whose values are spread evenly, then select Calculate partitions again and check that Count items is balanced;

    4. Ask the database administrator to check the TEMP tablespace. Every partition still needs a sort. Look at its size and at the autoextend of its temporary files:

      SELECT tablespace_name, file_name, ROUND(bytes / 1048576) AS size_mb, autoextensible, ROUND(maxbytes / 1048576) AS max_mb
FROM   dba_temp_files
ORDER  BY tablespace_name, file_name;

      and at the sessions that use TEMP when the entity starts (the view V$TEMPSEG_USAGE);

    5. Start the snapshot again.

    Check that it is fixed. The log has no ORA-01652, and the first partition finishes. The other partitions finish in a short time when they are balanced.

    The target has fewer rows than the source

    Check the cause in this order. Each one was seen on a snapshot.

    Nothing is written yet. With Bulk for Snapshot, Gluesync collects rows until it has Max Write Bulk Size rows (250,000 by default, see Target agent advanced settings), writes them to a staging table, and applies them to the final table. The count on the target does not move until a whole chunk is confirmed. A target that shows 0 rows while millions have been sent can be waiting for the first chunk, or be slow to write it: look at Write time (step 1). The figures on the entity page can also be late while the entity is still counting the source rows. On an IBM i target the user needs rights to create objects in the staging schema, see Bulk load.

    The source holds the same key twice, and bulk load is on. In a case on an IBM i source going to MySQL, the entity statistics showed a Snapshot Row Count of 54,091 and the target held 2,191 rows after a snapshot with Bulk for Snapshot. The same snapshot without bulk load loaded the table correctly and raised a warning for one duplicate row. Bulk load applies the rows with MERGE statements between the staging table and the target, so rows with the same key collapse into one, and no error or warning names the rows. A logical key that is not unique gives this result.

    1. In the Edit mapping dialog, select Check keys. It reports the rows that share a key;

    2. On the source, count the copies of every key:

      SELECT <key_column>, COUNT(*) AS copies
FROM   <schema>.<table>
GROUP  BY <key_column>
HAVING COUNT(*) > 1;

    3. Turn off Bulk for Snapshot while the keys are not unique. Use bulk load only when the data types are exact and the key is unique;

    4. Remove the duplicates on the source, or choose a key that is unique. See Choose a key strategy: physical, logical, RRN or computed.

    The count is an estimate. The Snapshot Row Count in the statistics is an estimate that can be rounded while it runs. To compare the two sides, pause the entity and use Count both, or Validator: see Find and fix row differences. That guide also covers target filters, Allowed operations, Keep and merge and rows edited on the target.

    The target has more rows. A target table without a primary key or a unique index can store the same row twice when it is applied twice. Create the key on the target, and see Choose keys for tables without a primary key.

    Check that it is fixed. Count both shows the same number of rows on both sides, or the difference you expect, and a Validator run on the entity ends as Completed.

    duplicate row(s) skipped, and the data is correct

    What you see. On an IBM i entity in CDC, with On duplicate key set to Skip, the Notifications list has the warning Duplicate rows skipped on <entity> with a text such as:

    2 duplicate row(s) skipped. Skipped rows (batch index and key values):
[#0 transactionId=<id> keys={<key>=<value>, ...}], [#2 transactionId=<id> keys={...}]

    The source has no duplicate key and the final rows on the target look right. The batch indexes do not follow the order of the journal entries.

    Why it happens. An application program renumbered the rows of a document inside one transaction: it moved rows up by changing their key, then inserted a new row with a freed key. Gluesync applies an update that changes a key as a delete of the old key and an insert of the new one. When the key columns of the target table have a case-insensitive collation (the case was a MySQL target), Gluesync merged the rows of the same transaction that had the same key, to avoid duplicates in a batch. The deletes were merged with the later inserts and lost. The inserts then found their keys still present on the target, and Skip discarded them. The row that was skipped can hold different data from the row that stayed, and then the target is wrong until a later change rewrites it. In the reported case it was a single row, which a later update corrected.

    A later 2.3 release applies the changes in the order of the journal. Update Gluesync from Platform updates.

    What to do.

    1. Read the keys listed in the warning. On the source and on the target, run SELECT * FROM <table> WHERE <key conditions> for each listed key, and compare the rows (see On duplicate key for the shape of the warning);

    2. Until you update, set On duplicate key to Upsert on the entities that raise the warning. A row is then overwritten instead of discarded, and the final result on the target is correct. Compare the two sides afterwards;

    3. After the update, run Validator on the entities that raised the warning, and use Reconcile or a resync of the table to realign the rows that were skipped earlier (see Find and fix row differences).

    Check that it is fixed. The warning does not return after a renumbering transaction, and Validator ends as Completed.

    Verify the result

    You have successfully explained the speed or the row count if:

    • You can name the slow side from Read time, Write time and Transmission time, and you changed one setting on that side;

    • The log has no Connection reset or ORA-01652 while the snapshot runs, and the progress bar reaches 100%;

    • The partitions of a partitioned snapshot have similar Count items;

    • Count both or Validator shows no unexplained difference between the source and the target;

    • A Bulk for Snapshot entity has keys that are unique on the source.

    Troubleshooting

    Problem Possible cause What to do

    The snapshot is slow, but the CPU of Gluesync and of both databases is low

    Large rows, a small page size, or too few writers

    Read Total data transferred divided by Row count, then follow the tuning guide

    The progress stays at 4% and the log has Communication link failure

    An idle timeout on the network path closes the read connection

    See The snapshot stops at the same percentage: connection reset

    The snapshot is far slower in production than in staging

    A different setting, a typing slip in the page size, or a column that is wider on the source

    See step 2

    The first row arrives long after the start of a partitioned snapshot

    Gluesync counts the rows of each partition before it reads

    Wait for the count. If one partition holds most rows, change the pivot column (step 3)

    ORA-01652 repeats on the first partition

    One partition holds almost the whole table and its sort fills TEMP

    Balance the partitions, ask the database administrator to check TEMP, see ORA-01652: unable to extend temp segment

    The target shows 0 rows during a bulk snapshot

    Rows wait for a whole chunk of Max Write Bulk Size rows

    Wait for the first chunk and read Write time, see The target has fewer rows than the source

    The target has fewer rows than the source after a bulk snapshot

    A key is not unique on the source, and MERGE collapses the copies

    Run Check keys, turn off Bulk for Snapshot or fix the key

    duplicate row(s) skipped and the source has no duplicate

    Changes of one transaction were merged on a key that changes (key renumbering)

    See duplicate row(s) skipped, and the data is correct

    After a connection reset, rows are missing on some entities

    Changes waited longer than the retention and were discarded

    Run a snapshot again for those entities

    Related guides

    Contact MOLO17 Support if you need assistance with a slow snapshot or a row count that does not match.