Compare Job is Slow

6.1 Compare Job is Slow

When you initiate a compare job, it runs in five phases - Pending, Initialising, Sorting, Initial Compare & COOS, and Finished. Compare job slows down because of lag in any one of these phases.

To find out the phase which is stalled or slow:

On the Veridata UI, select Running Jobs, and select View by Compare Pair.
Click Advanced Filter Options and check the status of the Run Phase column.

Based on the status showed in the Run Phase, follow the steps to resolve the issue.

Parent topic: Troubleshooting

6.1.1 Sorting Phase

Most common issues that can occur in the Sorting phase and their solutions are listed in this section.

Sorting is slow or stalled

This occurs when there is a lag in fetching the data from the source or target databases, due to:

Sorting done at Database

If you are sorting at the database, it can be slow. We recommend sorting at the server (Veridata server), which is faster.

Note:
Server sorting has a higher server memory usage, but it is faster.
To enable server sorting and to know more about sorting configurations, refer Sorting Configuration.
Insufficient memory allocated to the Sort Directory
Veridata stores sorted data in disk and the sorting process can slow down when there is insufficient space in the disk. To avoid this slow down, ensure that sufficient space is allocated to the sorting directory.

To know about the sort directory and its location, refer Sort Directory.

To know the memory requirement for the sort directory, refer Memory for Sorting and Sort Directory

To change memory mapped sort directory:

Memory mapped sort directory is where .map files are generated. By default it is temp directory of the operating system, but it can be changed from veridata.cfg file:

server.memory_mapped_sort_directory directorypath

Example: server.memory_mapped_sort_directory directorypath/tmp2
Apart from server.max_sort_memory, below parameters in the veridata.cfg file can also be modified to accelerate the sorting process.
- server.concurrent.writers: The number of writer threads per sort directory.
- server.number_sort_threads: The number of threads used to sort input buffers from the agent. This number should not be larger than the number of available processes.
- server.concurrent.readers : The number of reader threads for entire server.
Refer server.memory_mapped_sort_directory for more details.
Compare Fetch Size

To accelerate the compare process, the number of rows fetched from database can be increased in the connection settings, by changing the compare fetch size. Start with 10,000 and increase up to 100,000.

Refer Compare Fetch Size.

Increasing this value accelerates the fetch process during the initial comparison. Run the comparison again after changing the value.

Queries Stalled in the Database

To find out if queries are stalled:

On the Veridata UI, select Running Jobs, and select View by Compare Pair.

Click Advanced Filter Options and check the values in the Last Source Fetch and Last Target Fetch columns. If these values remain unchanged, despite not all rows from the source and target being fetched, then check values of the Source Fetch Latency and Target Fetch Latency colum.

If the latency values are high, then it can be due to slow network between server and agent or between agent and DB.
If all the required values look good, then database might have some running query that is not getting completed.

Refer Check Stalled Queries in Database, to check if any queries are running or stalled in both source and target DB.

Compare Pair is Stalled

If the compare pair remains stalled, despite changing all parameters, cancel the existing run, and rerun that particular compare pair. Verify the successful completion of the run.

Parent topic: Compare Job is Slow

6.1.2 Initial Compare Phase

The issues that can occur in the Initial Compare or the Initial Compare and COOS phases, are due to the lag in the comparison between the source and the target data. The possible solutions to these issues are listed in this section.

Check if the Table has Key Columns

Refer Specifying Primary Key (PK) Columns, to improve performance with key columns.

Use Delta Compare

Refer Delta Comparison, to enable delta compare to accelerate the comparison.

Source and Target Oracle Optimizer Hint

If the database is Oracle, you can use an optimizer hint to improve the processing of the queries.

Refer Optimizer Hint.

Managing Max Concurrent Comparison Threads

Running multiple comparison pairs in parallel can improve the overall compare time.

Refer Max Concurrent Comparison Threads.

Use Auto Partitions or Row Partitions or Table Partitions

You can divide large source and target tables into partitions. Refer Partitioning.

Setting ROWSCN

Set this parameter to skip full table comparison and compare data after a particular SCN value. Refer ROWSCN

Compare Pair is Stalled

If the compare pair remains stalled, despite changing all parameters, cancel the existing run, and rerun that particular compare pair. Verify the successful completion of the run.

Parent topic: Compare Job is Slow

6.1.3 COOS Phase

If the Run Phase is COOS or Initial Compare and COOS, but optimisations suggested in Initial Compare phase did not resolve the issue, follow the solutions listed in this section.

COOS Join Or COOS Batch Fetch

If DB is Oracle DB and temp table feature is supported in it, then use COOS join, else use Coos Batch Fetch.

To verify if the temp table is supported, run the command create private temporary table in DB.

Refer COOS Join and COOS Batch Fetch, for more information.

Note:

Do not use both COOS Join and COOS Batch Fetch together. Use one option.

Updating COOS Batch Size

Refer COOS Batch Size.

Source and Target Oracle Optimizer Hint

If the database is Oracle, you can use an optimizer hint to improve the processing of the queries.

Refer Optimizer Hint

Check Stalled Queries in the Database

Refer Check Stalled Queries in Database, to learn how to check for stalled queries in different databases.

Compare Pair is Stalled

If the compare pair remains stalled, despite changing all parameters, cancel the existing run, and rerun that particular compare pair. Verify the successful completion of the run.

Hardware Scaling (Memory and Processors)

If the above steps do not accelerate the compare, scale up the hardware.

RAM: Adding more memory to a single machine enables multiprocessing, leading to improved performance of applications that require significant memory. Based on the database size, approximately 60% of the data size is required for memory.

Processors: Upgrading to a more powerful CPU or adding additional cores to the existing CPU enhances the processing capabilit, accelerating the system speed and enabling more complex computations.

Parent topic: Compare Job is Slow

6.1.4 Check Stalled Queries in Database

Table 6-1 Queries Stalled in Database

Database	Query	Output
MySQL/ MariaDB	`SHOW PROCESSLIST;`	Lists active queries and their status. Look for queries that are in`Locked` status.
PostgreSQL	`SELECT pid, state, query, age(clock_timestamp(), query_start) AS duration FROM pg_stat_activity WHERE state <> 'idle';`	Lists all running queries and their states. You can indetify long-running queries by the duration.
Microsoft SQL Server	`SELECT session_id,status,command,wait_type,wait_time,wait_resource,cpu_time,total_elapsed_time,query_text.text AS query_text FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS query_text WHERE session_id > 50; -- Exclude system sessions`	Displays detailed information about running queries, including any that are waiting on resources.
Oracle DB	`SELECT s.sid, s.serial#, s.status, s.username, s.sql_id, t.sql_text FROM v$session s JOIN v$sqltext t ON s.sql_id = t.sql_id WHERE s.status = 'ACTIVE';`	Lists all active sessions and their SQL text.
SQLite	`PRAGMA database_list;PRAGMA locking_mode;`	Lists all active sessions and their SQL text.

Parent topic: Compare Job is Slow