따봉 구걸남's 블로그

Blue 와 Green 의 테이블 구조가 다른 상태에서 복제가 정상적으로 진행이 되는지 테스트 해보자 

1. mysql rds 생성 

 mysql rds 인스턴스 생성은 이전에 작성한 포스팅을 참조하자 

https://riorio.tistory.com/606

생성이 완료 되면 테스트 DB 를 생성하고 테스트 테이블을 하나 생성하자 

CREATE DATABASE TEST;

USE TEST

2. 블루그린 - 그린 인스턴스 추가 

그린 인스턴스를 생성한다. 그린 인스턴스 전용 파라미터 그룹을 새로 만들어서 설정해준다. 

그린 전용 파라미터 그룹에는 아래 두가지 파라미터가 수정이 되었다. 

read_only=0 
slave_type_conversions=ALL_NON_LOSSY

버튼을 누르면 그린 인스턴스가 생성작업이 시작된다.

생성이 완료되면 엔드포인트 정보로 접속하자. 

3. 테스트 

 테이블 구조를 다르게 유지하기 위해 그린에만 DDL 을 실행한다.

 insert 는 blue 에서 실행하고 green 에서 복제가 되는지 조회해보자

-- [G] 1. char set
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- [B] 1. char set
INSERT INTO users (username, email, age, address) VALUES ('fastlookup', 'fast@example.com','1','L.A santa monica beach');


-- [G] 2. col order
ALTER TABLE users MODIFY COLUMN email VARCHAR(100) AFTER age;
-- [B] 2. col order
INSERT INTO users (username, email, age, address) VALUES ('émilie', 'émilie@example.com','24','jeju-island');


-- [G] 3. col type varchar -> int 
ALTER TABLE users MODIFY COLUMN age int;
-- [B] 3. col type varchar -> int
INSERT INTO users (username, email, age) VALUES ('jane_smith', 'jane@example.com', 25);


-- [G] 3. col type  text -> varchar
ALTER TABLE users MODIFY COLUMN address VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- [B] 4. col type text -> varchar
INSERT INTO users (username, email, age, address) VALUES ('no_index', 'no_index@example.com','98','new-york,new-york');

블루-그린 테이블 구조가 달라도 데이터는 정상적으로 복제가 되었다. 

작업이 완료되었고, 복제가 완료되었으면 staging 을 prod 로 바꾸는 switch over 를 하자

전환이 완료되었다. 그린은 신규블루로 바뀌었고 엔드포인트도 기존 블루의 엔드포인트로 바뀌었다.

기존블루의 인스턴스 이름에는 "old" 가 붙게 된다. 

 사용자 입장에서는 잠깐의 순단이 발생하고 운영반영이 되었다. 

* 스위치오버 전후 유의점 

- 전환 전 그린 파라미터의 원복 : read_only,  slave_type_conversions ( 재부팅 필요) 

- 그린의 데이터 검증, sql 플랜 확인 

- 전환 작업은 DB연결 수가 적은 시점에 해야 원활하게 작업가능

    (리소스사용의 최소화, 빠른 전환, 데이터 불일치, 충돌 리스크 감소) 

mysql 복제 상태 show replication status\G 를 주기적으로 반복 실행하는 쉘 만들어보자 

원격서버의 상태를 체크한다고 하면 

mysql -h [호스트명] -P[포트번호] -u [사용자명] -p[패스워드] -e "SHOW SLAVE STATUS\G"

============================================================== 

#!/bin/bash

MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USER="your_username"
MYSQL_PASS="your_password"

# 중요한 상태 항목들
ITEMS_TO_SHOW="Source_Log_File|Read_Source_Log_Pos|Exec_Source_Log_Pos|Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Source|Last_IO_Error|Last_SQL_Error"

