OutOfMemory exception when syncing huge tables for the first time

[martonb]

OutOfMemory exception when syncing huge tables for the first time

[martonb]

I finally figured it out... So, the server table I was syncing from didn't actually have IDENTITY on the ID field (!), my SQL colleagues explained tables are split up and the ID is generated via a separate table, on production (but my local data model, where I was syncing to, still contained IDENTITY).
Because of this, millions of exceptions were thrown (1x/row, 7,609,471 rows in the source table), filling up the error rows list
var errorsRows = new List<(SyncRow SyncRow, Exception Exception)>()
causing massive slowdown of UPSERTS files processing and memory growing excessively.
Recreated my client table, now it all seems to work fine...

If I may suggest, for the production version, as soon as the errors count hit a preset count N, just abort the whole sync, so the app doesn't crash with Out Of Memory?

Thank you for your support!

[martonb]

Also, about the slowness you also noticed: I changed the retention period to 1 MINUTE in my client (destination) database (as we only sync from Server to Client) and the processing of the UPSERTS files became faster. So it looks like MS SQL Server's internal tracking is slowing the syncing down.

[Mimetis]

I'm not able to reproduce it.
Can you provide a small sample I can use to reproduce this memory leak ?

[martonb]

The table that causes this currently has 4,643,451 rows, totaling 1,626.703 MB (aka ~1.6 GB), so small sample is not ;)
But you probably could generate a table similar (half the size would do I think) with dummy data in it.
I left the fixed version run overnight, still crashed for some reason, running it now from debugger, to get a call stack on it.

Using:

• MS SQL Server 2008
• SqlSyncChangeTrackingProvider for client and server too
• SyncDirection.DownloadOnly
• BatchSize = 6 * 1024

I could provide the exact test application, if it helps, but not the data from the tables, as it is client's GPS coordinates and such.

[Mimetis]

I have two tables with 2 millions rows.

Here is the code I used to create two heavy tables with 1M rows each:

USE [master]
GO 

-- Server database
if (exists (select * from sys.databases where name = 'AdventureWorks'))
Begin
	ALTER DATABASE [AdventureWorks] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [AdventureWorks]
End
Create database [AdventureWorks]
Go

-- Client database. No need to create the schema, Dotmim.Sync will do
if (exists (select * from sys.databases where name = 'Client'))
Begin
	ALTER DATABASE [Client] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [Client]
End
Create database [Client]
Go
 
USE [AdventureWorks]
GO

CREATE TABLE [dbo].[ProductCategory](
	[ProductCategoryID] [uniqueidentifier] NOT NULL,
	[ParentProductCategoryID] [uniqueidentifier] NULL,
	[Name] [nvarchar](50) NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NULL,
	[ModifiedDate] [datetime] NULL,
	[IsActive] [bit] NOT NULL DEFAULT(1),
 CONSTRAINT [PK_ProductCategory_ProductCategoryID] PRIMARY KEY CLUSTERED ([ProductCategoryID] ASC)) ON [PRIMARY]

/****** Object:  Table [dbo].[ProductDescription]    Script Date: 07/10/2017 15:28:49 ******/
CREATE TABLE [dbo].[ProductDescription](
	[ProductDescriptionID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](400) NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductDescription_ProductDescriptionID] PRIMARY KEY CLUSTERED ([ProductDescriptionID] ASC)) ON [PRIMARY]
GO


INSERT [dbo].[ProductCategory] ([ProductCategoryID], [ParentProductCategoryID], [Name], [rowguid], [ModifiedDate], [IsActive]) 
VALUES ('cfbda25c-df71-47a7-b81b-64ee161aa37c', NULL, N'Bikes', newid(), CAST(N'2002-06-01T00:00:00.000' AS DateTime), 1)

INSERT [dbo].[ProductCategory] ([ProductCategoryID], [ParentProductCategoryID], [Name], [rowguid], [ModifiedDate], [IsActive]) 
		VALUES (NEWID(), 'cfbda25c-df71-47a7-b81b-64ee161aa37c', convert(varchar(100), N'Bike-' + Lower(convert(varchar(36), newid()))),newid(), CAST(N'2023-06-01T00:00:00.000' AS DateTime), 1)
go 1000000

go
INSERT [dbo].[ProductDescription] ([Description], [rowguid], [ModifiedDate]) 
VALUES (convert(varchar(100), N'Bike description-' + Lower(SUBSTRING(convert(varchar(36), newid()), 1, 4))),newid(), CAST(N'2023-06-01T00:00:00.000' AS DateTime))
go 1000000

