Greenplum GPText 설치/튜닝/SR

15.  Gptext
    1) gptext 개요
          - Greenplum Database엣 Sorl 텍스트 분석 엔진을 탑재하여 SQL으로 반구조화된 Raw Text 조회 및 분석 지원
         - GPText 아키텍처

# GPText장점

-     Full Text Search

a)    flexible indexing and search

b)   stemming, phonetic search, multi-lingual search

-      Scalable - one text processor  instance per segment: “MPP Text” - can linearly scale

-      Join structured & text in one query

-      Advanced Text Analytics Platform

a)    Can be run ad-hoc

b)   Supports multiple machine learning algorithms

c)    Extensible

    2) gptext 2.1 설치
          # 개요
                - 개선 사항 Gptext 의 Java_home을 임의 경로로 설정
                - 참조 링크: http://gptext.docs.pivotal.io
        
          # 설치 조건(Prerequisites)
                - RHEL /CentOS 5.X 또는 6.X (현재 7.X 지원 안됨)
                - Oracle JDK 1.8 이상 모든 노드에 사전 설치
                - nc (netcat) 사전 설치
                - lsof 사전 설치

## 사전 준비   1. 모든 노드에 Oracle JDK 설치   [root@mdw tmp]# rpm -ivh jdk-8u144-linux-x64.rpm     2. 모든 노드에 nc 설치   [root@mdw tmp]# yum install nc     3. 모든 노드에 lsof 설치   [root@mdw tmp]# yum install lsof

 

   n gptext설치

-     gptext 2.X에서는 zookeeper가 필요 함.

            -     Zookeeper 사용옵션

                                 a)       Default는 gptext 설치시 zookeeper를 설치 함.

                                 b)      기존 zookeeper를 사용해도 됨.

            -     설치예시는 gptext 설치시 zookeeper 자동설치 옵션으로 정리

-     gptext만을 위한 Java 경로 설정이 가능하며, 옵션 설정이 되지 않을 경우, 시스템의 Java_home 설정으로 인식하고, GPTEXT_JAVA_HOME 설정 시 해당 경로의 Java를 사용 함.

## gptext 설치시 gpadmin 으로 설치하면 경로 생성 못한다고 에러 발생하여, 사전에 경로 생성   1. 설치 파일 uncopressed [gpadmin@mdw ~]$ cd /home/gpadmin/ [gpadmin@mdw ~]$ tar zxf greenplum-text-2.1.1-rhel6_x86_64.tar.gz   2. 사전 경로 생성(root 계정), 경로가 없을 경우 설치시 에러 발생 [root@mdw ~]# . /usr/local/greenplum-db/greenplum_path.sh [root@mdw ~]# gpssh -h mdw -h smdw -h sdw1 -h sdw2 -h sdw3 => hostname [sdw3] sdw3.gphd.local [sdw2] sdw2.gphd.local [sdw1] sdw1.gphd.local [ mdw] mdw.gphd.local => mkdir -p /usr/local/greenplum-text-2.1.1 [sdw3] .. [ mdw] => chown gpadmin:gpadmin /usr/local/greenplum-text-2.1.1 [sdw3] .. [mdw] =>   3. 설치 config 환경 설정 [gpadmin@mdw ~]$ vi gptext_install_config GPTEXT_HOSTS="ALLSEGHOSTS"   ### GPDB primary 경로와 동일하게 일치시킴, 인스턴스가 2개 이상이면, 경로 추가필요 declare -a DATA_DIRECTORY=(/data/primary /data/primary)    ## Default 로 설치하고, 운영시 out of memory error 발생시 설정변경 하면 됨. JAVA_OPTS="-Xms1024M -Xmx2048M" GPTEXT_PORT_BASE=18983 GP_MAX_PORT_LIMIT=28983   ### Zookeeper 설치 및 구성,  BINDING 옵션은 gptext 설치시 zookeeper까지 설치하는 옵션 ZOO_CLUSTER="BINDING" declare -a ZOO_HOSTS=(sdw1.gphd.local sdw2.gphd.local sdw3.gphd.local)   ### zookeeper data  경로 ZOO_DATA_DIR="/data/primary/" ZOO_GPTXTNODE="gptext" ZOO_PORT_BASE=2188 ZOO_MAX_PORT_LIMIT=12188 ### 운영상 Java 버전을 여러 개 사용할 경우, GPText만을 위한 Java_Home 이 필요할 경우 설정 GPTEXT_JAVA_HOME=/usr/java/jdk1.8.0_144 4. gptext 설치 ### Binary 설치 [gpadmin@mdw ~]$ ./greenplum-text-2.1.1-rhel6_x86_64.bin -c gptext_install_config   ### gptext 함수 설치 예시 > $ gptext-installsql database [database2 ... ] [gpadmin@mdw ~]$ gptext-installsql gpadmin 5. 환경 설정 적용 [gpadmin@mdw ~]$ vi ~/.bash_profile source  /usr/local/greenplum-text-2.1.1/greenplum-text_path.sh    ## 추가    [gpadmin@mdw ~]$ source ~/.bash_profile     6. gptext 구동 [gpadmin@mdw ~]$ zkManager start [gpadmin@mdw ~]$  gptext-start

 