while true
do
  echo "==== $(date) ===="
  mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW REPLICA STATUS\G" | grep -E "$ITEMS_TO_SHOW"
  
  # 복제 지연 확인
  SECONDS_BEHIND=$(mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW REPLICA STATUS\G" | grep "Seconds_Behind_Source" | awk '{print $2}')
  if [ "$SECONDS_BEHIND" != "NULL" ] && [ "$SECONDS_BEHIND" -gt 0 ]; then
    echo "경고: 복제 지연이 $SECONDS_BEHIND 초 발생했습니다."
  fi
  
  echo ""
  sleep 10
done

==============================================================

===== window 버전 =============================

set HOST=[호스트명]
set PORT=[포트번호]
set USER=[사용자명]
set PASS=[비밀번호]

:loop
cls
echo Checking replication status...
mysql -h %HOST% -P %PORT% -u %USER% -p%PASS% -e "SHOW SLAVE STATUS\G" | findstr /C:"Slave_IO_Running" /C:"Slave_SQL_Running" /C:"Seconds_Behind_Master"
timeout /t 5
goto loop

AWS DMS 로 데이터를 옮기는 테스트를 해보자

1. Replication Instance 생성

 - subnet group 생성

- 복제 인스턴스 생성 

RI 가 속할 vpc 를 선택하고 위에 만들어둔 서브넷그룹을 선택한다. 

2. 데이터 마이그레이션 태스크 생성

 - 엔드포인트 생성

소스, 타겟 엔드포인트를 생성하고 연결테스트까지 해본다 

대상RDS 의 보안그룹의 인바운드 규칙에 DMS RI 의 주소를 추가해준다. 

연결테스트가 성공이 되면 테스크를 생성하자 

 - 태스크 생성

태스크가 생성되면 자동으로 데이터가 복제된다. 

## 테스트 스크립트 

create database dmstest;

use dmstest;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);
  
  INSERT INTO employees (first_name, last_name, email, hire_date, department, salary) VALUES
('John', 'Doe', 'john.doe@example.com', '2020-01-15', 'IT', 75000.00),
('Jane', 'Smith', 'jane.smith@example.com', '2019-05-22', 'HR', 65000.00),
('Michael', 'Johnson', 'michael.johnson@example.com', '2021-03-10', 'Marketing', 70000.00),
('Emily', 'Brown', 'emily.brown@example.com', '2018-11-30', 'Finance', 80000.00),
('David', 'Lee', 'david.lee@example.com', '2022-02-01', 'IT', 72000.00),
('Sarah', 'Wilson', 'sarah.wilson@example.com', '2020-09-18', 'Sales', 68000.00),
('Robert', 'Taylor', 'robert.taylor@example.com', '2019-07-05', 'Marketing', 71000.00),
('Lisa', 'Anderson', 'lisa.anderson@example.com', '2021-11-12', 'HR', 67000.00),
('William', 'Martinez', 'williahttp://m.martinez@example.com', '2018-04-20', 'Finance', 82000.00),
('Jennifer', 'Garcia', 'jennifer.garcia@example.com', '2022-01-03', 'Sales', 69000.00);

select * from employees;

정상적으로 데이터가 복제되었다.

GIN(Generalized Inverted Index) 인덱스란 ? 
PostgreSQL에서 복합적인 데이터를 효율적으로 검색하기 위해 사용되는 특수한 인덱스 유형
주요 특징은 아래와 같다. 

1. 복합 데이터 처리: 배열, JSON, hstore, tsvector 등 복합적인 데이터 타입에 적합
2. 키워드 기반 검색: 문서나 텍스트에서 특정 단어를 빠르게 찾을 수 있다.
3. 역인덱스 구조: 키워드나 토큰을 기반으로 데이터를 인덱싱
4. 전문 검색(Full-text search) 지원: 텍스트 검색 속도를 크게 향상
5. 다양한 연산자 지원: LIKE, ILIKE, ~, ~* 등 다양한 검색 연산자를 지원

GIN 인덱스는 특히 대량의 텍스트 데이터를 처리할 때 B-tree 인덱스보다 훨씬 효과적이며,
 검색 성능을 크게 향상시킬 수 있다.
  다만, 인덱스 생성과 업데이트에는 상대적으로 많은 시간이 소요될 수 있습니다

