끄적끄적_DB

🔍 MariaDB 10.6 OLTP 환경 - 시퀀스 채번 지연 문제 분석 정리

1️⃣ SHOW ENGINE INNODB STATUS\G 분석 방법

1. TRANSACTIONS 섹션
   • LOCK WAIT가 있으면 해당 트랜잭션이 다른 트랜잭션을 기다리고 있음.
   • ACTIVE N sec → 실행 시간이 길면 지연 가능성이 높음.
   • undo log entries 값이 크면 롤백 시 영향을 미칠 수 있음.
2. WAITING FOR LOCKS 섹션
   • lock_mode X waiting → 해당 행이 잠겨 있고, 다른 트랜잭션이 대기 중.
   • table test.sequence_table → 어느 테이블에서 잠금이 발생하는지 확인.
3. LATEST DETECTED DEADLOCK 섹션
   • 데드락 발생 시 충돌한 트랜잭션이 표시됨.
   • TRANSACTION ID를 확인하여 원인 분석 가능.

2️⃣ SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%'; 해석

✅ 현재 조회된 값 분석

🚨 문제점

• 트랜잭션이 평균 15초 이상 대기하고 있음.
• 최대 20분 동안 대기한 트랜잭션이 있음.
• 116번의 충돌 발생 → 경쟁이 심한 테이블일 가능성 높음.

🔧 해결책

1. SHOW ENGINE INNODB STATUS\G 실행하여 대기하는 트랜잭션 확인.
2. innodb_lock_wait_timeout을 120초에서 30초 이하로 줄여서 테스트.
3. INSERT ... ON DUPLICATE KEY UPDATE 방식 최적화 (필요 시 REPLACE INTO 또는 UUID 사용 고려).

3️⃣ innodb_lock_wait_timeout을 줄이면?

• 현재 값: 120초 → 대기 시간이 길어 트랜잭션이 지연됨.
• 예를 들어 30초로 설정하면:
  • 30초 동안 잠금을 얻지 못한 트랜잭션은 자동 롤백됨.
  • 롤백된 트랜잭션이 다시 시도할 수 있도록 애플리케이션 로직 필요.

🚨 세션을 새로 맺는 것이 아니라, 대기 시간이 초과된 트랜잭션이 롤백됨.

4️⃣ MariaDB는 AUTO COMMIT인가?

✅ 기본적으로 AUTO COMMIT이 활성화되어 있음 (autocommit = 1)

• 명시적으로 BEGIN; 또는 START TRANSACTION;을 사용하지 않으면 자동 커밋됨.
• 하지만, 다음과 같은 경우 트랜잭션이 롤백될 수 있음:
  1. LOCK WAIT TIMEOUT 초과 → 강제 롤백됨.
  2. 데드락 감지 → 자동 롤백됨.
  3. 애플리케이션에서 명시적으로 ROLLBACK; 실행.

🚨 INSERT 후에도 롤백될 수 있는 이유?

• INSERT ... ON DUPLICATE KEY UPDATE가 실행되었지만, 다른 트랜잭션과 충돌하여 대기.
• innodb_lock_wait_timeout 초과 또는 데드락 발생 시 자동 롤백됨.
• SHOW ENGINE INNODB STATUS\G에서 "ROLLING BACK" 메시지 확인 가능.

✅ 추천 해결 방법

1. 대기 중인 트랜잭션 분석
   • SHOW ENGINE INNODB STATUS\G 실행 후, LOCK WAIT 상태 확인.
   • SHOW PROCESSLIST; 실행하여 대기 중인 쿼리 확인.
2. innodb_lock_wait_timeout 조정
   • 현재 120초 → 30초로 조정 후 테스트.
3. INSERT 방식 개선
   • INSERT ... ON DUPLICATE KEY UPDATE 대신 REPLACE INTO 또는 UUID 방식 검토.
4. 트랜잭션 길이 최소화
   • BEGIN ... COMMIT 사용 시, 가능한 한 빨리 커밋하도록 개선.
5. 경합이 심한 테이블 분리 또는 샤딩 고려
   • 특정 테이블이 경합이 심하면, 분리하여 부하를 줄이는 것도 방법.

날짜, 시간 표현

#기본시간 형식 "2021-03-24 09:26:25"
SELECT NOW();
 
#날짜만 "2021-03-24"
SELECT DATE(NOW());
 
#시간만 "09:26:25"
SELECT TIME(NOW());
 