n  gptext상태 확인

[gpadmin@mdw ~]$ gptext-state 20170728:00:57:24:126518 gptext-state:mdw:gpadmin-[INFO]:-Execute GPText state ... 20170728:00:57:24:126518 gptext-state:mdw:gpadmin-[INFO]:-Check zookeeper cluster state ... 20170728:00:57:24:126518 gptext-state:mdw:gpadmin-[INFO]:-Check GPText cluster status... 20170728:00:57:24:126518 gptext-state:mdw:gpadmin-[INFO]:-Current GPText Version: 2.1.1 20170728:00:57:25:126518 gptext-state:mdw:gpadmin-[INFO]:-All nodes are up and running. 20170728:00:57:25:126518 gptext-state:mdw:gpadmin-[INFO]:------------------------------------------------ 20170728:00:57:25:126518 gptext-state:mdw:gpadmin-[INFO]:-Index state. 20170728:00:57:25:126518 gptext-state:mdw:gpadmin-[INFO]:------------------------------------------------ 20170728:00:57:25:126518 gptext-state:mdw:gpadmin-[INFO]:-   state    index count 20170728:00:57:25:126518 gptext-state:mdw:gpadmin-[INFO]:-   Green      1 20170728:00:57:29:126518 gptext-state:mdw:gpadmin-[INFO]:-Done. [gpadmin@mdw ~]$

 

n  gptext상태 확인

 

[gpadmin@mdw ~]$ gptext-state 20170728:00:57:24:126518 gptext-state:mdw:gpadmin-[INFO]:-Execute GPText state ... 20170728:00:57:24:126518 gptext-state:mdw:gpadmin-[INFO]:-Check zookeeper cluster state ... 20170728:00:57:24:126518 gptext-state:mdw:gpadmin-[INFO]:-Check GPText cluster status... 20170728:00:57:24:126518 gptext-state:mdw:gpadmin-[INFO]:-Current GPText Version: 2.1.1 20170728:00:57:25:126518 gptext-state:mdw:gpadmin-[INFO]:-All nodes are up and running. 20170728:00:57:25:126518 gptext-state:mdw:gpadmin-[INFO]:------------------------------------------------ 20170728:00:57:25:126518 gptext-state:mdw:gpadmin-[INFO]:-Index state. 20170728:00:57:25:126518 gptext-state:mdw:gpadmin-[INFO]:------------------------------------------------ 20170728:00:57:25:126518 gptext-state:mdw:gpadmin-[INFO]:-   state    index count 20170728:00:57:25:126518 gptext-state:mdw:gpadmin-[INFO]:-   Green      1 20170728:00:57:29:126518 gptext-state:mdw:gpadmin-[INFO]:-Done. [gpadmin@mdw ~]$

 

n solr 상태 확인

