Persistence: Slow reading of big snapshots

[TietoOliverKurowski]

We are seeing very slow performance when reading big snapshots on SQL Server.

Snapshots of some of your actors are in the range of over 100MB. Reading that snapshot takes around 15 minutes and causes 30% CPU usage.
I traced the issue down to the way the data reader is created:

using (var reader = await command.ExecuteReaderAsync(cancellationToken))

when I use the option CommandBehavior.SequentialAccess reading only takes around 2 seconds and no CPU usage.

Would it be possible to add that parameter?

• Which Akka.Net version you are using: 1.3.6
• Akka.Persistence.SqlServer: 1.3.2
• On which platform you are using Akka.Net: .Net 4.7.1, targeting 4.5.2

In parallel I'm working in getting the snapshot smaller.

[Aaronontheweb]

Thanks for reporting this! And yep, we can send in a PR to https://github.com/akkadotnet/Akka.Persistence.SqlServer to get that fixed.

[Aaronontheweb]

Although actually, that might be an issue with the Persistence.Sql.Common middleware in this project

[Horusiath]

I can do it, but it may take me some time (I'm still working on other stuff). I think, it's something that could be even picked up as first contribution. Essentially most of the changes could be closed inside Akka.Persistence.Sql.Common project.

IMO sequential access should be a configurable setting (example path: akka.persistence.snapshot-store.<sql-plugin>.use-sequential-access = true|false). If not provided, it should default to false to keep existing behavior intact.

AbstractQueryExecutor.SelectSnapshotAsync is the place for applying command behavior. Probably an additional parameter will be necessary.

Also this change most probably will hit all existing Sql providers in terms of backward compatibility.

[Aaronontheweb]

@TietoOliverKurowski, would you be interested in contributing this change? Otherwise one of us on the @akkadotnet/core team can pick it up.

[TietoOliverKurowski]

Yes, I want to give it a try. By end of next Friday I should have something working.