#년도만 "2021"
SELECT YEAR(NOW());
 
#월만 "3"
SELECT MONTH(NOW());
 
#날짜만 포맷지정 "2021/03/24"
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d');
 
#시간만 포맷지정 "09 26 25"
SELECT DATE_FORMAT(NOW(), '%H %i %s');

조건문

#최근 하루
SELECT * FROM table_a WHERE create_dt BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY ) AND NOW();
 
#최근 일주일
SELECT * FROM table_a WHERE create_dt BETWEEN DATE_ADD(NOW(), INTERVAL -1 WEEK ) AND NOW();
 
#최근 한달
SELECT * FROM table_a WHERE create_dt BETWEEN DATE_ADD(NOW(), INTERVAL -1 MONTH ) AND NOW();
 
#특정 날짜 지정 방법1(2021년 3월 24일)
SELECT * FROM table_a WHERE create_dt >= '2021-03-24 00:00:00' AND create_dt <= '2021-03-25 00:00:00';
 
#특정 날짜 지정 방법2(2021년 3월 24일)
SELECT * FROM table_a WHERE DATE_FORMAT(create_dt, '%Y-%m-%d') = '2021-03-24';
 
#날짜 조건
SELECT * FROM table_a WHERE DATE(create_dt) BETWEEN '2020-01-01' AND '2020-12-31';
SELECT * FROM table_a WHERE DATE(create_dt) >= '2020-01-01' AND DATE(a.create_dt) <= '2020-12-31';
 
#월별 조건
SELECT * FROM table_a WHERE DATE_FORMAT(create_dt, '%Y-%m') BETWEEN '2021-01' AND '2021-03';

InnoDB Buffer Pool과 innodb_buffer_pool_dirty 이해하기

InnoDB는 MySQL과 MariaDB의 저장 엔진 중 하나로, 데이터베이스의 데이터와 인덱스를 메모리에 캐싱하여 디스크 I/O를 줄이고 성능을 향상시키기 위해 Buffer Pool을 사용합니다. 이번 포스팅에서는 InnoDB Buffer Pool과 관련된 중요한 개념인 innodb_buffer_pool_dirty에 대해 자세히 설명하겠습니다.

InnoDB Buffer Pool

InnoDB Buffer Pool은 InnoDB에서 가장 중요한 메모리 구조 중 하나입니다. 이는 데이터베이스에서 자주 사용되는 데이터를 메모리에 저장하여 디스크 I/O를 줄이고, 데이터베이스의 성능을 향상시키는 역할을 합니다.

Dirty Pages

Dirty Pages란 버퍼 풀에 있는 데이터 페이지 중 디스크에 아직 기록되지 않은 페이지를 말합니다. 이러한 페이지들은 메모리 내에서는 수정되었지만 아직 영구 저장소인 디스크에 쓰여지지 않은 상태입니다.

innodb_buffer_pool_dirty

innodb_buffer_pool_dirty는 이러한 더티 페이지의 양을 나타냅니다. 이 값이 높을 경우, 많은 페이지가 디스크에 기록되지 않았음을 의미하며 이는 잠재적으로 성능 문제를 야기할 수 있습니다:

1. 데이터 일관성 문제: 시스템이 충돌하거나 정전이 발생하면, 더티 페이지가 디스크에 기록되지 않았기 때문에 데이터 손실이 발생할 수 있습니다.
2. 디스크 I/O 성능 저하: 더티 페이지를 디스크에 기록하는 과정에서 디스크 I/O가 집중적으로 발생하여 성능이 저하될 수 있습니다.

모니터링 및 튜닝

innodb_buffer_pool_dirty를 모니터링하고 최적의 성능을 유지하기 위해 몇 가지 설정을 조정할 수 있습니다:

1. innodb_max_dirty_pages_pct: 버퍼 풀에서 더티 페이지가 차지할 수 있는 최대 비율을 설정합니다. 기본값은 75%이며, 이 값을 낮추면 더 자주 더티 페이지를 디스크에 기록하게 됩니다.
2. sql

   코드 복사
   SET GLOBAL innodb_max_dirty_pages_pct = 50;
3. innodb_flush_neighbors: 더티 페이지를 플러시할 때 인접한 페이지도 함께 플러시할지 여부를 설정합니다. 이 값을 설정하면 플러시 효율이 향상될 수 있습니다.
4. sql

   코드 복사
   SET GLOBAL innodb_flush_neighbors = 0;
