2014년 10월 28일 화요일

프로시져를 이용한 재귀 쿼리 (2)

스토어드 프로시져를 이용해서, WITH 절을 에뮬레이션한 재귀 처리는 이미 살펴보았는데, 이 프로시져 예제는 성능보다는 범용성에 집중해서 만들어진 것이다. 사실 이 프로시져(with_emulator)로 성능 테스트를 해보진 않았지만, 임시 테이블 생성과 생각보다 많은 테이블 레벨의 조작들이 필요하므로 성능적으로 장점은 별로 없어 보였다.

그래서 특정 테이블의 구조에 제한적인 재귀 처리를 스토어드 프로시져로 만들어서 성능 테스트를 해보았다. 우선 아래와 같은 employees라는 사원 테이블을 가정해보자.

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `extension` varchar(10) NOT NULL,
  `email` varchar(100) NOT NULL,
  `office_code` varchar(10) NOT NULL,
  `boss_emp_no` int(11) DEFAULT NULL,
  `job_title` varchar(50) NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `ix_bossempno` (`boss_emp_no`),
  KEY `ix_officecode` (`office_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Oracle이라면 아래와 같이 재귀 쿼리를 이용해서 한번에 트리를 조회할 수 있을 것이다.

SELECT *, LEVEL as LV
FROM employees
CONNECT BY PRIOR emp_no = boss_emp_no
START WITH emp_no='1002';

+--------+-----------+------------+...+-------------+----------------------+------+
| emp_no | last_name | first_name |...| boss_emp_no | job_title            | lv   |
+--------+-----------+------------+...+-------------+----------------------+------+
|   1002 | Murphy    | Diane      |...|        NULL | President            |    1 |
|   1056 | Patterson | Mary       |...|        1002 | VP Sales             |    2 |
|   1088 | Patterson | William    |...|        1056 | Sales Manager (APAC) |    3 |
|   1611 | Fixter    | Andy       |...|        1088 | Sales Rep            |    4 |
|   1612 | Marsh     | Peter      |...|        1088 | Sales Rep            |    4 |
|   1619 | King      | Tom        |...|        1088 | Sales Rep            |    4 |
|   1102 | Bondur    | Gerard     |...|        1056 | Sale Manager (EMEA)  |    3 |
|   1702 | Gerard    | Martin     |...|        1102 | Sales Rep            |    4 |
|   1337 | Bondur    | Loui       |...|        1102 | Sales Rep            |    4 |
|   1370 | Hernandez | Gerard     |...|        1102 | Sales Rep            |    4 |
|   1401 | Castillo  | Pamela     |...|        1102 | Sales Rep            |    4 |
|   1501 | Bott      | Larry      |...|        1102 | Sales Rep            |    4 |
|   1504 | Jones     | Barry      |...|        1102 | Sales Rep            |    4 |
|   1143 | Bow       | Anthony    |...|        1056 | Sales Manager (NA)   |    3 |
|   1165 | Jennings  | Leslie     |...|        1143 | Sales Rep            |    4 |
|   1166 | Thompson  | Leslie     |...|        1143 | Sales Rep            |    4 |
|   1188 | Firrelli  | Julie      |...|        1143 | Sales Rep            |    4 |
|   1216 | Patterson | Steve      |...|        1143 | Sales Rep            |    4 |
|   1286 | Tseng     | Foon Yue   |...|        1143 | Sales Rep            |    4 |
|   1323 | Vanauf    | George     |...|        1143 | Sales Rep            |    4 |
|   1621 | Nishi     | Mami       |...|        1056 | Sales Rep            |    3 |
|   1625 | Kato      | Yoshimi    |...|        1621 | Sales Rep            |    4 |
|   1076 | Firrelli  | Jeff       |...|        1002 | VP Marketing         |    2 |
+--------+-----------+------------+...+-------------+----------------------+------+

하지만 MySQL에서는 아직 WITH절이나 CONNECT BY 절을 사용할 수 없으므로, 이와 유사한 결과를 만들어 내기 위해서 아래와 같은 스토어드 프로시져를 생성해 보았다. 물론 이 프로시져의 결과는 Oracle의 CONNECT BY와 동일한 정렬 순서를 보이지는 않지만, 이런 (Tree 형태의) 정렬 작업들은 응용 프로그램에서 얼마든지 쉽게 구현할 수 있으며, 이전 블로그에서 소개했던 with_emulator 프로시져를 참조하면 MySQL 쿼리로도 쉽게 구현할 수 있으므로 생략하도록 하겠다.


CREATE PROCEDURE recursive_static(p_start_value VARCHAR(100))

BEGIN
  DECLARE recursive_count INT UNSIGNED;
  DECLARE current_lv INT UNSIGNED;
  DECLARE parent_ids VARCHAR(1000);

  SET SESSION sql_log_bin=OFF;
  SET SESSION tx_isolation='READ-COMMITTED';
  SET recursive_count = 1;
  SET @_current_lv := 1;

  CREATE TEMPORARY TABLE IF NOT EXISTS _temp_buffer(
      emp_no int(11), 
      last_name varchar(50), 
      first_name varchar(50), 
      extension varchar(10), 
      email varchar(100), 
      office_code varchar(10), 
      boss_emp_no int(11), 
      job_title varchar(50), 
      lv int, 
      INDEX ix_lv(lv)
  ) ENGINE=MEMORY;

  INSERT INTO _temp_buffer 
    SELECT emp_no, last_name, first_name, extension, email, 
               office_code, boss_emp_no, job_title, @_current_lv AS lv 
      FROM employees WHERE emp_no=p_start_value;

  recursion: REPEAT
    SELECT GROUP_CONCAT(emp_no) INTO parent_ids 
      FROM _temp_buffer WHERE lv=@_current_lv;

    IF parent_ids IS NULL OR parent_ids="" THEN
      LEAVE recursion;
    END IF;

    SET @query = CONCAT("INSERT INTO _temp_buffer 
            SELECT emp_no, last_name, first_name, extension, email,
                        office_code, boss_emp_no, job_title, (? + 1) AS lv 
            FROM employees WHERE boss_emp_no IN (", parent_ids, ")");
    PREPARE stmt3 FROM @query;
    EXECUTE stmt3 USING @_current_lv;

    SET @_current_lv = @_current_lv + 1;
    SET recursive_count = recursive_count + 1;
    IF recursive_count > 10 THEN
      LEAVE recursion;
    END IF;
  UNTIL 0 END REPEAT;

  SELECT * FROM _temp_buffer;
  TRUNCATE TABLE _temp_buffer;
  SET SESSION sql_log_bin=ON;
  SET SESSION tx_isolation='REPEATABLE-READ';
END ;;


이 프로시져는 범용성은 포기하고 성능 및 테스트를 위해서 준비된 것이므로, 위에서 보인 employees 테이블의 구조에서만 사용될 수 있는 프로시져이고, 만약 employees 테이블의 구조가 변경된다면 프로시져의 내용도 변경되어야 할 것이다. 또한 이 프로시져에서는 바이너리 로그 기록과 격리 수준등 불필요하고 성능 저해 요소는 모두 비활성화하고 실행하도록 했다. 이 프로시져도 내부적으로는 임시 테이블을 사용하는데, 여기에서는 특정 컨넥션에서 이 프로시져가 처음 호출될 때에 임시 테이블을 만들고 계속 재활용될 수 있도록 프로시져를 만들어 보았다.

이렇게 준비된 recursive_static 프로시져를 실행하면, 아래와 같은 결과를 얻을 수 있다. 보시다시피 이 결과는 Oracle의 재귀 쿼리 결과와는 조금 다른 모양을 보이고 있다. 하지만 이 부분은 위에서 언급했듯이 이번 테스트의 관심 사항이 아니므로 넘어가도록 하겠다. 어찌되었거나 "Diane"을 포함해서 하위 조직의 사원들 23명의 정보를 가져왔다는 것에 집중하자.

mysql:test> CALL recursive_static('1002');
+--------+-----------+------------+..+-------------+----------------------+------+
| emp_no | last_name | first_name |..| boss_emp_no | job_title            | lv   |
+--------+-----------+------------+..+-------------+----------------------+------+
|   1002 | Murphy    | Diane      |..|        NULL | President            |    1 |
|   1056 | Patterson | Mary       |..|        1002 | VP Sales             |    2 |
|   1088 | Patterson | William    |..|        1056 | Sales Manager (APAC) |    3 |
|   1611 | Fixter    | Andy       |..|        1088 | Sales Rep            |    4 |
|   1612 | Marsh     | Peter      |..|        1088 | Sales Rep            |    4 |
|   1619 | King      | Tom        |..|        1088 | Sales Rep            |    4 |
|   1102 | Bondur    | Gerard     |..|        1056 | Sale Manager (EMEA)  |    3 |
|   1702 | Gerard    | Martin     |..|        1102 | Sales Rep            |    4 |
|   1337 | Bondur    | Loui       |..|        1102 | Sales Rep            |    4 |
|   1370 | Hernandez | Gerard     |..|        1102 | Sales Rep            |    4 |
|   1401 | Castillo  | Pamela     |..|        1102 | Sales Rep            |    4 |
|   1501 | Bott      | Larry      |..|        1102 | Sales Rep            |    4 |
|   1504 | Jones     | Barry      |..|        1102 | Sales Rep            |    4 |
|   1143 | Bow       | Anthony    |..|        1056 | Sales Manager (NA)   |    3 |
|   1165 | Jennings  | Leslie     |..|        1143 | Sales Rep            |    4 |
|   1166 | Thompson  | Leslie     |..|        1143 | Sales Rep            |    4 |
|   1188 | Firrelli  | Julie      |..|        1143 | Sales Rep            |    4 |
|   1216 | Patterson | Steve      |..|        1143 | Sales Rep            |    4 |
|   1286 | Tseng     | Foon Yue   |..|        1143 | Sales Rep            |    4 |
|   1323 | Vanauf    | George     |..|        1143 | Sales Rep            |    4 |
|   1621 | Nishi     | Mami       |..|        1056 | Sales Rep            |    3 |
|   1625 | Kato      | Yoshimi    |..|        1621 | Sales Rep            |    4 |
|   1076 | Firrelli  | Jeff       |..|        1002 | VP Marketing         |    2 |
+--------+-----------+------------+..+-------------+----------------------+------+

이제 recursive_static 프로시져가 얼마나 빨리 처리될 수 있는지 간단히 로드 제너레이터를 만들어서 한번 실행해보도록 하자. 로드 제너레이터의 내용에는 별다를 건 없다. 그냥 멀티 쓰레드로 기동된 프로그램이 JDBC를 이용해서 프로시져를 CALL하고 결과를 가져오는 작업을 반복하면서 초 단위로 Throughput을 출력하도록 했다.

Server spec : Intel Xeon 2.3GHz * 2 socket * 6 core (with HT)

MySQL 5.6.20에서는 초당 12000번 정도의 "CALL recursive_static()"을 처리했으며, MariaDB 10.0.12에서는 9300번 그리고 10.0.14에서는 대략 10200번 정도의 처리 성능을 보였다. MySQL 5.6.20이 MariaDB 10.0.14 보다 대략 20%정도 높은 성능을 보이고 있는데, 이는 아마도 MariaDB 10.0이 MySQL 5.5 코드를 베이스로 하고 있으며 아직 MariaDB가 MySQL 5.6의 최적화 내용들을 모두 포팅하지 못해서일 것으로 보인다. (일단 이 내용은 MariaDB 개발팀에 문의를 해두었으니, 조만간 개선될 것으로 보인다.)

이 테스트가 실행되는 동안 GROUP_CONCAT 함수로 인해서(레코드 건수가 적어서 인덱스를 이용하지 못함) 내부 임시 테이블이 사용되는데, 대략 테스트가 실행되는 동안 초당 45000 ~ 58000번 정도의 내부 임시 테이블이 생성되었다가 삭제되는 현상이 반복되었다. 실제 이렇게 임시 테이블이 아주 빈번하게 사용되는 환경에서는 Linux에서 기본으로 사용되는 PTMalloc보다는 JEMalloc과 같은 캐시 기반의 메모리 할당 라이브러리를 사용해주는 것이 그나마 조금 성능 향상에 도움이 될 것으로 보인다. (이 테스트는 나중에 다시 ..)

재귀 쿼리가 지원되지 않아서 MySQL을 회피하는 경우가 많은데, 서비스의 대 부분 쿼리가 재귀 쿼리로 개발된 서비스가 아니라면 그리고 이 정도의 스토어드 프로그램에 시간을 투자할 수 있다면 이런 방식도 충분히 괜찮은 Work-around가 되지 않을까 생각된다. 물론 이 테스트를 수행하는 동안 MySQL 서버의 CPU 사용량은 거의 90% 수준이었으니, 실제 프로덕션 환경의 MySQL 서버에서 이 정도 성능을 기대하기는 어려울 수도 있을 것이다. 또한 프로그램에서 필요로 하는 데이터의 레코드 수가 더 많거나 레코드의 크기가 크다면 성능은 더 떨어질 수도 있을 것이다. 

이 테스트 케이스에서 보였던 것처럼 초당 10000번의 재귀 쿼리가 실행되어야 하는 서비스는 아직까지 본 적이 없어서, 재귀 쿼리 기능이 RDBMS를 선정하는 기준이 될 수 있는지는 조금 더 고려가 필요해 보인다.

2014년 10월 27일 월요일

MySQL의 스토어드 프로그램

개요

MySQL의 스토어드 프로그램(이 글에서 스토어드 프로그램은 Stored procedure와 Stored Function에 한함)은 MySQL 5.0버전부터 지원되기 시작했다. MySQL 5.0의 첫번째 릴리즈 버전이 2005년도 10월에 출시되었으니, MySQL에 프로시져가 도입된지 벌써 10년 정도의 시간이 지나가고 있지만 실제로 MySQL에서 프로시져의 인기는 그다지 높지 않다.
요즘은 MSSQL이나 Oracle에 익숙한 사용자(개발자와 DBA 모두)들이 MySQL을 배우거나 사용하고자 하는 경우가 많이 늘어나고 있는데, 많은 사용자들이 MySQL 만의 특징에 익숙치 않아서 혼란스러워하는 경우를 많이 보았다.
특히나 MySQL은 주로 Web 기반의 서비스에서 사용되다 보니, MSSQL이나 Oracle과 같은 RDBMS에서 효율적으로 제공하는 기능들이 MySQL에서는 그렇지 못한 것들이 자주 있다. 물론 때로는 그 반대인 경우도 흔히 볼 수 있다.
그중에서 가장 많은 이슈가 되고 있는 스토어드 프로그램의 특징을 간단히 살펴보고, 왜 MySQL에서는 Oracle이나 MSSQL에서와 같이 스토어드 프로그램을 활용할 수 없는지를 소개해보고자 한다.


스토어드 프로그램의 컴파일

다른 상용의 RDBMS에서와 같이 MySQL 서버에서도 스토어드 프로그램은 컴파일 과정을 거치게 된다. 물론 C/C++과 같이 물리적인 CPU가 직접 해석할 수 있는 이진 코드가 만들어지는 것은 아니지만, Java와 같이 어떤 형태의 목적 코드(Java의 바이트 코드와 같은)가 만들어지고
이 목적 코드는 메모리상에 저장되어서 나중에 재실행 요청시에는 준비된 바이트 코드가 실행된다. 즉 스토어드 프로그램의 소스 코드가 매번 실행될 때마다 파싱되고 분석되어서 실행되는 것이 아니란 것을 의미한다.

간단히 아래와 같은 프로시져를 생각해보자.

CREATE PROCEDURE sp_test(p CHAR(16))
BEGIN
    DECLARE x INT;
    SET x = 3;
    WHILE x > 0 DO
        SET x = x-1;
        INSERT INTO tab_test VALUES (x, p);
    END WHILE;
END

위의 프로시져가 컴파일되면, 아래와 같은 목적 코드가 만들어지게 된다.
목적 코드에서는 단순히 스토어드 프로그램의 코드에서 SET 이나 WHILE과 같은 문장들을 sp_instr_set이나 sp_instr_jump 등과 같은 인스트럭션으로 변환된 형태로 관리하게 된다.
여기에서 한 가지 기억해야 할 것은 컴파일된 스토어드 프로그램의 목적 코드에서 SQL 문장은 그대로 문자열로 남아있게 된다는 것이다. 즉 MySQL의 스토어드 프로그램은 컴파일이 되어도 내부에 사용된 SQL 문장들을 바로 실행할 수 있는 실행 계획이나 Parsed-Tree 형태로 관리하는 것이 아니란 것을 의미한다.

---------+-----------------------------------------------------
Position | Instruction
---------+-----------------------------------------------------
 0       | sp_instr_set(1, '3')
 1       | sp_instr_jump_if_not(5, 'x>0')
 2       | sp_instr_set(1, 'x-1')
 3       | sp_instr_stmt('INSERT INTO tab_test VALUES (x, p)')
 4       | sp_instr_jump(1)
 5       | <end>
---------+-----------------------------------------------------

스토어드 프로그램 캐시

Oracle이나 MSSQL의 스토어드 프로그램은 전역의 스토어드 프로그램 캐시 공간(Memory)에 관리된다. 물론 MySQL 서버의 스토어드 프로그램도 컴파일되면 스토어드 프로그램 캐시(소스 코드에서는 이를 sp_cache라고 함)에 관리한다.
하지만 MySQL의 스토어드 프로그램 캐시는 전역이 아니라 Thread 단위로 관리된다. 여기서 Thread라 함은 사실은 Connection 기반으로 관리됨을 의미한다. 만약 Thread pool을 사용한다 하더라도, 실제 Linux의 Thread 단위가 아니라 Connection 단위의 메모리 공간(THD)에 관리되는 것이다.

큰 차이가 아닌 것 같지만, 사실 스토어드 프로그램 캐시가 전역이나 세션(로컬) 단위냐에 따라서 장단점은 크게 달라진다.


  • 전역 스토어드 프로그램 캐시
    •  장점 : 메모리 절약, 스토어드 프로그램의 컴파일과 최적화 회수가 적음
    •  단점 : 여러 클라이언트가 동시에 컴파일된 스토어드 프로그램을 참조하므로 동기화 비용이 필요하며, Re-Enterant와 Thread-safe한 데이터 구조체 및 구현 필요(뒷 부분은 사실 운영이 아니라 구현상의 이슈이므로, 사용자인 우리에게는 별로 중요하지 않음)
  • 로컬 스토어드 프로그램 캐시
    • 장점 : 클라이언트간의 공유 이슈가 없으므로 잠금이 없고 빠른 처리 가능, 구현이 쉬움
    • 단점 : 많은 메모리 공간이 필요하고, 클라이언트 컨넥션 단위로 스토어드 프로그램의 컴파일 필요


MySQL의 스토어드 프로그램 캐시 공간은 Connection 단위로 관리된다는 것은 컨넥션이 새로 생성되면 필요한 모든 프로시져의 컴파일이 필요하다는 것을 의미한다.
만약 Connection pool이나 PHP의 Persistent-connection을 사용하지 못하고 매번 Connection을 생성해야 하는 경우라면, 매번 스토어드 프로그램이 실행될 때마다 스토어드 프로그램을 (mysql.proc 테이블에서) 읽어서 컴파일을 해야 하므로 최악의 성능을 내게 될 것이다.
그렇다고 Connection pool이나 Persistent-Connection 환경이라고 안전한 것은 아니다. 많은 스토어드 프로그램이 사용되는 서비스에서 MySQL 서버에 연결된 컨넥션이 10000개라고 가정하면 엄청난 메모리 공간이 필요하게 될 것이다.
하지만 성능 향상을 고려한다면, 스토어드 프로그램 캐시 메모리 공간을 적게 설정할 수도 없는 진퇴양난의 상황에 빠지게 될 수도 있다.

스토어드 프로그램의 무효화

MySQL 서버의 스토어드 프로그램 캐시 공간은 컨넥션간 서로 공유되는 전역 공간이 아니라, 컨넥션 단위로 관리된다는 것을 앞에서 살펴보았다.
사실 스토어드 프로그램 캐시가 컨넥션 단위로 관리되기 때문에 발생하는 문제점이 또 있는데, ALTER나 CRETE 등과 같은 DDL을 이용해서 스토어드 프로그램의 코드를 변경하는 경우이다.
만약 컨넥션이 10000개가 만들어져서 각각의 컨넥션에서 sp_test라는 프로시져를 사용하고 있다고 가정해보자. 이때 DBA가 ALTER PROCEDURE나 DROP PROCEDURE + CREATE PROCEDURE를 실행했다고 가정해보자.
그럼 어떤 현상이 발생하게 될까 ?

프로시져를 변경하는 컨넥션에서는 단순히 해당 프로시져의 정보를 mysql DB에 있는 proc 테이블에 변경 저장하고, 해당 프로시져의 버전을 1 증가시키고 완료된다. 이때 해당 프로시져의 버전은 글로벌하게 전역 메모리 공간에 관리된다.
그리고 모든 서비스 컨넥션에서는 프로시져를 실행하기 전에 항상 로컬 스토어드 프로그램 캐시에 괸리되는 프로시져의 버전과 전역 공간의 프로시져 버전을 확인해서, 로컬 스토어드 프로그램 캐시의 버전이 낮으면 로컬 스토어드 프로그램 캐시에 저장되어 있던 컴파일된 목적 코드를 버리고 다시 컴파일을 수행한다.
이렇게 컴파일이 완료되면, 비로소 해당 프로시져를 실행할 수 있게 되는 것이다.

그나마 다행인 것은, 변경된 프로시져가 자주 실행되지 않는다면 모든 컨넥션이 한번에 동일 스토어드 프로그램을 컴파일하기 위해서 상당한 시간을 소모하지 않을 것이다. 하지만 스토어드 프로그램이 아주 빈번하게 모든 컨넥션에서 활용된다면 어떤 상황이 발생하게 될까 ?
이런 경우라면 일부러 사용량이 별로 없는 새벽 시간에 스토어드 프로그램을 배포해야 할 지도 모르겠다.

(참고로, Oracle의 MySQL 개발팀에서는 Production MySQL 서버에서 스토어드 프로그램을 갱신하는 것은 상당히 드문 케이스이며, 별로 심각하게 고려되지 않는 상황이라고 소개하고 있다. ㅠㅠ)있다

메모리 부족 예방

MySQL 서버의 스토어드 프로그램은 컨넥션 단위로 로컬 캐시 영역에 관리되기 때문에, 컨넥션이 많고 사용되는 스토어드 프로그램이 많다면 많은 메모리 공간이 필요할 것이다. 때로는 메모리 부족 현상으로 운영 체제가 MySQL 서버를 강제 종료시킬 수도 있다.
여기에서 스토어드 프로그램의 개수가 많고 적음은 상대적이며, Production MySQL 서버에 장착된 메모리 크기와 여러가지 상황에 따라서 의존적이므로 각 DBA가 적절하게 판단해야 할 것으로 보인다.

MySQL 서버에서는 이런 메모리 과다 사용을 막기 위해서 MySQL 5.5부터 stored_program_cache라는 시스템 변수를 제공하고 있다. 이 변수는 기본 값이 256이며, 설정하는 값의 의미는 스토어드 프로그램 캐시에 저장할 스토어드 프로그램의 개수이다.
스토어드 프로그램 하나 하나의 크기에 의해서도 메모리 사용량이 많이 좌우될 것으로 보이므로, 사실 256이라는 수치가 적절한지 큰 값인지는 판단하기 쉽지 않아 보인다.

만약 스토어드 프로그램 캐시에 저장된 스토어드 프로그램의 개수가 256을 넘게 되면, MySQL 서버는 현재 컨넥션의 스토어드 프로그램 캐시 내용을 모두 무효화시키고 다시 스토어드 프로그램을 하나씩 컴파일해서 저장하게 된다.
물론 스토어드 프로그램이 256개 이상이고 순서대로 하나씩 사용된다면, 위의 무효화 -> 컴파일 과정을 계속 반복하게 될 것이다.

결론

MySQL 스토어드 프로그램의 내부적인 처리 방식을 간단히 살펴보았는데, MySQL의 스토어드 프로그램을 Oracle이나 MSSQL의 그것과 동일하게 생각해서는 안되는 이유를 간략히 정리해보면...
1) 스토어드 프로그램 자체는 컴파일되어서 목적 코드로 관리되지만, 내부의 SQL문장을 파스된 형태(실행계획이나 Parsed-Tree 형태)로 관리하지 않는다.
2) 컴파일된 스토어드 프로그램 목적 코드는 각 컨넥션 단위로 관리되기 때문에 Oracle이나 MSSQL보다 많은 메모리 공간이 필요하다.
3) 스토어드 프로그램이 변경될 때마다, 모든 컨넥션에서 기존 목적 코드의 무효화 및 신규 프로시져의 컴파일 과정일 필요하다.

또한 MySQL은 Web 기반의 단순 쿼리를 고속으로 처리해주는 용도로 많이 활용된다. 그래서 Facebook이나 Twitter 등의 SNS 회사들은 WebScaleSQL이라는 목표로 MySQL 코드 패치를 수행하고 있기도 하다.
이런 방향성으로 본다면, 스토어드 프로그램과 같은 복잡한 절차적 코드(Compound-statement block)를 확장이 어려운 MySQL 서버에 둔다는 것은 적절치 않을 수 있다.
Oracle이나 MSSQL에서는 모든 처리를 DBMS 서버로 집중화하고 서버를 통합(Consolidation) 것이 목표였다면, MySQL의 목표는 그 반대로 볼 수 있다. MySQL은 라이센스 비용이 없으니깐 말이다.
물론 라이센스 비용 이야기는 어떤 형태의 기술 지원을 받는냐에 따라 이야기가 달라지겠지만, 그래도 Oracle이나 MSSQL의 라이센스 비용에 비할바는 아닐 것이다.

<<그렇다고 MySQL의 스토어드 프로그램은 사용해서는 안될 물건이라고 생각하지는 말자. 어디까지나 목적에 맞게 기능들을 잘 활용하자는 수준으로 해석할 것을 당부드린다.>>

2014년 10월 12일 일요일

WITH절을 이용한 재귀 쿼리 에뮬레이션


Oracle이나 MSSQL을 사용해 본 사람이라면 아마도 “WITH 절” 

을 사용해 보았을 것이다. 어떤 사람들은 이를 “서브 쿼리 팩토링(Subquery Factoring)”이라 하고, 또 어떤 사람들은  “COMMON TABLE EXPRESSION”이라고 한다

3개의 컬럼을 가지는 T1 테이블을 가정해보자.

CREATE TABLE T1(
YEAR INT, # 2000, 2001, 2002 ...
MONTH INT, # January, February, ...
SALES INT # how much we sold on that month of that year
);

이제 연도별로 판매 실적의 트렌드(증감)를 조회하고자 한다고 해보자.

SELECT D1.YEAR, (CASE WHEN D1.S>D2.S THEN 'INCREASE' ELSE 'DECREASE' END) AS TREND
FROM
  (SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR) AS D1,
  (SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR) AS D2
WHERE D1.YEAR = D2.YEAR-1;

위 쿼리에서 두개의 서브 쿼리는 동일한 서브 쿼리 문장을 사용하고 있다. 하지만 일반적인 DBMS는 그걸 인지할만큼 똑똑하지 않다. 그래서 DBMS 옵티마이저는 “SELECT YEAR, SUM(SALES), …” 문장을 두번 실행해서 각각 D1과 D2 임시 테이블을 채우게 될 것이다.  이렇게 동일한 문장을 두번 수행하는 작업은 성능적으로도 상당히 문제가 될 수 있다. WITH 절을 사용하게 되면, 이런 제한 사항이 없어지고 서브 쿼리는 단 한번만 수행하게 된다.

WITH D AS (SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR)
SELECT D1.YEAR, (CASE WHEN D1.S>D2.S THEN 'INCREASE' ELSE 'DECREASE' END) AS TREND
FROM
 D AS D1,
 D AS D2
WHERE D1.YEAR = D2.YEAR-1;

하지만  MySQL에서는 WITH 절이 지원되지 않는다. 하지만 VIEW를 이용하면 WITH절을 에뮬레이션할 수 있다.

CREATE VIEW D AS (SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR);

SELECT D1.YEAR, (CASE WHEN D1.S>D2.S THEN 'INCREASE' ELSE 'DECREASE' END) AS TREND
FROM
 D AS D1,
 D AS D2
WHERE D1.YEAR = D2.YEAR-1;
DROP VIEW D;

VIEW 대신 D 테이블을 일반 테이블로 생성할 수도 있다. 이때 D 테이블을 임시 테이블로 사용할 수는 없다. MySQL에서는 하나의 쿼리 문장에서 임시 테이블은 단 한번만 참조될 수 있다는 제한 사항이 있기 때문이다. (http://dev.mysql.com/doc/refman/5.7/en/temporary-table-problems.html)

이제 조금 더 복잡한 형태의 WITH 절 사용법(재귀적인 형태)을 살펴보자.
SQL 표준에 의하면, 재귀적인 형태를 사용하기 위해서는 “WITH RECURSIVE”를 사용해야 한다. 하지만 일반적인 DBMS에서는 RECURSIVE 키워드는 생략할 수 있는 것으로 보인다.

WITH RECURSIVE는 매우 강력한 기능을 제공하는데, 예를 들어서 오라클 RDBMS의 CONNECT BY와 동일한 형태의 쿼리를 처리할 수도 있다. WITH RECURSIVE를 이해하기 위해서 employees 라는 테이블(아주 전통적인 WITH RECURSIVE 예제용 테이블)을 생각해보자. (실제 오라클 RDBMS에서도 이제는 SQL 표준 형태인 WITH 절을 지원하므로, 더 이상 오라클 RDBMS에서도 CONNECT BY .. START WITH .. 구문을 사용하지 않아도 된다.)

CREATE TABLE EMPLOYEES (
  ID INT PRIMARY KEY,
  NAME VARCHAR(100),
  MANAGER_ID INT,
  INDEX (MANAGER_ID),
  FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES(ID)
);

INSERT INTO EMPLOYEES VALUES
(333, "Yasmina", NULL),
(198, "John", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(692, "Tarek", 333);

데이터를 간단히 살펴보면, Yasmina는 CEO이며 John과 Tarek의 보스는 Yasmina이며, Pedro의 보스는 John이다. 그리고 Sarah와 Pierre의 보스는 Pero이다. 매우 큰 회사라면, 이 테이블의 레코드 건수는 몇 천에서 몇 만건이 될 것이다.

이제 각 사원에 대해서 “얼마나 많은 사람들이 직간접적으로 조직 관계를 구성하는지”를 알아내고자 한다. 이를 위해서 우선 매니저가 아닌 사람들의 목록을 만들고자 하는데, 간단히 서브 쿼리를 이용해서 매니저인 사람의 목록을 만들고 NOT IN (subquery)를 이용해서 매니저가 아닌 사람들의 목록을 조회할 수 있다.

SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS
FROM EMPLOYEES
WHERE ID NOT IN (
  SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL
);

그리고 이 결과를 새로운 테이블 EMPLOYEES_EXTENDED에 저장하자. EMPLOYEES_EXTENDED 테이블의 마지막에는 “이 사원에게 직간접적으로 보고를 하는 사원 수를  저장하는” REPORTS 컬럼을 추가했다. 즉 REPORTS 컬럼의 값이 0인 사원은 매니저가 아닌 것이다. 이제 아래와 같은 쿼리를 이용해서 첫번째 레벨의 매니저(매니저가 아닌 사원의 직접적인 차상위 매니저)를 구할 수 있게 되었다.

SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS
FROM EMPLOYEES M 
  JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID
GROUP BY M.ID, M.NAME, M.MANAGER_ID;

위 쿼리 각 사원(매니저가 아닌)의 직접적인 보스 사원 정보를 0건 1건 이상을 만들어내게 된다. 이때 REPORTS 컬럼의 값이 1인 경우는 매니저가 아닌 사원을 의미하며, 2이상인 경우는 자신에게 보고하는 사원중에서 매니저가 아닌 사원의 수를 리턴하게 된다.

이제 EMPLOYEES_EXTENDED 테이블을 모두 지우고, 바로 위의 쿼리 결과를 EMPLOYEES_EXTENDED 테이블에 채워넣자. 이제 EMPLOYEES_EXTENDED 테이블에는 1차 레벨의 매니저로 채워지게 된다. 그리고 다시 똑같은 쿼리를 실행해보자. 그러면 그 결과로 2차 레벨의 매니저 목록을 조회할 수 있게 된다. 이 과정을 계속 반복하면 결국 Yasmina 레코드 한건만 EMPLOYEES_EXTENDED 테이블에 채워지게 될 것이다. 하지만 마지막 과정에서 SELECT 쿼리는 (E.MANAGER_ID가 NULL일 것이므로) 아무런 레코드를 만들어내지 않게 될 것이다.

지금까지의 과정을 간략히 정리해보면, EXMPLOYEES_EXTENDED 테이블은 매니저가 아닌 사원 그리고 1차 레벨 매니저 그리고 그 다음에는 2차 매니저  그리고 반복해서 조회한 N차 매니저를 저장하는 일종의 임시 버퍼로 사용된 것이다. 여기에서 우리는 재귀적인 기능을 사용한 것이다. 하지만 여기에서 한 가지 문제점은 이렇게 EMPLOYEES_EXENDED 테이블(임시 버퍼)에 저장되었던 결과들을 어떻게 병합(UNION ALL)할 것인지이다.

매니저가 아닌 사원의 목록이 재귀적인 처리의 시작이었는데, 이를 “앵커 멤버(Anchor Member)” 또는 “시드(Seed)”라고 한다. 그리고 재귀적으로 반복해서 실행되는 SELECT 쿼리는 “재귀 멤버(Recursive Member)”라고 한다. 완전히 WITH 절 쿼리는 아래와 같다.

WITH RECURSIVE
# The temporary buffer, also used as UNION result:
EMPLOYEES_EXTENDED
AS
(
  # The seed:
  SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS
  FROM EMPLOYEES
  WHERE ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL)
UNION ALL
  # The recursive member:
  SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS
  FROM EMPLOYEES M JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID
  GROUP BY M.ID, M.NAME, M.MANAGER_ID
)
# what we want to do with the complete result (the UNION):
SELECT * FROM EMPLOYEES_EXTENDED;

MySQL은 아직 WITH RECURSIVE 기능을 제공하지 않는다. 하지만 일반적인 스토어드 프로시져를 이용하면 아래와 같이 간단하게 에뮬레이션할 수 있다.

CALL WITH_EMULATOR(
"EMPLOYEES_EXTENDED",
"
  SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS
  FROM EMPLOYEES
  WHERE ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL)
",
"
  SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS
  FROM EMPLOYEES M JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID
  GROUP BY M.ID, M.NAME, M.MANAGER_ID
",
"SELECT * FROM EMPLOYEES_EXTENDED",
0,
""
);

눈치채고 있듯이, 스토어드 프로그램의 각 파라미터는 WITH 표준 문법의 각 멤버(“임시 버퍼의 이름”, “시드를 위한 쿼리”, “ 재귀 멤버를 위한 쿼리”, “최종 결과를 어떻게 할 것인지”)를 입력으로 사용하고 있다.  마지막 두개 파라미터는 0과 빈 문자값은 지금은 무시하도록 하자.

아래는 스토어드 프로그램 실행 결과이다.

+------+---------+------------+---------+
| ID   | NAME    | MANAGER_ID | REPORTS |
+------+---------+------------+---------+
|   72 | Pierre  |         29 |       0 |
|  692 | Tarek   |        333 |       0 |
| 4610 | Sarah   |         29 |       0 |
|   29 | Pedro   |        198 |       2 |
|  333 | Yasmina |       NULL |       1 |
|  198 | John    |        333 |       3 |
|  333 | Yasmina |       NULL |       4 |
+------+---------+------------+---------+
7 rows in set

Pierre와 Tarek 그리고 Sarah는 아무도 직속 멤버가 없으며, Pedro는 2명의 직속 멤버를 가지고 있다. 하지만 Yasmina는 2번 결과 셋에서 두번 나타났다. 이는 우리의 알고리즘이 매니저가 아닌 사원으로부터 시작했기 때문이다. 즉 나무(Yasmina는 나무의 뿌리)의 가지에서부터 재귀적으로 데이터를 가져왔기 때문이다. 그래서 마지막 쿼리를 이제 조금 바꿔 보았다.

CALL WITH_EMULATOR(
"EMPLOYEES_EXTENDED",
"
  SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS
  FROM EMPLOYEES
  WHERE ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL)
",
"
  SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS
  FROM EMPLOYEES M JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID
  GROUP BY M.ID, M.NAME, M.MANAGER_ID
",
"
  SELECT ID, NAME, MANAGER_ID, SUM(REPORTS)
  FROM EMPLOYEES_EXTENDED
  GROUP BY ID, NAME, MANAGER_ID
",
0,
""
);

이제서야 제대로 된 결과가 나왔다.

+------+---------+------------+--------------+
| ID   | NAME    | MANAGER_ID | SUM(REPORTS) |
+------+---------+------------+--------------+
|   29 | Pedro   |        198 |            2 |
|   72 | Pierre  |         29 |            0 |
|  198 | John    |        333 |            3 |
|  333 | Yasmina |       NULL |            5 |
|  692 | Tarek   |        333 |            0 |
| 4610 | Sarah   |         29 |            0 |
+------+---------+------------+--------------+
6 rows in set

이제 스토어드 프로시져의 실제 코드를 살펴보자. PreparedStatement를 이용하는 동적 쿼리가 상당히 사용된 것을 알 수 있다. 이 스토어드 프로시져는 특별히 해결하기 어려운 제한 사항들을 가지고 있지는 않으므로 다른 용도(대표적으로 각 부서의 조직도를 조회하는 재귀 쿼리)의 재귀적 쿼리에서도 충분히 활용할 수 있을 것이다. 자세한 내용은 스토어드 프로그램 코드의 주석을 참조하자.

# Usage: the standard syntax:
#   WITH RECURSIVE recursive_table AS
#    (initial_SELECT
#     UNION ALL
#     recursive_SELECT)
#   final_SELECT;
# should be translated by you to 
# CALL WITH_EMULATOR(recursive_table, initial_SELECT, recursive_SELECT,
#                    final_SELECT, 0, "").

# ALGORITHM:
# 1) we have an initial table T0 (actual name is an argument
# "recursive_table"), we fill it with result of initial_SELECT.
# 2) We have a union table U, initially empty.
# 3) Loop:
#   add rows of T0 to U,
#   run recursive_SELECT based on T0 and put result into table T1,
#   if T1 is empty
#      then leave loop,
#      else swap T0 and T1 (renaming) and empty T1
# 4) Drop T0, T1
# 5) Rename U to T0
# 6) run final select, send result to client

# This is for *one* recursive table.
# It would be possible to write a SP creating multiple recursive tables.

delimiter ;;

CREATE PROCEDURE with_emulator(
  recursive_table VARCHAR(100),         # name of recursive table
  initial_select VARCHAR(65530),        # seed a.k.a. anchor
  recursive_select VARCHAR(65530),      # recursive member
  final_select VARCHAR(65530),          # final SELECT on UNION result
  max_recursion INT UNSIGNED,           # safety against infinite loop, use 0 for default
  create_table_options VARCHAR(65530)   # you can add CREATE-TABLE-time options
  # to your recursive_table, to speed up initial/recursive/final SELECTs; example: "(KEY(some_column)) ENGINE=MEMORY"
)
BEGIN
  DECLARE new_rows INT UNSIGNED;
  DECLARE show_progress INT DEFAULT 0; # SET to 1 to trace/debug execution
  DECLARE recursive_table_next VARCHAR(120);
  DECLARE recursive_table_union VARCHAR(120);
  DECLARE recursive_table_tmp VARCHAR(120);

  SET recursive_table_next  = CONCAT(recursive_table, "_next");
  SET recursive_table_union = CONCAT(recursive_table, "_union");
  SET recursive_table_tmp   = CONCAT(recursive_table, "_tmp");

  # IF you need to reference recursive_table more than
  # once in recursive_select, remove the TEMPORARY word.

  # 0) cleaning for previous procedure failed
  SET @str = CONCAT("DROP TABLE IF EXISTS ", recursive_table_next);
  PREPARE stmt FROM @str;
  EXECUTE stmt;
  SET @str = CONCAT("DROP TABLE IF EXISTS ", recursive_table_union);
  PREPARE stmt FROM @str;
  EXECUTE stmt;
  SET @str = CONCAT("DROP TABLE IF EXISTS ", recursive_table_tmp);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # 1) create and fill T0
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table, " ", create_table_options, " AS ", initial_select);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # 2) create U
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " LIKE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # 3) create T1
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_next, " LIKE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  IF max_recursion = 0 THEN
    SET max_recursion = 100; # a default to protect the innocent
  END IF;

  recursion: REPEAT
    # add T0 to U (this is always UNION ALL)
    SET @str = CONCAT("INSERT INTO ", recursive_table_union, " SELECT * FROM ", recursive_table);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we are done IF max depth reached
    SET max_recursion = max_recursion - 1;
    IF not max_recursion THEN
      IF show_progress THEN
        SELECT CONCAT("max recursion exceeded");
      END IF;
      LEAVE recursion;
    END IF;

    # fill T1 by applying the recursive SELECT on T0
    SET @str = CONCAT("INSERT INTO ", recursive_table_next, " ", recursive_select);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we are done IF no rows in T1
    SELECT ROW_COUNT() INTO new_rows;
    IF show_progress THEN
      SELECT CONCAT(new_rows, " new rows found");
    END IF;

    IF NOT new_rows THEN
      LEAVE recursion;
    END IF;

    # Prepare next iteration:
    # T1 becomes T0, to be the source of next run of recursive_select,
    # T0 is recycled to be T1.
    SET @str = CONCAT("ALTER TABLE ", recursive_table, " RENAME ", recursive_table_tmp);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we use ALTER TABLE RENAME because RENAME TABLE does not support temp tables
    SET @str = CONCAT("ALTER TABLE ", recursive_table_next, " RENAME ", recursive_table);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    SET @str = CONCAT("ALTER TABLE ", recursive_table_tmp, " RENAME ", recursive_table_next);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # empty T1
    SET @str = CONCAT("TRUNCATE TABLE ", recursive_table_next);
    PREPARE stmt FROM @str;
    EXECUTE stmt;
  UNTIL 0 END REPEAT;

  # eliminate T0 and T1
  SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table_next, ", ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # Final (output) SELECT uses recursive_table name
  SET @str = CONCAT("ALTER TABLE ", recursive_table_union, " RENAME ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # Run final SELECT on UNION
  SET @str = final_select;
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # No temporary tables may survive:
  SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # We are done :-)
END ;;

delimiter ;


여기까지의 글은 Oracle의 MySQL 개발자인 "Guilhem Bichot"의 블로그 게시물을 번역한 것입니다.




“Guilhem Bichot”의 블로그 내용에 몇 가지를 추가해보면…

일반적으로 조직도를 조회하는 재귀 쿼리를 위해서 Top-Down 트리를 조회하는 쿼리는 아래와 같이 실행할 수 있다. 각 그룹별로 트리를 순회(Tree traversal)하는 형태의 결과와 같이 레코드를 정렬하기 위해서 sort_key 라는 컬럼도 같이 추가해서 구현해본 예제이다. Sort_key 컬럼에 저장되는 값은 SQL 문장을 보면 쉽게 이해할 수 있을 것으로 보이므로, 자세한 설명은 생략하도록 하겠다.
(물론, 이 경우에도 M-M 관계의 트리가 생성되는 경우라면, GROUP BY가 필요할 수 있을 것으로 보인다.)

CALL with_emulator(
"e$emp",
"
  SELECT id, name, manager_id, 1 as lv, @_seq:=0 as sort_key
  FROM emp
  WHERE manager_id is null
",
"
  SELECT m.id, m.name, m.manager_id, e.lv+1 as lv, CONCAT(e.sort_key, LPAD(@_seq:=@_seq+1, 5, '0')) as sort_key
  FROM emp m JOIN e$emp e ON m.manager_id=e.id
",
"
  SELECT id, name, manager_id, lv, sort_key
  FROM e$emp
  ORDER BY sort_key
",
0,
"(id INT NOT NULL, name VARCHAR(100), manager_id INT, lv INT, sort_key VARCHAR(100), KEY ix_sortkey(sort_key)) ENGINE=MEMORY"
);

+------+---------+------------+------+------------------+
| id   | name    | manager_id | lv   | sort_key         |
+------+---------+------------+------+------------------+
|  333 | Yasmina |       NULL |    1 | 0                |
|  198 | John    |        333 |    2 | 000001           |
|   29 | Pedro   |        198 |    3 | 00000100003      |
|   72 | Pierre  |         29 |    4 | 0000010000300004 |
| 4610 | Sarah   |         29 |    4 | 0000010000300005 |
|  692 | Tarek   |        333 |    2 | 000002           |
+------+---------+------------+------+------------------+
6 rows in set (0.01 sec)



그리고 아래 프로시져는 “Guilhem Bichot”의 블로그에 공유된 프로시져에, 내부적으로 사용되는 임시 테이블중에서 중간 테이블과 최종 테이블의 구조를 다르게 생성하도록 조금 수정해 본 것이다. 중간 과정에서 필요한 테이블에는 인덱스가 있으면 더 성능이 느려지지만, 마지막 결과를 저장하는 최종 테이블에는 인덱스가 있으면 더 효율적인 경우에는 이런 구성이 더 효율적일 수도 있을 것으로 보인다.
또한 추가로 임시 테이블과 쿼리를 위한 세션 변수 변경 및 복구와 SQL Error에 대한 처리 몇 가지를 더 추가한 것이다.

DELIMITER ;;

DROP PROCEDURE with_emulator2;;

CREATE PROCEDURE with_emulator2(
  recursive_table VARCHAR(100),         # name of recursive table
  initial_select VARCHAR(65530),        # seed a.k.a. anchor
  recursive_select VARCHAR(65530),      # recursive member
  final_select VARCHAR(65530),          # final SELECT on UNION result
  max_recursion INT UNSIGNED,           # safety against infinite loop, use 0 for default
  working_table_options VARCHAR(500),   # you can add CREATE-TABLE-time options for intermediate working table
  final_table_options VARCHAR(500)      # you can add CREATE-TABLE-time options for final table
  # to your recursive_table, to speed up initial/recursive/final SELECTs; example: "(KEY(some_column)) ENGINE=MEMORY"
)
BEGIN
  DECLARE loop_count INT UNSIGNED DEFAULT 0;
  DECLARE new_rows INT UNSIGNED;
  DECLARE show_progress INT DEFAULT 0; # SET to 1 to trace/debug execution
  DECLARE recursive_table_next VARCHAR(120);
  DECLARE recursive_table_union VARCHAR(120);
  DECLARE recursive_table_tmp VARCHAR(120);

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ## Restore session variables
    SET @@tmp_table_size = @orig_tmp_table_size;
    SET @@max_heap_table_size = @orig_max_heap_table_size;
    SET @@sort_buffer_size = @orig_sort_buffer_size;

    SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table_next);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table_tmp);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table_union);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    ## Resignaling the origin exception to user
    RESIGNAL;
  END;

  SET recursive_table_next  = CONCAT(recursive_table, "_next");
  SET recursive_table_union = CONCAT(recursive_table, "_union");
  SET recursive_table_tmp   = CONCAT(recursive_table, "_tmp");

  ## Backup session variables
  SET @orig_tmp_table_size = @@tmp_table_size;
  SET @orig_max_heap_table_size = @@max_heap_table_size;
  SET @orig_sort_buffer_size = @@sort_buffer_size;

  ## Update new session variables for with_emulator
  SET @@tmp_table_size = 1024*1024; # 1MB
  SET @@max_heap_table_size = 1024*1024; # 1MB
  SET @@sort_buffer_size = 1024*1024; # 1MB

  # IF you need to reference recursive_table more than
  # once in recursive_select, remove the TEMPORARY word.
  # 1) create and fill T0
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table, " ", working_table_options, " AS ", initial_select);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # 2) create U
  ## 작업 테이블과 최종 테이블의 구조를 다르게 가져가기 위해서는 initial_select를 두번 실행할 수밖에 없다.
  ## 작업 테이블에는 인덱스가 필요없지만 최종 테이블에는 인덱스가 필요한 경우가 많으므로, 원본 쿼리대신 recursive_table_union 테이블을 (recursive_table 복사가 아니라) initial_select를 이용해서 다시 생성하도록 수정함.
  ## ORIG :: SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " LIKE ", recursive_table);
  ## initial_select가 아주 가벼운 쿼리라면 아래 쿼리를 실행해도 무방하지만, initial_select 쿼리가 무겁다면 위의 원본 쿼리가 좋음.
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " ", final_table_options, " AS ", initial_select);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # 3) create T1
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_next, " LIKE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  IF max_recursion = 0 THEN
    SET max_recursion = 100; # a default to protect the innocent
  END IF;

  recursion: REPEAT
    IF loop_count > 0 THEN ## Copy recursive_table to recursive_table_union if this is not the first loop
      # add T0 to U (this is always UNION ALL)
      SET @str = CONCAT("INSERT INTO ", recursive_table_union, " SELECT * FROM ", recursive_table);
      PREPARE stmt FROM @str;
      EXECUTE stmt;
    END IF;

    # we are done IF max depth reached
    SET loop_count = loop_count + 1;
    IF loop_count > max_recursion THEN
      IF show_progress THEN
        SELECT CONCAT("max recursion exceeded");
      END IF;
      LEAVE recursion;
    END IF;

    # fill T1 by applying the recursive SELECT on T0
    SET @str = CONCAT("INSERT INTO ", recursive_table_next, " ", recursive_select);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we are done IF no rows in T1
    SELECT ROW_COUNT() INTO new_rows;
    IF show_progress THEN
      SELECT CONCAT(new_rows, " new rows found");
    END IF;

    IF NOT new_rows THEN
      LEAVE recursion;
    END IF;

    # Prepare next iteration:
    # T1 becomes T0, to be the source of next run of recursive_select,
    # T0 is recycled to be T1.
    SET @str = CONCAT("ALTER TABLE ", recursive_table, " RENAME ", recursive_table_tmp);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we use ALTER TABLE RENAME because RENAME TABLE does not support temp tables
    SET @str = CONCAT("ALTER TABLE ", recursive_table_next, " RENAME ", recursive_table);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    SET @str = CONCAT("ALTER TABLE ", recursive_table_tmp, " RENAME ", recursive_table_next);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # empty T1
    SET @str = CONCAT("TRUNCATE TABLE ", recursive_table_next);
    PREPARE stmt FROM @str;
    EXECUTE stmt;
  UNTIL 0 END REPEAT;

  # eliminate T0 and T1
  SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table_next, ", ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # Final (output) SELECT uses recursive_table name
  SET @str = CONCAT("ALTER TABLE ", recursive_table_union, " RENAME ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # Run final SELECT on UNION
  SET @str = final_select;
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # No temporary tables may survive:
  SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # We are done :-)

  ## Restore session variables
  SET @@tmp_table_size = @orig_tmp_table_size;
  SET @@max_heap_table_size = @orig_max_heap_table_size;
  SET @@sort_buffer_size = @orig_sort_buffer_size;
END ;;

DELIMITER ;




CALL with_emulator2(
"e$emp",
"
  SELECT id, name, manager_id, 1 as lv, @_seq:=0 as sort_key
  FROM emp
  WHERE manager_id is null
",
"
  SELECT m.id, m.name, m.manager_id, e.lv+1 as lv, CONCAT(e.sort_key, LPAD(@_seq:=@_seq+1, 5, '0')) as sort_key
  FROM emp m JOIN e$emp e ON m.manager_id=e.id
",
"
  SELECT id, name, manager_id, lv, sort_key
  FROM e$emp
  ORDER BY sort_key
",
0,
"(id INT NOT NULL, name VARCHAR(100), manager_id INT, lv INT, sort_key VARCHAR(100)) ENGINE=MEMORY",
"(id INT NOT NULL, name VARCHAR(100), manager_id INT, lv INT, sort_key VARCHAR(100), KEY ix_sortkey(sort_key)) ENGINE=MEMORY"
);

+------+---------+------------+------+------------------+
| id   | name    | manager_id | lv   | sort_key         |
+------+---------+------------+------+------------------+
|  333 | Yasmina |       NULL |    1 | 0                |
|  198 | John    |        333 |    2 | 000001           |
|   29 | Pedro   |        198 |    3 | 00000100003      |
|   72 | Pierre  |         29 |    4 | 0000010000300004 |
| 4610 | Sarah   |         29 |    4 | 0000010000300005 |
|  692 | Tarek   |        333 |    2 | 000002           |
+------+---------+------------+------+------------------+
6 rows in set (0.02 sec)



2014년 10월 6일 월요일

MySQL Online DDL 진행 상태와 Row log buffer 사용량 확인

MySQL 5.6과 MariaDB 10.0의 Online DDL 기능은 많은 사용자들이 기다려왔던 기능인데, 막상 기능은 추가되었지만 Online DDL이 어느정도 진행되었는지를 알려주지 않는다는 새로운 문제점이 있다.
Online DDL은 ALTER가 진행되는 동안 사용자로부터 유입되는 데이터 변경을 임시 메모리 공간(Row log buffer)에 담아두어야 하는데, Online DDL이 어느 정도 진행되었는지를 알 수 없으므로 Row log buffer가 부족해서 ALTER가 실패할지 사용자가 예측을 할 수 없다.
그래서 결국 사용자는 Row log buffer가 부족해질 때까지 기다려보는 방법밖에 없다. 만약 Online DDL이 완료되기까지 2시간을 기다려서 "Online DDL 실패"라는 메시지만 확인해야 할 수도 있다는 것이다.
Online DDL의 진행상황뿐만 아니라 Row log buffer가 얼마나 사용되었는지, 그리고 Row log buffer에 얼마나 많은 변경 레코드가 저장되어 있는지도 알려주지 않는다. 단순히 초당 유입되는 쿼리 수는 MySQL 서버 전체적인 것이므로, 지금 ALTER 테이블에만 유입되는 DML을 판단하기는 쉽지 않다.

그래서 Online DDL의 진행 상황이나 Row log buffer의 사용량은 Online DDL이 성공할지 실패할 지를 예측할 수 있도록 해주는 중요한 자료라고 생각된다.
진행상황이나 사용량을 사용자에게 알려주는 가장 좋은 방법은 Online DDL을 실행한 터미널의 화면에 표시해주는 것(MariaDB의 ALTER TABLE 명령과 같이)이지만, Online DDL은 그렇게 구현하기가 쉽자 않다. Online DDL은 기존 Copy 스타리의 ALTER TABLE과는 달리 스토리지 엔진(InnoDB)에서 처리되기 때문이다. 그래서 MySQL 서버에 3개의 새로운 상태 변수를 추가하는 방법을 선택하게 되었다. 이때 상태 변수는 전역(Global) 상태 변수를 사용하게 되는데, 세션 상태 변수인 경우 Online DDL을 실행한 세션외에는 확인할 수 없기 때문에 도움이 되지 않기 때문이다.
또한 이 상태 변수는 전역이기 때문에, 한번에 2개 이상의 컨넥션에서 Online DDL을 실행하는 경우에는 서로 진행 상태와 사용량이 섞여 버리기 때문에 주의해야 한다.



  • Innodb_onlineddl_rowlog_rows
    Row log buffer에 얼마나 많은 변경 레코드들이 저장되어 있는지를 보여준다.
  • Innodb_onlineddl_rowlog_pct_used
    Row log buffer의 현재 사용량을 보여준다. 이 수치는 0부터 10000까지 값을 가지며, 실제 버퍼 사용량에 * 100을 한 값이다. 즉 9529란 값은 95.29% 만큼 사용되었음을 의미한다.
  • Innodb_onlineddl_pct_progress
    Online DDL이 얼마나 진행되었는지를 보여준다. 실제 Online DDL 진행 상황은 디스크의 성능과 버퍼 풀의 워밍 업 상태에 따라서 많은 차이가 나기 때문에, 이 진행 상태는 정확하지 않을 가능성도 있다. 하지만 여전히 이 값을 이용해서 전체적인 진행 상황을 예측할 수는 있으며, 아무런 지표가 없는 것보다는 훨씬 도움이 될 것이다.



Innodb_onlineddl_pct_progress는 예측 값이지만, Innodb_onlineddl_rowlog_rows와 Innodb_onlineddl_rowlog_pct_used는 정확한 값이므로 Online DDL을 계속 진행할지 중간에 멈출지 판단하는데 더 많은 도움이 될 것으로 생각된다.
Innodb_onlineddl_pct_progress 상태 변수가 레포팅하는 진행 상황(%)는 정확하지 않을 수도 있는데, 이는 Inplace Alter(Online DDL)이 InnoDB 버퍼 풀의 워밍 업 정도와 디스크의 성능에 매우 의존적이기 때문이다. Innodb_onlineddl_pct_progress 상태 변수는 이런 모든 변수를 고려하진 않았으며, 단순히 해당 테이블의 각 인덱스의 페이지 개수와 경험을 기반으로 진행 상황을 보고한다.

Online DDL은 크게 다음 2가지 작업(주로 시간이 소모되는)으로 생각해볼 수 있다.

1. 스키마 변경 대상 테이블의 프라이머리 키(클러스터링 키)를 스캔
2. 읽은 레코드를 이용해서 프라이머리 키와 세컨드리 인덱스 빌드

Online DDL이 실행되면 1번 작업은 단 한번만 실행되지만, 2번 작업은 해당 테이블에 정의된 인덱스의 개수만큼 수행되어야 한다.
여기에서 단순히 이 두 가지 메인 작업에 대해서 비중(Weight)을 1.0으로 할당(2번 작업의 경우 각 인덱스별로 1.0 할당)했다. 그리고 2번 작업의 경우 고정(Fixed weight)된 비중과 동적 비중(Dynamic weight)을 0.5씩 할당했다.
모든 세컨드리 인덱스는 기본적으로 최소 정적 비중인 0.5 만큼의 비중을 가지고 되고, 인덱스의 크기(페이지 수)에 따라서 동적 비중을 추가로 가지게 된다. 그래서 두 번째 작업은 아래와 같이 2개의 서브 작업으로 구분될 수 있다.

2-1. 버퍼의 내용을 정렬하고 병합
2-2. 정렬된 버퍼의 내용을 실제 인덱스 트리에 저장

각 서브 작업은 디스크의 성능과 InnoDB 버퍼 풀의 워밍 업 상태에 매우 의존적이어서, 경험적으로 첫번째 서브 작업에 40%를 할당하고 2번째 서브 작업에 60%의 시간 소모(비중)을 할당했다.
Online DDL 작업은 각 작업과 서브 작업이 독립적으로 처리되기 때문에, Online DDL의 전체 진행률을 계산하기 위해서는 이렇게 비중을 할당해야 한다.

예를 들어서, 아래와 같이 인덱스를 가지고 있는 테이블에 컬럼을 하나 추가하는 Online DDL을 생각해보자.
(그리고 프라이머리 키는 100개의 페이지로 구성되어 있고, ix_fd2라는 세컨드리 인덱스는 50개의 페이지로 구성되어 있다고 가정)

CREATE TABLE tb_test(
  fdpk int,
  fd1  varchar(10),
  fd2  bigint,
  primary key (fdpk),
  index ix_fd2(fd2)
) ENGINE=InnoDB;

ALTER TABLE tb_test ADD fd3 DATETIME, LOCK=NONE, ALGORITHM=INPLACE;

위에서 설명했던 비중에 따라서, 전체 Online DDL 작업의 비중은 3.0이 될 것(1번 작업 = 1.0, 2번 작업 = 2.0)이며, 그중에서 전체 동적 비중은 1.0(각 인덱스마다 0.5)이 될 것이다.
그래서 프라이머리 키는 1.1667이라는 비중[ = 0.5(fixed weight) and 0.6667(dynamic weight, 1.0 * 100/(100+50)) ]을 가지고 되고, 세컨드리 인덱스는 0.8333[ = 0.5(fixed weight) and 0.3333(dynamic weight, 1.0 * 50/(100+50)) ] 비중을 가지게 될 것이다.
그리고 각 세부 작업별로 비중은 아래와 같이 할당될 것이다.

[weight:1.0000] 1. 스키마 변경 대상 테이블의 프라이머리 키(클러스터링 키)를 스캔
[weight:1.1667] 2. 프라이머리 키 리빌드
  [weight:40% of 1.1667] 2-1. 버퍼의 내용을 정렬하고 병합
  [weight:60% of 1.1667] 2-2. 정렬된 버퍼의 내용을 실제 인덱스 트리에 저장
[weight:0.8333] 3. 세컨드리 인덱스(ix_fd2) 리빌드
  [weight:40% of 0.8333] 3-1. 버퍼의 내용을 정렬하고 병합
  [weight:60% of 0.8333] 3-2. 정렬된 버퍼의 내용을 실제 인덱스 트리에 저장

최종적으로 각 작업과 서브 작업에 할당된 시간 비중은 아래와 같이 계산할 수 있다.

[Time:33.33%] 1. 스키마 변경 대상 테이블의 프라이머리 키(클러스터링 키)를 스캔
[Time:38.89%] 2. 프라이머리 키 리빌드
  [Time:15.56%] 2-1. 버퍼의 내용을 정렬하고 병합
  [Time:23.33%] 2-2. 정렬된 버퍼의 내용을 실제 인덱스 트리에 저장
[Time:27.78%] 3. 세컨드리 인덱스(ix_fd2) 리빌드
  [Time:11.11%] 3-1. 버퍼의 내용을 정렬하고 병합
  [Time:16.67%] 3-2. 정렬된 버퍼의 내용을 실제 인덱스 트리에 저장

그래서 만약 Online DDL이 현재 2-2 서브 작업까지 끝냈다면, 현재 Online DDL의 진행상황은 72.22%가 되는 것이다.

그리고 상태 변수로 추가된 진행 상황이 정확하지 않은 경우를 대비해서, 아래와 같이 각 작업과 서브 작업이 시작되고 완료되는 시점에 MySQL 서버의 에러 로그에 그 내용을 기록하도록 했다.

140921 13:58:44 [Warning] Online DDL : Start

140921 13:58:44 [Warning] Online DDL : Start reading clustered index of the table and create temporary files
140921 14:01:08 [Warning] Online DDL : End of reading clustered index of the table and create temporary files

140921 14:01:08 [Warning] Online DDL : Start merge-sorting index PRIMARY (1 / 2), estimated cost : 15.5547%
140921 14:03:24 [Warning] Online DDL : End of merge-sorting index PRIMARY (1/ 2)
140921 14:03:24 [Warning] Online DDL : Start building index PRIMARY (1 / 2), estimated cost : 23.3321%
140921 14:07:21 [Warning] Online DDL : End of building index PRIMARY (1 / 2)
140921 14:07:21 [Warning] Online DDL : Completed

140921 14:07:21 [Warning] Online DDL : Start merge-sorting index ix1 (2 / 2), estimated cost : 11.1119%
140921 14:09:44 [Warning] Online DDL : End of merge-sorting index ix1 (2 / 2)
140921 14:09:44 [Warning] Online DDL : Start building index ix1 (2 / 2), estimated cost : 16.6679%
140921 14:13:12 [Warning] Online DDL : End of building index ix1 (2 / 2)
140921 14:13:12 [Warning] Online DDL : Completed

Online DDL의 진행 상황 레포팅 기능을 확인하기 위해서 간단한 테스트를 해보았다.

-- // ---------------------------
-- // total rows : 141,577,818
-- // data size : 13GB
-- // index size : 7GB
-- // ---------------------------
CREATE TABLE tb_onlineddl1 (
  pk1 int(11) NOT NULL,
  pk2 bigint(20) NOT NULL,
  fd1 bigint(20) DEFAULT NULL,
  fd2 bigint(20) DEFAULT NULL,
  fd3 datetime DEFAULT NULL,
  fd4 text,
  fd5 varchar(50) DEFAULT NULL,
  fd6 bigint(20) DEFAULT NULL,
  fd7 bigint(20) DEFAULT NULL,
  PRIMARY KEY (pk1, pk2),
  UNIQUE KEY ux1 (pk2, pk1),
  KEY ix1 (fd6, fd7)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- // ---------------------------
-- // total rows : 141,577,818
-- // data size : 4GB
-- // index size : 2.5GB
-- // ---------------------------
CREATE TABLE tb_onlineddl2 (
  pk1 int(11) NOT NULL,
  pk2 bigint(20) NOT NULL,
  PRIMARY KEY (pk1, pk2),
  KEY ix1 (pk2, pk1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE tb_onlinedd1 ADD x VARCHAR(5), LOCK=NONE, ALGORITHM=INPLACE;

ALTER TABLE tb_onlinedd2 ADD x VARCHAR(5), LOCK=NONE, ALGORITHM=INPLACE;

아래의 그래프는 "Innodb_onlineddl_pct_progress" 상태 변수가 레포팅하는 Online DDL의 진행 상태를 1초 단위로 수집해서 그래프를 그려본 것이다.




그래프의 첫 부분에서는 조금 각도가 부자연스럽기는 하지만, 나머지 부분은 상당히 일관되기 진행 상태가 올라가는 것을 확인할 수 있다. ^^;


관련 소스 코드는 Kakao Github에 확인할 수 있으며, 또한 MariaDB 10.1(MDEV-6812)에서는 기본적으로 사용할 수 있을 것으로 보인다.