Overview

네이티브 파티셔닝 적용 이전의 MySQL은, 파티셔닝 파일들은 각각이 테이블로써 관리되었죠. 그래서, table cache 로 인한 메모리 부족 현상은 인지하고 있었습니다만.. 이것 외에는 특별히 성능 저하 요소는 없다고 생각해왔어요. (http://small-dbtalk.blogspot.com/2013/09/mysql-table-cache.html)

그런데, 얼마전 서버당 4개의 데이터베이스를 만들고, 각각 데이터베이스 안에 26개월로 분할된 파티셔닝된 테이블을 넣고, 간단한 Range scan 성능 테스트를 하였는데.. 말도안되는 수치를 보였습니다. 이 관련하여 간단하게 이에 대해 알아보도록 할께요. 🙂

Problem

하단과 같은 테이블 구조에서, 단순히 최근 10건의 데이터만 끌어오는 형식의 SQL을 다수 실행시켜 간단한 트래픽을 주었을 때.. 성능적으로 별다른 문제는 없을 것이라고 생각을 했습니다. 우리의 메모리는 기대치보다 훨씬 웃돌았기 때문에.. ㅎㅎ (참고로, InnoDB 버퍼풀 사이즈 대비 데이터 사이즈는 약 10배 이상이지만, 최근 파티셔닝 사이즈를 따지면, 버퍼풀 안에 충분히 들어올만한 상황이었습니다.)

Overview

대부분 서비스에서는 데이터 카운트를 합니다. 커뮤니티에서는 사용자 아티클 수를, 결제 서비스에서는 남은 물폼 수를 관리하기 위해서 사용하죠. 그리고 트랜잭션이 중요한 서비스라면, 데이터 일관성 유지를 위해 카운트 시 매번 데이터를 다시 읽어옵니다.

데이터가 적으면 큰 문제가 되지 않겠지만, 데이터 지속적으로 누적됨에 따라 성능 또한 기하급수적으로 저하됩니다.

그렇다면 이러한 환경에서 어떻게 카운트 퍼포먼스를 향상할 수 있을까요? 오늘 포스팅할 내용은 MySQL뿐만 아니라 행 단위 잠금을 지원하는 환경도 포함합니다.

통계 테이블 사용

빠른 데이터 건 수를 가져오는 방법으로는 별도의 통계 테이블을 관리하는 것입니다. 다음과 같이 통계 테이블을 별도로 구성하여 카운트 정보를 관리합니다. 이렇게 되면, 굳이 덩치가 큰 테이블에서 일일이 카운트를 하지 않고, 통계 테이블에서 한 건의 데이터만 읽어오므로, 카운트 성능을 크게 향상할 수 있죠.

Overview

MySQL 트랜잭션 Isolation Level로 인한 장애 사전 예방 법 포스팅에서 관련 주제를 다룬 적이 있습니다. InnoDB에서 Create Table .. As Select .. 과 같이 사용하는 경우 테이블 잠금이 발생할 수 있는 상황과 회피할 수 있는 팁이었죠.

테이블 Full-Scan 구문이 실행 시 발생할 수 있는 문제에 관한 것입니다. 하지만 때로는 변경 대상이 1 건이라도 쿼리 타입에 따라 테이블 잠금 같은 현항이 발생할 수도 있습니다.

이번 포스팅에서는 이에 관해 정리해보도록 하겠습니다. ^^

Overview

오래전 메가존(현 혜택존) 서비스를 담당하던 시기, 포인트 관련된 부분에서 심각한 성능 저하 및 유효성 취약 문제가 발생하였습니다. 장비 고도화를 통해 관련 문제를 해결하기에 앞서, 서비스 로직 및 테이블 재구성을 통한 최적화 작업을 통해 문제를 해결하였습니다.

이에 관해 간단하게 소개하도록 하겠습니다.

Problems

다음과 같이 크게 두 가지 문제가 있었습니다.

1. 성능 이슈
   • 로그 테이블은 거대한 한 개의 테이블로 구성
   • 데이터 누적에 따라 성능이 급격하게 저하
2. 유효성 이슈
   • 자바 어플리케이션에서만 유효성 체크, 비 정상적인 사용 존재
   • 포인트가 현금처럼 사용될 수 있으므로 반드시 필요함

Solutions

1) 파티셔닝을 통한 성능 최적화

오라클 엔터프라이즈 버전에서는 테이블 파티셔닝을 제공하지만, 아쉽게도 오라클 스탠다드에서는 관련 기능이 없습니다. 즉, 어플리케이션 레벨에서 적당히 데이터 분산을 유도하는 방법 밖에는 없습니다. 데이터를 분산하는 방법으로는 여러가지가 존재하겠지만, 저는 월별로 테이블을 분산 저장하는 방식을 사용하였습니다.