## 복합 데이터 처리

테스트 테이블을 만들고 GIN 인덱스 사용 전후의 플랜을 비교해보자 

-- Create a table with a JSONB column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    details JSONB
);

-- Insert sample data
INSERT INTO products (name, details) VALUES
    ('Laptop', '{"brand": "TechCo", "specs": {"ram": "16GB", "storage": "512GB SSD"}, "tags": ["electronics", "computer"]}'),
    ('Smartphone', '{"brand": "Gadgetron", "specs": {"ram": "8GB", "storage": "256GB"}, "tags": ["electronics", "mobile"]}'),
    ('Headphones', '{"brand": "AudioPro", "specs": {"type": "over-ear", "wireless": true}, "tags": ["audio", "accessories"]}');


-- Query using the GIN index to find products with specific attributes
EXPLAIN ANALYZE
SELECT name, details
FROM products
WHERE details @> '{"brand": "TechCo"}';

-- Query to find products with specific tags
EXPLAIN ANALYZE
SELECT name, details
FROM products
WHERE details->'tags' @> '"electronics"'::jsonb;

-- Create a GIN index on the JSONB column
CREATE INDEX idx_product_details ON products USING GIN (details);

## 전문검색 처리 

-- 문서 테이블 생성 
CREATE TABLE  documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    content_tsv tsvector
);

-- 함수 생성: 랜덤 문장 생성
CREATE FUNCTION random_sentence() RETURNS TEXT AS $$
DECLARE
    words TEXT[] := ARRAY['PostgreSQL', 'database', 'index', 'query', 'performance', 'GIN', 'B-tree', 'full-text', 'search', 'optimization', 'SQL', 'data', 'management', 'system', 'relational', 'ACID', 'transaction', 'concurrency', 'scalability', 'replication'];
    sentence TEXT := '';
    word_count INT;
BEGIN
    word_count := 5 + random() * 10; -- 5에서 15 단어 사이의 문장 생성
    FOR i IN 1..word_count LOOP
        sentence := sentence || ' ' || words[1 + random() * (array_length(words, 1) - 1)];
    END LOOP;
    RETURN trim(sentence);
END;
$$
 LANGUAGE plpgsql;

-- 10,000개의 샘플 문서 생성
INSERT INTO documents (content, content_tsv)
SELECT 
    random_sentence(),
    to_tsvector('english', random_sentence())
FROM generate_series(1, 10000);

-- GIN 인덱스 생성 
CREATE INDEX  idx_gin_content ON documents USING gin(content_tsv);

-- 통계 업데이트
ANALYZE documents;

-- 검색 쿼리 실행 및 실행 계획 확인
EXPLAIN ANALYZE
SELECT id, content
FROM documents
WHERE content_tsv @@ to_tsquery('english', 'postgresql & index');

1. 식별자 종류

주식별자 : PK
업무식별자 : 업무적으로 인스턴스를 구분하는 속성 (논리적 구분)
후보식별자 : 주식별자가 될수 있는 후보
대체식별자 : 주식별자로 선택되지 않은 후보식별자
인조식별자 : 물리적으로 인스턴스를 구분. 인스턴스 추가 기준 알수없다
슈퍼식별자 : 식별자 + 추가 속성(주로 성능적 이유, 안만드는 것이 좋다) 

2. 식별자 선정 과정 # 98p 

  1) 업무식별자 도출
  2) 후보식별자 도출
  3) 업무식별자가 주식별자로  "적합한지 검토" 
    -> 적합) 업무식별자를 주 식별자로 선정
    -> 부적합) 후보식별자 중 주 식별자 " 적합한지  검토" 
            => 적합) 후보식별자를 주 식별자로 선정
            => 부적합) 인조식별자를 주 식별자로 선정

우선순위 : 업무식별자 > 후보식별자 > 인조식별자

