문서번호 : 11-2132728
※ 테스트 장비 사양 - 8vCPU, 16GB
이전 포스트에서 SingleStore 의 Row Generation 은 7억건에서 메모리 부족 에러가 발생했습니다.
그 이유는 create_array 및 table 로 변환한 모든 row 를 대상으로 row_number() 함수로 정렬을 수행하기 때문에 부가적인 메모리가 더 필요하기 때문입니다. 물론 정렬 작업 때문에 시간도 조금 더 필요합니다.
create or replace function gen_rows(n bigint)
returns table as return
select row_number() over () as rn from table(create_array(n):>array(bigint));
SQL
복사
이제 정렬이 필요없도록 튜닝을 해보겠습니다.
먼저 to_array 함수를 하나 만들고 여기서 create_array 를 이용해 array 를 인수로 받은 x 만큼의 크기로 선언 및 초기화합니다.
이후 FOR LOOP 문에서 array 의 값을 1부터 순서대로 지정한 후 최종적으로 array 를 반환합니다.
아래와 같이 to_array 함수를 이용하여 row_number() 를 사용하지 않는 gen_rows2 TVF(Table Valued Function) 함수를 생성합니다. SQL의 길이가 크게 줄어들었으므로 gen_rows2() 함수를 만들지 않고 간단하게 SELECT 문만 사용해도 괜찮습니다.
DELIMITER //
CREATE OR REPLACE FUNCTION to_array (x BIGINT)
RETURNS ARRAY(INT) AS
DECLARE
arr ARRAY(INT) = CREATE_ARRAY(x);
BEGIN
FOR i IN 0..x-1 LOOP
arr[i] = i+1;
END LOOP;
RETURN arr;
END //
DELIMITER ;
create or replace function gen_rows2(n bigint)
returns table as return
select table_col as rn from table(to_array(n));
또는
select table_col as rn from table(to_array(n));
SQL
복사
이제 7억건 이상의 row 를 생성해 보겠습니다. 10억건 정도에서 메모리 부족 에러가 발생하므로 이전보다 3억건 정도 더 생성해 낼 수 있게 되었습니다. row 생성 시간도 정렬 작업을 없앤 덕분에 이전에 비해 크게 감소하였습니다.
singlestore> select count(*) from gen_rows2(700000000);
+-----------+
| count(*) |
+-----------+
| 700000000 |
+-----------+
1 row in set (4.82 sec)
singlestore> select count(*) from gen_rows2(800000000);
+-----------+
| count(*) |
+-----------+
| 800000000 |
+-----------+
1 row in set (6.60 sec)
singlestore> select count(*) from gen_rows2(900000000);
+-----------+
| count(*) |
+-----------+
| 900000000 |
+-----------+
1 row in set (6.34 sec)
singlestore> select count(*) from gen_rows2(1000000000);
ERROR 1712 (HY000): Unhandled exception
Type: ER_MEMSQL_OOM (1712) (callstack may be corrupt)
Message: Memory used by MemSQL (7947.88 Mb) has reached the 'maximum_memory' setting (14141 Mb) on this node.
Possible causes include (1) available query execution memory has been used up for table memory (in use table memory: 7677.75 Mb) and (2) the query is large and complex and requires more query execution memory than is available (in use query executio
Callstack:
#0 Line 8 in `airportdb`.`to_array`
SQL
복사
전/후 실행계획을 비교해 보면 확실히 Window Function인 row_number() 함수가 사라진 것을 알 수 있습니다.
singlestore> explain select * from gen_rows(10);
+--------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------+
| Project [gen_rows.rn] est_rows:108 |
| TableScan 0tmp AS gen_rows storage:list stream:yes est_table_rows:108 est_filtered:108 |
| Project [rn] est_rows:108 |
| Window [ROW_NUMBER() OVER () AS rn] |
| GeneratedTable table_func:[TABLE((CREATE_ARRAY((10:>bigint(20) NULL)):>array(bigint(20) NULL)))] alias:table |
+--------------------------------------------------------------------------------------------------------------+
singlestore> explain select * from gen_rows2(10);
+-----------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------+
| Project [table.table_col AS rn] |
| GeneratedTable table_func:[TABLE(`demo`.`to_array`((10:>bigint(20) NULL)))] alias:table |
+-----------------------------------------------------------------------------------------+
SQL
복사
이상으로 SingleStore 의 MPSQL 에서 CREATE_ARRAY 함수를 이용해 좀더 빠르고 메모리를 적게 사용하는 Row Generation 방법에 대해 알아 보았습니다.
※ 테스트 장비 사양 - 8vCPU, 16GB
