KLDP DB 서버 업그레이드를 위한 도움 요청

보통 이런 CMS 나 게시판들이 갖고 있는 특징들은
update 보다는 select query 가 많다는 것입니다.

(update 가 많은 게시판이나 CMS는 실제로 튜닝하기에 매우 힘들거나 시간이 걸리죠^^;)

mysql을 사용하시고 계시기 때문에
mysql 에서 사용하는 replication 기능을 사용하면 간단히 해결될것으로 생각합니다.

물론 drupal 이 replication 기능을 적절히 이용할수 있도록 query 가 구성되어 있을경우에만 가능하겠지요.
뭐 지금 생각이지만 drupal 이 중규모 이상의 site를 만들때 사용한다면 적당히 구성되어 있지 않을까 합니다.
아니라고 한다면 ..(음 지금으로써는 생각하기 싫군요, 좀 귀찮은 작업이 되겠죠)

그런데 replication을 사용하여 query 를 조절하는것이 어렵다고 한다면
mysql query cache 를 추천합니다.
(물론 이것도 drupal 이 쿼리를 이에 걸맞게 작성되어 있어야만 가능하겠지요)

만약 이것도 하실수 없다면 memory 사용을 권장드립니다.
아주 간단합니다

제일 로드가 많이 걸리는 서버를 memory mount 시켜서 사용하고
나머지 용량이 많고 적게 사용하는 서버는 스토리지에 사용합니다.
그리고 모든 데이타는 replication 시켜 실시간 백업 합니다.

이렇게 할경우 메인서버가 memory full 나 죽을경우 모든 DB가 slave에 안정적으로 보관되기
때문에 유실되지 않습니다.

단 memory 의 용량이 커야 서버가 안정적으로 돌것이기 때문에
최소 4G 최대 16G( 64BIT 환경이 되어야 할것입니다) 를 권장합니다.

drupal이 pgsql도 지원하던데 -.-;
예전 bbs.kldp의 수십만건의 자료들이 모두 이전되어 데이터 사이즈가 클것인데요
이럴 경우 pgsql의 비트맵 인덱스와 Partial인덱스가 큰 성능 향상을 줄 것입니다.. 특히 복잡한 쿼리에서는 다른 DBMS에 비해 좋은 성능을 보여줍니다
현재 drupal은 플러그인의 종류에 따라 답글이 많이 달린 게시물 조회시 쿼리를 거의 백번 가까이 할 수도 있다고 들었습니다; 하드웨어 성능도 좋아야 하겠지만 소프트웨어 적인 튜닝 없이는 데이터가 늘어나면 앞으로 같은 문제가 야기될 것 같습니다 -.-

DB 에 관한한 가장 문제가 되는 부분은 inner join 의 남발 입니다.
순선님 말씀대로 H/W 업데이트도 도움이 될 겁니다. 하지만 가장 중요한 것은 DB migration 입니다. DB 에서의 문제는.. query 를 백번 하는 것도 문제가 되지는 않습니다. 중요한 것은 inner join 을 하는 쿼리는 site 를 막고 혼자 널널하게 던져도 4초 정도가 걸리는 질의 들이 있다는 것이죠.

Drupal site 와 비교를 많이 하시는 듯 한데, drupal 과 kldp 의 상황을 비교하는 것도 무리이고, drupal 에서 kldp 에서 사용하는 모듈들을 그대로 사용하느냐도 문제가 될 수 있습니다. 현재 가장 문제가 되는 부분은 forum list page 쪽과 글 등록 부분이니까요.

다만.. 4.7 에서는 성능이 좀 좋아지기는 한 듯 싶습니다. 4.6 에서 도저히 견디지 못해서 막아 놓은 기능이 4.7 에서는 현재 풀려서 작동 중인데, 4.6 처럼 부하가 걸리지는 않고 있으니까요.

DB 서버의 부하 때문에 발생하는 문제는 아니지만, 널널한 상태에서의 query 가 4초 정도 걸려야 한다면, 서버성능을 올림으로서 migration 을 할 수 있는 부분이기도 합니다. 업체에서 이런 일을 할 경우라면, 돈을 바른다는 표현을 쓸 수 있을 것이고, KLDP 의 입장에서는 돈을 바르기도 힘들고, db tunning 을 해 주겠다는 분도 나타나지 않으므로 순선님 입장은 좀 난감할 것 같습니다. (제가 해 드리고 싶지만.. 저도 DBA 수준은 아니라서.. ^^)

