MemSQL이란?

MemSQL은 디스크 병목을 최소화하기 위해 만들어진 메모리 기반의 관계형 DBMS입니다. 메모리 기반으로 데이터 처리가 이루어지기 때문에, 엄청난 속도로 Read/Write이 가능하며, 기존의 NoSQL 또는 캐시로만 가능했던 퍼포먼스 향상이 있습니다. 실제로 디스크 기반 DBMS 대비 약 30배 이상의 성능 향상이 있다고 하니, 놀라울 따름입니다.

최근 들어 메모리 가격이 하루가 다르게 저렴해지고 있기 때문에 메모리 사이즈를 최대한 늘려서 가격 대비 성능 비를 최대로 이끌어 내는 DB입니다.

MemSQL 특징

1) 강력한 SQL기반의 통신

SQL 기반으로 통신하며, MySQL API를 따릅니다. 그렇기 때문에 기존 MySQL을 사용하는 서비스 경우 로직 변경이 불필요하다고 합니다. 하다못해 라이브러리 또한 기존 MySQL에서 사용하던 그대로 재사용해도 상관 없습니다.

MariaDB란?

MySQL이 Sun Microsystems로 넘어가면서 당시 MySQL AB 출신들이 따로 나와서 MySQL을 기반으로 한 다른 오픈 소스 기반의 DBMS를 배포했다고 합니다. 바로 MariaDB가 그것이며 MySQL과 유전 정보를 그대로 고수한 진짜 오픈 소스 기반의 DBMS입니다.

현재 Monty Program AB와 MariaDB Community에서 개발하고 있으며, MySQL과 기본적으로 구조 및 사용 방법 등 모두 동일합니다. (동일 소스에서 개발되고 있으니 당연한 말입니다.)

Monty Program AB에 따르면 많은 기능들이 MariaDB에서 먼저 구현을 하고 그 후 MySQL에도 반영이 된다고 하는데, 마치 CentOS와 Redhat 리눅스 관계 같다는 생각이 듭니다.^^

Overview

오늘은 Temporary Table에 관해 포스팅을 하겠습니다. Select및 Update 등을 이따금씩 Temporary Table을 활용하여 수행하는 경우가 있습니다. 동시에 많은 데이터를 일괄 변경하는 것에서는 분명 강점이 있을 것이라 판단되는데, 어떤 상황에서 적절하게 사용하는 것이 좋을까요? 관련 성능 벤치마크 결과를 공개하겠습니다.

Environment

테이블에는 약 1000만 건 데이터가 존재하며, Primary Key외에는 추가 인덱스는 생성하지 않았습니다. 서로 동등하게 빠른 데이터 접근이 가능하다는 가정 하에 PK외 인덱스에서 발생할 수 있는 성능 저하 요소를 배제하기 위해서 입니다.^^

Overview

**Amazon Relational Database Service(Amazon RDS)**는 클라우드에서 관계형 데이터베이스를 쉽게 설치, 운영 및 확장할 수 있는 서비스입니다.

자원을 유연하게 배분할 수 있는 이점이 있는 클라우드이지만, 모든 서비스는 결국에는 사람 손을 거쳐야 하고, 때로는 인재로 인한 데이터 유실 사고가 발생할 수 있습니다.

사용이 편리하게 구현되어 있지만, 사용자에게 제공하는 권한 또한 상당히 제약적(인스턴스 관리자일지라도)입니다.

오늘은 Amazon RDS 상에서 데이터 유실 장애가 발생한 경우 대처할 수 있는 방안에 관하여 포스팅하도록 하겠습니다. (기준은 MySQL이나 타 DBMS도 큰 차이가 없을 것 같네요^^)

Overview

트위터의 새로운 분산 관리 라이브러리 Gizzard를 소개합니다.를 알아보던 당시 부사수 임창선님과 진행했던 또 다른 해외 사례 Tumblr를 정리해보았습니다.

Tumblr는 국내에서는 사용자가 많지는 않지만, Twitter 정도의 트래픽을 자랑하는 Micro Blog 서비스입니다. 하루 평규 5억 이상의 PV가 나오고, 초당 4만 건 이상 Request가 나오며, 하루 평균 3TB 이상의 데이터를 쌓는다고 하니 엄청난 서비스인 것은 틀림 없습니다.

이정도 데이터를 관리하기 위해서 수 천대 이상의 서비스를 운영한다고 하는데, 데이터 관리를 MySQL을 활용하여 제공한다고 합니다. 그렇다면 MySQL로 어떻게 대용량 데이터를 멋지게 다뤘을까요?

