MariaDB ColumnStore Data Load

2018. 1. 23. 20:14

728x90

쿼리 성능 테스트를 위하여, 현재 특정 목적으로 사용되는 2억건이 넘는 테이블을 테스트 서버로 로드 하는 작업을 진행

테스트 서버 : 4Core/4G

테이블 : pageview (220,813,615 Rows)

0. Base Table 생성

비교 대상 테이블을 생성하기 위하여 MyISAM 엔진으로 테이블 생성

InnoDB보다 빠르게 생성하기 위하여 MyISAM으로 생성 후, 추후 성능 비교 시 InnoDB로 변경 예정.

4-5시간 소요 되었으며, 데이터 파일의 사이즈는 139G/인덱스 파일의 사이즈는 3G

-rw-rw---- 1 mysql mysql 46K 1월 19 09:19 pageview.frm

-rw-rw---- 1 mysql mysql 139G 1월 22 16:35 pageview.MYD

-rw-rw---- 1 mysql mysql 2.6G 1월 22 16:53 pageview.MYI

1. MyISAM --> ColumnStore (Normal)

MyISAM 테이블을 이용하여 ColumnStore테이블 생성

먼저 DDL문을 추출 후 테이블을 먼저 생성 해 놓는다.

Create table pageview_col .... engine=columnstore ;

MariaDB [columnstore]> insert into pageview_col select * from pageview ;

ERROR 2013 (HY000): Lost connection to MySQL server during query

일정 시간 수행되다가 위와 같은 에러 발생 함.

그런데 왜 일정 시간이 지난 후에 connection이 종료 되는지 파악이 안됨.

columnstore는 insert select 와 load data infile 사용 시에 내부적으로 bulk load를 하기 때문에 cpimport가 사용 됨.

[root@DTEST2 ~]# ps -ef | grep cpimport

mysql 20478 18968 19 13:18 pts/4 00:00:01 /usr/local/mariadb/columnstore/bin/cpimport -m 1 -N -s ? -e 0 -E ? columnstore pageview_col

root 20502 19302 46 13:18 pts/4 00:00:04 /usr/local/mariadb/columnstore/bin/cpimport.bin -e 0 -s ? -E ? -R /tmp/columnstore_tmp_files/BrmRpt002213180320478.rpt -m 1 -P pm1-20478 -u2d79a575-05a0-48d1-9cff-e383f3ec9a22 columnstore pageview_col

root 20572 20410 0 13:18 pts/7 00:00:00 grep --color=auto cpimport

2. MyISAM --> ColumnStore (Not use cpimport)

1번 실패 원인이 내부적으로 cpimport를 사용해서 발생 하는 것 같아서 insert select시에 cpimoprt를 사용하지 않게 설정 후 다시 작업 시작

infinidb_use_import_for_batchinsert=OFF

MariaDB [columnstore]> select @@global.infinidb_use_import_for_batchinsert , @@local.infinidb_use_import_for_batchinsert ;

+----------------------------------------------+---------------------------------------------+

| @@global.infinidb_use_import_for_batchinsert | @@local.infinidb_use_import_for_batchinsert |

+----------------------------------------------+---------------------------------------------+

| 1 | 1 |

+----------------------------------------------+---------------------------------------------+

1 row in set (0.00 sec)

MariaDB [columnstore]> set infinidb_use_import_for_batchinsert=OFF ;

Query OK, 0 rows affected (0.00 sec)

MariaDB [columnstore]> select @@global.infinidb_use_import_for_batchinsert , @@local.infinidb_use_import_for_batchinsert ;

+----------------------------------------------+---------------------------------------------+

| @@global.infinidb_use_import_for_batchinsert | @@local.infinidb_use_import_for_batchinsert |

+----------------------------------------------+---------------------------------------------+

| 1 | 0 |

+----------------------------------------------+---------------------------------------------+

1 row in set (0.01 sec)

MariaDB [columnstore]> insert into pageview_col select * from pageview ;

ERROR 1815 (HY000): Internal error: CAL0006: IDB-2008: The version buffer overflowed. Increase VersionBufferFileSize or limit the rows to be processed.

정상적으로 진행 하다가 에러 발생.

VersionBufferFileSize는 트랜잭션 일관성을 제공하는 디스크의 버전 버퍼 파일 크기를 지정 하는 파라미터로 Columnstore.xml 파일에 설정을 변경해야 하며, Restart가 되어야 함.

[root@DTEST2 etc]# cat /usr/local/mariadb/columnstore/etc/Columnstore.xml | grep VersionBufferFileSize

<!-- VersionBufferFileSize must be a multiple of 8192.

[root@DTEST2 etc]# vi /usr/local/mariadb/columnstore/etc/Columnstore.xml 1G --> 100G로 변경

