0 added
0 removed
Original
2026-01-01
Modified
2026-03-10
1
<p>Что делать, если при попытке работы с Yandex cloud PostgreSQL вы постоянно получаете ошибку "Too many connections for role" или "The connection pool has been exhausted"? Именно с такой проблемой я и столкнулся однажды.</p>
1
<p>Что делать, если при попытке работы с Yandex cloud PostgreSQL вы постоянно получаете ошибку "Too many connections for role" или "The connection pool has been exhausted"? Именно с такой проблемой я и столкнулся однажды.</p>
2
<h2>Причина</h2>
2
<h2>Причина</h2>
3
<p><em>Терпеть не могу этот ваш ДевОпс. Так или иначе. Postgresql очень капризный или ленивый. После MS SQL сервера всё кажется излишним. Очень много вещей надо настраивать самому.</em></p>
3
<p><em>Терпеть не могу этот ваш ДевОпс. Так или иначе. Postgresql очень капризный или ленивый. После MS SQL сервера всё кажется излишним. Очень много вещей надо настраивать самому.</em></p>
4
<p>Приложения написанные на Python, PHP или в моем случае asp.net core, часто создают одно или несколько подключений при запросе. Но стандартное количество соединений в пуле postgresql по умолчанию = 100. Это довольно оптимальное число для мелких и средних приложений. А если вы арендуете сервер, как я, так это еще и выгодно. Но приложение ничего не знает про эту настройку, поэтому пытается создать больше 100. Чтобы обеспечить грамотный пулинг, нужно использовать пул-балансер, например, # PgBouncer.</p>
4
<p>Приложения написанные на Python, PHP или в моем случае asp.net core, часто создают одно или несколько подключений при запросе. Но стандартное количество соединений в пуле postgresql по умолчанию = 100. Это довольно оптимальное число для мелких и средних приложений. А если вы арендуете сервер, как я, так это еще и выгодно. Но приложение ничего не знает про эту настройку, поэтому пытается создать больше 100. Чтобы обеспечить грамотный пулинг, нужно использовать пул-балансер, например, # PgBouncer.</p>
5
<h2>Лечение</h2>
5
<h2>Лечение</h2>
6
<p>Нужно установить PbBouncer между приложением и кластером. Оказывается, в кластере Postgresql Яндекс-облака уже встроен балансер (PgBouncer).</p>
6
<p>Нужно установить PbBouncer между приложением и кластером. Оказывается, в кластере Postgresql Яндекс-облака уже встроен балансер (PgBouncer).</p>
7
<p>Но непонятно, как он работает и какой режим пулинга у него по умолчанию. Я думаю, что SESSION, который не очень подходит для подобных приложений. К сожалению, на момент возникновения проблемы у них не было интерфейса для конфигурации этого балансера. Но можно воспользоваться командой сторокой YC (CLI).</p>
7
<p>Но непонятно, как он работает и какой режим пулинга у него по умолчанию. Я думаю, что SESSION, который не очень подходит для подобных приложений. К сожалению, на момент возникновения проблемы у них не было интерфейса для конфигурации этого балансера. Но можно воспользоваться командой сторокой YC (CLI).</p>
8
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><h3>Установите pooling mode в TRANSACTION</h3>
8
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><h3>Установите pooling mode в TRANSACTION</h3>
9
<p>Более подробные настройки можно глянуть тут: https://cloud.yandex.ru/docs/managed-postgresql/api-ref/Cluster/.</p>
9
<p>Более подробные настройки можно глянуть тут: https://cloud.yandex.ru/docs/managed-postgresql/api-ref/Cluster/.</p>
10
<p>Вообще, эта настройка у Яндекс-кластера просто дурацкая. Если есть балансер соединений, то почему я не могу увеличить соединения к PgBouncer-у? Это же PgBouncer -- в этом его суть!</p>
10
<p>Вообще, эта настройка у Яндекс-кластера просто дурацкая. Если есть балансер соединений, то почему я не могу увеличить соединения к PgBouncer-у? Это же PgBouncer -- в этом его суть!</p>
11
<h3>Настройка Pooling Mode в TRANSACTION в Cloud.Yandex. Не помогло!</h3>
11
<h3>Настройка Pooling Mode в TRANSACTION в Cloud.Yandex. Не помогло!</h3>
12
<p>Тем не менее данная настройка не помогла. Как я уже писал, встроенный PgBouncer в Яндекс-кластере не работает как PgBouncer т. е. вы не можете увеличить соединения.</p>
12
<p>Тем не менее данная настройка не помогла. Как я уже писал, встроенный PgBouncer в Яндекс-кластере не работает как PgBouncer т. е. вы не можете увеличить соединения.</p>
13
<p>У меня часто вылетало сообщение "The connection pool has been exhausted". Очевидно, что где-то утечка соединений. Однако как найти? Для начала я попробовал посмотреть все ли соединения закрываются. В объект соединения был добавлен делегат.</p>
13
<p>У меня часто вылетало сообщение "The connection pool has been exhausted". Очевидно, что где-то утечка соединений. Однако как найти? Для начала я попробовал посмотреть все ли соединения закрываются. В объект соединения был добавлен делегат.</p>
14
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; }<p>У данного подхода есть минус. Не понятно ID соединения, которое было открыто и закрыто. В общем, данный подход скорее всего может помочь, но мне не очень понравился. Я не заметил соединение, которое вызывало утечку.</p>
14
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; }<p>У данного подхода есть минус. Не понятно ID соединения, которое было открыто и закрыто. В общем, данный подход скорее всего может помочь, но мне не очень понравился. Я не заметил соединение, которое вызывало утечку.</p>
15
<h3>Как залогировать PostgreSQL через Npgsql.EntityFrameworkCore.PostgreSQL?</h3>
15
<h3>Как залогировать PostgreSQL через Npgsql.EntityFrameworkCore.PostgreSQL?</h3>
16
<p>В конце концов я решил залогировать все, что происходит с соединением моего кластера PostgreSQL. Чтобы залогировать все события Npgsql.EntityFrameworkCore.PostgreSQL, можно добавить свой провайдер.</p>
16
<p>В конце концов я решил залогировать все, что происходит с соединением моего кластера PostgreSQL. Чтобы залогировать все события Npgsql.EntityFrameworkCore.PostgreSQL, можно добавить свой провайдер.</p>
17
<p><strong>Перед тем как вызвать AddDbContext, просто добавьте свой провайдер</strong>.</p>
17
<p><strong>Перед тем как вызвать AddDbContext, просто добавьте свой провайдер</strong>.</p>
18
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; }<h3>Реализация провайдера, CustomNpgSqlLoggingProvider.cs</h3>
18
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; }<h3>Реализация провайдера, CustomNpgSqlLoggingProvider.cs</h3>
19
<p><a>Ссылка</a>на документацию.</p>
19
<p><a>Ссылка</a>на документацию.</p>
20
/// <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); } } }<p>В общем, после долгого исследования я наконец нашел утечку. Это действительно было соединение, которое не закрывалось. Теперь я буду осторожнее писать запросы.</p>
20
/// <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); } } }<p>В общем, после долгого исследования я наконец нашел утечку. Это действительно было соединение, которое не закрывалось. Теперь я буду осторожнее писать запросы.</p>
21
21