Пул соединений с базой данных в ADO.NET / OleDb - как избежать проблем с утечкой соединений в ASP.NET (перевод)

Открытие соединения с базой данных требует значительного количества времени и ресурсов. Пул соединений повышает производительность веб-приложений, повторно используя активные соединения с базой данных вместо создания нового соединения для каждого запроса. Менеджер пула соединений управляет открытыми соединениями. Когда приходит запрос на новое соединение, менеджер ищет неиспользуемые соединения в пуле и возвращает одно из них, если таковое имеется в наличии. Если все соединения в пуле заняты и максимальный размер пула не достигнут, создается новое соединение и добавляет в пул. После достижения максимального размера пула все новые запросы ставятся в очередь и ожидают освобождения одного из соединений в пуле или истечения времени ожидания.

Работа пула соединения регулируется параметрами в строке соединения. Следующие четыре параметра контролируют большую часть поведения пула соединений:

- Connect Timeout - время ожидания в секундах при запросе нового соединения, в случае превышения будет выброшено исключения.

- Max Pool Size - определяет максимальный размер пула соединений. По умолчанию 100. Большинство веб-сайтов используют не более 40 одновременных соединений под самой тяжелой нагрузкой, но это зависит от того, сколько времени занимает выполененние ваших запросов к базе

- Min Pool Size - начальное количестов соединений, который будут добавлены в пул после его создания. По умолчанию ноль, но вы можете заменить его небольшим числом, если вашему приложению требуется постоянно время ответа даже после многочасового простоя. В таком случае пользователю не придется ждать открытия этих соединений.

- Pooling - включает и выключает использование пула. Как вы наверно догадываетесь по умолчанию true. Ниже я опишу те ситуации, в которых вы можете использовать Pooling=false.

Распространенные проблемы и их решения

Проблемы с пулом соединений почти всегда вызваны "утечкой соединений" - ситуацией, когда ваше приложение не закрывает свои соединения с базой данных. "Утекшие" соединения остаются открытыми, пока сборщик мусора не закроет их за вас, вызвав метод Dispose. В отличие от старого ADO, ADO.NET требует от вас вручную закрывать соединения сразу после того, как вы закончите с ними работать. Если вы рассчитываете на выход объектов соединений за пределы видимости, подумайте еще раз. Могут пройти часы до того, как сборщик мусора займется ими. В это время ваше приложение может уже зависнуть встречая пользователей чем-то вроде:

