Tag Archives: Performance

MySQL 파티셔닝 테이블 SELECT가 느려요.

Overview

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

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

Problem

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

## table
CREATE TABLE `tab` (
`COL01` varchar(16) COLLATE utf8mb4_bin NOT NULL,
`PAR_DATE` varchar(8) COLLATE utf8mb4_bin NOT NULL,
`COL02` decimal(10,0) NOT NULL,
`COL03` decimal(10,0) NOT NULL,
.. skip ..
`APLY_TMST2` timestamp(3) NOT NULL,
PRIMARY KEY (`COL01`,`PAR_DATE`,`COL02`,`COL03`),
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(PAR_DATE)
(PARTITION PF_201707 VALUES LESS THAN ('20170801') ENGINE = InnoDB,
 PARTITION PF_201708 VALUES LESS THAN ('20170901') ENGINE = InnoDB,
 .. skip ..
 PARTITION PF_201907 VALUES LESS THAN ('20190801') ENGINE = InnoDB,
 PARTITION PF_201908 VALUES LESS THAN ('20190901') ENGINE = InnoDB);

## Query
select * from tab
where col01 = ?
order by par_date desc, col02 desc, col03 desc
limit 10

그러나.. 예상과는 다르게, 말도안되는 퍼포먼스와, 이상한 IO패턴을 보이는 결과를 보였죠.

Why?

결론부터 이야기를 하자면, InnoDB. 파티셔닝에서.. 실행계획을 세우는 단계에서 모든 파티션에 약 1~3개 정도의 페이지를 읽게되는데.. 이런 동작으로 인하여 엄청난 비효율이 발생하고 마는 것이죠. 모든 파티션에 해당하는 블록들이 InnoDB 버퍼풀에 올라와있지 않기 때문이죠. (실제로 메모리에 존재할지라도, 쿼리당 수십개의 페이지에 접근한다는 것 역시도 부하라고 생각합니다.)

실행계획 단계의 요 부분에서, 불필요하게 파티셔닝을 접근을 시도해서, 이런 비효율을 발생시키는데…

for (part_id = m_part_info->get_next_used_partition(part_id);
     part_id < m_tot_parts;          part_id = m_part_info->get_next_used_partition(part_id)) {
  index = m_part_share->get_index(part_id, keynr);
  .. skip ..
  int64_t n = btr_estimate_n_rows_in_range(index, range_start, mode1,
                                           range_end, mode2);
  n_rows += n;
  DBUG_PRINT("info", ("part_id %u rows %ld (%ld)", part_id, (long int)n,
                      (long int)n_rows));
}

출처: https://github.com/mysql/mysql-server/blob/4869291f7ee258e136ef03f5a50135fe7329ffb9/storage/innobase/handler/ha_innopart.cc#L3281

사실, 이렇게 대략적인 데이터 건 수를 판단한 후 일부 파티셔닝에 접근할 필요없이, 모든 파티셔닝에 데이터가 존재한다는 가정으로 데이터에 접근을 해도 큰 무리없다고 생각이 마구마구 드는 시점이네요.

Solution

사실 당장의 솔루션은 없습니다. 그냥, 파티셔닝 수를 최소화하는 수밖에 없답니다. 미네랄..ㅜㅜ

단, Percona MySQL경우에는 하단과 같이 추가 파라메터(force_index_records_in_range, records_in_range)를 추가하여, 해당 테이블에 데이터가 특정 건수(할당한 파라메터값)으로 지정을 함으로써 전체 파티셔닝에 접근하지 않도록 우회할 수 있는데요. (이렇게 유도하기까지 꽤나 어려웠어요.ㅠㅠ)

깃헙: https://github.com/dutow/percona-server/commit/677aa7c8aa64ca0aca25c07813e48309e5f06109

이렇게 소스를 변경해서, 테스트트를 해본 결과 놀라운 성능 변화가 있었습니다.

이런 말도 안되는 성능을 보였던 녀석이!!

set records_in_range = 0;
set force_index_records_in_range = 0;
=============================
Device: r/s rkB/s %util
disk01 28364.00 453824.00 100.00
disk01 29050.00 464800.00 99.80
disk01 31827.00 509232.00 99.90
disk01 29660.00 474560.00 99.40
disk01 31570.00 505120.00 99.90

time opr ms/opr
11:31:18 4553 11.01
11:31:19 4639 10.76
11:31:20 4581 10.86

단 몇줄 수정만으로도, 이런 성능 수치를 보였답니다. 대충 봐도 4배정도 향상이 이루어졌습니다, ㅎㅎ (4500 qps vs 17000 qps)

set records_in_range = 1000;
set force_index_records_in_range = 1000;
=============================
Device: r/s rkB/s %util
disk01 24930.00 398896.00 96.70
disk01 25671.00 410720.00 97.10
disk01 24245.00 387920.00 96.90
disk01 24938.00 399008.00 97.10
disk01 25155.00 402480.00 97.10

time opr ms/opr
11:36:48 17156 2.89
11:36:49 16873 2.94
11:36:50 16965 2.92

이 픽스는 다음번 GA 릴리즈 버전에 포함되어서 나오게 될 예정이고.. 동시에 Oracle 커뮤니티 버그 사이트에서 진행하던 동일 이슈도 나름 긍정적인 형태로 진행 중에 있는 듯 하군요.

관련 : https://bugs.mysql.com/bug.php?id=96233

Conclusion

엄청난 갯수의 파티셔닝을 가진 상황에서, 이상하게 쿼리가 느린 것을 경험하신 분들! 파티셔닝 테이블 조회 시 이와 같은 이슈로 리소스가 충분히 활용되고 있지 않은지를 한번쯤 확인을 해보세요. ㅎㅎ 어서어서 오라클에서도 관련된 픽스가 이루어져서, 이런 비효율이 없어지기를 빌어봅니다. ㅋ

간만에 끄적인 블로그를 너무 서술한듯한 느낌이네요. (오늘 ifkakao 발표 후인지라.. 피곤..ㅠㅜ) 이 다음부터는 더욱 재미난 내용을 붙이도록 하겠습니다. 꾸벅.

MySQL InnoDB에서 데이터 1건 변경 시에도 테이블 잠금 현상이 발생할 수 있다!!

Overview

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

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

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

Symptoms

다음과 같이 “NOT IN” 과 같이 부정적인 의미의 조건 구문을 사용 시 테이블 잠금 현상이 발생할 수 있습니다. NOT IN 결과 값이 1건일지로도요..

update test set col1 = 0
where col1 not in (0, 1);

이 경우 Read Committed 로 트랜잭션 Isolation Level을 설정해도 인덱스 칼럼 유무에 따라 테이블 잠금 현상이 발생하기도 하죠. 물론 Repeatable Read보다는 경우의 수가 크지는 않습니다.

현재 트랜잭션 Isolation Level은 다음과 같이 확인 할 수 있습니다.

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

Prepare Test

1) 테스트 환경 구성

