검색속도 개선을 위한 몇가지 질문을 두서없이 질문드립니다. 0 2 892

by 심라 [MySQL] replication 분산 성능 인덱스 [2020.01.27 17:29:15]


CentOS5.6 / PHP5.3 / MySQL5.6 / 메모리 8GB

[my.cnf]
key-buffer-size                = 512M
myisam-recover                 = FORCE,BACKUP
max-allowed-packet             = 16M
tmp-table-size                 = 512M
max-heap-table-size            = 512M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096

innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 64M
innodb-log-buffer-size         = 4M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-thread-concurrency      = 8
innodb-buffer-pool-size        = 2048M
autocommit = 1


뉴스기사 및 요약내용을 담고 있는 테이블이 있습니다.
대략 500만건정도 됩니다.
자료가 늘어남에 따라 검색속도가 현저히 느려져서 어떻게 하면 속도를 올릴수 있을까 고민중입니다.
MyISAM 을 사용하다 조회시 계속해서 락현상이 문제가 되어 현재는 InnoDB로 운영중인 상태입니다.

이 상황에서 두서없이 질문드립니다.

1. int 나 char 필드가 아닌 varchar, text 에 인덱싱을 하는게 의미가 있나요?
"검색어%" 처럼 첫글자부터 검색하는게 아닌 "%검색어%" 처럼 검색할때도 인덱싱이 의미가 있는건지 모르겠습니다.
FULLTEXT (title,content) 를 걸어서 "match(...) against(...)" 구문으로도 검색해보고, 일반 index 를 걸어서 "%검색어%" 로 해봐도 눈에띄는 성능차이는 모르겠습니다.

2. 현재 메모리가 8GB 인데 Free 로 메모리사용량을 보면 절반정도밖에 사용하질 않습니다.
위의 my.cnf 에서 성능개선 및 메모리를 최대한사용하려면 어느항목을 조정하는게 좋을지 의견주시면 감사하겠습니다.

3. 현재는 서버한대에 웹서버,디비서버가 같이 있습니다. 서버를 분리하면 성능에 개선이 될까싶어 찾아보니 MySQL 에 Replication 이라는게 있더군요.
디비서버를 여러대로 구성하여 부하를 분산시킨다는것으로 이해를 하는데, 여기서 못찾겠는부분이 있더군요.
예를들어 master 1대를두고 salve 를 2대를 둔다고 가정할때 보통 insert,upadte,delete 쿼리는 master 에서 처리하고 select 처리는 slave 에서 하는구조에 대한 얘기는 있는데, 그럼 insert,update,delete 문은 어느 DB서버와 접속해서 실행하고, select 는 어느디비와 접속해서 실행하는것은 PHP 소스에서 직접 처리해야 하는건가요? 아니면 Replication 설정옵션에 insert,upadte,delele,select 문에 따라 자동으로 디비서버가 분산처리를 하는건가요?


4. 1번에 질문한 검색속도가 한건 검색할때나혼자 접속해있다고 가정하는 상황)도 느린데 이걸 디비서버를 분신시킨다고 빨라지진 않을것 같은데요, 이보다 큰 건수의 자료를 다루는 포털이나 커뮤니티는 어떻게 디비를 처리하는거죠?
이 질문은 너무 방대한 내용을 낼름거리는것같아 죄송하지만 조금의 조언이라도 주시면 감사하겠습니다.

by 부쉬맨 [2020.01.28 11:28:22]

1. int 나 char 필드가 아닌 varchar, text 에 인덱싱을 하는게 의미가 있나요?
 -> 오라클같은경우 도메인 인덱스라는게 존재하는데 mysql은 없는듯하네요 앞단에 날짜 같은 검색조건을 인덱스로 잡아서 처리하는 방법 고민필요함

2. 현재 메모리가 8GB 인데 Free 로 메모리사용량을 보면 절반정도밖에 사용하질 않습니다.
 -> 메모리를 사용할수있도록 sql들을 조정하면 좀더 빨라질수있습니다."query-cache-size,key-buffer-size,max-heap-table-size,tmp-table-size"

3. 현재는 서버한대에 웹서버,디비서버가 같이 있습니다. 서버를 분리하면 성능에 개선이 될까싶어 찾아보니 MySQL 에 Replication 이라는게 있더군요.
 -> 충분한 효과를 볼 수 있을번한데.... 해당경우는 제가 많이보지않았지만, 말씀하신 read replica 로 구성하여서 dml작업과 select 작업을 분산시키는 용도더군요.  해당 경우는 게임쪽에서 많이 사용되던데

실시간 동접자가 많아서 느려지는 경우가 아니면 그렇게 효과는 보지못할듯합니다.


4. 1번에 질문한 검색속도가 한건 검색할때나혼자 접속해있다고 가정하는 상황)도 느린데 이걸 디비서버를 분신시킨다고 빨라지진 않을것 같은데요, 이보다 큰 건수의 자료를 다루는 포털이나 커뮤니티는 어떻게 디비를 처리하는거죠?
이 질문은 너무 방대한 내용을 낼름거리는것같아 죄송하지만 조금의 조언이라도 주시면 감사하겠습니다.

 -> 1번답변과 동일(%텍스트% 검색시 앞단 날짜 조건을 필수 조건으로 주어서 데이터 대상모수를 줄이는 방법밖에없음)


by DarkBee [2020.01.28 15:09:48]

Mysql에서도 text 검색을 빠르게 해보자 해서 나온게 Fulltext 인덱스 인데요. 가능하다면 조건절 전부 써봐주시겠어요? match(...) against(..)

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입