Ошибка "Too many connections for role" | OTUS
⚡ Подписка на курсы OTUS!
Интенсивная прокачка навыков для IT-специалистов!
Подробнее

Курсы

Программирование
iOS Developer. Professional Kotlin Backend Developer Flutter Mobile Developer Symfony Framework C++ Developer. Basic Unity Game Developer. Basic Java Developer. Professional
-35%
Highload Architect Unity Game Developer. Professional React.js Developer Специализация Java-разработчик
-25%
Алгоритмы и структуры данных
-16%
Scala-разработчик C# Developer. Professional
-23%
Разработчик голосовых ассистентов и чат-ботов Team Lead Архитектура и шаблоны проектирования NoSQL Web-разработчик на Python Golang Developer. Professional PostgreSQL Vue.js разработчик Супер-практикум по использованию и настройке GIT Разработчик IoT Подготовка к сертификации Oracle Java Programmer (OCAJP) Программист С HTML/CSS
Инфраструктура
Инфраструктурная платформа на основе Kubernetes Microservice Architecture Базы данных Highload Architect Reverse-Engineering. Professional
-8%
Network engineer. Basic Administrator Linux.Basic MongoDB Infrastructure as a code MS SQL Server Developer Cloud Solution Architecture Мониторинг и логирование: Zabbix, Prometheus, ELK Супер-практикум по использованию и настройке GIT Разработчик IoT Экcпресс-курс «ELK» Супер-интенсив "Tarantool" Экспресс-курс «CI/CD или Непрерывная поставка с Docker и Kubernetes» Экспресс-курс «Введение в непрерывную поставку на базе Docker»
Корпоративные курсы
Безопасность веб-приложений Экосистема Hadoop, Spark, Hive Пентест. Практика тестирования на проникновение Node.js Developer Java QA Engineer. Basic
-18%
Reverse-Engineering. Professional
-8%
DevOps практики и инструменты NoSQL Reverse-Engineering. Basic Cloud Solution Architecture Внедрение и работа в DevSecOps Супер-практикум по работе с протоколом BGP Game QA Engineer Супер - интенсив по Kubernetes Дизайн сетей ЦОД Экспресс-курс «IaC Ansible» Экспресс-курс по управлению миграциями (DBVC) Экспресс-курс "Версионирование и командная работа с помощью Git" Основы Windows Server
Специализации Курсы в разработке Подготовительные курсы Подписка
+7 499 938-92-02

Ошибка "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);
        }
    }
}

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

Не пропустите новые полезные статьи!

Спасибо за подписку!

Мы отправили вам письмо для подтверждения вашего email.
С уважением, OTUS!

Автор
0 комментариев
Для комментирования необходимо авторизоваться