현재 mysql를 사용 중이며, 500만 건 text, varchar 등 혼합된 구조의 테이블이 10개 정도 존재합니다.
이들 대상으로 통합검색을 사이트에 구현해야 합니다.
몇가지 후보군 중에 스핑크스(sphinx)가 적당하다 판단하고 설치부터 테스트, 구현까지의 과정을 포스팅합니다.
아래는 스핑스크 테스트할 사용 계획입니다.
- mysql에는 zip_code라는 우편번호 테이블이 존재합니다.
- 스핑크스를 이용하여 zip_code의 text, varchar 컬럼을 인덱스 로드합니다. 인덱스 로드한 이름은 sph_zip_code로 명명합니다.
- sphinxQL 이용하여 쿼리 테스트를 합니다.
- sphinxSE 이용하여 zip_code와 sph_zip_code를 innser join 하여 통합검색 서비스를 합니다.
스핑크스의 다음 세 가지 액세스 방법 중 하나를 사용하여 검색데몬(searchd)에 액세스 할 수 있습니다.
- sphinxQL방식 : 스핑크스 자체의 mysql 네트워크 프로토콜 구현
- sphinxSE 방식 : 플러그인 가능한 스토리지 엔진이 있는 서버를 사용(ex: mysql)
- sphinxAPI방식 : 네이티브 검색 API 사용 (php, java 등)
계속 연재될 sphinx 포스팅에는 1, 2번을 집중으로 다룰것이며, 3번 방식은 설명하지 않습니다.
[설치 환경]
OS : CentOS 7 64bit
mysql : MariaDB 10.2.18
sphinx : Sphinx 2.2.11-release
선행작업
스핑크스를 설치하기 이전에 반드시 mysql이 설치되어있어야 합니다.
정확히 말하면 스핑크스는 mysql이 필요가 없어도 됩니다.
하지만, 이 포스팅 자체가 mysql의 테이블을 스핑크스로 index를 만들고,
mysql에서 스핑크스를 연동하여 통합검색을 만드는게 목표이기 때문에 필요합니다.
위의 스핑크스 사용계획을 다시 읽어봐주세요.
mysql에 zip_code라는 테이블을 샘플로 설명이 되기 때문에 zip_code 테이블을 생성하고 데이터를 insert 해놔야 합니다.
sphinx 설치
yum install postgresql-libs unixODBC
설치파일 가져오기 (CentOS 7 인경우..)
wget -q http://sphinxsearch.com/files/sphinx-2.2.11-1.rhel7.x86_64.rpm
스핑크스 설치
rpm -Uvh sphinx-2.2.11-1.rhel7.x86_64.rpm
데이터(인덱스파일) 저장할 공간 만들기
mkdir /var/lib/sphinx/data
wget이 없다면 yum으로 설치하세요.
yum install wget
wget 으로 rpm 가져올수 없으면 URL이 변경되었을 수도 있습니다. 직접 다운로드하세요.
http://sphinxsearch.com/downloads/archive/
Archived releases | Sphinx
Sphinx 2.2.11-release (r95ae9a6; Jul 19, 2016)
sphinxsearch.com
Sphinx 2.2.11-release (r95ae9a6; Jul 19, 2016) --> RHEL/CentOS 7.x Source RPM 받았습니다.
sphinx 환경설정
환경설정 파일
vi /etc/sphinx/sphinx.conf
하나씩 설명해보겠습니다.
source
#############################################################################
## source settings
#############################################################################
source sph_zip_code
{
type = mysql
sql_host = mysql ip주소 (localhost)
sql_user = 계정명
sql_pass = 패스워드
sql_db = DB명
sql_port = 3306
sql_query_pre = SET NAMES utf8
sql_query_pre = SET SESSION query_cache_type=OFF
sql_query = \
SELECT seq, zip_code, add1, add2, add3, address \
FROM zip_code
sql_field_string = zip_code
sql_field_string = add1
sql_field_string = add3
sql_field_string = address
}
#############################################################################
source 부분은 어떤 데이터를 인덱스 시킬것인지 설정하는 부분입니다.
mysql에는 zip_code라는 테이블이 존재하며, 스핑크스를 통해 인덱싱을 시킬 예정입니다.
source명 : 실제 테이블이름앞에 "sph_" prefix 했습니다.
sql_qurey_pre : sql_query 실행하기 전에 실행됩니다. 인코딩 설정, 내부 카운트 업데이트 등에 사용 할 수 있습니다.
sql_query : mysql에서 인덱스를 걸고 싶은 쿼리를 작성하면 됩니다.
주의해야 할 점은 첫번째 컬럼은 반드시 정수타입의 Primary Key 또는 Unique Key를 작성해야 합니다.
sql_field_string : 열의 전체 텍스트 색인화하고 특성으로 지정할 수 있습니다.
sql_query 에서 작성한 컬럼을 인덱스를 생성시키고, sql_field_string 값을 표현 및 추후 where조건 절에서 사용할 수 있도록 해줍니다.
인덱스 걸고 싶은 컬럼은 그냥 설정하는걸로 합니다.
index
#############################################################################
## index settings
#############################################################################
index sph_zip_code
{
source = sph_zip_code
path = /var/lib/sphinx/data/sph_zip_code
docinfo = extern
charset_table = 0..9, A..Z->a..z, _, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F
ngram_len = 1
min_infix_len = 2
ngram_chars = U+4E00..U+9FBB, U+3400..U+4DB5, U+20000..U+2A6D6, U+FA0E, U+FA0F, U+FA11, U+FA13, U+FA14, U+FA1F, U+FA21, U+FA23, U+FA24, U+FA27, U+FA28, U+FA29, U+3105..U+312C, U+31A0..U+31B7, U+3041, U+3043, U+3045, U+3047, U+3049, U+304B, U+304D, U+304F, U+3051, U+3053, U+3055, U+3057, U+3059, U+305B, U+305D, U+305F, U+3061, U+3063, U+3066, U+3068, U+306A..U+306F, U+3072, U+3075, U+3078, U+307B, U+307E..U+3083, U+3085, U+3087, U+3089..U+308E, U+3090..U+3093, U+30A1, U+30A3, U+30A5, U+30A7, U+30A9, U+30AD, U+30AF, U+30B3, U+30B5, U+30BB, U+30BD, U+30BF, U+30C1, U+30C3, U+30C4, U+30C6, U+30CA, U+30CB, U+30CD, U+30CE, U+30DE, U+30DF, U+30E1, U+30E2, U+30E3, U+30E5, U+30E7, U+30EE, U+30F0..U+30F3, U+30F5, U+30F6, U+31F0, U+31F1, U+31F2, U+31F3, U+31F4, U+31F5, U+31F6, U+31F7, U+31F8, U+31F9, U+31FA, U+31FB, U+31FC, U+31FD, U+31FE, U+31FF, U+AC00..U+D7A3, U+1100..U+1159, U+1161..U+11A2, U+11A8..U+11F9, U+A000..U+A48C, U+A492..U+A4C6
}
#############################################################################
index 설정 부분입니다. 하나씩 살펴 보도록 하겠습니다.
index명 : source명과 동일하게 합니다.
source : source명 동일해야 합니다.
path : 인덱스 설정할 경로를 설정합니다.
/var/lib/sphinx/data/ 까지가 경로이고 sph_zip_code은 파일명이 됩니다.
참고로 인덱스 작업을 하면 /var/lib/sphinx/data/ 에 아래의 파일들이 생성됩니다.
-rw-r--r-- 1 root root 247268 12월 5 11:17 sph_bbs.spa
-rw-r--r-- 1 root root 31902 12월 5 11:17 sph_bbs.spe
-rw-r--r-- 1 root root 402996 12월 5 11:17 sph_bbs.spi
-rw------- 1 root root 0 12월 5 11:17 sph_bbs.spl
-rw-r--r-- 1 root root 4103471 12월 5 11:17 sph_bbs.spp
-rw-r--r-- 1 root root 7739914 12월 5 11:17 sph_bbs.sps
-rw-r--r-- 1 root root 0 12월 5 11:17 sph_bbs.spm
-rw-r--r-- 1 root root 0 12월 5 11:17 sph_bbs.spk
-rw-r--r-- 1 root root 1548 12월 5 11:17 sph_bbs.sph
-rw-r--r-- 1 root root 3740999 12월 5 11:17 sph_bbs.spd
docinfo : docinfo가 디스크와 RAM에 물리적으로 저장되는 방법을 저장합니다. 기본값은 extern입니다.
charset_table, ngram_chars는 한글까지 검색되도록 하기 위해서는 똑같이 작성 해주세요.
ngram_len : 1로 설정하면 됩니다. 한단어씩 색인됩니다.
좀더 자세한 내용을 원하시면 "ngram"을 구글에서 찾아보세요.
min_infix_len : 검색할때 반드시 매칭되어야 하는 최수 문자수
#############################################################################
## source settings
#############################################################################
source sph_zip_code {
type = mysql
sql_host = mysql ip
sql_user = mysql 계정
sql_pass = mysql 패스워드
sql_db = test
sql_port = 3306
sql_query_pre = SET NAMES utf8
sql_query_pre = SET SESSION query_cache_type=OFF
sql_query = \
SELECT seq, zip_code, add1, add2, add3, address \
FROM zip_code
sql_field_string = zip_code
sql_field_string = add1
sql_field_string = add3
sql_field_string = address
}
#############################################################################
## index settings
#############################################################################
index sph_zip_code {
source = sph_zip_code
path = /var/lib/sphinx/data/sph_zip_code
docinfo = extern
charset_table = 0..9, A..Z->a..z, _, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F
ngram_len = 1
min_infix_len = 2
ngram_chars = U+4E00..U+9FBB, U+3400..U+4DB5, U+20000..U+2A6D6, U+FA0E, U+FA0F, U+FA11, U+FA13, U+FA14, U+FA1F, U+FA21, U+FA23, U+FA24, U+FA27, U+FA28, U+FA29, U+3105..U+312C, U+31A0..U+31B7, U+3041, U+3043, U+3045, U+3047, U+3049, U+304B, U+304D, U+304F, U+3051, U+3053, U+3055, U+3057, U+3059, U+305B, U+305D, U+305F, U+3061, U+3063, U+3066, U+3068, U+306A..U+306F, U+3072, U+3075, U+3078, U+307B, U+307E..U+3083, U+3085, U+3087, U+3089..U+308E, U+3090..U+3093, U+30A1, U+30A3, U+30A5, U+30A7, U+30A9, U+30AD, U+30AF, U+30B3, U+30B5, U+30BB, U+30BD, U+30BF, U+30C1, U+30C3, U+30C4, U+30C6, U+30CA, U+30CB, U+30CD, U+30CE, U+30DE, U+30DF, U+30E1, U+30E2, U+30E3, U+30E5, U+30E7, U+30EE, U+30F0..U+30F3, U+30F5, U+30F6, U+31F0, U+31F1, U+31F2, U+31F3, U+31F4, U+31F5, U+31F6, U+31F7, U+31F8, U+31F9, U+31FA, U+31FB, U+31FC, U+31FD, U+31FE, U+31FF, U+AC00..U+D7A3, U+1100..U+1159, U+1161..U+11A2, U+11A8..U+11F9, U+A000..U+A48C, U+A492..U+A4C6
}
#############################################################################
## indexer settings
#############################################################################
indexer {
mem_limit = 128M
}
#############################################################################
## searchd settings
#############################################################################
searchd {
listen = 9312
listen = 9306:mysql41
log = /var/log/sphinx/searchd.log
query_log = /var/log/sphinx/query.log
read_timeout = 5
max_children = 30
pid_file = /var/run/sphinx/searchd.pid
seamless_rotate = 1
preopen_indexes = 1
unlink_old = 1
workers = threads
# for RT to work
binlog_path = /var/lib/sphinx/
}
중간 정리해봅시다.
스핑크스 설치 (완료)
환경설정 :: sphinx.conf (완료)
인덱싱 실행 :: indexer --all
데몬 실행 :: searchd
sphinxQL 실행 :: mysql -h0 -P9306
데몬 실행 중 재 인덱싱 :: indexer --all --rotate
indexer 실행
2번까지는 설명된 내용이고요, 환경설정한 sph_zip_code를 생성하기 위해서는 인덱싱 작업을 해줘야 합니다.
indexer는 전체 텍스트 인덱스를 만드는 유틸리티입니다.
만약 indexer 실행 시 WARNING을 만나셨다면 반드시 없애기 추천드립니다. 예기치 않은 검색결과가 나올 수 있습니다.
#############################################################################
indexer --all
Sphinx 2.2.11-id64-release (95ae9a6)
Copyright (c) 2001-2016,
Andrew Aksyonoff Copyright (c) 2008-2016,
Sphinx Technologies Inc (http://sphinxsearch.com)
using config file '/etc/sphinx/sphinx.conf'
... indexing index 'sph_zip_code'...
collected 52516 docs,
3.5 MB sorted 1.1 Mhits,
100.0% done total 52516 docs,
3483973 bytes total 0.337 sec,
10312158 bytes/sec,
155441.30 docs/sec .
#############################################################################
데몬 실행
외부 소프트웨어(ex:php, mysql 등)가 스핑크스의 전체 텍스트 색인을 검색 할 수 있게 해주는 데몬입니다.
#############################################################################
데몬실행
searchd
Sphinx 2.2.11-id64-release (95ae9a6)
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)
using config file '/etc/sphinx/sphinx.conf'...
listening on all interfaces, port=9312
listening on all interfaces, port=9306
precaching index 'sph_zip_code'
precached 1 indexes in 0.005 sec
구동확인
ps -ef | grep searchd
root 25259 1 0 12:37 ? 00:00:00 searchd
root 25260 25259 0 12:37 ? 00:00:00 searchd
root 25270 23459 0 12:37 pts/1 00:00:00 grep --color=auto searchd .
#############################################################################
[데몬 죽이기]
실행된 데몬을 죽이려면 kill 명령어를 사용해야 합니다.
#############################################################################
killall searchd
또는
killall -9 searchd
#############################################################################
[indexer 재실행]
searchd가 실행중일때 indexer --all 를 실행하면 에러가 납니다. 아래의 명령어를 사용해야 합니다.
#############################################################################
indexer --all --rotate
#############################################################################
sphinxQL 실행
#############################################################################
mysql -h0 -P9306
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1 Server version: 2.2.11-id64-release (95ae9a6)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
MySQL [(none)]> select * from sph_zip_code;
+--------+--------+------------------+--------------------------------+
| id | add1 | add3 | address |
+--------+--------+------------------+--------------------------------+
| 100011 | 서울 | 충무로1가 | 서울 중구 충무로1가 |
| 100012 | 서울 | 충무로2가 | 서울 중구 충무로2가 |
| 100013 | 서울 | 충무로3가 | 서울 중구 충무로3가 |
| 100014 | 서울 | 충무로4가 | 서울 중구 충무로4가 |
| 100015 | 서울 | 충무로5가 | 서울 중구 충무로5가 |
| 100021 | 서울 | 명동1가 | 서울 중구 명동1가 |
| 100022 | 서울 | 명동2가 | 서울 중구 명동2가 |
| 100031 | 서울 | 저동1가 | 서울 중구 저동1가 |
| 100032 | 서울 | 저동2가 | 서울 중구 저동2가 |
| 100041 | 서울 | 남산동1가 | 서울 중구 남산동1가 |
| 100042 | 서울 | 남산동2가 | 서울 중구 남산동2가 |
| 100043 | 서울 | 남산동3가 | 서울 중구 남산동3가 |
| 100051 | 서울 | 회현동1가 | 서울 중구 회현동1가 |
| 100052 | 서울 | 회현동2가 | 서울 중구 회현동2가 |
| 100053 | 서울 | 회현동3가 | 서울 중구 회현동3가 |
| 100060 | 서울 | 남창동 | 서울 중구 남창동 |
| 100070 | 서울 | 소공동 | 서울 중구 소공동 |
| 100080 | 서울 | 북창동 | 서울 중구 북창동 |
| 100091 | 서울 | 남대문로1가 | 서울 중구 남대문로1가 |
| 100092 | 서울 | 남대문로2가 | 서울 중구 남대문로2가 |
+--------+--------+------------------+--------------------------------+
#############################################################################
마무리
설치 및 구동 테스트까지 완료 되었습니다.
다음으로는 sphinxQL에서 유용하게 사용될 쿼리를 살펴보도록 하겠습니다.
본 포스팅은 연재분이며, 아래와 같이 예상하고 있습니다.
Sphinx #1 :: 스핑크스 설치 및 설정하기
sphinx #2 :: sphinxQL 쿼리 테스트
sphinx #3 :: sphinxQL을 PHP에서 사용하기
sphinx #4 :: sphinxSE 설치 및 쿼리 테스트(inner join 가능)
[참고]
https://sacstory.tistory.com/entry/Debain-%EA%B2%80%EC%83%89-%EC%97%94%EC%A7%84-Sphinx-%ED%95%9C%EA%B8%80-%EC%84%A4%EC%A0%95
http://sphinxsearch.com/
http://sphinxsearch.com/docs/manual-2.2.11.html#extended-syntax
https://nam24.tistory.com/10
'DB > DB 공통 관리' 카테고리의 다른 글
| 3. Sphinx스핑크스 쿼리 테스트 PHP 연동 (0) | 2023.03.26 |
|---|---|
| 1. Sphinx 이란 (0) | 2023.03.26 |
| mysql 상태 모니터링 (0) | 2023.03.26 |
| 사이트 접속했을때 Too many connection가 나올때 (0) | 2023.03.12 |
| MySQL, MariaDB 비밀번호 변경 및 유저 생성 (0) | 2023.03.12 |
댓글