[root@DTEST2 etc]# cat /usr/local/mariadb/columnstore/etc/Columnstore.xml | grep VersionBufferFileSize

<!-- VersionBufferFileSize must be a multiple of 8192.

[root@DTEST2 etc]# mcsadmin shutdownsystem y

[root@DTEST2 etc]# mcsadmin startsystem y

MariaDB [columnstore]> select @@global.infinidb_use_import_for_batchinsert , @@local.infinidb_use_import_for_batchinsert ;

+----------------------------------------------+---------------------------------------------+

| @@global.infinidb_use_import_for_batchinsert | @@local.infinidb_use_import_for_batchinsert |

+----------------------------------------------+---------------------------------------------+

| 1 | 1 |

+----------------------------------------------+---------------------------------------------+

1 row in set (0.00 sec)

MariaDB [columnstore]> set infinidb_use_import_for_batchinsert=OFF ;

Query OK, 0 rows affected (0.00 sec)

MariaDB [columnstore]> select @@global.infinidb_use_import_for_batchinsert , @@local.infinidb_use_import_for_batchinsert ;

+----------------------------------------------+---------------------------------------------+

| @@global.infinidb_use_import_for_batchinsert | @@local.infinidb_use_import_for_batchinsert |

+----------------------------------------------+---------------------------------------------+

| 1 | 0 |

+----------------------------------------------+---------------------------------------------+

MariaDB [columnstore]> insert into pageview_col select * from pageview ;

약 한시간 정도 수행 되었는데 10G 정도 밖에 데이터가 생성이 안되어서 중간에 끊음.

3. MyISAM --> ColumnStore (Explicitly use cpimport)

명시적으로 cpimport를 사용하여 넣어 보기로 함.

[root@DTEST2 ~]# mcsmysql -q -e 'select * from pageview;' -N columnstore | /usr/local/mariadb/columnstore/bin/cpimport columnstore pageview_new -s '\t'

Locale is : C

Column delimiter : \t

Using table OID 3402 as the default JOB ID

Input file(s) will be read from : STDIN

Job description file : /usr/local/mariadb/columnstore/data/bulk/tmpjob/3402_D20180122_T165631_S546783_Job_3402.xml

Log file for this job: /usr/local/mariadb/columnstore/data/bulk/log/Job_3402.log

2018-01-22 16:56:31 (2936) INFO : successfully loaded job file /usr/local/mariadb/columnstore/data/bulk/tmpjob/3402_D20180122_T165631_S546783_Job_3402.xml

2018-01-22 16:56:31 (2936) INFO : Job file loaded, run time for this step : 0.065784 seconds

2018-01-22 16:56:31 (2936) INFO : PreProcessing check starts

2018-01-22 16:56:37 (2936) INFO : PreProcessing check completed

2018-01-22 16:56:37 (2936) INFO : preProcess completed, run time for this step : 5.97217 seconds

2018-01-22 16:56:37 (2936) INFO : No of Read Threads Spawned = 1

2018-01-22 16:56:37 (2936) INFO : No of Parse Threads Spawned = 3

2018-01-22 16:56:37 (2936) INFO : Reading input from STDIN to import into table columnstore.pageview_new...

2018-01-22 18:25:42 (2936) INFO : For table columnstore.pageview_new: 220813615 rows processed and 220813615 rows inserted.

2018-01-22 18:25:42 (2936) WARN : Column columnstore.pageview_new.order_detail_name; Number of character strings truncated: 8299

2018-01-22 18:25:42 (2936) WARN : Column columnstore.pageview_new.order_detail_price; Number of character strings truncated: 3

2018-01-22 18:25:42 (2936) WARN : Column columnstore.pageview_new.order_detail_revenue; Number of character strings truncated: 3

2018-01-22 18:25:42 (2936) WARN : Column columnstore.pageview_new.order_name; Number of character strings truncated: 624

2018-01-22 18:25:42 (2936) WARN : Column columnstore.pageview_new.search_terms; Number of character strings truncated: 1

2018-01-22 18:25:42 (2936) WARN : Column columnstore.pageview_new.user_agent; Number of character strings truncated: 414

2018-01-22 18:25:42 (2936) WARN : Column columnstore.pageview_new.order_detail_cd; Number of character strings truncated: 121

2018-01-22 18:25:42 (2936) WARN : Column columnstore.pageview_new.level_item_ccd_route; Number of character strings truncated: 183

2018-01-22 18:25:42 (2936) WARN : Column columnstore.pageview_new.difficulty_item_ccd_route; Number of character strings truncated: 162

2018-01-22 18:25:42 (2936) WARN : Column columnstore.pageview_new.teacher_name_list; Number of character strings truncated: 162

2018-01-22 18:25:43 (2936) INFO : Bulk load completed, total run time : 5351.49 seconds

1시간 30분 정도에 테이블에 Bluk Load가 완료 됨.