Overview

트위터에서 우연히 성능 관련 가벼운 아는척(?)을 시작으로 일이 커지고 말았네요. ^^;; 성능 관련된 트윗을 보고 몇 가지 코멘트만 한다는 것이.. ㅎㄷㄷ한 멘션이 되고 말았습니다.

그래서 부족하나마, MySQL 성능 최적화 시 본능적으로 이행하는 몇 가지를 정리해보겠습니다.

Global Variable

성능과 연관이 되는 몇 가지 파라메터 변수는 반드시 체크를 하시기 바랍니다. MySQL에서 주로 InnoDB를 사용하는 상태라면 innodb_buffer_pool_size, innodb_log_file_size, innodb_log_files_in_group, innodb_flush_log_at_trx_commit, innodb_doublewrite, sync_binlog 정도가 성능에 직접적인 영향을 미치는 요소라고 볼 수 있습니다.

• innodb_buffer_pool_size
  InnoDB에게 할당하는 버퍼 사이즈로 50~60%가 적당하며, 지나치게 많이 할당하면 Swap이 발생할 수 있습니다.
• innodb_log_file_size
  트랜잭션 로그를 기록하는 파일 사이즈이며, 128MB ~ 256MB가 적당합니다.
• innodb_log_files_in_group
  트랜잭션 로그 파일 개수로 3개로 설정합니다.
• innodb_flush_log_at_trx_commit
  서비스 정책에 따라 다르게 설정하겠지만, 저는 일반적으로 2값으로 세팅합니다.
  • 0: 초당 1회씩 트랜잭션 로그 파일(innodb_log_file)에 기록
  • 1: 트랜잭션 커밋 시 로그 파일과 데이터 파일에 기록
  • 2: 트랜잭션 커밋 시 로그 파일에만 기록, 매초 데이터 파일에 기록
• innodb_doublewrite
  이중으로 쓰기 버퍼를 사용하는지 여부를 설정하는 변수로 활성화 시 innodb_doublewrite 공간에 기록 후 데이터 저장합니다. 저는 활성화합니다.
• sync_binlog
  트랜잭션 커밋 시 바이너리 로그에 기록할 것인지에 관한 설정이며, 저는 비활성 처리합니다.

참고로 innodb_buffer_pool_size를 32G 메모리 서버에서 24G로 할당한 적이 있는데, SQL트래픽이 많아짐에 따라 Swap이 발생하더군요. 버퍼풀에는 대략 한 시간 정도 Active한 데이터와 인덱스를 담을 수 있는 사이징이라면 적절할 것 같습니다.

Overview

정말 오랜만에 포스팅합니다. 그동안 개인적인 일이 조금 있어서.. 조금 소홀이 했었네요. ^^

오늘은 묵시적인 형 변한에 대해서 설명을 드릴까 합니다. 서비스 쿼리를 작성하다보면, 이상이 없는 데 성능이 이상하게 안좋은 경우가 있습니다. 명시적으로 쿼리 내용을 볼 수 있다면 괜찮겠지만, 어플리케이션에서 변수를 바인딩하여 SQL을 실행하는 경우는 더욱 찾기가 어렵습니다.

묵시적 형 변환은 단순히 MySQL 뿐만 아니라 Oracle, MS-SQL 등 다른 DBMS에서도 반드시 주의를 해야하는 사항입니다.

묵시적 형 변환이란?

묵시적 형 변환이란 조건절 데이터 타입이 다르면 우선 순위가 있는 쪽으로 형 변환이 내부적으로 발생하는 것을 말합니다.

Overview

MySQL 오픈 소스 진영은 더이상 단순 데이터 처리에만 강한 DBMS이기를 거부합니다. 이제는 대용량 처리에 적합하도록 탈바꿈 중입니다.

지금까지 MySQL에서는 단일 쓰레드로 Nested Loop 방식으로 쿼리를 처리하였기 때문에, 조인 건 수가 대형화될 수록 성능이 급속도로 악화되었습니다.

MariaDB는 5.3버전부터 DB 엔진과 스토리지 엔진 간의 데이터 전송이 개선되었고, 조인 시 추가적인 블록 기반의 조인 알고리즘을 제공합니다. 물론 MySQL도 5.6버전부터는 관련 기능을 어느정도 지원합니다.

변화하는 MariaDB에 대해 몇 가지 소개하도록 하겠습니다.

Disk access optimization

1) Index Condition Pushdown

MySQL/MariaDB는 구조적으로 DB 엔진과 스토리지 엔진 역할이 명확하게 구분됩니다.

Overview

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

Environment

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

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