현재 문제가 되는것들이 DB I/O쪽인지 쿼리 부분 문제인지 트랜젝션 문제인지를 어느정도 정리를 해 주세요.
각 요구사항에 따라서 분명 사양이 달라질테니까요.

아 지금은 또 다시 반응이 많이 느리군요;; -_-

동접이 많은 경우 그리고 select, insert, update, delete 등이 빈번하게 일어나면..

일반적으로 테이블 타입은 InnoDB를 사용하는게 좋을 듯합니다.

MyISAM 은 insert, delete, update 시 혹은 select 시에도 테이블 전체에 락이 걸릴수 있어

간단한 쿼리인데도 불구하고 다른 쿼리의 락킹 때문에 간단한 쿼리가 slow query 가 되어 버리곤 합니다.

정확히는 이 경우에 맞다고 할수는 없으나(저도 drupal을 사용해 보지 않아서..)

제 경험상 이런 부분이 충분히 있을 수 있다고 봅니다..

100개의 select 만 있다면 문제 될것이 없으나 100개의 select 랑 1개의 insert 가 있음면 insert 뒤에 온 select는 insert 가

끝날때 까지 모두 기다려야 된다는게 현재의 MyISAM이라고 알고 있습니다.

low level lock 를 사용하기 위해서는 InnoDB 타입의 테이블을 사용해야 한다고 알고 있습니다.

MyISAM의 테이블수준 락 때문에 업데이트(insert, update, delete)가 빈번한 테이블이라면 InnoDB도 충분히 고려대상입니다.
저의 경험에서 심지어는 돌고있는 서버에서 인덱스가 깨져서 리빌드 한다는 메시지도 보았으니까요.
하지만 MyISAM이 지원하는 모든 기능을 InnoDB도 지원하는 것이 아니라 바꾸기 전에 확인해야 할 사항들이 있습니다.

우선 제가 아는것만 해도 조건없는 COUNT(*)의 부담과 Fulltext Index의 미지원이 있습니다.
COUNT(*)함수가 실제 모든 row를 세는 작업을 하기 때문에 row의 갯수가 계속 늘어나는 환경에서 이 함수를 많이 사용하면 오히려 더 역효과가 발생합니다.

그리고 튜닝(쿼리+시스템) 작업이라는게 문제점을 찾아 하나하나 조절을 해야하는 것이라서 지금 당장 방법을 올리기는 어려울것 같습니다. 제가 DBA는 아니라 경험이 부족하거든요.

우선 작업은 제가 할수 있는 부분은 제가 하고 더이상 어려운 상황이라면 제가 알고있는 DBA분께 부탁하도록 하겠습니다.

김정균님 밑에 쿼리중에 좀 심하다 싶은것을 확인해 보니 cache, comments 테이블이 모두 들어가 있습니다.

우선 comments테이블에 (pid, status)로 잡힌 인덱스가 있는지 확인해 주세요. 순서도 일치해야 합니다. 뒤에 더 붙어도 상관 없구요.

그리고 현재 cache, comments 테이블의 row갯수도 필요합니다.

drupal 관리에 캐시설정같은게 있나요?

node_comment_statistics 테이블에서 정렬에 사용되는 last_comment_timestamp와 (join을 하지 않기 위하여) comment_count 두 컬럼을 node 테이블에도 넣어 저 sql처럼 ORDER BY last_post를 -동적으로 node와 node_comment_statistics를 join하여 정렬하는 것을- 없애겠습니다. 테이블의 이름을 봐서는 node에 comments가 쓰여질 때 1:M의 관계를 유지하고 통계에 대한 것은 node_comment_statistics로 따로 쓰는 것 같은데 위의 두 컬럼을 node에도 항상 최근 것으로 업데이트 하는 방식을 택하면 위의 문제가 되는 sql에서는 node에 있는 두 컬럼을 가지고도 인덱스를 쓰는 정렬을 할 수 있으니 성능 개선이 있을 듯 합니다.

따라서, 위에 정균님이 이야기 한 것이 언뜻 공감이 되는군요.
----
I paint objects as I think them, not as I see them.
Ubuntu Dapper user / Ubuntu KoreanTeam / Lanuchpad karma 16289