+--------------+----------------------+-----------------+-----------------+-------------+

+--------------+----------------------+-----------------+-----------------+-------------+

| columnstore | pageview_new | 18.27 GB | 24.55 GB | 42.82 GB |

+--------------+----------------------+-----------------+-----------------+-------------+

사이즈도 약 45G 정도에서 끝남.

그럼 2번 작업도 40분 정도만 끊지 말고 기다렸으면, 정상적으로 끝났을까 하는 궁금증이 생겨서 테스트 2번 테스트를 다시 진행해 보기로 함.

4. MyISAM --> ColumnStore (1,2 재 작업)

1번과 2번 작업을 다시 진행 해 봄.

기본설정을 이용한 1번 작업을 다시 해 봄.

2번테스트에서 수정한 VersionBufferFileSize 파라미터가 영향을 주어서 정상 성공을 할까 하는 기대감.

수행 시킨 후 다음 날 출근해서 보니 에러가 나 있음.

MariaDB [columnstore]> select @@global.infinidb_use_import_for_batchinsert , @@local.infinidb_use_import_for_batchinsert ;

+----------------------------------------------+---------------------------------------------+

| @@global.infinidb_use_import_for_batchinsert | @@local.infinidb_use_import_for_batchinsert |

+----------------------------------------------+---------------------------------------------+

| 1 | 1 |

+----------------------------------------------+---------------------------------------------+

1 row in set (0.02 sec)

MariaDB [columnstore]>

MariaDB [columnstore]> insert into pageview_new_conv select * from pageview ;

ERROR 1030 (HY000): Got error -1 "Internal error < 0 (Not system error)" from storage engine Columnstore

2번 작업을 다시 진행 해 봄.

역시 에러 발생 에러 발생

MariaDB [columnstore]> set infinidb_use_import_for_batchinsert=OFF ;

Query OK, 0 rows affected (0.00 sec)

MariaDB [columnstore]> select @@global.infinidb_use_import_for_batchinsert , @@local.infinidb_use_import_for_batchinsert ;

+----------------------------------------------+---------------------------------------------+

| @@global.infinidb_use_import_for_batchinsert | @@local.infinidb_use_import_for_batchinsert |

+----------------------------------------------+---------------------------------------------+

| 1 | 0 |

+----------------------------------------------+---------------------------------------------+

1 row in set (0.00 sec)

MariaDB [columnstore]> select now() ;

+---------------------+

| now() |

+---------------------+

| 2018-01-23 09:34:18 |

+---------------------+

1 row in set (0.00 sec)

MariaDB [columnstore]> insert into pageview_new_conv select * from pageview ;

ERROR 1815 (HY000): Internal error: Lost connection to DMLProc really [1:InetStreamSocket::write error: Broken pipe -- write from InetStreamSocket: sd: 71 inet: 127.0.0.1 port: 8614]

MariaDB [columnstore]> select now();

+---------------------+

| now() |

+---------------------+

| 2018-01-23 11:51:55 |

+---------------------+

1 row in set (0.06 sec)

+--------------+----------------------+-----------------+-----------------+-------------+

+--------------+----------------------+-----------------+-----------------+-------------+

| columnstore | pageview_new | 18.27 GB | 24.55 GB | 42.82 GB |

| columnstore | pageview_new_conv | 21.00 GB | 10.95 GB | 31.95 GB |

+--------------+----------------------+-----------------+-----------------+-------------+

위 에러가 발생하면 MySQL은 정상적으로 보이나 실제 columnstore상의 DMLProc 프로세스는 에러가 발생하고 있음.

getprocessstatus Tue Jan 23 12:01:56 2018

MariaDB ColumnStore Process statuses

Process Module Status Last Status Change Process ID

------------------ ------ --------------- ------------------------ ----------

ProcessMonitor pm1 ACTIVE Tue Jan 23 09:32:10 2018 5836

ProcessManager pm1 ACTIVE Tue Jan 23 09:32:16 2018 5928

DBRMControllerNode pm1 ACTIVE Tue Jan 23 11:50:29 2018 19152

ServerMonitor pm1 ACTIVE Tue Jan 23 11:50:31 2018 19210

DBRMWorkerNode pm1 ACTIVE Tue Jan 23 11:50:31 2018 19245

DecomSvr pm1 ACTIVE Tue Jan 23 11:50:35 2018 19290

PrimProc pm1 ACTIVE Tue Jan 23 11:50:37 2018 19319

ExeMgr pm1 ACTIVE Tue Jan 23 11:50:41 2018 19413

WriteEngineServer pm1 ACTIVE Tue Jan 23 11:50:45 2018 19477

DDLProc pm1 ACTIVE Tue Jan 23 11:50:49 2018 19530

DMLProc pm1 INITIAL Tue Jan 23 11:50:53 2018 19611