3. 엔터티 유형별 업무식별자 도출

 실체엔터티 : 보통 일시속성이 포함x , 포함 되었다면 순수 실체x     ex) 사원E 의 주민번호 + 입사일자 
 행위엔터티 : 누가(who),무엇(what),언제(when) 등 5w1h 로 따져서 업무식별자 도출

4. 식별자 선택 시 고려사항:
 - 업무적 활용도가 높을 것
 - 길이가 짧은 것
 - 대표성을 가질 것
 - 불변성 (한번 부여된 식별자는 변하지 않아야 함)
 - 유일성 (각 인스턴스를 유일하게 식별할 수 있어야 함)

mysql innodb 사용시 pk 선정에 대한 고려사항

1. innodb 의 특성 

 - pk컬럼의 값으로 데이터가 정렬되어 저장된다. 
 - 세컨더리 인덱스에서 rowid 같은 물리적 주소를 사용하지 않고 pk 값을 참조함.  
   이런한 특성으로 pk를 사용한 range 검색에 유리하지만, pk변경이 발생하게 되면 레코드의 재배치, 
   인덱스 재구성 과정이 발생하여 성능에 매우 불리함. 세컨더리 인덱스가 많을경우 pk 가 길면 저장공간이 커짐. 
 - PK가 크면 전체적인 인덱스 크기도 커진다. 작은 PK는 메모리 사용을 줄이고 인덱스 성능을 향상시킴.

2. innodb 에서 pk 선정

 우선순위 :   본질 식별자 >  auto increment > no pk 
  
  1) 본질식별자로 pk 설정하는 경우
 - 업무적으로 의미 있는 칼럼을 PK로 설정하면 쿼리 조건에 자주 사용되어 성능에 유리
 - 순차적이고 업데이트가 거의 발생하지 않는 값인지 확인이 필요. 
 - 단일키 구성이 어려울 경우, 복합키로 구성도 고려할수 있다.  
 
  2) auto increment 로 pk 를 설정하는 경우 (인조식별자, surrogate key)
   장점)
  - 삽입 성능이 우수함. 정수형 데이터로 인덱스 크기가 작고 효율적.
  - 자동으로 증가하는 값으로 중복을 피할 수 있어 고유성이 보장됨.

 단점)
  - 업무적 의미가 없어 쿼리 조건으로 사용되기 어려운 경우가 많음.
  - 보안 측면에서 취약할 수 있다. 키 값이 예측 가능해 SQL injection 공격에 노출 가능성 
  - 분산 환경에서 사용이 어렵다. 여러 데이터베이스에서 키 값 중복 문제가 발생할 수 있다

   로그성 테이블 같은 조회보다 insert 가 주 목적인 경우면 나쁘지 않다.
   
 3) pk 없이 테이블을 생성하는 경우
   innodb 는 내부적으로 인조식별자를 만든다. 이것은 시스템 내부적으로만 쓰이게 되어 성능상 이점이 없다.
   => 최소 auto increment 를 사용하는 것이 낫다.
   
 
 

mysql 은 replication 을 통해 복제서버를 사용할수 있게 해준다. 

복제서버는 백업이나 조회 용도로 쓸수 있어서 매우 유용하다. 

오라클은 ogg 를 사용하거나 data guard 를 사용해야하는데 라이센스를 추가로 구매해야 한다. 

그런면에서 mysql 은 참으로 혜자다. (오라클은 파티션도 EE 에서만 사용가능한데 mysql 은 무료다.)

MySQL Replication

mysql 이 제공하는 replication 은 데이터 변경을 기록하는 binlog 를 target db 에 전달하면 target 에서 relay log 라는 이름으로 기록하고 그것을 다시 target DB 에 반영하는 방식이다. 

이 구조에서의 특징은 아래와 같다. 

1. 소스와 타켓의 지연이 발생한다. 반영해야 할 변경량이 많을수록 지연은 커질수 있다. 완전 실시간 동기화를 요구하는 업무에는 맞지 않는다. 