## 테이블 생성
create table test(
  i int unsigned not null auto_increment,
  j int unsigned not null,
  k int unsigned not null,
  l int unsigned not null,
  primary key(i)
) engine = innodb;

## 타겟 칼럼과 관계 없는 인덱스 생성
alter table test add key(l);

타겟 칼럼에 인덱스를 추가하는 경우 다음과 같이 수행합니다.

## 타겟 칼럼에 인덱스 생성
alter table test add key(j);

2) 데이터 생성

빠른 데이터 생성을 위해 아래와 같이 Insert Into .. Select.. 구문을 사용합니다.

## 데이터 1 건 생성
insert into test (j, k, l)
values
(
    crc32(rand()) mod 2,
    crc32(rand()) mod 2,
    crc32(rand()) mod 2
);

## 하단 쿼리 17건 실행
insert into test (j, k, l)
select
    crc32(rand()) mod 2,
    crc32(rand()) mod 2,
    crc32(rand()) mod 2
from test;

3) 타겟 데이터 설정

타겟 데이터와 무관한 데이터를 구분하기 위해 i 값을 10000 이상인 데이터에 한에서 랜덤하게 업데이트 수행합니다.

## 타겟 칼럼1
update test set j = 99
where i > 10000
order by rand()
limit 1000;

## 비 타겟 칼럼1 - 인덱스 없음
update test set k = 99
where i > 10000
order by rand()
limit 1000;

## 비 타겟 칼럼2 - 인덱스 있음
update test set l = 99
where i > 10000
order by rand()
limit 1000;

Test Scenario

테스트는 Transaction Isolation Level(Repeatable Read, Read Committed), 인덱스 칼럼 여부, 데이터 칼럼 접근 방식(PK, Index, Full-Scan) 그리고 쿼리 타입(“in”, “not in”)을 구분하여 각각 테스트하였습니다.

원활한 테스트를 위해 모든 세션에 자동 커밋 DISABLE 설정합니다.

SET SESSION AUTOCOMMIT = 0;

Transaction Isolation 레벨은 테스트 상황에 맞게 다음과 같이 설정합니다.