제가 걍 하루 제낄거 같아서 우선 권순선님께 메일로 전화번호를 남겼습니다.
김정균님은 메일을 보낼수 없다고 나오네요. ㅡ,.ㅡ

물론 Mysql에서의 리플리케이션기능이 만족스럽지 못하고 미비한건 사실이나,
상용 디비 및 최상의 버젼의 Mysql이 아닌 상태에선 (대략 4.0정도버젼대)
그나마 한숨 덜어주는건 사실입니다.
리드, 라이트 디비를 두고 거기서 또 리플리케이션을 이루면 (최소 약 3대정도)
하루 페이지뷰 백만이 넘으며(잘못써서 수정합니다 ㅡ.,ㅡ::) 회원수가 600만이 넘는 사이트도 큰 무리 없이
웹서비스를 이용 할 수 있습니다.

물론 insert, update 등의 쿼리를 자주 요청 하는 싸이트에서도 적용 되는 내용이지요...

하지만 결과적으로 가장 좋은 방법은 클러스터링의 기능이 정확하게 지원되는 것이 안전하고 좋겠죠..
현재 Mysql의 리플리케이션은 단지 동기화, 및 실시간 복제서비스의 개념이 더 강하니 말이죠...
그나저나...KLDP에서도 디비문제로 고민중이신데, 저희 회사역시도 디비량이 엄청나지면서
디비 튜닝쪽으로 골머리 앓고있습니다...계속 주시해 가면서 많이 배워갈 수 있음 좋겠네용...
저또한 도움이 될 수 있으면 좋겠구요....^^

내 마음속의 악마가 자꾸만 나를 부추겨.
늘 해왔던 것에 만족하지 말고 뭔가 불가능해 보이는 것을 하라고 말야.

한참 헤메다가 생각해 봤는데 DISTINCT(n.nid) 이거 그냥 n.nid로 바꿔도 되지 않을까요?

n.nid는 PK인데. ㅡ,.ㅡ

그냥 DISTINCT만 빼고 돌리니 인덱스 잘 타고 그래서 속도 빠릅니다. 어찌해야 할까요?

이런 엄청난 체감속도 향상 효과는 뭐죠????
글쓰기도 꽤나 빨라졌군요....
제가 지금 KLDP에 와있는거 맞는거죠? :)

내 마음속의 악마가 자꾸만 나를 부추겨.
늘 해왔던 것에 만족하지 말고 뭔가 불가능해 보이는 것을 하라고 말야.

아, 위에 김정균님께서 올려주신건 나의 최근글 목록이었군요.

중복이 발생하겠네요.

나의 최근글 가져오는 쿼리입니다.
SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count
FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN users u ON n.uid = u.uid
LEFT JOIN comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS NULL)
WHERE (n.status = 1 AND n.uid = 2264) OR (n.status = 1 AND c.uid = 2264) ORDER BY last_post DESC LIMIT 0, 25;

이걸 아래와 같이 바꾸어 봤는데. 결과는 일치하는것 같습니다. 응답도 바로 나옵니다. 문제는
db버전이 바뀌거나 다른 디비로 바뀌었을때 잘 돌것이냐? 입니다. 제가 JOIN과 UNION에 대해서는 완전 무지하다고 보셔도 됩니다.
욕해주세요. ㅋㅋ

답글 달아주세요..

ALTER TABLE comments ADD INDEX uid(uid); // 코멘트에 인덱스 필요해서 추가후

SELECT * FROM
(
SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count
FROM node n, node_comment_statistics l, users u, comments c
WHERE c.uid=2264 AND n.nid=c.nid AND l.nid = n.nid AND u.uid=n.uid

UNION

SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count
FROM node n, node_comment_statistics l, users u
WHERE n.uid = 2264 AND l.nid = n.nid AND u.uid=n.uid
)zz ORDER BY last_post DESC LIMIT 0,25;

아 stadia님 제가 저 부분을 어떻게 해야할지 잘 몰라서 우선 *로 했습니다. ^^
alias를 주고 위에서도 그걸로 명시해 줘야 할까요?

지금현재 '나의 최근글보기'도 위의 쿼리로 수정했습니다.