그래서 특정 테이블의 구조에 제한적인 재귀 처리를 스토어드 프로시져로 만들어서 성능 테스트를 해보았다. 우선 아래와 같은 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를 선정하는 기준이 될 수 있는지는 조금 더 고려가 필요해 보인다.
댓글 없음:
댓글 쓰기