개요

8vCPU, 16GB 메모리 사양에서 SingleStore 의 Row Generation 은 7억건에서 메모리 부족 에러가 발생했습니다.

https://velog.io/@runway053/Row-Generation-성능-비교-SingleStore-Oracle-MySQL-PostgreSQL

그 이유는 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));

1차 튜닝

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

먼저 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 |
+-----------------------------------------------------------------------------------------+

2차 튜닝

1차 튜닝을 해도 10억건 정도의 row 를 생성할 때 에러가 나는 이유는 단일 Array 로 모든 row 를 저장하려다 보니 발생하는 경우 불가피한 경우인데 이를 회피하는 방법을 알아 보겠습니다.

아이디어는 간단한데 예전부터 DBA 나 개발자들이 잘 알고 있는 Cross Join (Cartesian Product) 를 이용하는 것입니다.

아래 테스트에서는 10억건이 에러없이 생성되고 시간은 2.74초가 소요되었습니다.

singlestore> select count(*) from gen_rows(100000) a, gen_rows(10000) b;
+------------+
| count(*)   |
+------------+
| 1000000000 |
+------------+
1 row in set (2.74 sec)

그런데 이렇게 하면 row 의 갯수는 10억건인데 컬럼은 중복된 값이 발생하고 그 값은 최대 100000 이 됩니다.

이 경우는 a 테이블 또는 b 테이블 중 하나를 기준으로 삼아 cross join 되는 대상의 (row 건수 -1 )를 곱한 후 자신의 컬럼값을 더해 주면 해결됩니다. 작은 수로 예제를 보시죠.

singlestore> select (a.rn-1)*100+b.rn new_rn from gen_rows(10) a, gen_rows(100) b order by 1 ;
또는
singlestore> select (a.rn-1)*10+b.rn new_rn from gen_rows(100) a, gen_rows(10) b order by 1 ;
+--------+
| new_rn |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
. . .
|    996 |
|    997 |
|    998 |
|    999 |
|   1000 |
+--------+
1000 rows in set (0.00 sec)

이제 생성된 row 의 컬럼도 distinct 하게 만드는 방법도 알아보았으니 100억건을 한번 만들어 보고 이번 포스트를 마치겠습니다. 제 테스트 장비에서는 51초 정도의 시간이 소요되었습니다.

singlestore> select count(*) from gen_rows(100000) a, gen_rows(100000) b;
+-------------+
| count(*)    |
+-------------+
| 10000000000 |
+-------------+
1 row in set (51.25 sec)

---

Image by starline on Freepik