[C#] ADO.NET의 Connection Pooling

ADO.NET의 Connection Pooling 

클라인언트가 데이타베이스 서버에 작업 명령을 보내기 전에 반드시 DB 서버 연결을 먼저 해야하는데, 이 Connection 작업은 클라이언트/서버간 핸드쉐이크를 비롯해 서버 로그인까지 여러 과정을 거치는 것으로 상대적으로 긴 시간을 소요한다. 따라서, DB 처리가 많은 웹 프로그램이나 기업 S/W에서는 한번 사용한 Connection을 끊지 않고 Connection Pool이라는 곳에 저장해 두었다가 다음 DB연결 요청에 재활용하는 방식을 사용한다. ADO.NET(ActiveX Data Objects, ADO)은 디폴트로 Connection Pooling을 ON 하고 있으며, Connection Pool은 디폴트로 최소 1개, 최대 100개의 Connection을 가질 수 있다. 

---

SQL Connection Pooling 생성 과정 

ADO.NET의 SqlConnection은 기본적으로 Connection Pooling 기능을 ON하고 있으므로, 다음과 같은 방식으로 Connection 생성 관리하게 된다. 클라이언트 프로그램이 시작되고 처음 SqlConnection 객체를 생성하고 Open하게 되면, SQL 서버에 연결 및 로그인을 하게 되는데, 이때 SQL 서버상에는 커넥션과 관련된 리소스를 할당하고 Connection관련 Context를 생성하게 된다 (이는 SQL 서버상에서 하나의 SPID로 표현된다). 만약 10개의 별도의 Connection을 맺게 된다면, SQL 서버에는 10개의 SPID가 생성된다. 클라이언트에서 이렇게 생성된 SQL Conneciton을 모두 사용하고 Close 하게 되면, 만약 Connection Pooling이 없다면, SQL 서버상의 SPID는 사라지게 될 것이다. 하지만, 실제 이 Connection은 Conneciton Pooling로 들어가게 되고 차후 DB 연결 요청을 기다리게 된다. 디폴트로 최소 Pool Size가 1이지만, 만약 이를 20으로 변경한다면, SQL 서버 상에는 20개의 SPID가 생성될 것이며, 이는 20개 Conneciton이 이미 만들어 졌다는 것을 의미한다. 사용자가 많아짐에 따라, DB Conneciton이 계속 증가하게 되는데, 디폴트로 최대 Connection Pool Size는 100으로 설정되어 있다. 이는 동시 사용자가 100명 이상을 넘는다면, 나머지는 연결을 못한다는 것을 의미한다(물론 옵션에서 설정가능). 예를 들어, 101번째 사용자가 DB 연결을 요청하면, 해당 사용자는 Queue에서 기존 사용자가 일을 끝내고 Conneciton을 리턴해 주기를 기다린다. 만약 디폴트가 15초인 Connection Timeout안에 여분의 Conneciton을 찾지 못하면, Exception을 throw하게 된다. 그리고, ADO.NET은 자동으로 시스템 피크 타임에 많이 만들어 졌던 Conneciton들은 계속 사용을 안할 경우 특정 시간이 지나면 자동으로 줄여서 SQL 서버의 부하를 줄여준다. 한가지 실무에서 자주 접하는 것은 만약 SQL Server가 클러스터 Failover 혹은 서버 재시작으로 클라이언트와의 모든 Connection을 잃게 된다면, 클라이언트가 Conneciton Pooling의 Conneciton을 가져와서 SQL문을 실행하는 싯점에 에러를 알게된다는 것이다. 로드밸런싱을 하는 SQL 서버라 하더라도 일단 끊어진 Conneciton을 다른 서버에서 연결된 상태로 만들 수 없다. 아래 예제는 최소 20개의 Pool Size를 Connection String에 지정한 후, Pool내의 Conneciton 객체를 성능 카운터로 살펴본 예이다. 그리고, 그 당시 SQL Server상에는 20개의 SPID가 생성되었음을 그림으로 보여준다. SqlConnection.ClearAllPools() 메서드는 Pool에 있는 Connection들을 지우는 기능을 하는데, 따라서 아래 2번째 출력은 모든 Conneciton이 Close되어 0이 된다. 

예제

public void TestConnectionPool()
{
    // 최소 20 Connection 지정
    string strConn = "Data Source=(local);Initial Catalog=pubs;User id=test;Password=1;Min Pool Size=20;";    

    using (SqlConnection conn = new SqlConnection(strConn))
    {
        conn.Open();
        var cmd = new SqlCommand("select * from authors", conn);
        SqlDataReader rdr = cmd.ExecuteReader();
        rdr.Close();
    }

    // ClearAllPools 호출 이전
    // 결과 : 20 커넥션
    ShowPerfCounter();
    Console.WriteLine("Press Enter.");
    Console.ReadLine();

    // ClearAllPools 호출 이후
    // 결과 : 0 커넥션
    SqlConnection.ClearAllPools();
    Console.WriteLine("After ClearAllPools");
    ShowPerfCounter();
}

private void ShowPerfCounter()
{
    string processName = Assembly.GetExecutingAssembly().GetName().Name;
    int pid = Process.GetProcessesByName(processName)[0].Id;
    string instanceName = string.Format("{0}[{1}]", processName, pid);

    // .NET Data Provider for SqlServer 카테고리 안의
    // NumberOfPooledConnections 카운터 측정
    var counter1 = new PerformanceCounter(".NET Data Provider for SqlServer", "NumberOfPooledConnections", instanceName);            
    var v1 = counter1.NextValue();            
    Console.WriteLine(v1);                                
}

---

SQL 서버 SPID (Server Process ID)

Connection Pool 생성 기준 

Connection Pooling은 기본적으로 Connection String에 따라 다르게 생성된다. 좀 더 구체적으로, 프로세스별, 한 프로세스내의 AppDomain별, Conneciton String별로 다른 Pool로 생성된다. 즉, WebService1과 WebService2가 동일한 SQL 서버/DB를 접속할 때, 둘은 다른 Pool을 갖는다. 또한 만약 Connection String이 완전히 똑같은 경우에만 같은 Connection Pool을 갖는다. 예를 들어, Data Source=(local)을 쓰는 것과 Data Source=MyLocalServer를 쓰는 것은 비록 의미상 같다 하더라도 다른 Pool을 갖는다. 또한, 같은 옵션인데 순서만 서로 바꾸는 경우도 다른 Pool로 인식한다. 그리고 마지막으로 만약 Connection String이 윈도우즈 인증(Integrated Security=SSPI)를 사용하는 경우, 해당 로그인 유저별로 다른 Pool을 갖는다. 따라서 만약 웹 프로그램을 사용할 경우, Integrated Security보다 SQL 로그인을 사용하는 것이 Connection Pool내의 Conneciton을 재활용한다는 측면에서 성능이 나을 수 있다. 

---

SQL Connection Pooling 옵션 

Connection Pooling을 끄고 끼는 옵션, 최대 최소 풀 사이즈를 정하는 것, Timeout을 정하는 것등 Conneciton Pool을 컨트롤하는 모든 옵션은 Conneciton String을 통해 하게 된다. Connection Pooling 관련된 옵션은 여러 가지(MSDN 참조)이지만, 가장 대표적인 옵션들로 Pooling, Min Pool Size, Max Pool Size, Connection Timeout, Enlist 등을 들 수 있다. 아래 예제는 Pooling을 켜고 (디폴트), 최소 풀 사이즈를 20으로 하고, 최대 풀 사이즈 100, 그리고 Conneciton 타임아웃을 디폴트인 15로 셋팅하고 있다. 실제 Min Pool Size(디폴트 1)를 제외하고 모두 디폴트 값이므로 생략할 수 있다. 

출처 : http://www.csharpstudy.com/Data/SQL-connection-pooling.aspx