5. innodb_flush_log_at_trx_commit: 트랜잭션이 커밋될 때 로그를 플러시하는 빈도를 제어합니다. 기본값은 1로 설정되어 있으며, 이 경우 모든 트랜잭션 커밋 시 로그가 디스크에 기록됩니다. 2로 설정하면 더티 페이지를 플러시하는 빈도를 줄일 수 있습니다.
6. sql

   코드 복사
   SET GLOBAL innodb_flush_log_at_trx_commit = 2;

이러한 설정을 통해 innodb_buffer_pool_dirty를 관리하고 MariaDB의 성능을 최적화할 수 있습니다. 성능과 데이터 일관성 사이에서 균형을 맞추는 것이 중요합니다.

 root 계정으로
할당받은 디스크를  oracleasm  에 추가

# oracleasm create disk [이름] /dev/mapper(실제공간위치)

#oracleasm scandisks

asmca 

추가 할 diskgroup 이름을 마우스 우클릭

add disk 로 추가하면 된다.

1. TABLE Tablespace MOVE / INDEX Tablespace MOVE (Rebuild)

-- TABLE Tablespace MOVE
ALTER TABLE ISP.VCT_GOT_I MOVE TABLESPACE TSKG01DT;

-- INDEX Tablespace MOVE(rebuild)
-- TABLE Tablespace MOVE 하면 INDEX 가 invalid 로 바뀜 
ALTER INDEX ISP.PK_VCT_GOT_I REBUILD TABLESPACE TSKG01IX;

2. Partition TABLE Tablespace MOVE / Partition INDEX Tablespace MOVE (Rebuild)

ALTER TABLE ISP.VCT_GOT_I MOVE PARTITION PT_L202210 TABLESPACE TSKG01DT;
ALTER INDEX ISP.PK_VCT_GOT_I REBUILD PARTITION PT_L202210 TABLESPACE TSKG01IX;

서브파티션 일 경우
ALTER TABLE ISP.ACT_GOT_I MOVE SUBPARTITION P_200801_SP2_000 TABLESPACE TSKG01DT;

▶ 파티션 테이블, 인덱스에서 DESCRIPTION 을 보면 테이블스페이스가 AS-IS 로 보일 때 DEFAULT ATTRIBUTES TABLESPACE 를 수동으로 변경한다.

ALTER TABLE ISP.VCT_GOT_I MODIFY DEFAULT ATTRIBUTES TABLESPACE TSKG01DT;
ALTER INDEX ISP.PK_VCT_GOT_I MODIFY DEFAULT ATTRIBUTES TABLESPACE TSKG01IX;

✅ MariaDB, MySQL root 패스워드 초기화 진행

먼저 구동 중인 데몬을 정지 시킵니다.

1. MariaDB 안전 모드 실행: mariadbd-safe

MariaDB일 경우

2. MariaDB 안전 모드 실행: MySQL_safe

Mariadb 일 경우

* mariadb  설치경로 ( ex - mariadb/instance/bin/  안에 

mariadb-safe --defaults-file=/DEV_DB/data/forshop/my.cnf --skip-grant & 실행

서버에서 사용하는 버전에 맞게 패스워드 접속을 시도합니다.

## sock

mysql -u root -p -S /tmp/forshop.sock

mariadb -u root -p
mysql -u root -p

이제 패스워드 변경을 진행합니다.

# mysql 5.6 이하(마리아 DB 5 버전)
mysql> update mysql.user set password = password('패스워드') where user = 'root';
# mysql 5.7 이상 및 MariaDB 10 버전 이상
ALTER USER 'root'@'localohst' IDENTIFIED BY '새로운패스워드';

✅ root 권한이 날라갔을 경우 

mariadb 10.4 버전 이후 부터는 mysql.user 테이블이 없어지고

global_priv 테이블로 변경이 되었다. 

root 의 권한이 날라갔을 경우 다른 instance 의 모든 권한이 있는 계정을 참고 하여

mysql.global_priv 테이블의  priv 컬럼의 값을 복사하여권한이 없는 instance 에 업데이트 쳐주면 정상적으로 권한이 부여된다. 

✅ 안전모드 종료하기

모든 작업이 진행되었으면 안전 모드 종료를 진행합니다.

조회 시 TO_SINGLE_BYTE 함수 사용하기

가끔 클라이언트에서 조회를 했는데

"ORA-29275: 부분 다중 바이트 문자 에러가 난다" 는 경우가 있습니다.

해당 오류의 원인은

서버의 문자 집합(CHARACTER SET)과 클라이언트의 문자 집합(CHARACTER SET) 값이 다르거나

데이터가 이상하게 들어갔을 경우 발생합니다.

가장 쉽게 해결할 수 있는 방법,
"TO_SINGLE_BYTE 함수를 사용하여 SELECT문 작성하기" 입니다.

[예시] SELECT TO_SINGLE_BYTE(필드명) [변수명]  FROM 테이블명;

참 간단하죵..! 😁

이 방법 외,

1) 서버 CHARACTERSET 값을 클라이언트의 값과 맞추기 (비추천)

