Row Generation 튜닝#1 - SingleStore

※ 테스트 장비 사양 - 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));

 

이제 정렬이 필요없도록 튜닝을 해보겠습니다.

먼저 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));

 

 

이제 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`

 

전/후 실행계획을 비교해 보면 확실히 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 |
+-----------------------------------------------------------------------------------------+

 

이상으로 SingleStore 의 MPSQL 에서 CREATE_ARRAY 함수를 이용해 좀더 빠르고 메모리를 적게 사용하는 Row Generation 방법에 대해 알아 보았습니다.

​

※ 테스트 장비 사양 - 8vCPU, 16GB