설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 68건, 최근 0 건
   

Function Inlining을 활용한 성능 개선

글쓴이 : 모델광 날짜 : 2021-04-11 (일) 21:21 조회 : 338
다음은 필자와 어느 신입 개발자간의 대화이다.
튜너 : (함수의 부작용 설명 후) 그러므로 간단한 로직은 함수를 사용하지 말고, 조인 문장을 사용하십시오.
개발자 : 함수가 DB성능을 악화시킨다는 것은 이해가 가는데, 튜너님 지침을 따르면 개발기간이 길어집니다. 조인 문장 만드느라 시간을 더 쓰게 된다면 프로젝트 입장에서는 ROI가 낮아지는 것 아닙니까?
튜너 : (나이도 어린게 시키는 데로 할것이지...라고 생각하며)함수를 남발하게 되면 결국 DBMS를 교체하거나 Hardware를 교체해야 하는 상황까지도 갈 수 있습니다. 나만 편하자고 함수를 쓰다보면, 결국 그 함수때문에 사용하지 않아도 되는 예산을 더 쓰게 될 수도 있습니다.
개발자 : (나이 많은 노친네가 고집만 세네...라고 생각하며)네 알겠습니다.
....며칠 후 개발자가 googling 열심히 하더니 Function Inlining 개념을 사용해서 함수가 Join보다 더 성능이 좋을 수 있음을 튜너에게 보여줌
개발자 : 튜너님, PostgreSQL은 Function Inlining 개념이 있습니다. 저희 프로젝트에서 빈번히 사용하는 코드는 Function을 이용해서 코드명을 가져오도록 개발하겠습니다.
튜너   : (Function Inlining 검색 후 얼굴이 붉어지며)어... 네 그렇게 하십시오.
         (SQL Server 최신 버전도 간단한 함수는 Inlining 동작한다.)

다음은 필자가 개발자에게 설명한 Function의 부작용 내용과, Function Inlining에 대한 예시이다.

SELECT A.ORD_NO

     , F_GET_PROD_NM(A.PROD_ID) AS PROD_NM

  FROM ORDERS_DETAIL A

 WHERE A.ORD_NO BETWEEN 1 AND 100000;

위 쿼리는 PROD_NM을 추출하기 위해서 F_GET_PROD 함수를 사용하고 있다.

| Gather (actual time=32.892..2255.193 rows=1000000 loops=1)                                            |

|   Workers Planned: 2                                                                                  |

|   Workers Launched: 2                                                                                 |

|   Buffers: shared hit=1001631 read=8801 written=3636                                                  |

|   ->  Parallel Bitmap Heap Scan data-on orders_detail a (actual time=29.616..2127.167 rows=333333 loops=3) |

|         Recheck Cond: ((ord_no >= 1) AND (ord_no <= 100000))                                          |

|         Heap Blocks: exact=2676                                                                       |

|         Buffers: shared hit=1001631 read=8801 written=3636                                            |

|         ->  Bitmap Index Scan data-on orders_detail_x01 (actual time=30.348..30.348 rows=1000000 loops=1)  |

|               Index Cond: ((ord_no >= 1) AND (ord_no <= 100000))                                      |

|               Buffers: shared hit=3 read=1149                                                         |

| Planning:                                                                                             |

|   Buffers: shared hit=1 read=3                                                                        |

| Planning Time: 0.113 ms                                                                               |

| Execution Time: 2318.876 m

