OceanBase 쿼리 성능 모니터링과 실행 계획 분석 실무

테스트 환경 구성 및 부하 발생기 설정

복잡한 SQL 문에 대한 실제 동작 양상을 파악하기 위해서는 충분한 트랜잭션 부하가 필요한데요, 이를 위해 OceanBase 에코 시스템의 오케스트레이션 도구인 OBD 와 연결 클라이언트 obclient 가 설치된 관리 서버가 필요합니다. 또한 독립적인 테스트 전용 테넌트가 할당된 상태여야 합니다. 성능 분석을 위한 초기 데이터를 생성하고 동적 로드 프로파일을 만들기 위해 표준 벤치마크 도구인 TPCC(TPC-C) 를 활용합니다. OBD 는 기본적으로 관련 플러그인을 보유하고 있으므로 업데이트 후 사용 가능하나, 네트워크 제약이 있는 환경이라면 BenchmarkSQL 소스 코드를 수동으로 배포해야 합니다.

TPCC 부하 시나리오 적용

간편하게 OBD 내부의 모듈을 호출하여 테스트를 진행할 수 있습니다. 우선 리포지토리를 추가하고 필수 패키지를 설치하는 과정입니다.

[admin@node ~]$ sudo yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
[admin@node ~]$ sudo yum install -y java obtpcc

설정이 완료되면 원하는 테넌트를 대상으로 특정 기간 동안 부하를 가할 수 있습니다. 예를 들어 'test_db' 테넌트에서 창고 규모를 20 개로 설정하고 3 분간 트랜잭션을 발생시키는 명령어는 다음과 같습니다.

[admin@node ~]$ obd test tpcc obtest --tenant=test_db --warehouses 20 --run-mins 3

이 과정에서 OBD 는 자동으로 JDBC 드라이버 설정, 테이블 생성, 인덱스 구축 및 데이터 로딩을 수행한 뒤 벤치마크를 실행합니다. 콘솔 출력에는 메모리 사용량, tpmC 수치 등 핵심 성능 지표가 표시됩니다. 만약 수동 설치를 선호하는 경우, GitHub 에서 BenchmarkSQL 소스를 가져와 속성 파일을 수정한 뒤 스크립트를 순차적으로 실행하면 됩니다.

git clone https://github.com/obpilot/benchmarksql-5.0.git
cd benchmarksql-5.0/run
# props.ob 파일 내에 실제 접속 정보를 기재한다
sh runLoader.sh props.ob       # 샘플 데이터 주입
sh runBenchmark.sh props.ob    # 실제 부하 실행

주요 쿼리 식별 (TOP SQL)

부하가 걸린 상태에서 시스템 뷰를 조회하여 최근 시간대 동안 가장 빈번하게 호출되었거나 지연 시간이 길었던 SQL 을 필터링해냅니다.

접근 빈도 기반 추출

최근 10 분 이내의 요청 수 기준으로 상위 5 개 쿼리를 확인하려면 글로벌 감사 로그视图를 조회합니다. 여기서 `tenant_id` 와 같은 조건은 실제 환경의 값을 반영해야 합니다.

SELECT /*+ PARALLEL(8) */ 
    stmt_id, 
    plan_ref_id, 
    COUNT(*) AS call_freq, 
    AVG(duration_us) AS avg_resp_time 
FROM oceanbase.GV$OB_SQL_AUDIT log_rec 
WHERE log_rec.tenant_id = 1002 
  AND log_rec.is_rpc_exec = FALSE 
  AND log_rec.exec_start_ts > UNIX_TIMESTAMP(NOW() - INTERVAL 10 MINUTE) * 1000000
GROUP BY log_rec.stmt_id 
ORDER BY call_freq DESC LIMIT 5;

응답 시간 기준 추출

반면, 처리 속도가 느려 병목이 될 가능성이 높은 쿼리를 찾을 때는 평균 응답 시간을 기준으로 정렬합니다.

SELECT /*+ PARALLEL(8) */ 
    stmt_id, 
    plan_ref_id, 
    COUNT(*) AS req_count, 
    AVG(duration_us) AS latency_avg  
FROM oceanbase.GV$OB_SQL_AUDIT rec  
WHERE rec.tenant_id = 1002 
  AND rec.exec_start_ts > UNIX_TIMESTAMP(NOW() - INTERVAL 15 MINUTE) * 1000000 
GROUP BY rec.stmt_id 
ORDER BY latency_avg DESC LIMIT 5;

식별된 `stmt_id`(SQL ID) 를 바탕으로 실제 SQL 텍스트를 재구성하여 분석 대상의 구조를 확인할 수 있습니다.

SELECT cached_sql_text FROM oceanbase.GV$OB_PLAN_CACHE_STAT WHERE stmt_hash='A460265EC2F0763A15DD27CE9E4E2200';

실행 전략 분석 (Explain vs 실제)

최종적으로 성능 저하의 원인을 규명하기 위해 최적화기에 의한 예상 실행 계획과 실제 런타임 캐시에 저장된 계획을 비교합니다. 먼저 `EXPLAIN` 명령어를 통해 옵티마이저가 제안한 논리적 경로를 검토합니다.

EXPLAIN SELECT total_low_stock FROM (    
    SELECT stock_w_id, stock_item_id, qty_remaining      
    FROM bmsql_stock s      
    WHERE s.stock_w_id = 10 AND s.qty_remaining < 15      
      AND s.stock_item_id IN (          
          SELECT orderline_item_id              
          FROM bmsql_district d              
          JOIN bmsql_order_line ol ON d.district_id = ol.line_dist_id              
          WHERE d.warehouse_id = 10 AND d.next_order >= 100        
      )     
);

출력 결과에서는 연산자 종류 (OPERATOR), 영향받는 테이블 (NAME), 예상 행수 (EST.ROWS), 추정 비용 (EST.TIME) 등의 정보가 나열됩니다. 반면, 실제로 DBMS 가 처리하며 메모리에 남긴 기록은 런타임 보기에서 확인 가능합니다. 특히 `V$OB_PLAN_CACHE_PLAN_EXPLAIN` 은 현재 세션이 연결된 Observer 노드의 캐시만 참조하므로, Proxy 를 거치지 않고 직접 데이터 서버에 접속했을 때 정확한 결과를 얻습니다.

SELECT operator_type, target_table, row_count_actual, cost_microsec 
FROM oceanbase.V$OB_PLAN_CACHE_PLAN_EXPLAIN 
WHERE tenant_id=1002 AND plan_id=3217;

주요 필드 비교 사항입니다:

OPERATOR: 연산자의 유형으로 실제 로그에서는 물리적 스캔 (PHY_TABLE_SCAN) 등이 명시될 수 있습니다.
ROWS: 설명 단계에서는 추정치이며, 실제 로그에서는 처리된 행의 총합이 기록됩니다.
COST: 각 단계에 소요된 마이크로second 단위의 실제 시간입니다.

분석 시 주의할 점은 쿼리 결과가 분산 저장되어 있다는 것입니다. 특정 노드에서만 해당 캐시가 존재하므로, 모든 쿼리 결과를 수집하려면 전체 클러스터의 각 Observer 인스턴스에 대해 별도의 조회 작업이 필요할 수 있습니다.

태그: OceanBase Database Tuning TPCC BenchmarkSQL Execution Plan

5월 26일 13:33에 게시됨

괴물 클럽