2) 클라이언트에서 요청한 값으로 데이터 수정을 하기

저 같은 경우에는

실무에서 가장 많이 사용되는 방법은 TO_SINGLE_BYTE 함수 이용하거나 2) 방법을 사용합니다.

1) 방법은 클라이언트에 맞추게 되면 모든 클라이언트의 CHARACTERSET 값도 바꿔야합니다.

사용자가 한 클라이언트거나 소수라면 상관없지만 대다수라면 정말 비추합니다.

2) 방법 같은 경우는 해당 클라이언트와 연락을 닿아 요청했던 값으로 데이터 수정을 합니다.

가장 평범한 방법이여서 경우에 따라 종종 사용합니다.

앞서 Cache Fusion의 개념에 대해 알아보았는데, 이번에는 Cache Fusion의 엑세스에 대해 알아보겠다.

Cache Fusion Access

Cache Fusion은 서로 다른 Node의 Instance에서 Block을 동기화하여 액세스하는 기술이다. 따라서 필요한 Block이 접속한 Instance에 존재하는 경우와 아닌 경우로 구분할 수 있다.

Local Access

요청한 Block의 최신 Block이 Local Instance에 존재할 경우 Instance 간 동기화없이 액세스한다. 

즉, Block을 액세스하는 세션의 위치 = 필요한 Block의 위치 = Master Node의 위치 인 경우를 Local 액세스라고 한다.  

Remote Access

요청한 Block이 다른 Instance에 의해 수정되어 Remote Instance에 존재할 경우 Interconnect를 통해 Block을 복제하여 가져온 후 Data에 액세스한다.

2-way Access

1. Instance 2에 접속한 세션에서 1번 Block을 액세스하는데, Instance 2에는 1번 Block이 없다.

2. Instance 2는 1번 Block을 Master Node인 Instance 3에게 요청한다.

3. Master Node는 GRD를 확인하여 1번 Block의 소유자(Holder Node)가 Instance 3인 것을 확인하고 Instance 3에게 Block 요청을 전달한다.

4. Instance 3은 Master Node의 요청을 받고 1번 Block의 이미지를 Instance 2에 전송한다.

5. Block을 전송받은 Instance 2는 세션의 요청에 대한 응답을 수행한다. 

즉, Block을 요청하는 세션의 위치 ≠ Block의 위치 = Master Node의 위치 인 경우 2개의 Instance에 액세스하기 때문에 2-way Access라고 한다.

3-way Access

1. Instance 2에 접속한 세션에서 1번 Block을 액세스하는데, Instance 2에는 1번 Block이 없다.

2. Instance 2는 1번 Block을 Master Node인 Instance 3에게 요청한다.

3. Master Node는 GRD를 확인하여 1번 Block의 소유자(Holder Node)가 Instance 1인 것을 확인하고 Instance 1에게 Block 요청을 전달한다.

4. Instance 1은 Master Node의 요청을 받고 1번 Block의 이미지를 Instance 2에 전송한다.

5. Block을 전송받은 Instance 2는 세션의 요청에 대한 응답을 수행한다. 

즉, Block을 요청하는 세션의 위치 ≠ Block의 위치 ≠ Master Node의 위치 인 경우 3개의 Instance에 액세스하기 때문에 3-way Access라고 한다.

Disk Access

1. Instance 2에 접속한 세션에서 1번 Block을 액세스하는데, Instance 2에는 1번 Block이 없다.

2. Instance 2는 1번 Block을 Master Node인 Instance 2에게 요청한다.

3. Master Node는 GRD를 확인하여 1번 Block이 어느 Instance에도 존재하지 않음을 확인한다.