Overview

MySQL은 단순 쿼리 처리 능력은 탁월하나 테이블 스키마 변경 시에는 상당히 불편합니다. 일단 테이블 스키마 변경 구문을 실행하면 임시 테이블 생성 후 데이터를 복사하고, 데이터를 복사하는 동안에는 테이블에 READ Lock이 발생하여 데이터 변경 작업을 수행하지 못합니다. (Table Lock이 걸리죠.)

이 같은 현상은 인덱스, 칼럼 추가/삭제 뿐만 아니라 캐릭터셋 변경 시에도 동일하게 발생합니다. (최근 5.5 버전에서는 인덱스 추가/삭제에서는 임시 테이블을 생성하지 않습니다.)

얼마 전 서비스 요구 사항 중 테이블 칼럼을 무중단으로 변경하는 것이 있었는데, 이에 관해 정리 드리겠습니다.^^

Overview

MySQL에서 전체 데이터를 Scan 하는 쿼리를 질의하여 서비스에 큰 영향이 발생할 수 있습니다.

InnoDB 스토리지 엔진의 기본 Isolation Level이 REPEATABLE-READ이기 때문에 발생하는 현상인데, 이것은 세션 변수 일부를 변경하여 문제를 사전에 해결할 수 있습니다.

얼마 전 이와 비슷한 장애가 발생하여 원인 분석 및 해결 방안을 포스팅합니다.

Symptoms

Transaction Isolation Level이 REPEATABLE-READ(MySQL Default) 상태에서 Insert into Select 혹은 Create Table As Select 로 전체 테이블 참조 쿼리 실행 시 참조 테이블에 데이터 변경 작업이 대기 상태에 빠지는 현상이 있습니다.

Overview

안정적인 서비스 유지를 위해서는 쿼리 작성이 상당히 중요합니다. 잘못된 쿼리 하나가 전체적인 퍼포먼스를 크게 저해하기도 하고 최악의 경우 장애 상황까지 치닫기 때문이죠

단일 코어에서 Nested Loop Join으로 데이터를 처리하는 MySQL 특성 상 쿼리 구문에 큰 영향을 받습니다. (반드시 알아야할 MySQL 특징 세 가지 참고)

그래서 오늘은 쿼리 작성 시 기피해야 하는 사항 세 가지정도 골라봅니다.

Case 1

SELECT @RNUM:=@RNUM+1 AS RNUM, ROW.*
FROM (SELECT @RNUM:=0) R,
(
    SELECT
        M.MASTER_NO,
        M.TITLE,
        MI.PATH,
        M.REGDATE,
        CM.TYPE
    FROM MAIN AS M
    LEFT OUTER JOIN TAB01 AS MI
        ON M.MASTER_NO = MI.MASTER_NO
    INNER JOIN TAB02      AS CM
        ON M.MASTER_NO = CM.MASTER_NO
    WHERE M.DEL_YN = 'N'
    ORDER BY M.MASTER_NO DESC
) ROW
LIMIT 10000, 10

안녕하세요. 오늘은 MySQL을 사용할 때 지켜야할 사항 몇 가지 정리합니다.

나름 혼자서 정리를 해 본 것들인데, MySQL로 서비스를 준비 중이라면 한 번쯤은 고려를 해봤으면 하는 내용입니다.^^

테이블 설계 시 유의 사항

1. 반드시 Primary Key를 정의하고 최대한 작은 데이터 타입을 선정한다.

• 로그 성 테이블에도 기본적으로 PK 생성을 원칙으로 함
• InnoDB에서 PK는 인덱스와 밀접한 관계를 가지므로 최대한 작은 데이터 타입을 가지도록 유지

2. 테이블 Primary Key는 auto_increment를 사용한다.

• InnoDB에서는 기본 키 순서로 데이터가 저장되므로, Random PK 저장 시 불필요한 DISK I/O가 발생 가능
• InnoDB의 PK는 절대 갱신되지 않도록 유지
  (갱신 시 갱신된 행 이후 데이터를 하나씩 새 위치로 옮겨야 함)

3. 데이터 타입은 최대한 작게 설계한다.

• 시간정보는 MySQL데이터 타입 date/datetime/timestamp 활용
• IP는 INET_ATON(‘IP’), INET_NTOA(int) 함수를 활용
• 정수 타입으로 저장 가능한 문자열 패턴은 최대한 정수 타입으로 저장

4. 테이블 내 모든 필드에 NOT NULL 속성을 추가한다.

