Yandex Cloud (too many connections for role)

При папытке работы с Yandex cloud postgresql. Постоянно получал ошибку » too many connections for role» или «The connection pool has been exhausted».

Причина

Терпеть немогу этот ваш ДевОпс. Так или иначе. Postgresql очень капризный или ленивый. После MS SQL сервера всё кажется излишним. Очень много вещей надо настраивать самому.

Приложения написанные на Python, PHP или в моем случае asp.net core. Часто создают одно или несколько подключений при запросе. Но стандартное количество соединений в пуле postgresql по умолчанию = 100. Это давольно оптимальное число для мелких и стредних приложений. А если вы арендуете сервер как я так это еще и выгодно. Но приложение ничего не знает про эту настройку, поэтому пытается создать больше 100. Чтобы обеспечить грамотный пулинг нужно испозоваться пул балансер, например, PgBouncer.

Лечение

Нужно установить PbBouncer между приложением и кластером. Оказывается в кластере Postgresql яндекс облака. Уже встроен балансер (PgBouncer).

Но не понятно как он работает и какой режим пулинга у него по умолчанию. Я думаю что SESSION, который не очень подходит для подобных приложений. К сожалению сейчас у них нет интерфейса для конфигурации этого балансера. Но можно воспользоваться команной сторокой YC (CLI).

yc managed-postgresql cluster get <CLUSTER NAME> --full

$ yc managed-postgresql cluster update --help

$ yc managed-postgresql cluster update <имя кластера> --connection-pooling-mode <SESSION|TRANSACTION|STATEMENT>

Установите pooling mode в TRANSACTION

Более подромные настройки можно глянуть тут https://cloud.yandex.ru/docs/managed-postgresql/api-ref/Cluster/

Вообще эта настройка у яндекс кластера просто дурацкая. Если есть балансер соединений, то почему я не могу увеличить соединения к PgBouncer-у? Это же блин PgBouncer в этом его суть!

Настройка Pooling Mode в TRANSACTION в Cloud.Yandex не помогло!

Тем не менее данная настройка не помогла. Как я уже писал встроенный PgBouncer в яндекс кластере не работает как PgBouncer т.е. вы не можете увелисить соединения.

У меня часто вылетало сообщение » The connection pool has been exhausted». Очевидно, что где-то утечкая соединений. Однако как найти. Для начала я попробовал посмотреть все ли соединнеия закрываються. В объекту соединения был добавлен дилегат.

    private static NpgsqlConnection CreateConnection(string connectionString, ILogService logService)
    {
        var connection = new NpgsqlConnection(connectionString);

        connection.StateChange += (sender, args) =>
        {
            logService.Info($"ConnectionState: {args.OriginalState}. 
                Current time: {DateTime.Now.ToShortTimeString()}");
        };

        return connection;
    }

У данного подхода есть минус. Не понятно ID соединения, которое было открыто и закрыто. В общем данный подход скорее всего может помочь, но мне не очень понравился. Я не заметил соединение, которое вызывало утечку.

Как залогировать PostgreSQL через Npgsql.EntityFrameworkCore.PostgreSQL?

В конце концо я решил залогировать все что происходи с соединением моего кластера PostgreSQL. Чтобы залогировать все события Npgsql.EntityFrameworkCore.PostgreSQL можно добавить свой провайдер.

Перед тем как вызвать AddDbContext просто добавьте свой провайдер.

Npgsql.Logging.NpgsqlLogManager.Provider = new CustomNpgSqlLoggingProvider(logService);

        public static IServiceCollection AddCustomContext
            (this IServiceCollection services, IConfiguration configuration, bool useCluster = true)
        {
            var provider = services.BuildServiceProvider();
            var logService = provider.GetService<ILogService>();

            Npgsql.Logging.NpgsqlLogManager.Provider = new CustomNpgSqlLoggingProvider(logService); // На провайдер

                var connectionString = configuration.GetConnectionString("DbCustomCluster");
                logService.Info($"Use DbCustomCluster connection: ${connectionString.Substring(0, 19)}");

                services.AddDbContext<DbCustomContext>(options =>
                    options.UseNpgsql(CreateConnection(connectionString, logService), builder =>
                    {
                        builder.EnableRetryOnFailure(3, TimeSpan.FromSeconds(10), new[] { "EnableRetryOnFailure" });
                        builder.RemoteCertificateValidationCallback(ValidateServerCertificate);
                        builder.ProvideClientCertificatesCallback(ProvideClientCertificates);
                    }));

            return services;
        }

Реализация провайдер, CustomNpgSqlLoggingProvider.cs

Ссылка на документацию

/// <summary>
/// Source documentation https://www.npgsql.org/doc/logging.html
/// </summary>
public class CustomNpgSqlLoggingProvider : INpgsqlLoggingProvider
{
    private readonly ILogService _logService;

    public NpgSqlLoggingProvider(ILogService logService)
    {
        this._logService = logService;
    }

    public NpgsqlLogger CreateLogger(string name)
    {
        return new NpgsqlCustomLogger(_logService);
    }
}

internal class NpgsqlCustomLogger : NpgsqlLogger
{
    public ILogService LogService { get; }

    public NpgsqlCustomLogger(ILogService logService)
    {
        this.LogService = logService;
    }

    public override bool IsEnabled(NpgsqlLogLevel level)
    {
        // Тут можно указать какой уровень логов показывать. У меня для простоты все логи.
        return true; // all levels
    }

    public override void Log(NpgsqlLogLevel level, int connectorId, string msg, Exception exception = null)
    {
        // Я логирую только открытие, сам запрос и закрытие. Выглядит это так
        // Level: Debug, connectorId: 1237043804, msg: Connection opened
        // Level: Debug, connectorId: 1237043804, msg: Executing statement(s):\n UPDATE "Item..
        // Level: Debug, connectorId: 1237043804, msg: Connection closed

        if (msg.Contains("Connection opened") || msg.StartsWith("Executing statement") || msg.Contains("Connection closed"))
        {
            this.LogService
                .Debug($"POSTGRESQL Level: {level}, connectorId: {connectorId}, msg: {msg.Substring(0, Math.Min(msg.Length, 250))}", exception);
        }
    }
}

В общем после долгого иследования я наконец нашел утечку. Это дейтсвительно было соединение, которое не закрывалось. Теперь я буду осторожнее писать запросы.