4. Master Node에게 그 사실을 전달받은 Instance 2는 Disk에 존재하는 해당 Block을 Instance 2의 DB Buffer Cache에 캐싱한다.

5. Instance 2에 해당 Block이 캐싱되었으므로 해당 Block을 엑세스한다. 

즉, 요청한 Block이 어느 Instance에도 존재하지 않을 경우 Disk Access를 한다.

Oracle RAC의 DB Buffer Cache

오늘은 오라클 RAC 특징에 있어서 가장 중요한 cache fusion에 대해서 알아보겠다. 그러면 그 전에 DB Buffer cache에 대해서 알아야 한다.

Node

하나의 DB에 연결된 서버를 의미한다. 2 Node RAC는 2대의 물리적인 서버가 하나의 DB에 연결된 경우이다.

Instance

각각의 Node는 각각의 Instance를 가진다. 하나의 Instance는 여러 Node에 중복 존재할 수 없지만 하나의 Node는 여러개의 Instance로 구성할 수 있다.

SGA

하나의 Instance는 반드시 하나의 SGA를 가지며 SGA는 Shared Pool, DB Buffer Cache, Redo Log Buffer 등으로 구성된다.

Cache Fusion과 DB Buffer Cache

DB Buffer Cache는 유저가 요청한 SQL문을 수행하기 위해서 필요한 Data Block을 Disk로부터 메모리로 올리는(Caching) 하는 영역이다. Cache Fusion에서 DB Buffer Cache가 중요한 이유는 Cache Fusion이 서로 다른 Instance의 DB Buffer Cache에서 Block을 이동시키는 부분에서 발생하기 때문이다.

Cache Fusion

Cache Fusion은 OPS(Oracle Parallel Server)의 Block Transformation의 문제를 극복하기 위해 나타난 아키텍쳐이며 이를 통해 Oracle RAC는 성능적으로 안정성을 갖추게 되었다.

*Block Transformation

특정 Instance에 존재하는 Block을 다른 Instance에서 액세스하기 위해서 해당 블럭을 공유 Storage에 write한 후, 그 Block을 호출한 Instance의 DB Buffer Cache로 캐싱하여 Data Block을 다른 Node로 이동시키는 방식이다.

하지만 각각의 Instance에서 많은 작업이 수행되고, 각각의 Instance 사이에서 이동하는 Block이 많으면 성능 저하가 발생한다. 이 이유로 OPS는 사라지게 되었다.

Cache Fusion은 아래와 같은 방식으로 수행된다.

① Instance 2에 접속한 A 프로세스는 Instance 1에 존재하는 1번 Block을 액세스하기 위해 호출한다.

② Instance 1에 존재하는 1번 Block은 Instance 2의 호출에 응답하기 위해 Instance 2의 SGA의 DB Buffer Cache로 이동해야 한다. 이동을 위해 공유 Storage에 존재하는 DB를 이용하지 않고 Instance 사이의 Interconnect를 이용한다.

③ Interconnect를 이용하여 Instance 2로 캐싱된 1번 Block을 A 프로세스가 액세스할 수 있게 된다.

Cache Fusion의 목적

Block이 특정 Node의 DB Buffer Cache에 로드되어 있다면 Disk I/O가 발생하지 않기 때문에 Block 이동을 빠르게 처리할 수 있어서 Oracle RAC의 성능 향상에 가장 중요한 아키텍쳐이다.

Cache Fusion의 구성 요소

• GRD(Global Resource Directory)

   RAC에서 Global Resource를 관리한다. Cluster의 동기화를 위해서 Cluster 내의 Resource 정보들(Resource Master,       Resource Holder)을 저장한다.

*Resource Master : 특정 Resource의 Master를 지칭한다. 특정 Global Resource는 반드시 하나의 GRD에서 최신 정보를 관리한다.

*Resource Holder : 특정 Resource의 최종 버전을 가지고 있는 Node를 의미한다.

• GCS(Global Cache Service)

   Data의 일관성 및 무결성을 유지하는 서비스로, DB Buffer Cache 동기화에 대한 Global 정보를 저장한다. 즉, Block에     대한 Lock 정보를 관리한다.

• GES(Global Enqueue Service)

   DB Lock에 대한 정보를 관리하는 서비스로 Block 이외의 Lock 정보를 저장한다. 즉, Enqueue Lock, Library Cache         Lock, Row Cache Lock 등의 정보를 관리한다.