[gpadmin@mdw ~]$ ssh sdw1 [gpadmin@sdw1 solr0]$  cd /data/primary/ [gpadmin@sdw1 primary]$ ls gpseg0  solr0  zoo0 [gpadmin@sdw1 primary]$ cd solr0/ [gpadmin@sdw1 solr0]$ ls data  log4j.properties  logs  solr-18983.pid  solr.in.sh $ [gpadmin@sdw1 solr0]$ ifconfig eth3      Link encap:Ethernet  HWaddr 00:0C:29:D3:6C:FA             inet addr:172.16.150.168  Bcast:172.16.150.255  Mask:255.255.255.0 $    ### Solr 인스턴스 확인

 

         3)   데이터 로딩

n 참조 링크

  -       http://www.ahschulz.de/enron-email-data/

               -       http://metajungle.net/2014/02/database-postgres-full-text-search/

n 테스트 파일 다운로드

 

 

$ wget http://www.ahschulz.de/pub/R/data/enron-mysqldump_v5.sql.gz perl -i -pe 's/`//g' enron-mysqldum_v5.sql cat enron-mysqldum_v5.sql | grep -v "#" > enron-mysqldum_v5.sql.new ð  Mysql Format 이므로, DDL 추출, Insert 구문 수정 필요

   n    테이블 생성
    

## 주의 : ID 컬럼에는 반드시 bigint 으로 선언해야 함.   drop TABLE if exists employeelist; drop TABLE if exists message; drop TABLE if exists recipientinfo; drop TABLE if exists referenceinfo;   CREATE TABLE employeelist ( eid int  , firstName varchar(31) NOT NULL DEFAULT '', lastName varchar(31) NOT NULL DEFAULT '', Email_id varchar(31) NOT NULL DEFAULT '', Email2 varchar(31) DEFAULT NULL, Email3 varchar(31) DEFAULT NULL, EMail4 varchar(31) DEFAULT NULL, folder varchar(31) NOT NULL DEFAULT '', status varchar(50) DEFAULT NULL ) with (appendonly=true, compresslevel=5) distributed by (eid) ; CREATE TABLE message ( mid bigint NOT NULL DEFAULT 0, sender varchar(127) NOT NULL DEFAULT '', date timestamp DEFAULT NULL, message_id varchar(127) DEFAULT NULL, subject text, body text, folder varchar(127) NOT NULL DEFAULT '' ) with (appendonly=true, compresslevel=5) distributed by (mid); CREATE TABLE recipientinfo ( rid bigint  NOT NULL DEFAULT '0', mid bigint  NOT NULL DEFAULT '0', rtype varchar(100) DEFAULT NULL, rvalue varchar(127) DEFAULT NULL, dater timestamp DEFAULT NULL ) with (appendonly=true, compresslevel=5) distributed by (rid); CREATE TABLE  referenceinfo ( rfid  bigint    , mid  bigint, reference text ) with (appendonly=true, compresslevel=5) distributed by (rfid) ;

 

n    테스트 데이터 확인

$ psql -f enron-mysqldum_v5.sql.new ## 데이터 건수 확인 select count(*) from  employeelist; select count(*) from  message; select count(*) from recipientinfo; select count(*) from referenceinfo;   ## 테이터 확인 select * from employeelist limit 100; select * from message limit 100; select * from recipientinfo limit 100; select * from referenceinfo limit 100;

 

n Id 컬럼 index 생성

create index idx_message_mid on message (mid);

 

4)        Gptext 인덱싱 및 Search

n    인덱싱

-  Return 값이 t 으로 나와야 함.

1. Create the empty Index select * from gptext.create_index('public', 'message', 'mid', 'body'); 2. Populate the Index select * from gptext.index(table(select * from public.message), 'gpadmin.public.message'); 3. Commit the Index select * from gptext.commit_index('gpadmin.public.message');   4. 쿼리 결과 확인 SELECT a.mid, a.sender, a.date, a.subject, a.body         , b.score         , row_number () over (order by b.score desc) rnk FROM   message a         , gptext.search(TABLE(SELECT 1 SCATTER BY 1), 'gpadmin.public.message' , 'an amount', null, 'rows=5') b WHERE  a.mid = b.id ORDER by b.score DESC;

 

6)        Gptext 인덱싱 및 Search (멀티 컬럼 serarching 기능)

n    샘플 데이터 생성

