DB
| 2007-06-28 16:30:46
Hit : 291
맛의 완성은 데코레이션 DB의 완성은 튜닝 데이터베이스 튜닝
안타까운 일이지만 불과 몇 년 전만 해도 많은 개발자들이 데이터베이스의 튜닝에 별다른 관심을 보이지 않았다. 그나마 다행스러운 것은 최근 들어 데이터베이스 관련 커뮤니티에 튜닝과 관련된 내용의 글이 자주 등장한다는 점이다. 이는 최근 만들어진 데이터베이스에 문제가 있거나 갑자기 데이터베이스 튜닝에 대한 이슈가 부각 되었다기보다는 오랫동안 묵혀둔 병이 이제야 그 증상을 나타내기 시작하고 있다고 생각하는 편이 옳을 것이다. 사람이나 데이터베이스나 조기 진단과 처방만큼 든든한 것이 없다. 적은 지면을 통해 튜닝에 대한 모든 지식을 전달할 수는 없겠지만 이 글이 데이터베이스 튜닝에 대한 관심에 불을 댕기는 도화선이 되어 주기를 바란다.
튜닝(Tuning)은 ‘조율’ 또는 ‘미세조정’이라는 사전적 의미를 가지고 있지만 데이터베이스 세계에서는 그 의미가 조금 다르다. 조율이나 조정이라기보다는 ‘최적화’가 더 가까운 뜻으로 쓰이고 있기 때문이다. 그럼 데이터베이스 튜닝을 통해 무엇을 최적화 할 수 있는 것일까? 그 대상은 매우 많다. 업무를 최적화 할 수도 있고, 설계나 하드웨어적인 병목 현상을 해결하여 최적화 하거나 SQL을 최적화 할 수도 있다.
이러한 최적화는 단지 처리 속도를 높이는 것에 그치는 것이 아니라 사용자가 필요한 때에 원하는 정보를 제공하는데 그 목적이 있다고 할 수 있다.
사용 중인 데이터베이스를 튜닝하여 적시성을 확보하려면 먼저 시스템이 느려지는 원인을 파악해야 한다. 하지만 실제로 성능 저하의 원인을 파악하는 것은 그리 쉬운 일이 아니다. 그래서 쉬운 방법으로 하드웨어를 증설하거나 괜한 DBMS 제품을 의심해 보기도 하지만 성능 저하의 원인을 DBMS에 떠넘기기는 곤란하다. 일례로 비슷한 규모의 동종 업체라도 비싼 장비를 가지고도 데이터베이스의 문제를 해결하지 못하는 반면, 어떤 업체는 아주 저렴한 장비로 훌륭하게 데이터베이스를 활용하고 있는 경우를 들 수 있다.
사실 DBMS와 하드웨어에 대한 성능의 차이는 벤치마킹 사이트(http://www.tpc.org)에서 쉽게 확인할 수 있기 때문에 대부분 쉽게 자기 회사의 규모에 맞는 시스템을 선택할 수 있다. 적당한 혹은 넉넉한 서버의 자원을 가지고도 데이터베이스를 삐걱거리게 만드는 문제의 중심에는 사람이 있다고 보는 것이 옳다. 특히 데이터베이스 설계자는 그 중에서도 가장 핵심적인 인물이다.
코드테이블과 식별자 관계
흔히 데이터베이스의 설계라고 하면 물리적인 설계를 의미한다. 즉, 인덱스, 테이블, 제약조건 등에 대한 정의가 포함된다. 요즘의 데이터베이스라면 코드테이블이 하나쯤은 있는 것이 당연하게 여겨지기도 한다. 예를 들면, (직업코드, 직업명)과 같은 형태이다. ERP(Enterprise Resource Planning)와 같은 시스템의 경우 ‘코드마스터’ 라는 명칭으로 코드테이블이 대부분 존재한다. 이렇게 코드테이블을 두는 이유는 뭘까? 단순히 선배들이 그렇게 해왔기 때문이나 남들이 그렇게 하기 때문에 또는 책에서 그렇게 지시했기 때문이라고 대답할 수는 없는 일이다. 코드 테이블을 두는 이유는 유지보수의 편리성과 성능 향상에 있다.
먼저 유지보수의 편리성에 대해 이야기 해보자. 뒤에 있는 <그림3>에서 Dept가 ‘부서코드’ 테이블이고 부서의 명칭이 ‘전산실’에서 ‘정보시스템관리실’로 변경되었다고 가정하자. 그럼 위와 같은 경우 Dept 테이블에서 ‘전산실’을 ‘정보시스템관리실’로 변경하면 끝이다. 그러나 Emp 테이블에서 DeptNo(부서번호) 대신 DeptName(부서명)으로 들어갔다면 해당되는 사원의 부서명을 모두 고쳐주어야 한다.
그럼, 성능은 어떻게 향상되는 것일까? 코드는 INT형이고, 코드명은 가변길이 문자형(최대 20) 이라면 INT형을 저장하는 것이 저장 공간을 적게 잡아먹고, 만약 인덱스 등의 객체가 생성된다면 인덱스의 크기도 줄어들 것이다. 예를 들어, 100만 건의 데이터가 있다고 가정하자. INT형이라면 4×1,000,000 바이트이며, 가변길이 문자형이라면 20×1,000,000 바이트가 된다. 쉽게 말해 같은 데이터에 가변길이 문자형을 저장할 경우 500페이지짜리 책을 만들 수 있는 반면, INT형을 사용하면 1,000페이지짜리 책이 만들어 지는 것의 차이와 같은 것이다.
데이터베이스 관련 책을 보면 모델링에는 모델러의 주관적인 생각이 들어간다는 말을 쉽게 접할 수 있다. 사실 맞는 말이지만 ‘주관’이라는 의미는 좋은 의미로 해석될 수도 있지만 나쁜 의미로 해석될 수도 있다. 다시 말해 주관은 실제세계를 표현하는 방법의 차이 정도로 해석할 수도 있지만, 나쁜 의미로 해석하자면 모델러의 능력이라고 볼 수도 있기 때문이다(사실 프로젝트에 투입되는 인원 중 모델이나 설계문서를 검토하여 문제점을 지적해 낼 수 있는 사람이 거의 없기 때문에 검증된 외부업체에 검토를 의뢰하는 것이 좋다). 설계에 따라서 어떻게 다른 구현이 이루어지는지 또한 성능상의 차이가 있는지 예를 통해 알아보자.
<그림 1> 비식별자 관계와 식별자 관계
<그림 1>과 같이 하나의 대상을 두고도 서로 다른 두 사람이 두 가지 형태의 모델을 도출했다고 가정하자. A, B 모델의 차이는 그림에서 보는 바와 같이 A 모델은 비식별자 관계를 맺고 있고, B 모델은 식별자 관계(외부 키가 기본 키의 일부 또는 전체가 됨)를 맺고 있다. 어떤 것이 더 좋은 모델일까? 현재로써는 알 수 없다. 단지 A 모델을 만든 사람이 왜 A처럼 만들었지 왜 B처럼 만들었는지 확실하게 알고 있다면 두 모델은 문제점이 없다고 봐도 무방하다. 하지만 왜 그렇게 했는지 모른다면 그게 바로 문제이다. ERD(entity Relationship Diagram)처럼 생긴 문서를 던져주고 개발을 지시하면서 개발 시간을 재촉한다면 그 자체가 잘못인 것이다.
<그림 1>에서 두 모델이 실제 물리적으로도 똑같이 구현되어 있고, 두 모델의 최상위에 있는 테이블 A를 코드테이블이라고 가정하자. 만약 G 테이블에서 A 테이블에 있는 특정 컬럼의 데이터를 가져와야 한다면 두 모델에는 어떤 차이가 있을까? 실제 SQL로 만들어 본다면 <리스트 1>과 같을 것이다.
<리스트 1> 코드테이블과 식별자 관계
--A의 경우
SELECT
A.UID_A
, G.UID_G
FROM G
INNER JOIN F ON G.UID_F = F.UID_F
INNER JOIN C ON F.UID_C = C.UID_C
INNER JOIN A ON C.UID_A = A.UID_A
--B의 경우
SELECT
A.UID_A
, G.UID_G
FROM G INNER JOIN A ON G.UID_A = A.UID_A
--A의 경우
SELECT
A.UID_A
, G.UID_G
FROM G
INNER JOIN F ON G.UID_F = F.UID_F
INNER JOIN C ON F.UID_C = C.UID_C
INNER JOIN A ON C.UID_A = A.UID_A
--B의 경우
SELECT
A.UID_A
, G.UID_G
FROM G INNER JOIN A ON G.UID_A = A.UID_A
이 경우 두 모델에서 SQL의 실행 결과는 같다. 그러나 A 모델은 몇 번의 조인을 거쳐야만 비로소 A 테이블의 데이터를 조회할 수 있다. B의 경우는 단 한 번의 조인으로 원하는 데이터를 조회할 수 있다. A 모델은 조인한 만큼의 데이터를 처리해야 하고 원하는 데이터를 찾기 위해 많은 길을 거쳐야 한다. 데이터의 출처 또한 불명확해진다. 실제로 모델의 느낌은 A가 더 복잡함을 알 수 있다. 느낌이 오는가? 어떻게 설계하는지에 따라 성능에 많은 차이가 나고, 데이터의 출처가 아주 명확해 지고 SQL도 단순해진다.
그렇다면 B 모델이 더 좋은 모델이라고 말 할 수 있을까? B 모델은 관리해야 할 컬럼 수가 늘어나고 그 만큼의 추가적인 저장 공간도 더 필요하다. 설계의 기준은 데이터가 어디까지 접근할 필요성이 있는지에 대한 요구사항이 정의되어 있어야 한다. 만약 F 테이블이 A 테이블에 접근해야 하고, G 테이블은 A, C, F 모두 접근해야 하고, 나머지는 접근의 필요성이 없다면 모델은 <그림 2>와 같은 A, B 모델의 혼합된 형태가 될 것이다.
어떤 것이 더 유리한지 판단하는 것은 모델링 또는 설계하는 사람의 능력에 따라 달라진다. 필자도 ‘상황’이 주어지지 않는 이상 어떤 모델이 더 좋다고는 말할 수 없다. 주의해야 할 것은 최상위 테이블인 A가 매우 단순해야 한다는 것이다. 물리적으로 보면 UID_A가 INT형인 경우와 CHAR(50)인 경우는 관계를 맺을 때와 인덱스 크기에 영향을 끼치게 된다. 즉, 최상위 테이블이 가벼우면 가벼울수록 좋다는 것이다. UID_A가 CHAR(50)인 경우 C, F, G 테이블을 보면 각 행(Row)마다 CHAR(50) 만큼의 저장 공간이 필요하다는 것을 알 수 있다. 그래서 일반적으로 코드 테이블 같은 경우는 대리키(설계속성)를 사용하여 이러한 저장 공간과 인덱스의 크기를 줄여 성능과 함께 정보의 질을 높인다.
<그림 2> A, B 모델이 혼합된 형태
관계를 끊을 것인가? 유지 할 것인가?
얼마 전 필자의 홈페이지에 ‘참조 무결성’ 즉, 관계를 맺는 것은 부하인가? 에 대한 질문이 올라왔다. 필자는 누군가 이러한 생각을 하는 사람이 있다는 자체가 매우 반가웠다. 예전에 여러 업체를 방문하여 관계를 왜 끊어 놓았냐고 하면, ‘개발할 때 데이터 넣기가 힘들어서...’ 라는 답변이 가장 많았다. 그러면서 관계형 데이터베이스에 대한 이야기를 하곤 한다. 더 우스운 것은 많은 시간과 비용을 투자하여 열심히 찾아낸 관계를 물리모델에서는 끊어놓고 데이터가 맞지 않는다고 외부 조인(Outer Join)을 쓰고 있다는 것이다. 이는 관계를 찾아내려고 들인 노력, 시간, 비용을 한 순간에 날려버리는 일이다. 또한 외부 조인으로 DBMS가 불리한 실행계획이 만들어 지기 때문에 성능도 저하된다. 이런 경우 SQL을 튜닝하고 인덱스를 생성하느라 애 먹을 필요가 전혀 없다. 단지 몇 개의 데이터가 맞지 않아서 외부 조인을 한다면 몇 개의 데이터를 찾아내어 수동으로 맞춰주고, 관계를 맺어 무결성을 보장하고, 내부 조인(Inner Join)으로 바꾸면 이 부분에 대한 성능 튜닝은 끝난다. 튜닝 작업을 한 근본적인 원인이 무엇인가? 관계가 맺어있지 않기 때문이다. 그렇다면 관계를 맺을 때와 맺지 않았을 때는 물리적으로 어떤 차이점이 있는지 살펴보자.
<그림 2>와 같이 물리적으로 구현이 되어 있다면 E 테이블에 한 건의 데이터를 입력하면 B 테이블에 데이터가 존재하는지 검사하게 된다. SQL만 본다면 단 한 줄의 문장이지만 DBMS 내부적으로는 B 테이블과 E 테이블을 조인하여 B 테이블에 데이터가 존재하는지 판단하게 된다. 이는 실행계획을 통하여 확인할 수 있다. 다음의 예를 보자.
<그림 3> 사원과 부서의 관계를 나타낸 ERD
<화면 1>과 같이 테이블이 만들어져 있다. 관계를 맺고 있을 때와 관계를 끊었을 경우 Emp 테이블에 데이터 1건을 입력하면 실행계획이 어떻게 만들어지는지 살펴보자.
<화면 1> 관계가 맺어 있는 경우의 실행계획
<화면 2>를 보면 Dept 테이블과 조인하는 것이 보인다. 즉, 데이터 무결성 체크를 한 것이다. 반면에 <화면 3>에서는 무결성이 보장되지 않아 아무것도 없이 그냥 Emp 테이블에만 데이터를 입력한다. 이 둘의 차이는 성능 측면에서만 본다면 관계를 끊는 것이 도움이 된다. 하지만 우리가 여기서 생각해야 할 것은 성능이 최우선이 아니라는 것이다. ‘최적화’의 대상은 성능이 아니라 바로 ‘정보의 질’이다. 데이터의 무결성은 정보의 정확성과 관련된 것이므로 만약 DBMS에서 무결성이 보장될 수 없다면 애플리케이션에서라도 무결성을 보장하는 것이 옳다. 물론 일반적인 이야기이다. 만약 하나의 트랜잭션을 매우 짧게 실행하고, 사용자가 매우 많은 환경에서는 무결성 때문에 서비스에 영향을 준다면 관계를 끊는 것이 맞다. 물론 데이터 무결성은 어디서나 지켜져야 하며 정확성이 떨어질 수 있음을 감수할 수 있는 환경에서 일 것이다.
<화면 1> 관계가 맺어 있는 경우의 실행계획
<화면 2> 관계가 맺어 있지 않은 경우의 실행계획
<화면 2>를 보면 Dept 테이블과 조인하는 것이 보인다. 즉, 데이터 무결성 체크를 한 것이다. 반면에 <화면 3>에서는 무결성이 보장되지 않아 아무것도 없이 그냥 Emp 테이블에만 데이터를 입력한다. 이 둘의 차이는 성능 측면에서만 본다면 관계를 끊는 것이 도움이 된다. 하지만 우리가 여기서 생각해야 할 것은 성능이 최우선이 아니라는 것이다. ‘최적화’의 대상은 성능이 아니라 바로 ‘정보의 질’이다. 데이터의 무결성은 정보의 정확성과 관련된 것이므로 만약 DBMS에서 무결성이 보장될 수 없다면 애플리케이션에서라도 무결성을 보장하는 것이 옳다. 물론 일반적인 이야기이다. 만약 하나의 트랜잭션을 매우 짧게 실행하고, 사용자가 매우 많은 환경에서는 무결성 때문에 서비스에 영향을 준다면 관계를 끊는 것이 맞다. 물론 데이터 무결성은 어디서나 지켜져야 하며 정확성이 떨어질 수 있음을 감수할 수 있는 환경에서 일 것이다.
<리스트 2> 관계를 끊을 것인가? 유지 할 것인가?
CREATE TABLE dbo.Dept (
DeptNo int NOT NULL ,
DeptName varchar (50) NULL
) ON PRIMARY
GO
CREATE TABLE dbo.Emp (
EmpNo int NOT NULL ,
EmpName varchar (50) NULL ,
DeptNo int NULL
) ON PRIMARY
GO
ALTER TABLE dbo.Emp ADD
CONSTRAINT FK_Emp_Dept FOREIGN KEY
(
DeptNo
) REFERENCES dbo.Dept (
DeptNo
)
GO
--Dept 테이블 데이터 입력
INSERT INTO Dept VALUES(1, ''영업부'')
INSERT INTO Dept VALUES(2, ''총무부'')
GO
--관계가 맺어져 있을 경우
INSERT INTO Emp Values(101, ''이재학'', 1)
GO
--관계 삭제
ALTER TABLE dbo.Emp DROP CONSTRAINT FK_Emp_Dept
GO
--관계를 끊었을 경우
INSERT INTO Emp Values(102, ''박정연'', 1)
GO
CREATE TABLE dbo.Dept (
DeptNo int NOT NULL ,
DeptName varchar (50) NULL
) ON PRIMARY
GO
CREATE TABLE dbo.Emp (
EmpNo int NOT NULL ,
EmpName varchar (50) NULL ,
DeptNo int NULL
) ON PRIMARY
GO
ALTER TABLE dbo.Emp ADD
CONSTRAINT FK_Emp_Dept FOREIGN KEY
(
DeptNo
) REFERENCES dbo.Dept (
DeptNo
)
GO
--Dept 테이블 데이터 입력
INSERT INTO Dept VALUES(1, ''영업부'')
INSERT INTO Dept VALUES(2, ''총무부'')
GO
--관계가 맺어져 있을 경우
INSERT INTO Emp Values(101, ''이재학'', 1)
GO
--관계 삭제
ALTER TABLE dbo.Emp DROP CONSTRAINT FK_Emp_Dept
GO
--관계를 끊었을 경우
INSERT INTO Emp Values(102, ''박정연'', 1)
GO
개체집합의 정의에 따른 모델 변화
대부분의 개체집합이나 관계의 정의는 설계단계에서 결정된다. 하지만 데이터베이스를 사용하는 과정에서 업무상황이 바뀌면 당연히 현실을 표현하고 있는 데이터 모델도 따라서 변화되어야 한다. 또한 개체집합을 어떻게 정의하느냐에 따라서도 모델이 변화한다.
<그림4> 수강 ERD
<그림 4>의 ERD에서는 ‘교실’ 개체집합이 현실상 존재하는 건물(예: 이공관 501호)로 정의했다. 그러나 강의정책이 변해서 개설된 강좌가 가상공간에서 일어나는 ‘사이버강좌’가 되었다면 앞서 정의한 ‘교실’ 개체집합의 범위에서 벗어나 버린다. 다시 말해 ‘교실’ 개체집합의 개념 범위가 확장된 것이다. 그러면 명시적(Mandatory)이었던 관계는 임의(Optional)관계로 바뀐다.
<그림5> 임의(Optional) 관계
<그림5> 임의(Optional) 관계
여기에서 임의(Optional)는 관계가 명확하지 않음을 의미한다. 그러므로 개체집합의 범위를 확장하여 ‘교실’ 개체집합의 정의에 ‘사이버강좌의 URL’ 을 포함한다면 자연스레 다시 명시적 관계가 형성된다.
명시적 관계와 임의 관계는 정보의 단절 여부에 따라 적용된다. 즉, 임의 관계가 되면 물리적으로는 NULL 값을 가질 수 있다는 뜻이 된다. NULL 값은 ‘알 수 없는 값’ 이므로 개체 집합 간의 연결을 통하여 얻는 데이터에 대해서 NULL 값을 가지는 데이터는 일관성이 없어질 수도 있게 된다. 즉, 임의 관계의 경우 개설된 강좌는 이공관 501호에서 강의할 수도 있고, 강의를 어디서 받는지 몰라 학생들이 우왕좌왕 할 수도 있다는 뜻이 된다. 이러한 경우 모델러는 강한 책임감으로 문제점을 찾아내어 프로젝트 이해관계자들을 이끌어야 한다.
비정규화 테이블은 일반적으로 컬럼의 개수가 많다. 컬럼의 개수가 많다는 이야기는 데이터 행의 크기가 크다는 뜻도 된다. 앞서 살펴본 바와 같이 하나의 데이터 행의 크기가 커지면 최소 입출력 단위에 데이터 행이 많이 들어가지 못한다. 심한 경우 입출력 동작이 늘어나고 잘못하면 데이터 행이 각각의 페이지에 존재할 수도 있다. 비정규화의 문제점에 대해 좀 더 자세히 알아보기 위해 <리스트 3>과 같은 테이블이 만들었다고 가정하자.
<그림6> URL 지정으로 임의 관계를 명시적 관계로 만들기
정규화의 이점
데이터베이스 설계에서 빼놓을 수 없는 것이 바로 정규화이다. 정규화는 무결성을 보장하고 성능을 향상시키기 위해 사용할 수 있는 최고의 설계 솔루션이기 때문이다. 이 솔루션의 핵심은 ‘데이터의 중복을 없애는 것’이라고 할 수 있다. 데이터의 중복을 없애는 것은 데이터베이스에서는 매우 중요한 일이다. 데이터의 중복은 통합된 정보의 취득을 어렵게 만들고, 데이터베이스 시스템의 성능 저하되는 원인이 된다. 물론 인덱스를 제대로 활용하지 못한다거나 하드웨어에서의 병목 등 여러 가지 상황적인 요소도 성능 저하의 원인이 되겠지만 이런 사항들은 비교적 쉽게 개선할 수 있다. 하지만 설계상의 문제로 데이터가 중복되는 것은 SQL이 복잡해지고 성능 저하의 문제를 가져올 뿐 아니라 개선도 어려워진다는 점이 문제인 것이다.
왜 그럴까? 이쯤에서 우리는 기본적인 컴퓨터의 구조를 생각해야 한다. 기초 전산학에는 ‘폰 노이만’의 ‘프로그램 내장방식’이라는 말이 나온다. 기본적으로 CPU는 메인 메모리(RAM)에만 접근할 수 있다는 것이다. 그러므로 CPU가 처리하기 위해서는 데이터를 HDD에서 RAM으로 끌어 올려야만 한다. 이 과정에서 컴퓨터는 매우 큰 비용을 사용하게 된다. HDD가 매우 느린 하드웨어 자원이기 때문이다. 그렇다면 데이터의 중복이 있다고 생각을 해보자. HDD에서 RAM으로 데이터를 올려야 한다. 데이터가 중복되어 있다면 중복된 만큼 HDD를 읽으며 비용을 사용하는 횟수가 늘고 비좁은 RAM의 저장 공간도 더 많이 필요하게 된다. 또한 CPU는 데이터가 중복된 만큼 더 처리를 해야 하는 것이다. 이 과정에서 데이터베이스 시스템의 성능이 떨어지는 것은 물론이고 정보의 질과 궁극적으로는 사용자의 정보 욕구를 만족시키지 못하는 결과를 초래하게 되는 것이다.
비정규화와 통합
비정규화 테이블은 일반적으로 컬럼의 개수가 많다. 컬럼의 개수가 많다는 이야기는 데이터 행의 크기가 크다는 뜻도 된다. 앞서 살펴본 바와 같이 하나의 데이터 행의 크기가 커지면 최소 입출력 단위에 데이터 행이 많이 들어가지 못한다. 심한 경우 입출력 동작이 늘어나고 잘못하면 데이터 행이 각각의 페이지에 존재할 수도 있다. 비정규화의 문제점에 대해 좀 더 자세히 알아보기 위해 <리스트 3>과 같은 테이블이 만들었다고 가정하자.
<리스트 3> 차량관리비내역 테이블
CREATE TABLE 차량관리비내역(
차량번호 VARCHAR(10)
, 관리부서 NUMBER
, 연료 NUMBER
, 주차비 NUMBER
, 세차비 NUMBER
, 윤활유 NUMBER
, 부품대 NUMBER
, 수리비 NUMBER
, 타이어교환비 NUMBER
, 자동차세 NUMBER
, 환경세 NUMBER
, 보험료 NUMBER)
CREATE TABLE 차량관리비내역(
차량번호 VARCHAR(10)
, 관리부서 NUMBER
, 연료 NUMBER
, 주차비 NUMBER
, 세차비 NUMBER
, 윤활유 NUMBER
, 부품대 NUMBER
, 수리비 NUMBER
, 타이어교환비 NUMBER
, 자동차세 NUMBER
, 환경세 NUMBER
, 보험료 NUMBER)
잘 보면 이와 같은 경우는 명백한 1차 정규화 위배라는 사실을 알 수 있다. 연료, 주차비, 세차비, 윤활유 등은 ‘차량관리비’ 정도로 만들고, ‘구분’ 컬럼을 만들면 된다. 즉, ‘차량관리비’는 다중 값 속성이 되어 다른 테이블로 떨어져 나가게 된다. 또한 여러 컬럼을 통해 다른 개체집합이 있음을 알 수 있다. 그래서 <그림 7>과 같은 논리 ERD가 그려질 것이다.
<그림 7> 차량관리비내역의 논리 ERD
각각의 테이블에 할당 받은 저장 공간을 효율적으로 사용하려면 테이블을 최대한 좁게 설계해야 한다. 좁게 설계한 데이터 행이어야만 최소 입출력 단위에 꽉꽉 채울 수 있기 때문이다. 이 과정에서 입출력단위가 줄어드는 것 뿐 아니라 여러 가지 이상 현상이 발생하는 것도 미연에 방지하는 효과를 함께 얻을 수 있다.
차량관리비내역에서 ‘검사비’가 추가된다고 가정하자. 비정규화된 경우는 테이블을 변경해야 새로운 컬럼을 추가할 수 있다. 그러나 <그림 7>과 같은 경우 비용과 구분을 추가하는 것으로 검사비 항목을 해결할 수 있다. 최소한의 변경만으로 유연성이 확보될 수 있는 것이다. 또한 인덱스와 같은 추가적인 물리적 객체의 생성도 막을 수 있다. 이것은 관리해야 할 객체의 수가 줄어드는 것을 의미한다.
차량관리비내역에서 ‘검사비’가 추가된다고 가정하자. 비정규화된 경우는 테이블을 변경해야 새로운 컬럼을 추가할 수 있다. 그러나 <그림 7>과 같은 경우 비용과 구분을 추가하는 것으로 검사비 항목을 해결할 수 있다. 최소한의 변경만으로 유연성이 확보될 수 있는 것이다. 또한 인덱스와 같은 추가적인 물리적 객체의 생성도 막을 수 있다. 이것은 관리해야 할 객체의 수가 줄어드는 것을 의미한다.
실제로 가로로 놓느냐 세로로 놓느냐는 업무에 따라 달라질 수 있다. 성능이 매우 중요한 경우에는 세로 보다는 가로로 놓는 편이 효과적일 때가 있다. 세로로 놓는 다는 것은 통합을 의미하며 대부분은 통합이 유리하다. 가로로 펼쳐 놓다 보면 인덱스를 유지하는 비용이 더 커지기 때문이다. 비슷한 예로 다음과 같은 스키마를 들 수 있다.
● 서적(ISBN, 도서명, 페이지수, 저자1, 저자2, 저자3)
● 회원(회원ID, 회원명, 전화번호, 가입일시 계약일시, 결제일시, 발송일시, 방문일시)
● 회원(회원ID, 회원명, 전화번호, 가입일시 계약일시, 결제일시, 발송일시, 방문일시)
<리스트 4> 회원정보 조회
SELECT
회원ID
, 회원명
, 전화번호
FROM 회원
WHERE
가입일시 = ''20060505''
OR 계약일시 = ''20060505''
OR 결제일시 = ''20060505''
OR 발송일시 = ''20060505''
OR 방문일시 = ''20060505''
SELECT
회원ID
, 회원명
, 전화번호
FROM 회원
WHERE
가입일시 = ''20060505''
OR 계약일시 = ''20060505''
OR 결제일시 = ''20060505''
OR 발송일시 = ''20060505''
OR 방문일시 = ''20060505''
먼저 서적의 스키마를 보자. 무엇이 문제인가? 저자1, 저자2, 저자3이 문제이다. 관계형 모델의 주요 원칙 중에 하나인 ‘컬럼의 원자 값’을 위반했기 때문이다. 컬럼의 원자 값이란 하나의 값이 아니라 단 하나의 의미를 가져야 한다는 것으로 보아야 한다. 저자1, 저자2, 저자3과 같이 표현된 것은 ‘저자’의 의미와 ‘순서’의 의미가 있다. 즉, 배열과 같은 의미로 쓰인 것이다. 물론 물리적인 구현은 DBMS의 배열형 컬럼에 대한 지원여부도 크다. 하지만 논리 모델은 논리 모델일 뿐 물리적인 것과는 독립성을 가져야 한다는 것이 원칙이다. 또한 두 모델에 추가 요구사항이 발생하면 어쩔 수 없이 테이블을 변경해야 한다. 서적의 경우 저자 1명이 늘어나면 ‘저자4’ 컬럼이 추가되어야 하며, 회원의 경우도 마찬가지로 ‘탈퇴일시’가 필요하다면 역시 컬럼이 추가되어야 하는 문제가 발생한다.
회원 스키마의 경우는 어떠한가? 서적과는 조금 차이를 보일 것이다. 서적의 경우는 단순히 다중 값 속성을 컬럼으로 늘어뜨린 경우이다. 하지만 회원의 경우에는 이렇게 하기가 어렵다. 회원의 경우에는 <리스트 4>와 같은 SQL을 요구할 수 있다.
<리스트 4>처럼 회원을 관리하는 경우 회원이 천만 명이라면 어떤 요구가 발생했을 때 천만 건의 데이터를 모두 검색해야 원 하는 결과를 얻을 수 있는 셈이 된다. 그렇다면 인덱스는 어떨 까? 가입일시, 계약일시 등 모든 컬럼에 인덱스를 생성하는 것 역시 쉽지 않은 일이다. 이렇게 많은 데이터를 처리해야 할 때에 는 데이터를 통합하는 것이 가장 좋은 방법이다. <그림 8>과 같은 테이블을 구성하면 SQL이 어떻게 바뀌는지 살펴보자.
<그림 8> 회원 정보 테이블
<리스트 5> 회원 정보 테이블의 SQL
SELECT
회원ID
, 회원명
, 전화번호
FROM 회원
WHERE
발생일시 = ''20060505''
AND 발생구분 IN (''가입'', ''계약'', ''결제'', ''발송'', ''방문'')
SELECT
회원ID
, 회원명
, 전화번호
FROM 회원
WHERE
발생일시 = ''20060505''
AND 발생구분 IN (''가입'', ''계약'', ''결제'', ''발송'', ''방문'')
얼핏 보아도 SQL이 상당히 간단해 졌다는 것을 확인할 수 있 다. 인덱스는‘발생일시 + 발생구분’으로 결합 인덱스를 생성하 면 추가 요구사항이 발생해도 쉽게 원하는 결과를 가져올 수 있 다. 즉, ‘탈퇴일시’라는 추가적인 요구사항이 발생하면‘발생구 분’컬럼 값의 도메인에‘탈퇴’가 추가되면 된다. 이는 테이블이 변하는 것이 아니라 값이 변하는 것이기 때문에 테이블 구조는 전혀 변할 필요가 없다. 여기서 놓치지 말아야 할 문제가 있는데 바로 <그림 8>의‘회 원’이라는 명칭이다. 이는 회원이 어떤 행위를 했을 때 하나의 개 체가 생성되는 행위개체집합이다. 그러므로‘회원’은 아닐 것이 다. 최종적으로는 관계의 통합에 의해 발생된 형태로 볼 수 있다. 쉽게 생각해보면 발생구분 한 개의 개체가 생성될 때마다 회원명, 전화번호가 중복되어 저장된다. <그림 8>에서 회원ID가 yasicom인 회원이 탈퇴 했다고 가정하여 데이터를 삽입하면 중 복되는 것을 알 수 있다. 그러므로 이 스키마는 정규화 대상이다. 현재까지 살펴 본 것으로만 판단하면 처음에 소개했던 옆으로 늘어뜨린 스키마구조는 여러 가지로 불리하다. 하지만 추가적인 요구사항이 발생하지 않고, 앞서 예로 든 SQL과 같이 요구가 없 이 하나의 행만 접근하는 경우라면 옆으로 늘어뜨린 비정규화가 더 유리하다. <그림 7>과 같은 경우는‘발생구분’컬럼이 추가됨 으로서 저장 공간이 늘어나는 단점이 있다. 어떤 것이 유리한지 는 현재와 미래의 상황을 고려한 종합적인 판단이 요구된다.
인덱스 종류와 선택
인덱스는 데이터베이스 물리 설계에서 매우 중요한 위치를 차지한다. 같은 데이터베이스라 하더라도 인덱스 활용 전략에 따라서 매우 많은 성능의 차이를 보이기 때문이다. 여기에서는 인덱스의 종류와 어떤 경우에 어떤 인덱스를 적용해야 하는지 알아보도록 한다.
일반적인 RDBMS에서의 인덱스는 다음과 같이 두 가지 종류로 나뉜다.
일반적인 RDBMS에서의 인덱스는 다음과 같이 두 가지 종류로 나뉜다.
● 스파스 인덱스(Sparse Index) : 해당 레코드 존재 페이지를 가리키는 포인터를 저장하는 인덱스
● 덴스 인덱스(Dense Index) : 해당 레코드를 가리키는 포인터를 저장하는 인덱스
* 힙(Heap) : 데이터가 입력되는 순서에 따라 정렬되어 쌓이는 테이블 구조
● 덴스 인덱스(Dense Index) : 해당 레코드를 가리키는 포인터를 저장하는 인덱스
* 힙(Heap) : 데이터가 입력되는 순서에 따라 정렬되어 쌓이는 테이블 구조
페이지에는 여러 개의 레코드가 있으므로 일반적인 경우는 스파스 인덱스가 유리하다. 하지만 레코드의 길이가 페이지의 크기와 비슷한 경우라면 스파스 인덱스는 덴스 구조보다 디스크 액세스를 많이 수행하게 되므로 성능이 떨어진다. 물론 늘 그런 것은 아니며 DBMS가 어떻게 구성되었느냐에 따라 다른 결과가 나타날 수도 있다. 만약 해당 DBMS에서 데이터 행 체인(Row Chain)이 발생된다면 불리할 수 있으나 체인을 지원하지 않고, 행 마이그레이션(Row Migration)만 지원한다면 큰 차이가 없다. 어떤 DBMS는 최소 입/출력 단위에 데이터를 꽉꽉 채울 수 있는 반면, 어떤 DBMS는 데이터를 모두 채울 수 없기 때문에 두 인덱스 방식의 장/단점 비교는 조건이 잘 주어져야 가능하다.
상용 DBMS는 <표 1>과 같이 다양한 종류의 인덱스를 지원한다. 오라클에서는 인덱스 조직 테이블(Indexed-Organized Table)이 지원되는데 이는 MS SQL 서버의 클러스터드 인덱스를 가진 테이블과 비슷한 구조로 생각하면 되며 스파스 인덱스 방식이라고 생각해도 무방하다.
또 한 가지 중요한 사실은 스파스 인덱스와 덴스 인덱스가 혼재되어 있을 경우 나누어 저장하는 것이 효과적이라는 점이다.
데이터베이스 시스템과 떼어놓을 수 없는 중요한 사항이 바로 트랜잭션이다. 작은 트랜잭션 하나가 잠금을 얻으면 잠금 수준에 따라서 많은 양의 트랜잭션이 대기할 수도 있다. 힙과 같은 구조는 데이터가 들어오는 순서대로 쌓이기 때문에 특정 페이지(최소 입/출력 단위) 잠금이 걸린다면 해당 페이지에 접근하고 있는 트랜잭션은 모두 대기 상태가 된다. 이러한 경우 레코드 단위의 잠금을 걸면 해결되지만, 레코드의 입력이 너무 빈번하게 발생한다면 결국 병목현상이 발생할 수밖에 없다. 그러므로 MS SQL Server와 같은 경우에는 테이블에 클러스터드 인덱스(Sparse Index)를 생성하기를 권한다.
하지만 클러스터드 인덱스는 추가(Insert), 갱신(Update), 삭제(Delete)할 때마다 데이터를 재정렬해야 하기 때문에 부하가 많이 걸린다. 범위 연산이나 한 번에 많은 데이터를 가져와야 하는 경우라면 클러스터드 인덱스를 생성해야 한다. 일반적인 OLTP시스템이라면 클러스터드 인덱스는 권장사항이다. 그러나 특정 행 단위 위주로 접근하는 경우라면 클러스터드 인덱스는 사용하지 않는 편이 좋다. 조회 성능이 중요한 클러스터드 인덱스 생성은 적극 고려해야 할 대상이다.
만약 시간이 된다면 주로 사용하는 인덱스의 순서에 맞추어 테이블의 데이터를 다시 넣어두면 성능을 높이는데 도움이 된다. 또한 추가, 갱신, 삭제 시 인덱스도 같이 변경되어야 하므로 MS SQL Server는 PAD_INDEX, FILLFACTOR 옵션을 인덱스 생성, 변경 시 제공하고 있다. 이 두 옵션은 인덱스의 저장 공간을 비워두어 추가적인 작업(Insert 등)에 대해 인덱스의 재조정을 막는 역할을 한다.
<그림 9> 스파스 인덱스와 덴스 인덱스
상용 DBMS는 <표 1>과 같이 다양한 종류의 인덱스를 지원한다. 오라클에서는 인덱스 조직 테이블(Indexed-Organized Table)이 지원되는데 이는 MS SQL 서버의 클러스터드 인덱스를 가진 테이블과 비슷한 구조로 생각하면 되며 스파스 인덱스 방식이라고 생각해도 무방하다.
<그림10> 힙(Heap)에서의 트랜잭션 집중
또 한 가지 중요한 사실은 스파스 인덱스와 덴스 인덱스가 혼재되어 있을 경우 나누어 저장하는 것이 효과적이라는 점이다.
데이터베이스 시스템과 떼어놓을 수 없는 중요한 사항이 바로 트랜잭션이다. 작은 트랜잭션 하나가 잠금을 얻으면 잠금 수준에 따라서 많은 양의 트랜잭션이 대기할 수도 있다. 힙과 같은 구조는 데이터가 들어오는 순서대로 쌓이기 때문에 특정 페이지(최소 입/출력 단위) 잠금이 걸린다면 해당 페이지에 접근하고 있는 트랜잭션은 모두 대기 상태가 된다. 이러한 경우 레코드 단위의 잠금을 걸면 해결되지만, 레코드의 입력이 너무 빈번하게 발생한다면 결국 병목현상이 발생할 수밖에 없다. 그러므로 MS SQL Server와 같은 경우에는 테이블에 클러스터드 인덱스(Sparse Index)를 생성하기를 권한다.
하지만 클러스터드 인덱스는 추가(Insert), 갱신(Update), 삭제(Delete)할 때마다 데이터를 재정렬해야 하기 때문에 부하가 많이 걸린다. 범위 연산이나 한 번에 많은 데이터를 가져와야 하는 경우라면 클러스터드 인덱스를 생성해야 한다. 일반적인 OLTP시스템이라면 클러스터드 인덱스는 권장사항이다. 그러나 특정 행 단위 위주로 접근하는 경우라면 클러스터드 인덱스는 사용하지 않는 편이 좋다. 조회 성능이 중요한 클러스터드 인덱스 생성은 적극 고려해야 할 대상이다.
만약 시간이 된다면 주로 사용하는 인덱스의 순서에 맞추어 테이블의 데이터를 다시 넣어두면 성능을 높이는데 도움이 된다. 또한 추가, 갱신, 삭제 시 인덱스도 같이 변경되어야 하므로 MS SQL Server는 PAD_INDEX, FILLFACTOR 옵션을 인덱스 생성, 변경 시 제공하고 있다. 이 두 옵션은 인덱스의 저장 공간을 비워두어 추가적인 작업(Insert 등)에 대해 인덱스의 재조정을 막는 역할을 한다.
테이블의 수평 분할/수직 분할
커뮤니티의 질문 게시판에 가보면 테이블의 분할에 대한 질문이 자주 올라오곤 한다. 또한 데이터가 늘어남에 따라 성능이 느려졌다고 판단한 관리자들이 테이블의 분할을 고려하기도 한다. 실제로 이런 이유 때문에 많은 사이트에서 테이블을 이미 분할하여 사용하고 있다. 하지만, 테이블을 분할해서 사용하는 이유에 대해 구체적인 판단 근거를 제시하지 못하는 경우가 대부분이다. DBMS는 총 행의 수가 백건 중 1건을 가져오는 것이나 천만 건 중 1건을 가져오는 것이나 비슷한 시간을 가지도록 설계된다. 이러한 설계는 위에서 살펴본 인덱스에 기반을 두고 있다. 천만 건 중 1건이나 백건 중 1건이나 처리하는 시간이 비슷하다면 왜 테이블 분할을 고려해야 하는 걸까? <그림 11>을 보자.
<그림11> 컬럼별로 서로 다른 트랜잭션 처리 형태
테이블에서 회원ID, 패스워드, 회원명, 전화번호에 대해서 집중적인 범위 조회가 일어나고, 접속일시는 집중적인 갱신 트랜잭션이 발생한다고 가정해 보자. 만약 회원ID가 yasicom인 회원의 접속일시를 ‘20060505’로 갱신한다면 해당 행에 대해 잠금(Lock)이 걸리게 된다. 그러면 yasicom을 포함하는 범위조회는 잠금이 풀릴 때까지 대기를 해야 한다. 또한 읽는 동안의 일관성을 보장해야 하므로 갱신 트랜잭션도 대기해야 하는 상황이 발생할 수 있다. 이러한 경우라면 성능에 매우 큰 지장을 주기 때문에 수직분할을 하는 것이 바람직하다. <그림 12>는 수직분할의 예이다.
<그림12> 회원 테이블의 수직분할
일반적으로 수평분할은 데이터가 1억 건을 넘을 경우에나 고려해 볼 만 하다. 물론 컬럼의 수가 많다면 행의 수가 더 적더라도 수평 분할을 고려해야 할 것이다. 데이터베이스가 대용량이라면 고려해야 할 사항들이 매우 많아진다. 예를 들면 인덱스를 다시 생성하는데 걸리는 시간이 길어지고 병렬처리 및 백업/복원 전략도 달라진다. 또, 트랜잭션에 대해서도 고려해야 한다. MS SQL Server의 경우 행에 4,861개 이상의 잠금이 걸리면 잠금의 수준이 높아지고, 다른 트랜잭션은 대기(블록킹)하게 된다. 실제로 잠금은 데이터의 일관성을 보장하기 위해 꼭 필요한 것이다. 대기하는 것 자체가 문제는 아니지만 그로 인해 작업이 큐(Queue)에 쌓이게 되어, 잠금이 풀릴 때 한 번에 많은 양의 부하가 걸리는 원인이 되기도 한다.
문제는 개발자들이 테이블의 분할을 너무 쉽게 생각한다는 데 있다. 테이블을 분할하면 처리과정이 복잡해지는 것은 당연하다. 또한 중복된 데이터를 버려야 하는지 중복된 데이터를 가지고 있어야 하는지에 대한 고려도 필요하다. 분할을 할 때는 반드시 타당한 근거를 제시해야 하며, 단순히 빨라질 것이라는 기대만으로 수평분할을 하게 되면 많은 사람이 고생하게 되므로 충분히 고려를 하는 것이 좋다.
대부분의 경우 데이터베이스 튜닝은 고급기술을 필요로 하지는 않는다. 데이터베이스 문제의 대략 70% 이상은 기본적인 사항을 지키지 않기 때문에 발생한다. 무슨 뜻인지도 모르고 기본이 받쳐주지 않는 상황에서 경험에 의존하는 것도 문제다. 어떤 주먹구구의 선두주자는 사용자 화면마다 테이블을 생성하기도 한다. 테이블 명칭도 ‘매입입력’과 같이 전형적인 프로세스에 의존적인 테이블을 만들고 있다. 현실을 반영하기는커녕 전혀 근거도 없는 자신만의 세계를 구축하기도 한다. 예전에 이렇게 했었는데도 잘만 돌아가더라는 것이 그 이유이다. 그리고는 하드웨어나 DBMS에 핑계를 떠넘기는 것은 쉽게 마주할 수 있는 일들이다.
데이터베이스에서 어떤 결정을 내리기 위해서는 반드시 합당한 근거를 제시하여 올바른 방향을 설정해야 한다. 어떠한 시스템이든지 특정 한 부분이 잘 구성되거나 잘 개발되었다고 하여 시스템의 성능이 향상되는 것은 아니다. 개발 초기부터 명확히 정의하고, 각각의 단계를 차곡차곡 밟아나가며, 물리적인 구현에서는 주어진 시스템의 자원을 최소한으로 사용하여 최대한의 성능을 발휘할 수 있도록 노력해야 한다. 데이터베이스의 설계와 튜닝에 있어서는 어느 하나도 무시할 수가 없다. 아무리 애플리케이션 튜닝이 잘 되었다고 해도 하드웨어가 받쳐주지 못하면 성능을 제대로 발휘할 수 없다. 튜닝에 답은 없다. 어떠한 경우든지 상대적이다.
사고방식을 바꾸는 것은 매우 중요하다. 그러므로 대부분의 튜닝서적은 계몽서의 느낌이 든다. 다들 왜 그렇게 잔소리가 많은지를 알아야 한다. 필자도 중간 중간에 잔소리를 했을 것이다. 눈에 거슬렸다면 필자의 순수한 마음에서 우러난 조언이었다고 생각해주기 바란다.
SQL 문을 튜닝하는 경우 특정 부분만 살짝 바꾸어 주어도 성능이 월등히 향상되는 경우가 많이 있는데 원래 그렇게 해야 할 것을 고친 것이므로 좋아할 필요는 없다. 주어진 시스템의 자원을 최대한 활용하게 하는 것이 튜닝의 목적이다. 궁극적으로 추구해야 할 것은 우리가 개발한 시스템에 사용자가 불만을 가지지 않도록 하는 것이 목적이다. 여기에 나오는 내용들은 모두 사용자의 정보욕구를 만족시키기 위한 것일 뿐이다. 사용자의 정보욕구를 만족시키고 사용자로부터 참 유용한 정보가 많이 나오는 시스템이란 소리를 들었을 때. 바로 그때가 우리가 기뻐해야 할 순간이다.
참고자료
1. 실무 예제 중심의 고급 SQL 서버 개발자 가이드， 피어슨에듀케이션코리아，Ken Henderson
2. Microsoft SQL Server 2000 성능튜닝 Technical Reference, 정보문화사，Steve Adrien Deluca외 3. 강력한 SQL 프로그래밍 을 위 한건nsiqht, Ken Henderson
4. 대용량 데이터베이스 솔루션, 대청，이화식，조광원
5. 데이터 아키텍처, 대청，이화식
6. 데이터 아키텍춰, 형설, 문송천
7. 데이터베이스 처리론，교보，나연묵외
8. 데이터베이스 시스템，인터비전，김형주
9. 데이터베이스 시스템，정익사，이석호
10. 데이터베이스 설계，홍릉과학출판사，조규익
11. 데이터베이스 설계와 구축，한빛미디어，이춘식
12. 데이타베이스 설계，홍릉과학출판사，박석
13. 정보공학 1, 2, 영한출판사，제임스마틴
14. 데이터베이스 관리，박문각，서길수
15. Database Tuning, 브레인코리아, 최용락외
1. 실무 예제 중심의 고급 SQL 서버 개발자 가이드， 피어슨에듀케이션코리아，Ken Henderson
2. Microsoft SQL Server 2000 성능튜닝 Technical Reference, 정보문화사，Steve Adrien Deluca외 3. 강력한 SQL 프로그래밍 을 위 한건nsiqht, Ken Henderson
4. 대용량 데이터베이스 솔루션, 대청，이화식，조광원
5. 데이터 아키텍처, 대청，이화식
6. 데이터 아키텍춰, 형설, 문송천
7. 데이터베이스 처리론，교보，나연묵외
8. 데이터베이스 시스템，인터비전，김형주
9. 데이터베이스 시스템，정익사，이석호
10. 데이터베이스 설계，홍릉과학출판사，조규익
11. 데이터베이스 설계와 구축，한빛미디어，이춘식
12. 데이타베이스 설계，홍릉과학출판사，박석
13. 정보공학 1, 2, 영한출판사，제임스마틴
14. 데이터베이스 관리，박문각，서길수
15. Database Tuning, 브레인코리아, 최용락외
출처명: 마이크로 소프트웨어 -[2006년 6월호]
DB
| 2007-06-28 16:05:03
Hit : 301
.반드시 컴파일 하라! 10-30% 속도 향상 !
소스를 가지고 컴파일 하세요. MySQL 메뉴얼에 따르면 10-30% 속도가 빠르다고 합니다.
RPM 이나 바이너리 설치를 하지 마세요 !
1-2.최신 버전을 사용하라
최신 버전이 좋은 점은 자동 튜닝 하는 것 입니다.
버그를 수정 하구요. 되도록 이면 최신 버전을 사용하세요 !!
현재 3.23.49 입니다.
2. HEAP 테이블이 가장 빠르다!
일반적으로 가장 많이 쓰이는 테이블 타입은 MyISAM 타입 입니다.
MyISAM 타입은 무자게 빠르며, 대용량에도 강합니다. 그러나 트랜잭션은 지원되지 않습니다.
이노디비(InnoDB) 는 트랜잭션이 지원 됩니다. 쇼핑몰에서는 반드시 사용해야 합니다 ^^
HEAP 테이블 타입은 가장 빠르며, 단점은 메모리에 있기 때문에, MySQL에 중지 될 경우 모두 날아 갑니다.
검색을 하고 재검색을 다시 하는 경우, 임시 검색 테이블을 만들어 놓는 것도 좋은 방법입니다.
HEAP 테이블 메뉴얼 !
http://www.mysql.com/doc/H/E/HEAP.html
HEAP 테이블 만들기 !
mysql>CREATE TABLE email_addresses TYPE=HEAP (
->email char(55) NOT NULL,
->name char(30) NOT NULL,
->PRIMARY KEY(email) );
3.mysql 서버 top 보기
mysql 서버의 메모리 상황을 보여 주는 프로그램 입니다.
리눅스나 유닉스의 top 기능을 mysql 에서 가능하게 한것 입니다.
top 정보는 튜닝의 기본 이기 땜시 자주 자주 보아야 합니다. ^^
http://public.yahoo.com/~jzawodn/mytop/
PHP 소스 자료실에 파일 다운 로드 하시면 됩니다.
4.mysql_connect Vs mysql_pconnect
서버 메모리가 최소 2G 이상일 경우 mysql_pconnect 를 추천 함다 !
연결을 계속 하지 않기 땜시 빠릅니다. !
그러나 메모리가 2G 이하 일 경우는 mysql_connect 사용하세요 !
5.int,smallint,tinyint 데이터형 !
int 는 굉장히 큰수 입니다. 4바이트를 차지 하구요.
tinyint 는 몇백 까지만 됩니다. 1바이트 구요.
쓸데 없이 int 를 사용하지 마세용 !!
4바이트와 1바이트는 4배 차이 입니다.조그만것 1개 1개가 모여 서버 부하를 일으 킵니다.!!
데이터 량이 얼마만큼 들어가는지 체크 하고 데이터형을 선택 하세요 ^^
만약 쓸데없는 데이터 형이다 싶으면 alter table 로 데이터 형을 바꾸세요 !
6.인덱스의 사용
인덱스는 반드시 필요한 곳에만 넣으세요 !
인덱스를 줄 경우 하드 용량을 더 차지 하기 때문에 속도를 떨어 뜨릴 수 있습니다.
모든 칼럼에 인덱스를 주는 것은 절대 추천 하지 않습니다.
1개의 테이블에 주키외에 2-3 개 이상의 인덱스는 주지 마세요!
주키는 당근 인덱스 입니다. ^^
CREATE TABLE albums (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(80)NOT NULL,
INDEX title_idx (title)
);
☞Alter Table 로 인덱스 추가
ALTER TABLE albums ADD INDEX title_idx (title)
결합 인덱스의 경우 너무 많은 인덱스를 사용할 경우 CPU 오버헤드나 하드 오버헤드를 불러 일으 킵니다.
적당히 사용하세요 ^^
http://www.mysql.com/doc/I/n/Indexes.html
http://www.mysql.com/doc/M/u/Multiple-column_indexes.html
http://www.mysql.com/doc/O/R/ORDER_BY_optimisation.html
6-1. 바보 같은 인덱스의 사용 ?
인덱스는 %$search% 가 먹지 않습니다.
그런디 게시판 제목(Subject) 에 인덱스 걸어 놓고 , 검색을 %$search% 이렇게 하면 될까요?
인덱스 거나 안거나 똑같습니다. !!
$search% 이렇게 사용하세요. 그런디.. $search% 사용하면 제목 처음 단어 밖에 검색이 안됩니다.
그렇다면 ? 다른 검색 방법은 ?
7.UDF의 사용
MySQL은 스토어드 프로시져 같은 개념이 존재 하지 않습니다.
그대신 C 언어로 만든 함수를 사용할 수 있습니다.
조금더 빠른 쿼리를 원한다면 UDF 를 사용해보세요 !
UDF 함수 보기
http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/
http://www.mysql.com/doc/A/d/Adding_functions.html
스토어드 프로시져가 먼뎅?
스토어드 프로시져는 쉽게 말해 MS-SQL 함수 입니다.
오라클에도 아마 있을검당..^^
그러니까 게시판에서 내용을 넘길때나 불러 올때
mysql 쿼리가 3-4 번 정도 이루어 집니다. 또는 ms- sql 쿼리가 이루어지죵..
3-4 번 정도 쿼리가 되면..그만큼 디비 접속이 잦아 지기 때문에..
속도가 느려 집니다.
많게는 10번 정도의 insert into 와 update 가 이루어집니다.
그래서 ms - sql 자체 내에 인서트 함수 나 목록 보기 함수를 만들어 놓습니다.
글구 1번의 ms-sql 접속만 해서 인서트 함수를 불러서 처리하는 것입니다.
그렇기 때문에 2-3 번의 쿼리가 절약 되서 빠르다는 것이죵..ㅋㅋㅋ
또는 10번의 쿼리 할것을 MS-SQL 스토어드 프로시져를 1번만 호출 함으로 해서 디비 접속이 절약이 되죵..ㅌㅌ
UDF 를 꼭 사용해야 하는가?
안해도 됩니다.만... 사용하면 좋은점 많습니다. 새로운 함수를 추가 할 수 있으므로 ^^
MS-SQL의 스토어드 프로시져 기능 비스므리 하게 사용할 수 도 있구요...
UDF 나 MS-SQL 스토어드 프로시져의 사용법을 익히기 보다는 캐슁을 연구하세용 ^^
동적인 PHP 를 정적인 HTML 로 만드는 방법을요...
또는 UDF 에서 MS-SQL 스토어드 프로시져 처럼 사용이 가능 합니다. 그 부분을 연구하세요.
www.zdnet.co.kr 이나 www.zdnet.com 가시면 기사 파일이 1000,29920,2892.html 파일 이란것을 보게 됩니다.
어키 구현된것일까요? zdnet 게네 들은 강좌를 원래 부터 HTML 로 만들어서 올리는 것일까용??
HTML 로 만드는 부분도 많이 생각 해야 합니다. 강좌가 1만개 라면, 1만개의 파일이 생성 됩니다.
zdnet 의 경우는 조회수가 10만-20만을 넘는 초대형 사이트 이기 때문에 HTML 로 만드는 것이 퍼포먼스가 좋습니다.
UDF 배워 두면..남주지 않습니다.
8.조인보다는 쿼리를 나누어라!
조인(Join)하는 것보다 쿼리를 2개로 나누는 것이 속도가 빠릅니다.
조인을 생각 하기 이전에 쿼리를 나누는 것을 생각 하세요 ^^
어쩔 수 없는 경우는 당근 조인 해야죠.
글고 서브쿼리는 아직 지원 안됩니다.
Ms-SQL이나 오라클에서 서브쿼리 보다는 서브쿼리를 하지 않는 방향의 데이터 정규화를 하세요 ^^
조인 보다 서브쿼리가 느리다.
서브 쿼리 보다는 조인을 사용하세요 ^^
9.full text index와 search
3.23.23 부터 mysql 에서는 full text index 를 지원 합니다. 자세한 사항은 아래 !
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search
http://www.mysql.com/doc/F/u/Fulltext_Fine-tuning.html
10. SELECT * FROM sometable
SELECT * FROM sometable 에서 * 모든을 사용하는 것은 무식한 방법 입니다.
모든 칼럼을 불러오는 경우는 드물거든요.
SELECT code,tadate,see FROM sometable
사용할 것만 불러 오세요 ^^
11.데이터베이스 정규화
테이블을 아무렇게나 만들면 안됩니다.
데이터베이스 정규화 원칙에 의거, 테이블을 나눌것은 나누고 만드시는 것이 좋습니다.
제1 정규화, 제2 정규화 정도는 사용하셔야 합니다.
게시판을 만들때 아직도 테이블 1개에 만드시나요?
온라인 폴 만들때 , 테이블 1개에 만드시나요?
12.REPLACE INTO문 사용하기
REPLACE INTO albums VALUES (6, "tood.net")
insert 문대신에 replace 문을 사용해보세요.
메뉴얼 보시고 연구하세요 ^^
주키일 경우 사용하시면 됩니다.
13. explain 사용하기
explain 를 사용하여 테이블의 키 값이 얼마나 잘 활용 되는지 알 수 있습니다.
EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS, SHOW PROCESSLIST
http://www.mysql.com/doc/E/X/EXPLAIN.html
17.BLOB과 TEXT는 분리하라
BLOB과 TEXT 칼럼은 테이블을 분리 하는 것이 좋다. 다른 칼럼의 내용 보다 크기 때문이다 !
OPTIMIZE TABLE 명령을 자주 사용해라 !
Not null 로 지정 하는 것이 빠르다.
varchar 보다 char 이 훨빠르다.
참고: 게시판의 특성
일반 게시판들 보세요. 여기 질문 게시판 만 봐도 알 수 있구요.
읽기(select) 가 무자게 많습니다. 쓰기(insert into) 보다 30-40 배 정도 많죠.
하룻동안 올라온 글이 30 개 라면, 하룻동안 글읽기 조회수 총 합은 최소 100 에서 900 까지 갑니다.
물론 더 엄청날 수 있구요.
그렇기 때문에 게시판 목록 보기와 글읽기는 HTML 파일로 만들어 놓는 것이 좋습니다.
또는 MySQL 에서 튜닝 시에 insert 보다는 select 가 빨리 되도록 튜닝 하는 것이 좋구요 ^^
[참고사이트]
http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#SEC457
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search
소스를 가지고 컴파일 하세요. MySQL 메뉴얼에 따르면 10-30% 속도가 빠르다고 합니다.
RPM 이나 바이너리 설치를 하지 마세요 !
1-2.최신 버전을 사용하라
최신 버전이 좋은 점은 자동 튜닝 하는 것 입니다.
버그를 수정 하구요. 되도록 이면 최신 버전을 사용하세요 !!
현재 3.23.49 입니다.
2. HEAP 테이블이 가장 빠르다!
일반적으로 가장 많이 쓰이는 테이블 타입은 MyISAM 타입 입니다.
MyISAM 타입은 무자게 빠르며, 대용량에도 강합니다. 그러나 트랜잭션은 지원되지 않습니다.
이노디비(InnoDB) 는 트랜잭션이 지원 됩니다. 쇼핑몰에서는 반드시 사용해야 합니다 ^^
HEAP 테이블 타입은 가장 빠르며, 단점은 메모리에 있기 때문에, MySQL에 중지 될 경우 모두 날아 갑니다.
검색을 하고 재검색을 다시 하는 경우, 임시 검색 테이블을 만들어 놓는 것도 좋은 방법입니다.
HEAP 테이블 메뉴얼 !
http://www.mysql.com/doc/H/E/HEAP.html
HEAP 테이블 만들기 !
mysql>CREATE TABLE email_addresses TYPE=HEAP (
->email char(55) NOT NULL,
->name char(30) NOT NULL,
->PRIMARY KEY(email) );
3.mysql 서버 top 보기
mysql 서버의 메모리 상황을 보여 주는 프로그램 입니다.
리눅스나 유닉스의 top 기능을 mysql 에서 가능하게 한것 입니다.
top 정보는 튜닝의 기본 이기 땜시 자주 자주 보아야 합니다. ^^
http://public.yahoo.com/~jzawodn/mytop/
PHP 소스 자료실에 파일 다운 로드 하시면 됩니다.
4.mysql_connect Vs mysql_pconnect
서버 메모리가 최소 2G 이상일 경우 mysql_pconnect 를 추천 함다 !
연결을 계속 하지 않기 땜시 빠릅니다. !
그러나 메모리가 2G 이하 일 경우는 mysql_connect 사용하세요 !
5.int,smallint,tinyint 데이터형 !
int 는 굉장히 큰수 입니다. 4바이트를 차지 하구요.
tinyint 는 몇백 까지만 됩니다. 1바이트 구요.
쓸데 없이 int 를 사용하지 마세용 !!
4바이트와 1바이트는 4배 차이 입니다.조그만것 1개 1개가 모여 서버 부하를 일으 킵니다.!!
데이터 량이 얼마만큼 들어가는지 체크 하고 데이터형을 선택 하세요 ^^
만약 쓸데없는 데이터 형이다 싶으면 alter table 로 데이터 형을 바꾸세요 !
6.인덱스의 사용
인덱스는 반드시 필요한 곳에만 넣으세요 !
인덱스를 줄 경우 하드 용량을 더 차지 하기 때문에 속도를 떨어 뜨릴 수 있습니다.
모든 칼럼에 인덱스를 주는 것은 절대 추천 하지 않습니다.
1개의 테이블에 주키외에 2-3 개 이상의 인덱스는 주지 마세요!
주키는 당근 인덱스 입니다. ^^
CREATE TABLE albums (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(80)NOT NULL,
INDEX title_idx (title)
);
☞Alter Table 로 인덱스 추가
ALTER TABLE albums ADD INDEX title_idx (title)
결합 인덱스의 경우 너무 많은 인덱스를 사용할 경우 CPU 오버헤드나 하드 오버헤드를 불러 일으 킵니다.
적당히 사용하세요 ^^
http://www.mysql.com/doc/I/n/Indexes.html
http://www.mysql.com/doc/M/u/Multiple-column_indexes.html
http://www.mysql.com/doc/O/R/ORDER_BY_optimisation.html
6-1. 바보 같은 인덱스의 사용 ?
인덱스는 %$search% 가 먹지 않습니다.
그런디 게시판 제목(Subject) 에 인덱스 걸어 놓고 , 검색을 %$search% 이렇게 하면 될까요?
인덱스 거나 안거나 똑같습니다. !!
$search% 이렇게 사용하세요. 그런디.. $search% 사용하면 제목 처음 단어 밖에 검색이 안됩니다.
그렇다면 ? 다른 검색 방법은 ?
7.UDF의 사용
MySQL은 스토어드 프로시져 같은 개념이 존재 하지 않습니다.
그대신 C 언어로 만든 함수를 사용할 수 있습니다.
조금더 빠른 쿼리를 원한다면 UDF 를 사용해보세요 !
UDF 함수 보기
http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/
http://www.mysql.com/doc/A/d/Adding_functions.html
스토어드 프로시져가 먼뎅?
스토어드 프로시져는 쉽게 말해 MS-SQL 함수 입니다.
오라클에도 아마 있을검당..^^
그러니까 게시판에서 내용을 넘길때나 불러 올때
mysql 쿼리가 3-4 번 정도 이루어 집니다. 또는 ms- sql 쿼리가 이루어지죵..
3-4 번 정도 쿼리가 되면..그만큼 디비 접속이 잦아 지기 때문에..
속도가 느려 집니다.
많게는 10번 정도의 insert into 와 update 가 이루어집니다.
그래서 ms - sql 자체 내에 인서트 함수 나 목록 보기 함수를 만들어 놓습니다.
글구 1번의 ms-sql 접속만 해서 인서트 함수를 불러서 처리하는 것입니다.
그렇기 때문에 2-3 번의 쿼리가 절약 되서 빠르다는 것이죵..ㅋㅋㅋ
또는 10번의 쿼리 할것을 MS-SQL 스토어드 프로시져를 1번만 호출 함으로 해서 디비 접속이 절약이 되죵..ㅌㅌ
UDF 를 꼭 사용해야 하는가?
안해도 됩니다.만... 사용하면 좋은점 많습니다. 새로운 함수를 추가 할 수 있으므로 ^^
MS-SQL의 스토어드 프로시져 기능 비스므리 하게 사용할 수 도 있구요...
UDF 나 MS-SQL 스토어드 프로시져의 사용법을 익히기 보다는 캐슁을 연구하세용 ^^
동적인 PHP 를 정적인 HTML 로 만드는 방법을요...
또는 UDF 에서 MS-SQL 스토어드 프로시져 처럼 사용이 가능 합니다. 그 부분을 연구하세요.
www.zdnet.co.kr 이나 www.zdnet.com 가시면 기사 파일이 1000,29920,2892.html 파일 이란것을 보게 됩니다.
어키 구현된것일까요? zdnet 게네 들은 강좌를 원래 부터 HTML 로 만들어서 올리는 것일까용??
HTML 로 만드는 부분도 많이 생각 해야 합니다. 강좌가 1만개 라면, 1만개의 파일이 생성 됩니다.
zdnet 의 경우는 조회수가 10만-20만을 넘는 초대형 사이트 이기 때문에 HTML 로 만드는 것이 퍼포먼스가 좋습니다.
UDF 배워 두면..남주지 않습니다.
8.조인보다는 쿼리를 나누어라!
조인(Join)하는 것보다 쿼리를 2개로 나누는 것이 속도가 빠릅니다.
조인을 생각 하기 이전에 쿼리를 나누는 것을 생각 하세요 ^^
어쩔 수 없는 경우는 당근 조인 해야죠.
글고 서브쿼리는 아직 지원 안됩니다.
Ms-SQL이나 오라클에서 서브쿼리 보다는 서브쿼리를 하지 않는 방향의 데이터 정규화를 하세요 ^^
조인 보다 서브쿼리가 느리다.
서브 쿼리 보다는 조인을 사용하세요 ^^
9.full text index와 search
3.23.23 부터 mysql 에서는 full text index 를 지원 합니다. 자세한 사항은 아래 !
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search
http://www.mysql.com/doc/F/u/Fulltext_Fine-tuning.html
10. SELECT * FROM sometable
SELECT * FROM sometable 에서 * 모든을 사용하는 것은 무식한 방법 입니다.
모든 칼럼을 불러오는 경우는 드물거든요.
SELECT code,tadate,see FROM sometable
사용할 것만 불러 오세요 ^^
11.데이터베이스 정규화
테이블을 아무렇게나 만들면 안됩니다.
데이터베이스 정규화 원칙에 의거, 테이블을 나눌것은 나누고 만드시는 것이 좋습니다.
제1 정규화, 제2 정규화 정도는 사용하셔야 합니다.
게시판을 만들때 아직도 테이블 1개에 만드시나요?
온라인 폴 만들때 , 테이블 1개에 만드시나요?
12.REPLACE INTO문 사용하기
REPLACE INTO albums VALUES (6, "tood.net")
insert 문대신에 replace 문을 사용해보세요.
메뉴얼 보시고 연구하세요 ^^
주키일 경우 사용하시면 됩니다.
13. explain 사용하기
explain 를 사용하여 테이블의 키 값이 얼마나 잘 활용 되는지 알 수 있습니다.
EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS, SHOW PROCESSLIST
http://www.mysql.com/doc/E/X/EXPLAIN.html
17.BLOB과 TEXT는 분리하라
BLOB과 TEXT 칼럼은 테이블을 분리 하는 것이 좋다. 다른 칼럼의 내용 보다 크기 때문이다 !
OPTIMIZE TABLE 명령을 자주 사용해라 !
Not null 로 지정 하는 것이 빠르다.
varchar 보다 char 이 훨빠르다.
참고: 게시판의 특성
일반 게시판들 보세요. 여기 질문 게시판 만 봐도 알 수 있구요.
읽기(select) 가 무자게 많습니다. 쓰기(insert into) 보다 30-40 배 정도 많죠.
하룻동안 올라온 글이 30 개 라면, 하룻동안 글읽기 조회수 총 합은 최소 100 에서 900 까지 갑니다.
물론 더 엄청날 수 있구요.
그렇기 때문에 게시판 목록 보기와 글읽기는 HTML 파일로 만들어 놓는 것이 좋습니다.
또는 MySQL 에서 튜닝 시에 insert 보다는 select 가 빨리 되도록 튜닝 하는 것이 좋구요 ^^
[참고사이트]
http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#SEC457
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search
PHP
| 2007-06-27 12:15:18
Hit : 309
// Use option: OCI_DEFAULT for execute command to delay
execution
OCIExecute($stmt, OCI_DEFAULT);
// for retrieve data use (after fetch):
$result = OCIResult($stmt, $n);
if (is_object ($result)) $result = $result->load();
// For INSERT or UPDATE statement use:
$sql = "insert into table (field1, field2) values (field1 = 'value',
field2 = empty_clob()) returning field2 into :field2";
OCIParse($conn, $sql);
$clob = OCINewDescriptor($conn, OCI_D_LOB);
OCIBindByName ($stmt, ":field2", &$clob, -1, OCI_B_CLOB);
OCIExecute($stmt, OCI_DEFAULT);
$clob->save ("some text");
OCICommit($conn);
OCIExecute($stmt, OCI_DEFAULT);
// for retrieve data use (after fetch):
$result = OCIResult($stmt, $n);
if (is_object ($result)) $result = $result->load();
// For INSERT or UPDATE statement use:
$sql = "insert into table (field1, field2) values (field1 = 'value',
field2 = empty_clob()) returning field2 into :field2";
OCIParse($conn, $sql);
$clob = OCINewDescriptor($conn, OCI_D_LOB);
OCIBindByName ($stmt, ":field2", &$clob, -1, OCI_B_CLOB);
OCIExecute($stmt, OCI_DEFAULT);
$clob->save ("some text");
OCICommit($conn);
PHP
| 2007-06-27 11:18:05
Hit : 301
| |||||||
용어들
| 2007-06-27 10:19:50
Hit : 262
|
윈도우 단축키 모음
1. 윈도우 + ?
윈도우키 : 시작메뉴 부르기(Ctrl + Esc ) 윈도우키 + Ctrl + F : 검색창
2. Alt + ?
Alt + 방향키 : 페이지 이동 Alt + Space Bar : 시스템 메뉴 호출 Alt + Esc : 페이지 이동
3. Ctrl + ?
Ctrl + Esc : 시작메뉴 부르기 Ctrl + Alt + Delete : 프로그램 강제 종료
4. Shift + ?
Shift + Delete : 휴지통을 거치지 않고 바로 삭제
5. 마우스와 단축키
Ctrl + 클릭 : 떨어져있는 복수 개체 선택
6. Space Bar '단축키 기능'
가. 인터넷 페이지에서 마우스나 방향키를 쓸 필요없이 스페이스바를 치면 현재 화면에서 아래 화면으로 내려갑니다. 치면 아래로 내려갑니다. 다.
7. Alt + Esc
윈도우의 멀티태스킹 기능으로 여러 프로그램 띄워 놓고 있을 때, 작업전환은 보통
마우스로 하거나, 단축키 `Alt + Tab' 으로 많이 합니다. 하지만 마우스로 하면 시간이 걸리고, 'Alt + Tab'은 여러 프로그램들을
골라서 페이지를 열어 헷갈리지만, Alt + Esc는 다릅니다. 더 유용한 것 같습니다.
8. Esc (시작화면 없이 바로 윈도우 띄우기)
컴퓨터를 부팅할 때 윈도우 로고, 즉 시작화면을 띄우는 시간이 지겨운 분들은
윈도우 부팅할 때 `Esc' 를 누르세요. |
DB
| 2007-06-27 10:04:42
Hit : 115
원본: http://www.cena.co.kr/zboard/zboard.php?id=tip&no=75
MySQL Replication 개요
| MySQL Replication 개요 - 해당 글은 MySQL 4.0.x 버전에서 테스트후 작성된 글이며 공식적으로 Replication기능은 3.23 이상의 버전부터 지원되는것으로 알고 있습니다. - 빠른 글 설명을 위해 대부분의 존칭은 생략합니다. 1. Replication 의 개요 & 필요성 나는 회사 서버에서 실제로 사용해본 데이터베이스는 MySQL과 MSSQL정도로만 압축되며, 현재 회사 서비스의 메인데이터베이스는 모두 MySQL로 이루어져있다. 좀 지난이야기이지만 데이터베이스가 엉켜(?)서 MySQL내의 특정 테이블이 말소되어버린적이 있다. 혹은 phpMyAdmin같은 웹 데이터베이스 관리 프로그램이나 MySQL-Front같은 어플리케이션 관리도구등을 사용한다면 버튼하나, 링크하나잘못 클릭해서 데이터베이스가 통째로 날라갈수가 있다. 그런데 일반 pc에서 하드포맷이나 데이터 삭제시 도스시절 unformat이나 undelete, finaldata 등등의 여러가지 방법으로 복구를 했던반면 데이터베이스는 한번 날라가면 끝이다. 데이터베이스의 보존방법은 크게 Dump를 이용한 방식과 Replication을 이용하는 방식이 있는데 두가지의 분명한 장단점이 존재한다! ㄱ. Dump를 이용한 데이터베이스 백업의 장단점 - 문제가 생겼을때 특정 시점으로의 복원이 가능하다(덤프 받아놓은 날짜의 데이터베이스로 돌려놓을수 있다. - 한번 덤프를 받을때 데이터가 많을수록 덤프 시간은 기하급수적으로 늘어난다. - 한번 덤프를 받아놓은것들이 누적되면 용량이 장난아니게 불어난다. ㄴ. Replication을 이용한 데이터베이스 백업의 장단점. - 실시간으로 데이터를 옮겨적기때문에 원본 데이터베이스에 문제가 발생해서 서비스를 중단할 경우에도 리플리케이션서버로 대처가 가능하다. - 특정시점으로의 복원은 불가능하다. - Replication에 사용하는 Log를 주기적으로 비워주지 않으면 용량의압박이 상당하다. 위에서도 볼수있듯, 두가지의 가장큰 차이점은 특정시점으로의 복원여부, 그리고 실시간 백업이다. 그래서 나의 경우는 두가지 백업을 모두 애용하는 편이며, Replication은 이제 없으면 허전한 그런것이 되어버렸다. 리플리케이션의 구현시, 장점은 위에 있는게 전부가 아니다. 원본 데이터베이스(master)는 쓰기만 하고, 쿼리문 등으로 데이터베이스를 읽는 작업은 Slave에서 처리해도 가능한, 효율적인 분산작업을 위해서도 필요한것이다. 2. Replication의 설정 리플리케이션에서는 하드 IDE시절 수도없이 작업했던 Master, Slave의 개념만 잘 알고 있으면 된다. Master는 원본데이터베이스, Slave는 백업받는 대상 데이터베이스라고 생각하면 되며, 일반적으로 알려진 리플리케이션의 설정은 다음과 같다. (리눅스버전, MySQL 4.0 기준 설명) ㄱ. Master의 설정 일단 처음부터 쉽게 생각하려면, Replication의 기본 구성은 다음과 같다. a. Master에서는 데이터베이스에서 일어나는 모든 작업을 Log에 기록하게 되며, 해당 로그는 Postion 값을 가지고 있다. b. Slave에서는 Master에 설정되어 있는 계정으로 접근하여 Master의 정보를 보게되며, Log의 Postion값을 읽어들여, 어디까지 가져왔는지를 알아내서 포지션값을 갱신하면서 데이터를 계속 긁어온다. 윗말을 정리하자면 Master는 값을 계속 기록만 하고, Slave에서는 Master로 접근후에 데이터를 계속 가져오기만 한다. 그럼 Slave에서 Master로 연결하기 위한 계정 설정은 Master에서 만들어주는것이며, 계정설정은 다음과 같은 방법으로 Master에 기록하게 된다. GRANT file ON *.* TO 리플리케이션아이디@'아이피.아이피.아이피.아이피' IDENTIFIED BY '패스워드' 권한 설정을 할때 file대신 All privileges 권한을 줘도 무방하긴하지만, 어짜피 file에 대해서만 긁어오는 작업을 수행하므로, 기타 권한에 대해 주는것은 불필요하다 또한 아이피부분 역시 %로 대처할수도 있으나, 대부분의 리플리케이션은 같은 네트워크안에 속해있는 서버끼리 작업하는게 보통이므로 아이피는 적어주는게 좋다. 권한 설정은 다 되었으므로, my.cnf에 대한 설정방법은 다음과 같다. (윈도우 버전은 my.ini의 설정을 고치면 된다.) -------------------/etc/my.cnf-------------------------- [mysqld] ...... # MySQL 의 리플리케이션 기능을 사용하기 위한 binary log 설정 log-bin = /mysqltemp/log/replication.log server-id = 1 binlog-do-db = 데이터베이스1 binlog-do-db = 데이터베이스2 -------------------------------------------------------- log-bin = 파일경로/파일이름 입력하지 않는경우 기본적으로 Mysql의 Lib Directory에 기록이 된다. 해당 로그가 쌓이게될때 용량을 무시할 수준이 아니므로 기억하기 좋은 위치로 지정해서 사용하기 바란다. server-id = 번호 해당 아이디의 숫자는 고유한 숫자를 입력해야되며 중요한건 slave와 id가 틀려야 한다. binlog-do-db = 데이터베이스1 해당 MySQL내에서 여러 데이터베이스중 Replication을 할 데이터베이스를 적어준다. 데이터베이스가 다수인경우 해당 구절을 계속 추가해야되며, binlog-do-db = 데이터베이스1,데이터베이스2 식으로 추가하는건 동작하지 않는다고 알려져있다. ㄴ. Slave의 설정 Slave에서는 아까도 이야기 했듯 Master로 접근후에 데이터베이스를 가져오기만 하므로 별도의 계정생성이 필요없으며 my.cnf 설정파일을 수정하는것으로 작업이 완료된다. -------------------/etc/my.cnf-------------------------- [mysqld] ...... server-id = 2 master-host = 아이피.아이피.아이피.아이피 master-user = 리플리케이션아이디 master-password = 패스워드 replicate-do-db = 가져올 데이터베이스1 replicate-do-db = 가져올 데이터베이스2 master-port = 3306 -------------------------------------------------------- slave설정시 Master와 틀린건 가져올 마스터 데이터베이스의 아이피를 적어줘야한단것과(어디서 읽어올지는 알아야하니까) 접근에 필요한 계정(아까 GRANT file On...으로 생성한 계정의 아이디/패스워드)를 기록, 그리고 읽어들일 데이터베이스명 (Master의 binlog-do-db=데이터베이스1)을 기재해주어야 한다는것이다. 3. 동기화 팁 보통 리플리케이션은 백지상태에서 시작하기보다는 특정 데이터베이스가 돌아가고 있는 상황에서 리플리케이션 서버를 추가로 운영하는 경우로 발전되므로, 다음과 같은 두가지 처리방법이 있다. ※리플리케이션 동기화시에는 최대한 데이터베이스의 내용이 일치하는게 좋으므로, Master에서 더이상 DB의 입출력이 일어나지 않는 상태에서 작업하는것이 좋다. 즉. slave와 master의 mysql을 중단시켜놓고 작업을 하는것이 좋다. (/etc/rc.d/init.d/mysql stop) ㄱ. Dump를 이용하여 처리하는방법 - 데이터베이스를 덤프뜬후, Slave에서 Restore시키는 방식, 단점으론 시간이 굉장히 많이 소모되므로 추천하지 않는다. ㄴ. MySQL 데이터 디렉토리를 통째로 옮기는 방법 - 권장하는 방식이며, 데이터베이스가 위치한곳 (/var/lib/mysql또는 /var/db같은...)을 통째로 압축해서 복사하거나, 아니면 그냥 복사하는 방식이다. 본인은 Linux(AnnyungLinux 1.2)서버에서 다른 리눅스서버(AnnyungLinux 1.2)로 옮길때 한번에 2기가 이상은 ftp에서 옮겨지지 않는 기현상이 발생하여 다음과 같은 처리를 하였다. a. tar cvfpz - sourcedirectory | split -b 1500m - databasebackup.tar.gz (1.5G로 분할 압축) - Master에서 가져올 디렉토리를 압축 b. Slave에 접근후 ftp -> open 아이피.아이피.아이피.아이피, -> 압축파일 경로로 변경 -> get databasebackup.tar.gz c. 가져온후 마스터측의 압축은 삭제, 복사작업등의 권한(chown,chmod)등은 적절하게 설정 4. 리플리케이션의 실제 동작명령 동기화가 완료되었다면 mysql에서 다음과 같은 명령어로 여러 동작들을 확인 가능하다. (아이피는 임의처리하였다) 먼저 master 서버의 상태를 보자. ㄱ. show processlist; - 현재 동작하고 있는 processlist를 확인가능하다. mysql> show processlist; +--------+----------+----------------------+---------+-------------+--------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+----------+----------------------+---------+-------------+--------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ | 8 | replsvr | ***.***.***.***:**** | NULL | Binlog Dump | 747916 | Has sent all binlog to slave; waiting for binlog to be updated | NULL ㄴ. show master status; - 리플리케이션 마스터의 동작 상태, 포지션값등을 알수있다. mysql> show master status; +-----------------+-----------+---------------+------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +-----------------+-----------+---------------+------------------+ | replication.002 | 351161171 | test1, test2 | | +-----------------+-----------+---------------+------------------+ 1 row in set (0.00 sec) 여기서 중요한건 file에서 replication.002에 현재 기록중인것을 알고 있으며, slave측에서 001등을 다 가져온 상태라면 해당 로그(replication.001)는 지워도 무방하다. slave의 상태는 다음과 같이 확인 가능하다. mysql> show processlist; +----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------+------------------+ | 2 | system user | | NULL | Connect | 748267 | Waiting for master to send event | NULL | | 3 | system user | | NULL | Connect | 8 | Has read all relay log; waiting for the I/O slave thread to update it | NULL | ㄷ. 동일하게 show slave status로 slave의 상태와 포지션값을 알수있다.(아이피, port 임의처리) mysql> show slave status; +----------------+-------------+-------------+---------------+-----------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+------------+------------+--------------+---------------------+-----------------+ | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +----------------+-------------+-------------+---------------+-----------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+------------+------------+--------------+---------------------+-----------------+ | ***.***.**.*** | replsvr | **** | 60 | replication.002 | 355000057 | wisereplication-relay-bin.003 | 355000141 | replication.002 | Yes | Yes | test1,test2 | | 0 | | 0 | 355000057 | 355000141 | +----------------+-------------+-------------+---------------+-----------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+------------+------------+--------------+---------------------+-----------------+ 이때 중요한것은 master status와 slave status를 봤을때 읽어들이는 파일명(위에서는 replication.002)와 Postion을 비교해서 차이가 나지 않으면 성공한것이다. ㄹ. 그외 명령어 reset Master; reset slave; 위의 명령어로 둘의 포지션값과 로그파일 생성을 초기화 시킬수 있다. slave stop; master stop; 위의 명령어로 리플리케이션 서버의 동작정지를 시킬수 있다. slave start; master start; 위의 명령어로 리플리케이션 서버의 정지를 해제하고 다시 가동시킬수 있다. 5. 그 외의 환경 실험. 예전 리플리케이션을 구현할때 4.0과 4.1에서도 서로 리플리케이션이 가능했다. 4.0과 5.0간에는 테스트 해보지 않았으나 충돌이 발생할 확률이 지대일듯 하다. (가급적 같은 버전을 쓰자?) 또한 리눅스(master), 윈도우즈(slave)같은 환경에서도 제대로 동작했다. (운영체제는 가리지 않는다. 단지 윈도우즈/MySQL 조합은 그리 권장하는 조합이 아니란건...) 6. 끝으로 얼마전 윈도우즈 서버 해킹으로 인해 윈도우즈의 스케쥴 관리 기능을 이용해 매일 특정시간 주기적으로 데이터베이스를 덤프했는데 둘다 리눅스 머신으로 바뀌면서 Crontab, 쉘스크립트등의 사용법을 몰라서 아직 매일 Dump작업을 못하고 있는 실정이다. 다음 강좌에서 저말고 누군가 리눅스에서 crontab을 쓰는방법과 쉘스크립트 작성법등을 강좌 해주면 캐감사하겠다. |
원본: http://oops.org/?t=lecture&sb=mysql&n=1 안녕리룩스 배포자 김정균님 홈페이지
| MySQL Replication
|
| Hanterm - cat /etc/my.cnf |
|
log-bin 은 Replication 을 위한 binary log 를 남기게 한다. server-id 는 Replication 기능 |
| Hanterm - cat /etc/my.cnf |
|
server-id 는 역시 유일값 이어야 한다. MASTER 에서 1 을 사용했으니 SLAVE 에서는 2 를 사용 |
| Hanterm - mysql -e "show process" |
|
SLAVE Server |
| Hanterm - mysql -e "show process" |
|
와 같이 나와 있음을 확인하면 된다. |
용어들
| 2007-06-27 09:44:18
Hit : 239
데이터를 암호화는 알고리즘과 키로 이루어 집니다.
정확히는 알고리즘과 그 알고리즘에 맞는 키라고 해야겠죠! *^^*
암호화 대상이 되는 데이터를 알고리즘으로 암호화 하고 키로 복호화 합니다.
복호화란 해독한다는 뜻이죠~~ ^^;;;;;;;
알고리즘은 일반에 공개 되지만 키는 공개되지 않습니다. 아니 공개할 수 없습니다.
기껏 알고리즘을 짜서 암호화 해놓고 열쇠를 줘 버리면 필요가 없죠?
알고리즘은 수학적 함수를 사용합니다. 일반적으로 해쉬(Hash)라고 합니다.
이 해쉬는 고유의 숫자를 산술해냅니다. 이 산출된 고유 숫자가 바로 키가 됩니다.
128비트는 2진수
입니다.
※ 128비트 암호화의 의미는 키의 길이가 128비트라는
말입니다.
키가 길어서 암호를 해킹하는게 불가능하지는 않지만 그만큼 암호를 해킹하는데
오랜시간이 걸린다는 말이죠.
예를 들어 몇년에서 몇십년이 걸린다고도 하고 몇백년이 걸린다고도 합니다.
기술이 발전해가면서 예전에 많이
사용돼었던 40비트 암호화의 경우 지금은 몇분 이내로 암호를 풀어버리기 때문에 무용지물이 돼어버렸습니다.
128 비트의 예)
1010100100 0101001001 0101010010 1010101010 1010101001 1010101010 1010101010
1010101001 0101010101 0101010101 1010010011 1110101110 01110111
40비트의 예)
0101010101 0101010101 1010010011 1110101110
이 128비트로 표현 가능한 숫자는 2의 128제곱
개의 숫자입니다. 어마어마하게 큰 숫자죠. 다시말하면 128비트 암호화 기술에는 2의 128승 개의 키가 존재한다는 말이죠. 비트수가 높으면
높을 수 록 키를 찾기가 힘들겠죠?
이글을 읽으시는 분들은 다 아시겠지만 2의 40제곱과 2의 128제곱은 상상도 못할 개수의 키의 숫자의 차이가 있습니다.
암호화하는방법에는 비밀키(대칭키) 방식인 DES 와 공개키(비대칭키) 방식인 RSA 방식이 대표적입니다. 인증서는 인터넷뱅킹이나 쇼핑시에 개인의 정보를 보호하고 안전한 거래를 위해 설치하게 되는 일종의 보안장치 입니다. 쉽게 말해 인증서가 깔려있는 컴에서만 거래가 가능하게 하는방식이죠. 물론 개인확인 절차도 거치구요..
대칭 암호화 (비밀키 암호화 : DES)란 암호할 때와 해독(복호화)할 때 똑같은 키를 사용하는 암호화 방식 입니다. 대칭 암호화 방식을 사용하여 통신하려는 두 사람은 사전에 그 키에 동의해야 합니다. 비밀키를 가지고 암호화를 하는데 둘중에 하나만 키를 알고 있으면 안되겠죠? ^^ 넷마블에서 비밀방 만들어서 친구를 불러서 고스톱을 치는데 비밀번호를 저만 알고 있다면 그 친구를 물먹이는 지름길이 될거예요.. 한번 해보세요.. 하하하 *^^*
다시 본론으로 들어가서 보내는 사람은 그 