위 실행계획을 보면 f_get_prod_nm 함수가 1000000회 수행되었음을 알 수 있다.
f_get_prod_nm 함수의 소스코드는 아래와 같다.
CREATE OR REPLACE FUNCTION portal.f_get_prod_nm(i_prod_id character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
DECLARE
  O_PROD_NM VARCHAR(100);
BEGIN

  SELECT PROD_NM
    INTO O_PROD_NM
    FROM PROD
   WHERE PROD_ID = I_PROD_ID;
   RETURN O_PROD_NM;
END; $function$;
위 SQL을 아래와 같이 JOIN 문장으로 수정하였다.

SELECT A.ORD_NO, B.PROD_NM

  FROM ORDERS_DETAIL A, PROD B

 WHERE A.PROD_ID = B.PROD_ID

   AND A.ORD_NO BETWEEN 1 AND 100000;

| Hash Join (actual time=24.446..303.434 rows=999999 loops=1)                                          |

|   Hash Cond: ((a.prod_id)::text = (b.prod_id)::text)                                                 |

|   Buffers: shared hit=9497                                                                           |

|   ->  Bitmap Heap Scan data-on orders_detail a (actual time=24.416..124.040 rows=1000000 loops=1)         |

|         Recheck Cond: ((ord_no >= 1) AND (ord_no <= 100000))                                         |

|         Heap Blocks: exact=8344                                                                      |

|         Buffers: shared hit=9496                                                                     |

|         ->  Bitmap Index Scan data-on orders_detail_x01 (actual time=23.610..23.610 rows=1000000 loops=1) |

|               Index Cond: ((ord_no >= 1) AND (ord_no <= 100000))                                     |

|               Buffers: shared hit=1152                                                               |

|   ->  Hash (actual time=0.015..0.016 rows=5 loops=1)                                                 |

|         Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                 |

|         Buffers: shared hit=1                                                                        |

|         ->  Seq Scan data-on prod b (actual time=0.009..0.011 rows=5 loops=1)                             |

|               Buffers: shared hit=1                                                                  |

| Planning:                                                                                            |

|   Buffers: shared hit=4                                                                              |

| Planning Time: 0.371 ms                                                                              |

| Execution Time: 335.462 ms 


함수를 없애고 JOIN 문으로 수정하니, block I/O가 1001631-->9497,

elapsed time이 2318 ms --> 335ms 성능 개선이 되었다.

만약 f_get_prod_nm 함수가 자주 사용되는 함수라면, DB서버의 부하나 성능에 미치는 영향은 엄청나다. DB서버 부하로 H/W 교체 검토중인 사이트에 가보면 위와 같은 비효율적인 SQL 몇 개만 수정해도 DB서버 부하가 확 떨어지고, 연쇄적으로 lock 현상도 줄어들어 다른 SQL 성능들도 좋아지는 경우가 많다.

신입 개발자가 찾아낸 PG의 Function Inlining을 활용하려면 위의 함수르 아래와 같이 수정한다.

CREATE OR REPLACE FUNCTION portal.f_sqlget_prod_nm(i_prod_id character varying)

 RETURNS character varying

 LANGUAGE sql

 STABLE PARALLEL SAFE

AS $$

  SELECT CASE WHEN $1 IN ('PP0') THEN '999998TEST_NAME'

              WHEN $1 IN ('PP1') THEN '999997TEST_NAME'

              WHEN $1 IN ('PP2') THEN '999999TEST_NAME'

              WHEN $1 IN ('PP3') THEN '333333TEST_NAME'

              WHEN $1 IN ('PP4') THEN '444444TEST_NAME'

          ELSE 'N/A'

       END;

$$;

위 함수를 이용해서 SQL을 다시 수행해 보자.

SELECT A.ORD_NO

     , F_SQLGET_PROD_NM(A.PROD_ID) AS PROD_NM

  FROM ORDERS_DETAIL A

 WHERE A.ORD_NO BETWEEN 1 AND 100000;

| Bitmap Heap Scan data-on portal.orders_detail a (actual time=25.666..227.984 rows=1000000 loops=1)                            

|   Output: ord_no, CASE WHEN ((prod_id)::text = 'PP0'::text) THEN '999998TEST_NAME'::text WHEN ((prod_id)::text = 'PP1'::text) THEN '999997TEST_NAME'::text WHEN ((prod_id)::text = 'PP2'::text) THEN '999999TEST_NAME'::text WHEN ((prod_id)::text = 'PP3'::text) THEN '333333TEST_NAME'::text WHEN ((prod_id)::text = 'PP4'::text) THEN '444444TEST_NAME'::text ELSE 'N/A'::text END |

|   Recheck Cond: ((a.ord_no >= 1) AND (a.ord_no <= 100000))                                                                

|   Heap Blocks: exact=8344

|   Buffers: shared hit=9496

|   ->  Bitmap Index Scan data-on orders_detail_x01 (actual time=24.782..24.783 rows=1000000 loops=1) 

|         Index Cond: ((a.ord_no >= 1) AND (a.ord_no <= 100000))

|         Buffers: shared hit=1152 

| Planning:

|   Buffers: shared hit=4

| Planning Time: 0.157 ms

| Execution Time: 262.608 ms

실행계획을 보면 함수를 사용하였으나, optimizer가 함수를 풀어 헤쳐서 단순 SQL 문장으로 Transformation을 했음을 알 수 있다.

JOIN 문장에 비해서 block I/O는 9497 --> 8344,

                     elapsed time은 335ms --> 262 ms 로 개선되었다.

코드테이블을 엑세스 하지 않으니 성능이 좋아지는 것은 당연한 결과다.

위와 같이 Funtion Inlining 의 장단점은 아래와 같다.

Disadvantages

1. 코드테이블 변경 시 반드시 코드 값 입력시 이름을 추출하는 함수도 수정해야 한다.

   - 몇 번 개발자가 교체되면 실수할 가능성이 높다.

   - 모든 코드그룹에 대해서 함수를 만들게 되면 관리할 함수 수가 너무 많아진다.

2. 매우 간단한 business logic만 inlining 동작하는 함수로 작성 가능하다.


Advantages

1. 코드테이블 JOIN하는 SQL보다 성능이 좋다.

  - 자주 사용하는 함수라면, 그 impact는 엄청나다.

2. 개발 생산성이 높아진다.

  - 코드 데이터 수정때마다 함수도 함께 수정해야 하는 단점이 있기는 하다.

  - 사용 빈도 TOP 5개 정도의 코드그룹만 선정해서 Inlining Function 사용해도 DB서버 성능개선이 큰 효과가 있다.


PostgresDBA 2021-04-14 (수) 21:43
PostgreSQL 성능에 관심이 많으시군요.
좋은 글 많이 올려주세요~~
같이 일할 기회가 있음 하네요.
댓글주소
   

postgresdba.com