Simple Topics

JDBC 살펴보기 (JDBC communication internals with postgresql)

smileostrich 2023. 5. 14. 02:43

 

우리는 DB 를 일상적으로 사용하고 있지만, 내부적으로 어떤 일이 발생하고 있는지 놓치고 있는 경우가 종종 있습니다.

이 포스팅을 통해 jdbc 와 DB 사이에 어떤 일이 일어나고 있는지, 그리고 선택 가능한 옵션이 어떤게 있는지, 발생하는 상황, 이유 등에 대해 알아봅시다 :)

 

체크포인트

  • simple protocol 과 extended protocol 의 차이점
  • 왜 extended protocol 이 더 효율적이고 jdbc 드라이버에서 디폴트로 채택되었는가?
  • 프로토콜을 이해하고 구현하는법

Extended Query Protocol

postgreSql JDBC 드라이버에서 사용하는 디폴트 프로토콜은 Extended Query Protocol(이하 EQP) 입니다.

EQP 는 Frontend/Backend Protocol 의 일부입니다.

> PostgreSQL uses a message-based protocol for communication between frontends and backends (clients and servers). The protocol is supported over TCP/IP and also over Unix-domain sockets. Port number 5432 has been registered with IANA as the customary TCP port number for servers supporting this protocol, but in practice any non-privileged port number can be used.

 

우선, EQP 는 아래 세 가지 메시지를 사용합니다.

  • parse
  • bind
  • execute

 

프로토콜의 선택은 postgres jdbc 연결 속성 PreferQueryMode 설정에 따라 달라지며 디폴트는 extended 입니다.

(따라서 jdbc 는 일반적으로 EQP 를 사용합니다.)

PreferQueryMode 는 Enum 에 지정된 대로 여러 가지 옵션이 있습니다:

  • SIMPLE : simple query protocol 모드를 사용합니다.
  • EXTENDED : extended query protocol 모드를 사용합니다. (디폴트)
  • EXTENDED_FOR_PREPARED : simple query protocol 모드를 사용하지만 prepared statements 에 대해 extended query protocol 모드를 사용합니다.
  • EXTENDED_CACHE_EVERYTHING : 마찬가지로 extended query protocol 모드를 사용합니다.

 

이러한 옵션이 있는 이유는 postrgresql 이 sql 메시지를 실행하는 아래 두 가지 프로토콜 중 한가지를 선택할 수 있기 때문입니다.

  • simple query protocol(SQP)
  • extended query protocol(EQP)
- 동일한 작업을 수행하는 두 가지 프로토콜이 존재하는 이유

Frontend/Backend Protocol (Version 3) 이전에는 extended 프로토콜이 존재하지 않았기 때문
(Frontend/Backend Protocol 은 postgresql 7.4 에서 도입)

Simple Query Protocol

상당 수의 postgres application 이나 유틸리티들은 simple query protocol (이하 SQP) 을 사용하고 있습니다.

(대표적으로 아래 세 가지 이유 때문)

  1. SQP 를 사용했던 가장 큰 이유는 클라이언트가 버전 7.4 이전부터 SQP 를 사용했기 때문.
    (대표적으로 psql 도 SQP 를 사용)
  2. 데이터베이스 클라이언트 메시지에 변수가 없는 경우
    (이 경우, 당연히 EQP 를 사용할 이유가 없어짐. 그래서 simple query protocol 이기도 하고..)
  3. 특정 메시지에 대해 여러 개의 메시지를 세미콜론으로 구분하여 전송할 수 있기 때문 (이게 바로 우리가 잘 알고 있는 statement batching)

PostgreSql Execution Steps

EQP 가 3가지 메시지를 사용하는 이유는 postrgres 가 statements 실행을 수행하는 방식과 관련이 있습니다.