ALTER TABLE [dbo].[ProductDescription] ADD  CONSTRAINT [AK_ProductDescription_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC)
ALTER TABLE [dbo].[ProductCategory] ADD  CONSTRAINT [AK_ProductCategory_Name] UNIQUE NONCLUSTERED ([Name] ASC)
ALTER TABLE [dbo].[ProductCategory] ADD  CONSTRAINT [AK_ProductCategory_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC)
ALTER TABLE [dbo].[ProductCategory]  WITH CHECK 
ADD CONSTRAINT [FK_ProductCategory_ProductCategory_ParentProductCategoryID_ProductCategoryID] 
	FOREIGN KEY([ParentProductCategoryID]) 
	REFERENCES [dbo].[ProductCategory] ([ProductCategoryID])
ALTER TABLE [dbo].[ProductCategory] CHECK CONSTRAINT [FK_ProductCategory_ProductCategory_ParentProductCategoryID_ProductCategoryID]

Then the sync is pretty simple:

var clientProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(clientDbName));
var serverProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(serverDbName));
var setup = new SyncSetup("ProductCategory", "ProductDescription");
// avoiding reference check constraint failing on product category table fk 
var options = new SyncOptions { DisableConstraintsOnApplyChanges = true };

var progress = new SynchronousProgress<ProgressArgs>(s =>
    Console.WriteLine($"{s.ProgressPercentage:p}:  " +
    $"\t[{s?.Source?[..Math.Min(4, s.Source.Length)]}] {s.TypeName}: {s.Message}"));

var agent = new SyncAgent(clientProvider, serverProvider, options);

// output batch applied
agent.LocalOrchestrator.OnBatchChangesApplied(Console.WriteLine);

try
{
    Console.ForegroundColor = ConsoleColor.Green;
    var s = await agent.SynchronizeAsync(setup, progress);
    Console.WriteLine(s);
}
catch (Exception e)
{
    Console.ResetColor();
    Console.WriteLine(e.Message);
}

Using a Performance analysis, I'm under 97 Mo of memory used, without any leaks.

Note: I did not used SqlSyncChangeTrackingProvider. For some reasons it's slower... I'm searching why (and honestly I don't see why)

Can you test this code and confirm you have the same behavior ?

[martonb]

I used the SqlSyncChangeTrackingProvider because it doesn't need the extra _tracking tables and change tracking is supposedly lower overhead on the SQL Server.
Will try your way tomorrow morning and let you know. Thanks!

[martonb]

Running with SqlSyncProvider causes errors in the application writing in the source database:
SqlException: Transaction (Process ID 97) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.
As our use case is to keep the 2 databases in sync while the source is changed by an other app, I think this option will not work in this case...

Memory usage was indeed stable, though!

But my original fix (runner.DisposeAsync()) is still needed, IMO, as that class does implement IDisposable, but Dispose it's not called on it.

[Mimetis]

SqlException: Transaction (Process ID 97) was deadlocked

Can you provide a small sample to see the deadlock ? (ideally starting from my sample)

But my original fix (runner.DisposeAsync()) is still needed

I need to check for runner.DisposeAsync() because basically runner.CommitAsync() does the almost the same thing (it is not disposing the connection itself)

Memory usage was indeed stable, though!

Does it means, we have a memory leak using SqlSyncChangeTrackingProvider and not SqlSyncProvider ?

[martonb]

Does it means, we have a memory leak using SqlSyncChangeTrackingProvider and not SqlSyncProvider ?

It definitely looks like it, as with SqlSyncProvider memory usage is stable, but with SqlSyncChangeTrackingProvider I run out of memory...

[Mimetis]

Ok, still need a sample to reproduce it, because it's not the case on my side

[martonb]

So, I tried the database provided by you, then tried again on mine: I cannot reproduce the OutOfMemory exception any more.
I went over my code multiple times, the only thing I could think of, is that I had DisableConstraintsOnApplyChanges on false and it generated so many exceptions, it crashed in the end? Or I had mixed provisioning? Not sure...
If I manage to get back to the same situation, I'll zip up my sources and attach it here.
Thank you for your assistance!

[martonb]

I just reproduced it again, on one of our tables, super strange...
At beginning, memory was stable, then at the later stages I noticed my mouse getting lazy, opened Task Manager and voila :O

[Mimetis]

That's a very good idea !
I think defining a threshold limit for errors can preserve a little bit your memory :)
I will think about it

[VagueGit]

I upvoted the comment by @martonb

If I may suggest, for the production version, as soon as the errors count hit a preset count N, just abort the whole sync, so the app doesn't crash with Out Of Memory?

We recently had an issue when a sync merge statement for a single user threw millions of errors causing the server cpu to max out. So to protect the server for all users, it would be a good idea to have sync abort after a preset number of errors.