• NULL을 유지를 위한 추가 비용 발생
  (NULL 허용 칼럼을 인덱싱 할 때 항목마다 한 바이트 씩 더 소요)

5. Partitioning을 적절하게 고려하여 데이터를 물리적으로 구분한다.

• 데이터 및 인덱스 파일이 커질수록 성능이 저하되므로Partitioning 유도
• PK 존재 시 PK 내부에 반드시 Partitioning 조건이 포함되어야 함

인덱스 설계 시 유의 사항

1. 인덱스 개수를 최소화 한다.

• 현재 인덱스로 Range Scan이 가능한지 여부를 사전에 체크
• 인덱스도 서버 자원을 소모하는 자료구조이므로 성능에 영향을 줌

2. 인덱스 칼럼은 분포도를 고려하여 선정한다.

• 인덱스 칼럼 데이터의 중복이 줄어들수록 인덱스는 최대의 효과를 가짐
• 하단 쿼리 결과 값이 1에 가까울수록(0.9이상 권고) 인덱스 컬럼으로 적합함

SELECT count(distinct INDEX_COLUMN)/count(*)
FROM TABLE;

3. 커버링 인덱스(Covering Index)를 활용한다.

• 쿼리 조건이 인덱스 안에 포함된 경우 인덱스에서만 연산 유도
• 인덱스는 일반적으로 행 전체보다 작으므로 불필요한 Disk I/O 회피 가능
  MySQL에서 커버링 인덱스로 쿼리 성능을 높여보자!! 편 참고

4. 스토리지 엔진 별 INDEX 특성을 정확히 인지한다.

• InnoDB에서 데이터는 PK 순서로 저장되고, 인덱스는 PK를 Value로 가짐
• MyISAM은 PK와 일반 인덱스의 구조는 동일하나, Prefix 압축 인덱스를 사용
  (MyISAM 엔진에서 ORDER BY 시 DESC는 가급적 지양)

5. 문자열을 인덱싱 시 Prefix 인덱스 활용한다.

• 긴 문자열 경우 Prefix 인덱스(앞 자리 몇 글자만 인덱싱)를 적용

CREATE INDEX IDX01 ON TAB1(COL(4), COL(4))

• Prifix Size는 앞 글자 분포도에 따라 적절하게 설정
  (하단 결과가 1에 가까울 수록 최적의 성능 유지, 0.9이상 권고)

  SELECT count(distinct LEFT(INDEX_COLUMN,3))/count(*)
FROM TABLE;

6. CRC32함수 및 Trigger를 활용하여 인덱스 생성한다.

• URL/Email같이 문자 길이기 긴 경우 유용
• INSERT/UPDATE 발생 시 Trigger로 CRC32 함수 실행 결과 값을 인덱싱
• CRC32 결과값을 저장할 칼럼 추가 및 인덱스 생성

alter table user_tbl add email_crc int unsigned not null;
create index idx01_email_crc on user_tbl (email_crc);

• Insert Trigger 생성

create trigger trg_user_tbl_insert
before insert on user_tbl
for each row
begin
  set new.email_crc = crc32(lower(trim(new.email)));
end$

• Update Trigger 생성

create trigger trg_user_tbl_update
before update on user_tbl
for each row
begin
  if old.email <> new.email then
      set new.email_crc = crc32(lower(trim(new.email)));
  end if;
end$

• 검색 쿼리

select *
from user_tbl
where email_crc = crc32(lower(trim('mail@domain.com')))
and email= 'mail@domain.com'

CRC32 결과가 중복되어도, email값을 직접 비교하는 부분에서 중복이 제거됩니다.

Overview

DB를 운영하다 보면, 한 개의 MySQL 인스턴스에 여러 개의 데이터베이스를 모아서 보관하는 경우가 있습니다. 그러면 가끔 DB명이 충돌나는 경우도 발생하죠. 오늘은 Dump/Rename/Import 등 모든 프로세스를 자동화할 수 있는 방안을 제시해 봅니다.

요구사항

무조건 자동으로 동작해야 하고, 기억력이 나쁜 제가 나중에 사용하기 쉽게 재사용성도 좋아야한다는 것입니다. 그리고 사용 방법을 잊어도 쉽게 상기할 수 있는 방안도 있어야겠죠.ㅋ (제가 정한 요구사항입니다. ㅋ)

1. 모든 프로세스는 자동화되어야 한다.
2. 스크립트 수정 없이 재사용이 가능해야 한다.
3. 사용 매뉴얼이 있어야 한다.

자동화 구현

프로세스 순서는 다음과 같고 2단계부터는 파이프( | )로 한번에 처리합니다.