2. 복제가 깨지기 쉽다.  오라클 RAC 처럼 shared storage 를 쓰는 경우 어느 인스턴스에서 접근해도 동일한 data 를 보장하지만, mysql 복제 방식은 각자 자기의 DB 를 가지고 사용하기에 동일한 DB임이 보장되지 않는다. 

실제로 mysql replication 구조를 운영환경에서 사용하는 중에 복제가 깨지는 일은 종종 일어난다. 

그렇지만 의도적으로 복제DB를 다른 형상으로 유지하고 싶은 경우도 있다. 인덱스를 다르게 유지하거나 스키마를 다르게 유지하는 등의 유연한 운영이 가능하다. 

AWS aurora mysql 

AWS 에서는 aurora 라고 부르는 DB cluster 를 통해 복제DB 를 제공한다. 

AWS 는 레거시 환경의 서버구조를 computing unit 과 storage 로 분리해서 제공하는데,

Aurora cluster 도 아래 그림처럼 sql, trasaction, caching 을 담당하는 instance 와  logging 과 storage 를 담당하는 shared storage volume 구조로 구성되어 있다. 

DB instance 는 writer (primary) instance 와 reader instance 의 두가지 타입으로 이루어져 있다. 

replication 과 다른 특이한 점은 writer 가 6개의 storage 에 write 를 하고 reader 에서는 자기의 storage 를 read만 할 뿐이다. 

여기서 몇가지 의문점이 생길수 있다. 

Q1) 하나의 인스턴스에서 6개의 볼륨에 write 를 하는 것이 성능상 문제가 없는가? 

Q2) 클러스터를 유지하기 위해 6개의 스토리지 비용을 감당하는 것인가? 

그에대한 답은 아래와 같다. 

A1) write 명령에 대해서는 실제 data를 update 하는 것이 아닌 log record 만 우선 update 하는 방식으로 시간을 절약(궁극적으로는 당연히 모든 노드(6개)에서 page update를 한다)

A2) 모든 스토리지가 동일한 구성이 아니고 3개는 data 와 로그레코드를 가진 full segment, 3개는 로그레코드만 가진 tail segemnt 로 구성된다. 그래서 실제로 비용은 6배가 아닌 3.x 배 정도 소요된다. 

형상이 동일하게 유지되기 때문에 replication 방식처럼 의도적으로 다르게 형상을 관리하는 것이 불가능하다. 

RAC 와 replication  의 중간 정도 느낌이다.  

aurora cluster 가 6개의 storage 를 사용하는 이유는 어느하나가 일시적 장애를 만나도 서비스가 가능하게 만드려는데 목적이 있고 내부적으로는 quorum model 을 통해서 그것이 가능해진다. 이 내용은 별도의 포스팅에서 정리해보겠다.

mysql 에서 사용하는 로그들 정리해보자 

1. General log 

general log 를 활용하면 client 에서 어떤 쿼리들을 mysql server 에 보내는지 확인 할수 있다. 

 1) Binary log 와의 차이

general log 는 서버에 쿼리 요청이 들어오는 순서대로 로그에 기록된다. 
반면 binary log 는 실행시간에 따라 기록이 된다. 
또한 binary log 는 select 는 포함하지 않는다. 
general log 는 조회 쿼리도 포함되기에 log 의 양이 많다.  

 2) output 종류 (slow 동일)

아래 log_output 을 아래 세가지 중 하나를 선택할 수 있다. 
none 으로 설정을 하면 우선순위가 앞서며 general_log=on 으로 설정해도 로그가 남지 않는다. 

 - file      =>  *.log 를 생성
 - table   =>  *.csv 를 생성 
 - none 

table 을 선택하면 general_log 이름으로 DB에서 조회가 가능해진다. 
테이블을 사용하는데 size 가 걱정되어 테이블 사이즈를 조회하는 쿼리를 날리면 0 으로 나온다. 
이것은 general_log 테이블이 테이블이라는 object 에 쌓는 것이 아니고
실제로는 CSV 엔진으로 general_log.CSV 라는 파일에 덤프를 쌓고 있기 때문이다. 

 3) Table output 의 장점 

 - query 에 조건문을 넣어서 질의 하는 것이 가능해진다.
 - 원격에서도 확인이 가능 
 - csv 파일 접근 가능한 스프레드시트에서 활용이 가능해진다. 


 4) general log 사용하기 

