Akka .NET Connection Pool Timeout Issues

Question

We are creating a new system using Akka.NET and have a basic cluster setup with sharding and persistence.

We've used the official documentation as well as some Petabridge blog posts to get sharding working correctly. However, we've hit a problem where the shards are exceeding the maximum number of connections allowed by the SQL Server connection pool. As such, we're getting the following message...

2017-04-20 14:04:31.433 +01:00 [Warning] "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

We believe this is happening when the shards are updating the sharding journal.

How come the sharding module doesn't manage its SQL connections properly? Is there a configuration issue here?

Is it possible to get it to retry when this kind of error occurs? As it stands we lose messages for each instance of this error.

Here's the relevant HOCON

cluster.sharding {
    journal-plugin-id = "akka.persistence.journal.sharding"
    snapshot-plugin-id = "akka.persistence.snapshot-store.sharding"
}
persistence {
    journal {
        plugin = "akka.persistence.journal.sql-server"
        sql-server {
            class = "Akka.Persistence.SqlServer.Journal.SqlServerJournal, Akka.Persistence.SqlServer"
            connection-string = "Server=.;Database=akkasystem;Integrated Security=true"
            schema-name = dbo
            auto-initialize = on
        }
        # a separate config used by cluster sharding only 
        sharding {
            connection-string = "Server=.;Database=akkasystem;Integrated Security=true"
            auto-initialize = on
            plugin-dispatcher = "akka.actor.default-dispatcher"
            class = "Akka.Persistence.SqlServer.Journal.SqlServerJournal, Akka.Persistence.SqlServer"
            connection-timeout = 30s
            schema-name = dbo
            table-name = ShardingJournal
            timestamp-provider = "Akka.Persistence.Sql.Common.Journal.DefaultTimestampProvider, Akka.Persistence.Sql.Common"
            metadata-table-name = ShardingMetadata
        }
    }
    snapshot-store {
        sharding {
            class = "Akka.Persistence.SqlServer.Snapshot.SqlServerSnapshotStore, Akka.Persistence.SqlServer"
            plugin-dispatcher = "akka.actor.default-dispatcher"
            connection-string = "Server=.;Database=akkasystem;Integrated Security=true"
            connection-timeout = 30s
            schema-name = dbo
            table-name = ShardingSnapshotStore
            auto-initialize = on
        }
    }
}

Answer 1

It may be a sign that SQL journal is flooded with incoming events so heavily, that connection timeout triggers, while an event is awaiting for the next connection from the pool to be freed up.

From your config I suspect, that this might have happen if you've started persisting an events and creating shards/entities with high ratio. Existing SQL journals are going to get significant speed boost in the near future (see batching journals). Hopefully this could help to solve your problems.