drop table if exists sec_code_info_test ; create table sec_code_info_test with (appendonly=true, compresslevel=5) as     select  i::bigint id      , chr(i%2+65)||chr(i%3+65)||trim(to_char(i%1000 + 75, '0000')) eqp_id      , chr(i%2+67)||chr(i%3+69)||chr(round(random()*100)::int%15+65)||trim(to_char(i%20000+32, '00000')) lot_id      , chr(i%5+70)||chr(i%2+70)||chr(i%10+70)||chr(i%8+70)||chr(round(random()*100)::int%15+65)||trim(to_char(i%200+654, '0000')) wafer_id      , 'AR_'||chr(i%2+80)||trim(to_char(i%5, '0')) area_id      , chr(i%10+69)||chr(i%10+78)||chr(i%3+80)||chr(i%15+65)||chr(round(random()*100)::int%15+65)||trim(to_char(i%100000+532, '000000')) param_id      , chr(i%3+81)||chr(i%3+81)||chr(i%10+84)||trim(to_char(i%200+12, '000')) step_id      , round((i%10000 * random())::int       )::int param_cnt      , round((i%100 * random())::numeric, 1 ) val  from    generate_series(1, 1000000) i  distributed by (id) ; create index idx_sec_code_info_test_id on sec_code_info_test(id) ;

   n    Gptext 인덱스 생성 및 searching

-- gptext인덱스 생성 select * from gptext.drop_index('public', 'sec_code_info_test'); --gptext1.3 select * from gptext.create_index('public', 'sec_code_info_test', 'id', '*'); select * from gptext.index(table(select * from public.sec_code_info_test), 'gpadmin.public.sec_code_info_test'); select * from gptext.commit_index('gpadmin.public.sec_code_info_test'); -- 조회 select a.*, b.score From   sec_code_info_test a,        gptext.search(table(select 1 scatter by 1), 'gpadmin.public.sec_code_info_test', '*:*', '{eqp_id:*C0*,  step_id:SSV014, lot_id:CG*, param_cnt:[0 TO 1000] }') b Where a.id = b.id   --조회시 주의 사항 -- Range 검색시에는 반드시 TO (대문자로 수행해야 함) --검증 select * from   sec_code_info_test where  eqp_id like '%C0%' and    step_id = 'SSV014' and    lot_id like 'CG%' and    param_cnt between 0 and 1000;

7)        Delimeter 적용

    n    참조 : https://cwiki.apache.org/confluence/display/solr/Tokenizers
                 n    샘플 데이터 생성

drop table if exists sec_code_info ; create table sec_code_info with (appendonly=true, compresslevel=5) as       select  i::bigint id      , chr(i%2+65)||chr(i%3+65)||trim(to_char(i%1000 + 75, '0000')) eqp_id      , chr(i%2+67)||chr(i%3+69)||chr(round(random()*100)::int%15+65)||trim(to_char(i%20000+32, '00000'))||'-'|| chr(i%5+70)||chr(i%2+70)||chr(i%10+70) lot_id      , chr(i%5+70)||chr(i%2+70)||chr(i%10+70)||chr(i%8+70)||chr(round(random()*100)::int%15+65)||trim(to_char(i%200+654, '0000')) ||' '||chr(i%3+81)||chr(i%3+81)||chr(i%10+84) wafer_id      , 'AR_'||chr(i%2+80)||trim(to_char(i%5, '0')) area_id      , chr(i%10+69)||chr(i%10+78)||chr(i%3+80)||chr(i%15+65)||chr(round(random()*100)::int%15+65)||trim(to_char(i%100000+532, '000000'))||'_'||chr(i%10+69)||chr(i%10+78)||chr(i%3+80) param_id      , chr(i%3+81)||chr(i%3+81)||chr(i%10+84)||trim(to_char(i%200+12, '000')) step_id      , round((i%10000 * random())::int       ) param_cnt      , round((i%100 * random())::numeric, 1 ) val     from    generate_series(1, 10000) i     distributed by (id) ; drop table if exists sec_code_info_gptext; create table sec_code_info_gptext with (appendonly=true, compresslevel=5) as select id,               eqp_id||'^'||               lot_id||'^'||               wafer_id||'^'||               area_id||'^'||               param_id||'^'||               step_id||'^'||               trim(to_char(val, '99990')) as body,               eqp_id, lot_id, wafer_id, area_id, param_id, step_id, param_cnt, val from   sec_code_info distributed by (id) ;

 