mysqld pm1 ACTIVE Tue Jan 23 11:50:28 2018 19037

시간이 조금 지나면 정상적으로 되나, 실제 작업이 되지 않음.

getsystemstatus Tue Jan 23 12:47:23 2018

System columnstore-1

System and Module statuses

Component Status Last Status Change

------------ -------------------------- ------------------------

System ACTIVE Tue Jan 23 12:32:50 2018

Module pm1 ACTIVE Tue Jan 23 11:50:52 2018

MariaDB ColumnStore Process statuses

Process Module Status Last Status Change Process ID

------------------ ------ --------------- ------------------------ ----------

ProcessMonitor pm1 ACTIVE Tue Jan 23 09:32:10 2018 5836

ProcessManager pm1 ACTIVE Tue Jan 23 09:32:16 2018 5928

DBRMControllerNode pm1 ACTIVE Tue Jan 23 11:50:29 2018 19152

ServerMonitor pm1 ACTIVE Tue Jan 23 11:50:31 2018 19210

DBRMWorkerNode pm1 ACTIVE Tue Jan 23 11:50:31 2018 19245

DecomSvr pm1 ACTIVE Tue Jan 23 11:50:35 2018 19290

PrimProc pm1 ACTIVE Tue Jan 23 11:50:37 2018 19319

ExeMgr pm1 ACTIVE Tue Jan 23 11:50:41 2018 19413

WriteEngineServer pm1 ACTIVE Tue Jan 23 11:50:45 2018 19477

DDLProc pm1 ACTIVE Tue Jan 23 11:50:49 2018 19530

DMLProc pm1 ACTIVE Tue Jan 23 12:32:51 2018 19611

mysqld pm1 ACTIVE Tue Jan 23 11:50:28 2018 19037

정상적으로 프로세스가 기동 중인것 같아서, 작업 중이던 테이블 정리 작업 진행

MariaDB [columnstore]> truncate table pageview_new_conv ;

ERROR 1815 (HY000): Internal error: Cannot execute the statement. DBRM is read only!

[root@DTEST2 columnstore]# /usr/local/mariadb/columnstore/bin/viewtablelock

There is 1 table lock

Table LockID Process PID Session Txn CreationTime State DBRoots

columnstore.pageview_new_conv 1 DMLProc batchinsert 6691 10 9557 Tue Jan 23 09:34:35 2018 LOADING 1

[root@DTEST2 columnstore]# /usr/local/mariadb/columnstore/bin/cleartablelock 1

BRM error: DBRM is in READ-ONLY mode

Table lock 1 is not cleared.

서버 재 시작

[root@DTEST2 ~]# mcsadmin shutdown y

[root@DTEST2 ~]# mcsadmin start y

startsystem Tue Jan 23 12:51:21 2018

startSystem command, 'columnstore' service is down, sending command to

start the 'columnstore' service on all modules

System being started, please wait....................

System Not Ready, DMLProc is checking/processing rollback of abandoned transactions. Processing could take some time, please wait..........................................................................................................

한참 롤백 중.....

getprocessstatus Tue Jan 23 12:57:38 2018

MariaDB ColumnStore Process statuses

Process Module Status Last Status Change Process ID

------------------ ------ --------------- ------------------------ ----------

ProcessMonitor pm1 ACTIVE Tue Jan 23 12:51:31 2018 10727

ProcessManager pm1 ACTIVE Tue Jan 23 12:51:37 2018 10822

DBRMControllerNode pm1 ACTIVE Tue Jan 23 12:51:48 2018 11224

ServerMonitor pm1 ACTIVE Tue Jan 23 12:51:50 2018 11241

DBRMWorkerNode pm1 ACTIVE Tue Jan 23 12:51:50 2018 11295

DecomSvr pm1 ACTIVE Tue Jan 23 12:51:54 2018 11328

PrimProc pm1 ACTIVE Tue Jan 23 12:51:56 2018 11349

ExeMgr pm1 ACTIVE Tue Jan 23 12:52:00 2018 11448

WriteEngineServer pm1 ACTIVE Tue Jan 23 12:52:04 2018 11555

DDLProc pm1 ACTIVE Tue Jan 23 12:52:09 2018 11608

DMLProc pm1 INITIAL Tue Jan 23 12:52:13 2018 11689

mysqld pm1 ACTIVE Tue Jan 23 12:51:48 2018 11097

롤백 중일 때는 DMLProc 프로세스는 INITIAL 로 되었다가, 롤백이 완료 되는 시점에 정상으로 되고, 이후 DB는 정상적으로 동작 함.

한번 Internal Error가 나면 정상적으로 보이더라도 Reboot을 해야 정상화 되는 것 같음.

멋지게 놀아라

MariaDB ColumnStore Data Load

+ Recent posts

티스토리툴바