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단계부터는 파이프( | )로 한번에 처리합니다.

안녕하세요. 오늘 짧지만 재미있는 내용을 하나 공유할까 합니다.

커버링 인덱스(Covering Index)라는 내용인데, 대용량 데이터 처리 시 적절하게 커버링 인덱스를 활용하여 쿼리를 작성하면 성능을 상당 부분 높일 수 있습니다.

커버링 인덱스란?

커버링 인덱스란 원하는 데이터를 인덱스에서만 추출할 수 있는 인덱스를 의미합니다. B-Tree 스캔만으로 원하는 데이터를 가져올 수 있으며, 칼럼을 읽기 위해 굳이 데이터 블록을 보지 않아도 됩니다.

인덱스는 행 전체 크기보다 훨씬 작으며, 인덱스 값에 따라 정렬이 되기 때문에 Sequential Read 접근할 수 있기 때문에, 커버링 인덱스를 사용하면 결과적으로 쿼리 성능을 비약적으로 올릴 수 있습니다.

안녕하세요. 한동안 MySQL에 빠져 있다가, 최근 Oracle 데이터 이관 작업 도중 재미난 사례 공유 합니다. DB Link를 통한 CTAS(Create Table As Select)와 Export/Import를 통한 데이터 이관 비교입니다.

서비스 요구 사항

• 서비스 DBMS 버전 : Oracle 9i
• 전체 데이터 파일 사이즈 : 120G (인덱스 포함)
• 타겟 테이블 데이터 사이즈 : 26G (인덱스 제외)
• 네트워크 속도 : 100Mbps (max: 12.5MB/s)
• 일 1회 현재 서비스 데이터 동기화 수행
• 모든 작업은 자동화하여 운영 이슈 최소화

위 환경을 고려하였을 때, 전체 데이터 파일 Copy는 동기화 시간 및 스토리지 낭비 요소가, Archive Log 활용하기에는 운영 이슈가 존재했습니다.

Overview

어느 시스템에서도 병목 현상은 어딘가에 있습니다. DBMS 또한 CPU, Memory, Disk로 구성된 하나의 시스템이기 때문에 당연히 특정 구역에서 병목현상이 발생할 수 있죠. 오늘은 Disk에서 발생하는 병목에 관해서 말씀드리겠습니다.

Memory Processing

Permanent Link: 디스크 배열(RAID)에 따른 DB 성능 비교 에서, 메모리가 충분하면 아래와 같다는 그래프를 보여드렸습니다.

어떤 경우든 메모리에만 연산이 가능하다면, CPU자원을 거의 활용 가능하다고 할 수 있죠. 그러나 만약 디스크 I/O가 발생하는 순간부터 CPU는 전혀 연산하지 않는 현상이 발생합니다. 바로 디스크 I/O Wait 으로 인한 시스템 병목 현상 발생입니다.

Overview

MySQL DBMS 하드웨어 구성 시 어떠한 정책으로 움직이는 것이 가장 효율적일지, 메모리/디스크 설정을 변경하여 테스트를 진행하였습니다. 디스크는 RAID 레벨을 변경하였고, innodb_buffer_pool을 조정함으로써 메모리 환경을 구성하였습니다. 서비스 특성에 따라 하드웨어 구성을 달리함으로써, 장비를 더욱더 효율적으로 사용할 수 있을 것으로 기대됩니다.^^

디스크배열(RAID)란?

RAID란 Redundant Array of Inexpensive Disks의 약자로 디스크를 여러장 묶어서, 데이터 중복성 및 성능 향상을 유도할 수 있는 기법입니다. RAID 기법은 참으로 많이 있으나, 일반적으로 실무에서는 RAID0, RAID1, RAID5, RAID10또는 RAID01을 많이 사용합니다.

Overview

MySQL Replication 시리즈 마지막 3탄, 활용에 관한 포스트입니다. 앞 선 시리즈 MySQL Replication 이해(1) – 개념와 MySQL Replication 이해(2) – 구성에서 기본적인 개념과 구성을 다뤘다면, 이 자리에서는 실제적으로 어떤 분야에 활용할 수 있는지 설명드리겠습니다.

1. Scale Out
2. High Availability
3. Data Partitioning

자, 그럼 시작해볼까요?

Scale out

MySQL Replication이 가장 많이 활용되는 분야입니다.
MySQL Replication은 READ관련 Scale out만 가능합니다. 만약 WRITE 이슈가 있다면, MySQL 레벨에서는 Scale out이 불가합니다. 특히나 Replication 운영 시 마스터 트래픽이 과도하게 발생하면, Master와 Slave 간 데이터 동기화 지연 현상이 발생합니다. 반드시 알아야할 MySQL 특징 세 가지 내용을 읽어보시면 이해가 조금더 수훨하겠네요.^^

Overview

바로 이전 하루 2.5억 트윗을 저장하는 트위터의 새로운 저장 스토어 포스팅에서 트위터의 새로운 저장 스토어에 관해서 전반적으로 설명 드렸는데요, 이번에는 그 중 Gizzard에 관해서 심층 분석(?)을 해볼까합니다.

Gizzard는 트위터에서 데이터를 분산 및 복제 관리하기 위한 자체 개발 프레임워크입니다. 클라이언트와 서버 사이에 위치하며 모든 데이터 요청을 처리하는 구조입니다. Gizzard 관련 몇 가지 키워드는 아래와 같습니다.

1. 분산 관리(Sharding), 분할(Partitioning), 복제(Replication)
2. 부하분산(Load-Balancing)
3. 장애복구(Fail-Over)
4. 멱등성(idempotent), 가환성(commutative)
   • 멱등성 : 연산을 여러 번 적용하더라도 결과가 달라지지 않는 성질
   • 가환성 : 연산의 순서를 바꾸어도 그 결과가 변하지 않는 일

분산 관리(Sharding)이란?

과거에는 서비스 성능 저하가 발생하면 곧바로 해당 서버에 CPU또는 Memory 사이즈를 증설하여 성능 이슈를 해결하였습니다. 하지만, 최근 Web 서비스에서 데이터 사이즈가 급증하여, 더 이상은 서버 성능 고도화만으로는 한계가 있기 때문에, 다수 장비에 데이터를 분산 위치(Data Sharding)하여 데이터를 처리하는 움직임이 일반화되고 있습니다.