n    Gptext 인덱스 생성

-- gptext인덱스 생성 select * from gptext.drop_index('public', 'sec_code_info_gptext'); select * from gptext.create_index('public', 'sec_code_info_gptext', 'id', 'body');

 

n    구분자 변경을 위한schema.xml 설정 변경

## Master Node에서 수행 $ gptext-config -f schema.xml -i gpadmin.public.sec_code_info_gptext -e vi ##### 확인할 사항 ##### Vi 가 오픈되면서 상단 96 라인 에 아래와 같이 각 컬럼별 Type을 확인 ð  대부분의 문자열은 text_intl Type 임 … #####  #### 수정할 사항 #### Search : name="text_intl" / name="text_intl" ### 변경 전 ### text_intl " positionIncrementGap="100">                             ....                                    ....       ### 변경 후 ### 주위 사항: parttern에서 특수 문자가 들어갈 경우 \ (역슬래쉬 추가해야함) text_intl " positionIncrementGap="100">                       />        ....                             />        ....       ### 수정 후 #### :wq!

n    구분자 변경을 위한schema.xml 설정 변경

     

-- terms 활성화 SELECT * FROM gptext.enable_terms('gpadmin.public.sec_code_info_gptext', 'body'); --인덱스 population select * from gptext.index(                        table(select id, body, eqp_id, lot_id, wafer_id, area_id, param_id, step_id, param_cnt, val from public.sec_code_info_gptext)                     , 'gpadmin.public.sec_code_info_gptext'); --인덱스 Commit select * from gptext.commit_index('gpadmin.public.sec_code_info_gptext'); --terms 확인 select id, body from sec_code_info_gptext where  eqp_id = 'AC0077'                                          id Body 2       AC0077^CGC00034-HFH^HFHHE0656 SSV^AR_P2^GPRCE000534_GPR^SSV014^1 select * from gptext.terms(table(select 1 scatter by 1), 'gpadmin.public.sec_code_info_gptext', 'body', 'eqp_id:AC0077', null) order by id, positions ; ID term posions 비고 2 ac0077 {0}   2 cgc00034-hfh {1} ‘-‘  포함됨. 2 hfhhe0656 ssv {2} white space 포함 됨. 2 ar_p2 {3}   2 gprce000534_gpr {4} ‘_’ 포함 됨 2 ssv014 {5}   2 1 {6}   --text search 쿼리 select a.*, b.score From   sec_code_info_gptext a,        gptext.search(table(select 1 scatter by 1), 'gpadmin.public.sec_code_info_gptext', '*:*', '{eqp_id:*C0*,  step_id:SSV014, lot_id:CG*, param_cnt:[0 TO 1000] }') b Where a.id = b.id ;

8)        튜닝

#  성능 이슈

                -     Greenplum Database에서 Heavy Query로 부하를 받는 경우
                -     GPText에서 Java Heap space를 작게 잡을 수 있음.
                -     GPText query 수행시 많은 데이터를 처리하는 경우(indexing or searching)
                -     GPText 설정에서 Java Heap 메모리가 부족한 경우
                -  Garbage Collection이 발생하는 경우

#    Out of Memory 발생 이슈

                -       Java Heap Space for GPText Solr instances 설정 변경

                -       https://discuss.pivotal.io/hc/en-us/articles/229801888-How-to-set-Java-Heap-Space-for-GPText-Solr-instances

