2014년 7월 4일 금요일

MariaDB for TokuDB 7.1.6 Subquery를 포함한 DML문의 쿼리 최적화에 대한 이슈

Subquery를 포함한 DML문에서 쿼리가 비정상적으로 느려지는 문제가 발견되었다.


DELETE FROM item 
 WHERE item_no IN (SELECT item_no FROM tmp_item);
Query OK, 102 rows affected (45.32 sec) 
  • 위의 쿼리는 tmp_item이라는 테이블에 삭제할 item 리스트를 임시로 저장하고 이 테이블에 존재하는 item 리스트를 item이라는 원본 테이블에서 데이터를 삭제하는 쿼리이다.
  • tmp_item 테이블은 item 테이블과 동일한 구조를 가진 임시 테이블로 item 테이블은 약 34만 건의 item이 있으며, tmp_item테이블에는 삭제할 item 리스트가 매 시간 약 100건 정도 적재되고 이 문장이 실행된 후 TRUNCATE 된다.
  • 두 테이블 모두 PK가 item_no이다.

적재된 데이터 양을 고려할 때 아주 빠른 시간 내에 데이터가 처리될 것으로 예상되나 쿼리 타임에서 보여지듯이 비정상적으로 느린 현상을 목격할 수 있었다.
위 쿼리가 실행될 때 초 단위로 SHOW PROCESSLIST를 찍어보면 Queried about 20000 rows ... Queried about 38000 rows.. 등 메인 테이블의 데이터 건 수에 비례하여 패치하듯이 실행되는 것을 목격할 수 있었는데, TokuDB의 Subquery Optimization이 MariaDB의 그것과 다르게 동작하는 것으로 추측된다.

위 배치 쿼리를 아래와 같이 Join Delete로 바꾸어서 실행하여 InnoDB 에서와 비슷한 속도향상을 기대할 수 있었다.
DELETE t1
  FROM item t1
 INNER JOIN tmp_item t2
    ON t1.item_no = t2.item_no;
Query OK, 102 rows affected (0.00 sec)

비슷한 예로 아래의 UPDATE문도 JOIN UPDATE로 문장을 변경하여 실행하여 쿼리 성능을 향상시킬 수 있었다.
UPDATE sum_shop_order a
   SET a.cnt = 
    ( SELECT count(*)
        FROM order d, item e
       WHERE d.item_no = e.item_no
         AND d.regdt >='2014-07-03'
         AND d.regdt <'2014-07-04'
         AND (e.shop_no = a.shop_no) )
 WHERE a.dates = '2014-07-03'
   AND a.shop_type ='S';
Query OK, 137 rows affected (21.28 sec)
Rows matched: 206  Changed: 137  Warnings: 0
UPDATE sum_shop_order a
  LEFT JOIN (SELECT shop_no, COUNT(*) AS cnt
               FROM order d
              INNER JOIN item e
                 ON d.item_no = e.item_no
              WHERE d.regdt >='2014-07-03'
         AND d.regdt <'2014-07-04'
              GROUP BY shop_no) AS b
    ON a.shop_no = b.shop_no
   SET a.cnt = IFNULL(b.cnt, 0)
 WHERE a.dates = '2014-07-03'
   AND a.shop_type ='S';
Query OK, 137 rows affected (0.01 sec)
Rows matched: 206  Changed: 137  Warnings: 0

댓글 없음:

댓글 쓰기