## REPEATABLE READ 인 경우 테스트 환경
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

## READ COMMITTED 인 경우 테스트 환경
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

세션1

변경 대상 데이터는 i 값이 10,000보다 큰 행입니다. 하단 결과 테이블에 있는 쿼리를 질의문으로 수행한 상태에서 세션2 쿼리를 실행합니다.

세션2

i 값이 10,000 이하인 데이터 타겟 데이터과 관계없는 데이터를 업데이트 수행합니다.

update test set j = 1 where i = 1;

Test Result

테스트 결과를 캡쳐해서 알아보기는 힘들지만, 다양한 케이스를 테스트하여 정리합니다. ^^

1) “NOT IN” 테스트

  • Repeatable Read : Not In  사용 시 모든 경우 Lock 발생
  • Read Committed : 인덱스(PK 포함)를 사용하여 접근 시 Lock 발생

not in result

2) “IN” 테스트

  • Repeatable Read : 풀스캔 시 Lock 발생, 인덱스(PK포함) 접근 시 Lock 발생 안함
  • Read Committed : 풀스캔, 인덱스 스캔 모두 Lock 발생 안함

in test result

Solutions

1) Transaction Isolation Level 변경 & IN 구문 사용

Transaction Isolation Level을 Read Committed로 변경하고, IN 구문을 사용하도록 합니다. Not In 사용이 반드시 필요하다면 Limit 구문을 통해 한번에 많은 데이터 변경 작업이 일어나지 않도록 유도합니다.

2) Select 후 결과 값으로 데이터 접근

NOT IN 구문이 필요하다면, Select 구문으로 따로 구분하고 그 결과 값으로 데이터를 다시 업데이트합니다.  MySQL의 Repeatable Read에서는 스냅샷 형태로 Select 데이터 일관성을 보장하며, 데이터 업데이트와 무관한 Select 결과 값에는 다른 세션에서 데이터 변경 작업이 가능합니다.

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
                   "select i from test where j not in (0, 1)"
               );
int i = 0;
String inStr = "";
while(rs.next()){
    inStr += ((i++) > 0 ? "," : "") + rs.getInt(1);
}
rs.close();
stmt.executeUpdate(
    "update i set j = 0 where i in (" + inStr + ")"
);

Conclusion

복잡한 위의 결과를 간단하게 정리하면 다음과 같습니다.

  1. 트랜잭션 Isolation Level이 Read Committed더라도 NOT IN 과 같은 부정적인 조건 질의 시 인덱스 칼럼으로 데이터에 접근하는 상황에서는 Lock이 발생합니다.
  2. 트랜잭션 Isolation Level이 Read Committed인 상태에서 IN 구문과 같은 긍정적인 조건 질의 시에는 연관없는 데이터에는 Lock을 걸지 않습니다.
  3. 트랜잭션 Isolation Level이 Repeatable Read인 경우 NOT IN 사용 시 무조건 테이블 Lock 현상이 발생합니다.
  4. 트랜잭션 Isolation Level이 Repeatable Read인 상태에서 IN 구문을 사용하는 경우에는  인덱스 칼럼으로 접근 시에는 Lock 현상이 발생하지 않습니다.

가장 안정적으로 Lock을 피하기 위해서는 트랜잭션 Isolation Level을 Read Committed로 설정하고, 긍적적인 조건만 주는 경우입니다.

물론 인덱스가 있는 상황이라면, 부정적인 조건 사용 시에도 데이터 접근이 빠르므로, 변경 대상 데이터가 많거나 트랜잭션이 오래 유지되지 않는 한 큰 문제는 없습니다. ^^

단, Read Committed로 변경 시 Binary Log 포멧이 자동으로 ROW 형태로 변경되기 때문에, Binary Log 파일이 급격하게 늘어날 수 있는 경우가 발생할 수 있으니, 내부 검토가 반드시 필요합니다.

사용할수록 나날이 새로운 사실이 발견되는 MySQL이네요.

감사합니다.

서비스 로직 흐름을 변경하여 DB 튜닝을 해보자!

Overview

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

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

Problems

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

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

Solutions

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

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

매월 하단과 같은 스키마의 테이블을 MCHIP”YYYYMM” 형식으로 생성을 합니다.

물론 해당 월 다음 달로 생성을 해야겠죠?

 Name            Type
 --------------- ------------
 SEQ             NUMBER
 USER_NO         VARCHAR2(12)
 POINT           NUMBER
 ISSUE_DATE      DATE

그리고 인덱스 필요 시 데이터 테이블스페이스와는 “물리적으로 분리”된 전용의 테이블스페이스에 생성하여 DISK I/O 비효율도 최소화 유도하였습니다.