postgres 의 모든 sql 문은 statement 를 실행하기 위해 아래 4 단계를 수행합니다.

  1. Parse : sql 텍스트 구문 분석. 텍스트를 parse tree 로 변환
  2. Rewrite :
    1. Parse : parse tree 의 semantic parse 하는 과정 (테이블 존재 여부를 확인하거나, 사용자 엑세스 등을 확인함)
    2. Rewrite : view 와 같은 parse tree 를 rewrite 하는 과정 (view 를 view 의 기반이 되는 테이블로 변환)
  3. Plan : planner 는 잠재적으로 rewrite 된 parse tree 를 가져와서 다양한 엑세스 방법의 비용(가정), 엑세스 순서, plan node 의 계산된 result rows 를 기반으로 execution tree 를 만듬 (plan node 의 tree. 우리가 흔히 알고 있는 execution plan)
  4. Execute : executor 가 execution tree 를 가져와 실행함

자 이제 코드와 같이 살펴봅시다.

SQP 는 Q 유형의 단일 메시지나 simple query 를 페이로드와 함께 전송합니다. (이러한 데이터베이스 모든 단계는 서버 측 SQP 에 의해 실행됨)

case 'Q':			/* simple query */
    {
        const char *query_string;

        /* Set statement_timestamp() */
        SetCurrentStatementStartTimestamp();

        query_string = pq_getmsgstring(&input_message);
        pq_getmsgend(&input_message);

        if (am_walsender)
        {
            if (!exec_replication_command(query_string))
                exec_simple_query(query_string);
        }
        else
            exec_simple_query(query_string);

        valgrind_report_error_query(query_string);

        send_ready_for_query = true;
    }
    break;

반면, EQP 는 statement 를 실행하기 위해 최소 3개의 메시지, 즉 P(Parse), B(Bind), E(Execute)를 보내야 합니다.

case 'P':			/* parse */
    {
        const char *stmt_name;
        const char *query_string;
        int			numParams;
        Oid		   *paramTypes = NULL;

        forbidden_in_wal_sender(firstchar);

        /* Set statement_timestamp() */
        SetCurrentStatementStartTimestamp();

        stmt_name = pq_getmsgstring(&input_message);
        query_string = pq_getmsgstring(&input_message);
        numParams = pq_getmsgint(&input_message, 2);
        if (numParams > 0)
        {
            paramTypes = palloc_array(Oid, numParams);
            for (int i = 0; i < numParams; i++)
                paramTypes[i] = pq_getmsgint(&input_message, 4);
        }
        pq_getmsgend(&input_message);

        exec_parse_message(query_string, stmt_name,
                           paramTypes, numParams);

        valgrind_report_error_query(query_string);
    }
    break;

case 'B':			/* bind */
    forbidden_in_wal_sender(firstchar);

    /* Set statement_timestamp() */
    SetCurrentStatementStartTimestamp();

    /*
     * this message is complex enough that it seems best to put
     * the field extraction out-of-line
     */
    exec_bind_message(&input_message);

    /* exec_bind_message does valgrind_report_error_query */
    break;

case 'E':			/* execute */
    {
        const char *portal_name;
        int			max_rows;

        forbidden_in_wal_sender(firstchar);

        /* Set statement_timestamp() */
        SetCurrentStatementStartTimestamp();

        portal_name = pq_getmsgstring(&input_message);
        max_rows = pq_getmsgint(&input_message, 4);
        pq_getmsgend(&input_message);

        exec_execute_message(portal_name, max_rows);

        /* exec_execute_message does valgrind_report_error_query */
    }
    break;

 

혹시 메시지 이름이 postgresql 실행 단계와 부분적으로 겹친다는 것을 눈치채셨나요?

아래 표를 통해 정리해봅시다 :)

SQP message query      
EQP message parse   bind execute
postgres execution steps parse rewrite plan execute

 

와!!! 이제 여러분은 postgres jdbc 구현 프로토콜과 이 프로토콜이 postgresql db execution 에 어떻게 매핑되는지 이해하셨습니다!

 

이제 대략적인 개념은 이해했으니, 간단한 예제 코드를 통해 EQP 에 대해 더 깊이 살펴봅시다! 흐흐