show variables like 'general%';
set global general_log=on;
set global log_output='TABLE';
set global general_log=off

2. Slow log

실행된 쿼리 중 오래 돌았던 쿼리를 기록. 

남기는 기준은 long_query_time 파라미터 (min 0, default 10)

사용여부 : slow_query_log = 0 disable, 1 enable
파일이름 : slow_query_log_file=file_name

general log 와 마찬가지로 log_output 이 table 일 경우 CSV 엔진을 사용하여 *.CSV 파일을 만든다. 

size 가 커질 경우 조회

3. Bin/relay log 

data 를 변경하는 statement 를 기록. select 는 포함되지 않는다.

복제서버 구조에서 source 쪽의 binlog 를 target 에 전달하면 target 의 relay log 에 동일한 포맷으로 기록된다.

로그포맷 형식에는 아래 3가지가 있다. 

  - Statement-Based Logging (SBL)

  - Row-Based Logging  (RBL) 

  - Mixed

binmysqlbinlog

4. Error log 

mysqld 를 운영, 중지, 시작시 만나게 되는 에러를 기록 

 - 기동관련 정보성 및 에러메시지
 - 비정상종료 복구 메세지
 - 쿼리처리 도중 발생 에러메시지 
 - aborted connection
    max_connect_errors
    
 - innodb모니터링, show engine innodb status 결과 
 - mysql 종료메시지

이렇게 정리해놓고 봐도 알수있는게 mysql 은 네이밍에 일관성이 없는 것이 특징이다.  

자주 사용되는 시스템 뷰

pg_tables: 데이터베이스의 모든 테이블 정보 
pg_views: 모든 뷰에 대한 정보 
pg_indexes: 데이터베이스의 모든 인덱스 정보 
pg_stats: 플래너(query planner)가 사용하는 통계 정보 
pg_settings: 서버 설정 파라미터에 대한 정보 
pg_roles: 데이터베이스 롤(사용자 및 그룹)에 대한 정보 
pg_user: 데이터베이스 사용자 정보 

추가적인 유용한 시스템 뷰

pg_locks: 현재 보유 중인 락(lock)에 대한 정보 
pg_stat_activity: 현재 실행 중인 쿼리와 세션에 대한 정보 
pg_prepared_statements: 준비된 구문(prepared statements)에 대한 정보 
pg_available_extensions: 사용 가능한 확장(extensions)에 대한 정보 
pg_cursors: 현재 열려있는 커서에 대한 정보 
pg_file_settings: 설정 파일의 내용 요약을 제공 
pg_matviews: 모든 물리화된 뷰(materialized views)에 대한 정보 

# 테이블 권한 부여하기 

select *
  from pg_tables
 where schemaname='public'
   and tablename like 'abc%'
;

select *
  from pg_views
 where schemaname='public'
   and tablename like 'abc%'
;

   
select *
  from information_schema.role_table_grants
 where grantee='USER1'
   and table_name like 'abc%'
 
grant select on table public.abc to "USER1";

 
   
   

* dbeaver 유용한 단축키 
 
- 한줄주석 : 블럭잡고 ctrl + / 
- 쿼리 여러줄 실행 : 블럭잡고 alt + x
- 실행계획 확인 : ctrl + shift + E 

* 기타 유용한 기능

- 행번호 표시 : 에디터 맨좌측에서 우클릭
-system object (mysql) 보기 : 커넥션이름에서 우클릭 -> connection view -> show system objects
-테이블 정보 : ctrl 누르고 마우스 커서를 테이블 이름 위에 
- 디비 연결정보 옮기기 : 파일 -> 내보내기,
다른pc에서 가져오기, .dbp파일 선택 -> set active project