Exception: System.InvalidOperationException Message: 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. Source: System.Data at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() ... Exception: System.InvalidOperationException Message: 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. Source: System.Data at System.Data. SqlClient.SqlConnectionPoolManager.GetPooledConnection ( SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open()

Закрывайте свои соединения

Если вы собрались закрыть свое соединение, убедитесь, что вы действительно его закрыли. Нижеприведенный код на первый взгляд выглядит нормально, но на самом деле может вызывать утечку соединений:

SqlConnection conn = new SqlConnection(myConnectionString);
conn.Open();
doSomething();
conn.Close();

Если doSomething() выбросил исключение, соединение никогда не будет правильно закрыто. Это можно исправить вот так:

SqlConnection conn = new SqlConnection(myConnectionString);
try
{
	conn.Open();
	doSomething(conn);
}
finally
{
	conn.Close();
}

или

using (SqlConnection conn = new SqlConnection(myConnectionString))
{
	conn.Open();
	doSomething(conn);
}

Вы заметили, что в первом случае мы явно вызвали conn.Close(), а во втором мы заставили компиллятор сгенерировать неявный вызов conn.Dispose() сразу после завершения блока using? Блок using в C# гарантирует, что на его обьекте будет вызван метод Dispose сразу после завершения этого блока. Для обьекта Connection методы Close и Dispose равнозначны. Ни один из них не имеет каких-то особых преимуществ.

Создавая обьект соединения методом класса убедитесь, что вы сохранили его локально и вызвали метод Close. Следующий код приведет к утечке соединений:

OleDbCommand cmd new OleDbCommand(myUpdateQuery, getConnection());
intres = cmd.ExecuteNonQuery();

getConnection().Close(); //  Полученное при первом вызове метода getConnection() соединение не было закрыто, сейчас мы создаем новое соединение и пытаемся закрыть уже его

Если вы используете SqlDataReader, OleDbDataReader и т.п., закройте их. Даже если закрытие соединения должно закрывать и их, приложите дополнительные усилия для закрытия ваших объектов чтения данных после использования.

Последнее по порядку, но не по важности, никогда не вызывайте Close или Dispose вашего соединения в деструкторе класс или вашем методе Finalize. Это не только бессмысленно в плане закрытия соединений, но и может вызывать ошибки из-за вмешательства в работу сборщика мусора. Больше информации здесь.
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconprogrammingessentialsforgarbagecollection.asp.

Тестируем ваши изменения

Единственный способ узнать эффект ваших изменений в работе с пулом соединений - это провести нагрузочное тестирование вашего приложения. Если у вас уже есть юнит-тесты - используйте их. Циклическое воспроизведение юнит-тестов создаст хорошую нагрузку на ваше приложение. Если у вас их нет, использует инструмент для нагрузочного тестирования веб-приложений. Есть масса коммерческих инструментов для нагрузочного тестирования. Если вы предпочитаете бесплатный софт, можете попробовать OpenSTA. После установки вы сможете прокликать в нем ваше приложение и сохранить это как сценарий нагрузочного теста.

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

Отслеживание поведения пула соединений

Обычно вам просто надо знать, что приложение не выходит за рамки пула соединений. Если количество соединений постепенно растет после начального периода "разогрева" при неизменной нагрузке, то скорее всего вы столкнулись с утечкой соединений. Простейши способ отслеживать количество соединений с базой данных - это использование Performance Monitor в Administrative tools на большинстве версий Windows. Если вы используете MS SQl Server, добавьте счетчик SQL Server General Statistics -> User Connections (он доступен на компьютере с SQL Server, так что возможно вам понадобится добавить ее имя или IP-адрес в поле Select Counters From Computer). Кроме этого можно использовать запросы к вашей базе. Для SQL Server выполнете:

EXEC SP_WHO

Для Oracle

SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL

Счетчики производительности .NET CLR Data performance counters

В документации вы могли прочитать про счетчики производительности .Net CLR. Они великолепны, ечли вы знаете пределы их возможностей. Помните, что они не всегда правильно сбрасываются. Слдующая статься проливает немного света на эту проблему, но по моему мнению не покрывает все возможные случаи. http://support.microsoft.com/default.aspx?scid=kb;en-us;314429. Так же стоит помнить о том, чьл IIS выгружаем домены приложений под нагрузкой, так что не удивляйтесь, если ваше количество соединений станет нулевым, даже если минимальный размер пула 5!

Краткосрочные исправления

Что, если вы обнаружили утечку соединений на рабочей версии не можете остановить ее до выяснения подробностей? Отключите пул. Пусть производительность приложения сильно упадет, но оно будет работать! Потребление памяти тоже вырастет. Но что, если приложение падает не так уж и часто и вы не хотите жертвовать производительностью? Попробуйте следующее:

conn = new SqlConnection();
try
{
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER
DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;"; // Обратите внимание, что таймаут всего две секунды!
conn.Open();
}
catch(Exception)
{
if (conn.State != ConnectionState.Closed) conn.Close();
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE = YOUR_DB_NAME;Pooling=false;Connect Timeout=45;";
conn.Open();
}

Если у меня не получается открыть соединение из пула за две секунды я пытаюсь открыть соединение не из пула. Вы получите двухсекундную задержку при отсутствии доступных соединений в пуле, но если ваша утечка проявляется очень редко, это неплохой способ спустить пар.

Tuning Up ADO.NET Connection Pooling in ASP.NET Applications


Комментарии

Пул соединений с базой данных в ADO.NET / OleDb - как избежать проблем с утечкой соединений в ASP.NET (перевод) — Комментарии (4)

  1. Спасибо! То что было нужно! Хороший блог! Почему уже не пишешь? И где форма подписки на обновления в блоге?!

  2. Стас:
    Спасибо! То что было нужно! Хороший блог! Почему уже не пишешь? И где форма подписки на обновления в блоге?!

    Спасибо за отзыв - разные бывают в жизни обстоятельства в том числе со здоровьем связанные. Стандартный RSS-поток всегда здесь, хотя заметную кнопку надо будет прикрутить - у меня давно спецкнопка для RSS-потоков новостей вынесена на главную панель инструментов Firefox, даже странно подумать что может быть иначе.

    А еще есть мои перевода на Хабрахабре и Geektimes

  3. Здравствуйте, есть очень интересные статьи, но не могли бы вы подсказать статьи про разработку сетевых приложений?
    socet - ы, http запросы и т.п Очень хочется , а информации об этом мало

Добавить комментарий для Стас Отменить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *


*

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>