(혹시 실습을 같이 진행해보고 싶으신분들을 위해 여기에 pgJdbc 링크를 남겨두겠습니다)

 

테스트 환경

  • temurin-17
  • postgresql 15
  • jdbc - 42.6.0
  • wrieshark

Java 코드는 아래와 같습니다.

import java.sql.*;
import java.util.Properties;

class Test {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://HOST:PORT/DATABASE";
        Properties properties = new Properties();
        properties.setProperty("user", "user");
        properties.setProperty("password", "password");
        properties.setProperty("preferQueryMode", "extended");
        
        try {
            Connection connection = DriverManager.getConnection(url, properties);
            ResultSet result;
            Statement stmt = connection.createStatement();
            result = stmt.executeQuery("select now()");
            result.next();
            result.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
 

이제 준비가 다 되었으니, wrieshark 로 통신 과정을 살펴봅시다!

샘플 코드를 실행하면, 아래와 같은 로그를 확인 할 수 있습니다.

(다만 이 로그는 디코딩하지 않은 상태이기에, 큰 틀만 보고 넘어갈게요)

 
위 로그를 처음 접한다면 부담스러울 수 있으니 단계를 나눠서 살펴봅시다 :)
 
 
1. create DB connection
   34   0.194003    127.0.0.1 → 127.0.0.1    TCP 161 [TCP Retransmission] 52983 → 5432 [PSH, ACK] Seq=178 Ack=119 Win=408128 Len=109 TSval=3577723749 TSecr=391281476
   35   0.194014    127.0.0.1 → 127.0.0.1    TCP 52 5432 → 52983 [ACK] Seq=119 Ack=287 Win=408000 Len=0 TSval=391281581 TSecr=3577723749
   36   0.194018    127.0.0.1 → 127.0.0.1    TCP 52 [TCP Dup ACK 35#1] 5432 → 52983 [ACK] Seq=119 Ack=287 Win=408000 Len=0 TSval=391281581 TSecr=3577723749
   37   0.202886    127.0.0.1 → 127.0.0.1    PGSQL 516 <R/R/S/S/S/S/S/S/S/S/S/S/S/S/S/K/Z
   38   0.202901    127.0.0.1 → 127.0.0.1    TCP 516 [TCP Retransmission] 5432 → 52983 [PSH, ACK] Seq=119 Ack=287 Win=408000 Len=464 TSval=391281590 TSecr=3577723749
   39   0.202912    127.0.0.1 → 127.0.0.1    TCP 52 52983 → 5432 [ACK] Seq=287 Ack=583 Win=407680 Len=0 TSval=3577723758 TSecr=391281590
   40   0.202915    127.0.0.1 → 127.0.0.1    TCP 52 [TCP Dup ACK 39#1] 52983 → 5432 [ACK] Seq=287 Ack=583 Win=407680 Len=0 TSval=3577723758 TSecr=391281590
   41   0.234307    127.0.0.1 → 127.0.0.1    PGSQL 115 >P/B/E/S
   42   0.234331    127.0.0.1 → 127.0.0.1    TCP 115 [TCP Retransmission] 52983 → 5432 [PSH, ACK] Seq=287 Ack=583 Win=407680 Len=63 TSval=3577723790 TSecr=391281590
   43   0.234356    127.0.0.1 → 127.0.0.1    TCP 52 5432 → 52983 [ACK] Seq=583 Ack=350 Win=407936 Len=0 TSval=391281622 TSecr=3577723790
   44   0.234364    127.0.0.1 → 127.0.0.1    TCP 52 [TCP Dup ACK 43#1] 5432 → 52983 [ACK] Seq=583 Ack=350 Win=407936 Len=0 TSval=391281622 TSecr=3577723790
   45   0.235952    127.0.0.1 → 127.0.0.1    PGSQL 77 <1/2/C/Z
   46   0.235960    127.0.0.1 → 127.0.0.1    TCP 77 [TCP Retransmission] 5432 → 52983 [PSH, ACK] Seq=583 Ack=350 Win=407936 Len=25 TSval=391281623 TSecr=3577723790
   47   0.235972    127.0.0.1 → 127.0.0.1    TCP 52 52983 → 5432 [ACK] Seq=350 Ack=608 Win=407680 Len=0 TSval=3577723791 TSecr=391281623
   48   0.235977    127.0.0.1 → 127.0.0.1    TCP 52 [TCP Dup ACK 47#1] 52983 → 5432 [ACK] Seq=350 Ack=608 Win=407680 Len=0 TSval=3577723791 TSecr=391281623
   49   0.236280    127.0.0.1 → 127.0.0.1    PGSQL 136 >P/B/E/S
   50   0.236292    127.0.0.1 → 127.0.0.1    TCP 136 [TCP Retransmission] 52983 → 5432 [PSH, ACK] Seq=350 Ack=608 Win=407680 Len=84 TSval=3577723791 TSecr=391281623
   51   0.236298    127.0.0.1 → 127.0.0.1    TCP 52 5432 → 52983 [ACK] Seq=608 Ack=434 Win=407808 Len=0 TSval=391281623 TSecr=3577723791
   52   0.236300    127.0.0.1 → 127.0.0.1    TCP 52 [TCP Dup ACK 51#1] 5432 → 52983 [ACK] Seq=608 Ack=434 Win=407808 Len=0 TSval=391281623 TSecr=3577723791
   53   0.236795    127.0.0.1 → 127.0.0.1    PGSQL 122 <1/2/C/S/Z

2. execute statement

   57   0.285849    127.0.0.1 → 127.0.0.1    PGSQL 108 >P/B/D/E/S
   58   0.285864    127.0.0.1 → 127.0.0.1    TCP 108 [TCP Retransmission] 52983 → 5432 [PSH, ACK] Seq=434 Ack=678 Win=407616 Len=56 TSval=3577723841 TSecr=391281623
   59   0.285898    127.0.0.1 → 127.0.0.1    TCP 52 5432 → 52983 [ACK] Seq=678 Ack=490 Win=407808 Len=0 TSval=391281673 TSecr=3577723841
   60   0.285908    127.0.0.1 → 127.0.0.1    TCP 52 [TCP Dup ACK 59#1] 5432 → 52983 [ACK] Seq=678 Ack=490 Win=407808 Len=0 TSval=391281673 TSecr=3577723841
   61   0.287709    127.0.0.1 → 127.0.0.1    PGSQL 151 <1/2/T/D/C/Z

3. close connection

   65   0.298611    127.0.0.1 → 127.0.0.1    PGSQL 57 >X
   66   0.298630    127.0.0.1 → 127.0.0.1    TCP 57 [TCP Retransmission] 52983 → 5432 [PSH, ACK] Seq=490 Ack=777 Win=407488 Len=5 TSval=3577723854 TSecr=391281675
   67   0.298664    127.0.0.1 → 127.0.0.1    TCP 52 5432 → 52983 [ACK] Seq=777 Ack=495 Win=407744 Len=0 TSval=391281686 TSecr=3577723854
   68   0.298674    127.0.0.1 → 127.0.0.1    TCP 52 [TCP Dup ACK 67#1] 5432 → 52983 [ACK] Seq=777 Ack=495 Win=407744 Len=0 TSval=391281686 TSecr=3577723854
   69   0.298987    127.0.0.1 → 127.0.0.1    TCP 52 52983 → 5432 [FIN, ACK] Seq=495 Ack=777 Win=407488 Len=0 TSval=3577723854 TSecr=391281686
   70   0.299003    127.0.0.1 → 127.0.0.1    TCP 52 [TCP Retransmission] 52983 → 5432 [FIN, ACK] Seq=495 Ack=777 Win=407488 Len=0 TSval=3577723854 TSecr=391281686
   71   0.299025    127.0.0.1 → 127.0.0.1    TCP 52 5432 → 52983 [ACK] Seq=777 Ack=496 Win=407744 Len=0 TSval=391281686 TSecr=3577723854

이렇게 쪼개서 보니, 조금 더 직관적인가요?

 

자, 이제 이게 어떤 내용인지 디코딩해서 제대로 살펴봅시다! (Postgresql 의 protocol-flow 참고)

우리는 위 로그에서 Java 코드로 statement 를 실행했을때, 2개 프레임을 사용한 것을 확인할 수 있습니다.

따라서 Frame 57, Frame61 을 하나씩 차례로 살펴보겠습니다.

1. Frame 57

Frame 57: 108 bytes on wire (864 bits), 108 bytes captured (864 bits) on interface any, id 0
Raw packet data
Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1
Transmission Control Protocol, Src Port: 53174, Dst Port: 5432, Seq: 434, Ack: 678, Len: 56
PostgreSQL
    Type: Parse
    Length: 20
    Statement:
    Query: select now()
    Parameters: 0
PostgreSQL
    Type: Bind
    Length: 12
    Portal:
    Statement:
    Parameter formats: 0
    Parameter values: 0
    Result formats: 0
PostgreSQL
    Type: Describe
    Length: 6
    Portal:
PostgreSQL
    Type: Execute
    Length: 9
    Portal:
    Returns: all rows
PostgreSQL
    Type: Sync
    Length: 4

이 프레임(frame 57)은 Java 클라이언트의 실행 요청입니다. (즉, 단일 실행 요청에 대해 5개의 postgres 요청)

위 내용과 같이 단일 프레임 네트워크 패킷 안에 아래 메시지들을 포함하고 있습니다.

  • Parse (P) : parse request 로 전달 받은 쿼리를 unnamed statement 로 파싱합니다
  • Bind (B) : unnamed portal 을 사용하는 unnamed statement 에 대한 잠재적 파라미터를 바인딩합니다
  • Describe (D) : 반환되는 데이터의 row 포맷을 describe 합니다
  • Execute (E) : unnamed portal 을 실행합니다
  • Sync (S) : 실행을 완료하고 현재 트랜잭션을 닫습니다

 

request 프레임을 살펴봤으니, 이제 response 프레임(frame 61)을 살펴봅시다!

2. Frame 61

Frame 61: 151 bytes on wire (1208 bits), 151 bytes captured (1208 bits) on interface any, id 0
Raw packet data
Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1
Transmission Control Protocol, Src Port: 5432, Dst Port: 53174, Seq: 678, Ack: 490, Len: 99
PostgreSQL
    Type: Parse completion
    Length: 4
PostgreSQL
    Type: Bind completion
    Length: 4
PostgreSQL
    Type: Row description
    Length: 28
    Field count: 1
        Column name: now
            Table OID: 0
            Column index: 0
            Type OID: 1184
            Column length: 8
            Type modifier: -1
            Format: Text (0)
PostgreSQL
    Type: Data row
    Length: 39
    Field count: 1
        Column length: 29
        Data: 323032332d30352d31342030303a35313a31372e3230303131382b3039
PostgreSQL
    Type: Command completion
    Length: 13
    Tag: SELECT 1
PostgreSQL
    Type: Ready for query
    Length: 5
    Status: Idle (73)

이번 frame 61 에는 아래 6가지 메시지가 있습니다.

  • Parse completion (1) -> response to (P)
  • Bind completion (2) -> response to (B)
  • Row description (T) -> response to (D)
  • Data row (D) -> response to (E)
  • Command completion (C)
  • Ready for query (Z)

 

응답 프레임의 경우 응답 메시지가 모두 단일 프레임 내에서 다시 전송되는 것을 볼 수 있습니다.

여기서 기억해야 할 중요한 포인트는 EQP 의 statement 실행 흐름이 여러개의 메시지를 사용한다는 것입니다.

즉, 클라이언트의 parse, bind, execute 뿐만 아니라 다른 메시지들도 단일 프레임에 결합될 수 있습니다.

(이 Java 클라이언트 statement 실행에는 최소 프레임인 2프레임을 사용한것을 확인할 수 있습니다)

 

SQP 로 테스트

앞서 말씀드렸듯, psql 을 포함해 많은 클라이언트가 SQP 를 사용하고 있습니다.

아래와 같이 preferQueryMode 속성을 'simple'로 설정 후 SQP 실습을 진행해보도록 하겠습니다.

properties.setProperty("preferQueryMode", "simple");
아래 로그를 통해 flow 를 같이 살펴봅시다.

우선 Request 는 Parse, Bind, Execute, Sync 모두 "Q" 메시지로 통합된것을 확인할 수 있습니다.

다만, Response 는 EQP 와 큰 차이가 없습니다.
parse 와 bind completion 메시지는 사라졌지만, Row Description(T), Data Row(D), Command Completion(C), Ready for query(Z) 등 다른 메시지는 여전히 존재하는것을 확인할 수 있습니다. 
그렇다면 SQP 는 여러 statement 를 보낼 수 없을까요?
SQP 에서도 ; 으로 구분된 statement 를 추가하면 단일 메시지에 여러 statement 을 보낼 수 있습니다. (이는 DDL과 같이 결과를 반환하지 않는 statement 입니다)
그러나 EQP 에는 동일한 원리를 달성할 수 있는 특정 옵션이 있는데 이게 바로 우리가 잘 알고있는 "batching" 입니다.
 
자 여기까지의 과정들을 통해 EQP 와 SQP 를 비교해보았는데요,
statement 를 실행하는데 SQP 와 EQP 중 어떤게 더 좋은 선택지일까요?
(변수나 바인딩이 없는 statement 인 경우) SQP 가 조금 더 적은 byte 를 전송하지만 사실 그것이 큰 이점을 가져온다고 보기 어렵고, 다른 명확한 이점이 존재한다고 보기 어렵습니다.
반면, SQP 는 query parameter 의 바인딩을 허용하지 않으므로 string concat 을 사용해 동적 쿼리를 작성하기에 sql injection 과 같은 위협에 노출될 수있습니다.
 

정리

postgresql jdbc 드라이버는 디폴트로 EQP 를 채택했습니다.
EQP 는 SQP 와 달리 쿼리 실행을 Parse, Bind, Execute 로 나눠서 처리합니다.
(이러한 메시지는 동일한 네트워크 패킷 내에서 전송되며 메시지 간 네트워크 roundtrip 이 필요하지 않으므로 문제되지 않음)
반면 SQP 는 쿼리 실행을 위해 데이터베이스에 단일 메시지를 전송합니다.

사실 언뜻 생각했을땐 SQP 가 훨씬 효율적인게 아닌가? 라고 생각할 수 있는데, 실제로는 거의 비슷한걸 확인할 수 있었습니다.
그 이유는 EQP 에서의 데이터베이스 실행이 Parse, Bind 및 Execute 메시지로 분할되는 목적이 데이터베이스 prepared statement 를 사용할 때 Parse 메시지를 건너뛸 수 있기때문입니다.

얘기를 나누다보니, prepared statement 내용을 좀 더 추가해야겠다는 생각이 드는데..

다음 포스팅에서 이어서 설명하거나, 혹은 이 포스팅에 내용을 덧붙여 보겠습니다 (아마도 곧...)

 

 

마무리 (잡담)

가벼운 내용이지만, 혹시나 이런 내용에 관심 있으실 분들을 위해 작은 지식을 나눠보고자 작성해보았습니다. (필요하신 분들께 도움이 되었으면 좋겠습니다.)

 

 

혹시 틀린 부분이 있다면, 언제든 편하게 지적해주세요!

이메일 ian.ilminmoon@gmail.com

'Simple Topics' 카테고리의 다른 글

A Brief Introduction to Modern Password Hashing: Argon2 Variants and Balloon Algorithm  (0) 2023.09.19
MySQL 8.1 출시 (Innovation Release와 LTS 알아보기)  (1) 2023.07.19
UUID 의 세계(UUID 선택 가이드)  (0) 2023.05.15

'Simple Topics'의 다른글

  • 현재글JDBC 살펴보기 (JDBC communication internals with postgresql)

관련글

티스토리툴바