데이터 조회는 기본적으로 월별로만 가능하며, 최근 3개월 동안만 조회할 수 있도록 하였고, 필요 시 과거 테이블을 DROP하는 방식으로 변경하였습니다. 만약 최근 3개월 데이터가 필요할 시에는 “UNION ALL” 구문으로 데이터를 가져왔습니다.

다음은 쿼리를 월별로 생성하여 데이터를 저장하는 간단한 자바 프로그램입니다. 저는 SpringFramework 환경에서 구현했었는데, 그것보다는 알아보기 쉬운 단순 자바 프로그램으로 보여드리는 것이 좋을 것 같습니다. (설마 아래 있는 것을 그대로 쓰시는 것은 아니겠죠? ㅎㅎ)

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Test {
    public static void main(String[] args) {
        DateFormat df = new SimpleDateFormat("yyyyMM");
        Date date = new Date();

        // 현재 기준 년월 
        String yyyymm = df.format(date).toString();

        // 쿼리 생성
        String sql = "INSERT INTO MCHIP"+yyyymm+" \n" +
                "(SEQ, USER_NO, POINT, ISSUE_DATE) \n" +
                "VALUES \n" +
                "(SEQ_MCHIP.NEXTVAL, ?, ?, SYSDATE)";

        System.out.println(sql);
    }
}

위와 같이 쿼리를 생성하고, 원하는 테이블에 선별적으로 데이터를 넣습니다. 조회 시에도 위와 같은 방식으로 쿼리를 생성합니다. 만약 한달 이상의 데이터가 필요하다면 UNION ALL로 쿼리를 붙여서 데이터를 조회하면 되겠죠. ^^

2) 트리거를 사용한 유효성 체크

포인트 내역을 저장하는 테이블이 있었다면, 포인트 현황을 조회하기 위한 전용 테이블 또한 존재했습니다. 포인트 현황에 저장된 점수가 사용자가 현재 소지하고 있는 포인트이며, 이를 차감하여 서비스에서 사용하는 방식으로 사용하고 있었습니다.

개인 당 한 건의 데이터만 있기 때문에, 사용자 데이터를 조회하는 것에는 큰 무리가 없었지만, 유효성을 자바 어플리케이션에서만 체크했었기 때문에 여기저기 헛점이 많은 상태였습니다. 물론 내구성이 뛰어나게 개발되었다면 큰 문제는 없었겠지만, 협력사가 시간에 쫓겨서 급하게 개발한 산출물인지라.. 아무래도.. ^^;;

자바 소스 전체를 뒤져서 모든 유효성을 체크하는 것은 거의 무리에 가까웠고, 그래서 제가 채택한 방식은 트리거였습니다. 트리거를 사용하여 사용자 포인트 유효성 여부를 DB레벨에서 체크해서 어플리케이션과 분리하자는 의도였죠.

MCHIP201209 테이블에 포인트가 내역이 들어가는 동시에 유효성 체크 후 정상적이면 포인트 현황 테이블에 반영하는 트리거입니다.

CREATE OR REPLACE TRIGGER TRG01_MCHIP201209
BEFORE INSERT
ON MCHIP201209
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
  POINT NUMBER;
BEGIN
  /*** 현재 사용자 포인트 조회 ***/
  SELECT POINT INTO POINT FROM MCHIP_INFO
  WHERE USER_NO = :NEW.USER_NO;
  … 중략…

  /*** 포인트 유효성 체크  ***/
  IF POINT - :NEW.POINT < 0 THEN
    RAISE_APPLICATION_ERROR(-20001,'Not enough point!!');
  END IF;

  /*** 포인트 현황 업데이트 ***/
  UPDATE MCHIP_INFO SET POINT = POINT + POINT_NEW
  WHERE USER_NO = :NEW.USER_NO;
END;
/

Conclusion

오래전에 성능을 최적화한 내용을 정리하였습니다.

당시 서버에 과부하로 인하여 서버 고도화 및 장비 도입을 위한 투자를 검토 중이었으나, 서비스 로직 재구성 이후 서버가 안정적이었기 때문에 기존 장비로 서비스를 진행하였습니다. 게다가 현재 기존 장비 사용률이 CPU 기준 80~90%였던 상황이 10~20%로 유지되는 쾌거를 거두었죠.

데이터 처리량을 최소로 유도하는 것이 성능 최적화의 첫걸음이라는 것을 느낀 경험이었습니다.

좋은 포스팅으로 다시 찾아뵐께요^^;