쿼리 성능 테스트를 위하여, 현재 특정 목적으로 사용되는 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. <VersionBufferFileSize>1GB</VersionBufferFileSize> [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. <VersionBufferFileSize>100GB</VersionBufferFileSize>
[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가 완료 됨.
+--------------+----------------------+-----------------+-----------------+-------------+ | TABLE_SCHEMA | TABLE_NAME | DATA_DISK_USAGE | DICT_DISK_USAGE | TOTAL_USAGE | +--------------+----------------------+-----------------+-----------------+-------------+ | 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]> 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)
+--------------+----------------------+-----------------+-----------------+-------------+ | TABLE_SCHEMA | TABLE_NAME | DATA_DISK_USAGE | DICT_DISK_USAGE | TOTAL_USAGE | +--------------+----------------------+-----------------+-----------------+-------------+ | 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을 해야 정상화 되는 것 같음. |