ㅁ 에러 메시지 SQL_ERROR. Class: 'XX', Condition: '000', Detail: 'ERROR: Error sending data. java.lang.OutOfMemoryError: Java heap space (solr_curl.c:826) (seg64 slice5 sdw11:40004 pid=10240) (cdbdisp.c:1326); Error while executing the query'   ㅁ 경로 설명 $SEGMENT_DATA_DIRECTORY : 특정 세그먼트의 데이터 경로 Ex) /data1/primary/gpseg0   1. 현재 프로세스의 java heap 메모리 사이즈 확인 $ ps -ef | grep solr gpadmin 7893 1 0 17:52 ? 00:00:05 /usr/local/greenplum-db/ext/jre-1.6.0_32/jre1.6.0_32/bin/java -server -Xms1024M -Xmx2048M -Dlog4j.configuration=file:/gpdata/segments/gpseg0/solr/lib/log4j.properties -Djetty.home=/usr/local/greenplum-text-1.3.0.3/greenplum-solr -Dsolr.solr.home=/gpdata/segments/gpseg0/solr -Djetty.port=39900 -jar /usr/local/greenplum-text-1.3.0.3/greenplum-solr/start.jar   $ cat $SEGMENT_DATA_DIRECTORY/solr/jetty.conf | grep JAVA export JAVA_OPTS="-Xms1024M -Xmx2048M"   2. Java Heap Space 사이즈 변경 $ source /usr/lib/greenplum-text/greenplum-text_path.sh $ gptext-config -o "-Xms1024M -Xmx4096M"   3. 설정 파일 확인 (jetty.conf) $ cat $SEGMENT_DATA_DIRECTORY/solr/jetty.conf | grep JAVA export JAVA_OPTS="-Xms1024M -Xmx4096M" 4.  GPText Restart $ gptext-stop $ gptext-start 5. 프로세스로 적용 확인 $ ps -ef | grep solr gpadmin 7893 1 0 17:52 ? 00:00:05 /usr/local/greenplum-db/ext/jre-1.6.0_32/jre1.6.0_32/bin/java -server -Xms1024M -Xmx4096M -Dlog4j.configuration=file:/gpdata/segments/gpseg0/solr/lib/log4j.properties -Djetty.home=/usr/local/greenplum-text-1.3.0.3/greenplum-solr -Dsolr.solr.home=/gpdata/segments/gpseg0/solr -Djetty.port=39900 -jar /usr/local/greenplum-text-1.3.0.3/greenplum-solr/start.jar:

9)        로그 수집(SR)

n    로그 수집 방법

-       SR을 위하여 기본적인 정보 로그 수집 방법

# Master Node 0. Serial number (if it is EMC DCA platform) 1. Collect pg_log/ 2. /home/gpadmin/gpAdminsLogs/gptext-*.log 3. Outputs of below commands $ gpconfig -s gp_interconnect_type $ gpconfig -s gp_vmem_protect_limit $ cat $MASTER_DATA_DIRECTORY/postgresql.conf | grep gptext   4. From psql =# select version(); =# select * from gptext.version(); =# select * from gp_segment_configuration order by content; =# select * from gp_configuration_history order by 1 desc;   5. gptext-state $ gptext-state  # Identify hostname & solr port reported ‘Down' : 20161102:10:13:27:022809 gptext-state:mdw:gpadmin-[INFO]:-  Down Primary Solr Instances 20161102:10:13:27:022809 gptext-state:mdw:gpadmin-[INFO]:---------------------------------------------------------- 20161102:10:13:27:022809 gptext-state:mdw:gpadmin-[INFO]:-    sdwXX-1:nnnnn 20161102:10:13:27:022809 gptext-state:mdw:gpadmin-[INFO]:----------------------------------------------------------   # Segment Node 1. Collect pg_log ($SEGMENT_DATA_DIRECTORY/pg_log/) 2. Collect gptext.log ($SEGMENT_DATA_DIRECTORY/solr/logs/) 3. Collect below process/memory information - $ ps aux $ ps -efs $ top -n 1 $ jps -v $ jps -v | grep nnnnn # identify & pg_log directory. $ jmap -J-d64 -heap pid   # Run jstack few times $ jstack    # Run strace few times. If it runs away Control-C immediately. $ strace -p    # Collect process stack & trigger core file generation $ gdb -p (gdb) bt full    # capture output of this command (gdb) generate-core-file /var/crash/core.     $ cat /proc/meminfo    4. Check & collect the core file(/var/crash/core.) using the packcore utility    # gptext Restart 시 (master) $ gptext-stop $ gptext-start