따봉 구걸남's 블로그

IOPS와 throughput은 데이터베이스 및 스토리지 시스템의 성능을 측정하는 중요한 지표이다.

IOPS (Input/Output Operations Per Second)

초당 처리할 수 있는 읽기/쓰기 작업의 수를 나타냄
작은 파일들에 대한 빈번한 접근이 필요한 경우에 중요한 지표
데이터에 대한 빠른 접근 및 처리가 필요한 환경에서 중요

Throughput (처리량)

초당 처리할 수 있는 데이터의 크기를 측정. 주로 메가바이트 단위로 표현
큰 파일들을 주로 다루고 데이터 처리 작업이 많지 않은 경우에 중요한 지표
한 번에 큰 용량의 파일을 읽어야 할 때 중요

계산식

IOPS x I/O size = Throughput
예: 16,000 IOPS × 64 KiB = 1,024 MiB/s (Throughput)

mysql 의 auto_increment 특징 과 주의점들을 알아보자 

# AUTO_INCREMENT의 특징, 최대 값

1) 자동 증가: 새로운 레코드가 삽입시 자동으로 고유한 숫자를 생성. 주로 기본 키로 사용, 각 행의 고유성을 보장
2) 초기값 및 증가값 설정 가능: 기본적으로 1부터 시작하며, 각 행이 추가될 때마다 1씩 증가. 
3) 데이터 타입 제한: 주로 INT 타입으로 설정, 데이터 타입에 따라 최대값이 제한됩니다. 예를 들어, INT의 최대값은 약 21억입니다
4) 연속성: 삭제된 행의 ID는 재사용되지 않고 건너뛴다.

* mysql 에서 innodb 를 엔진으로 사용할때 auto_increment 컬럼을 pk 사용하는 것이 적절한가?

innodb 사용시 pk 의 선정

# 타입 별 최대 값

# 현재 값 확인하기

방법 1) SHOW TABLE STATUS

SHOW TABLE STATUS WHERE Name = '테이블명';

테이블의 상태 정보를 반환하며, 다음 AUTO_INCREMENT 값을 포함하여 테이블의 엔진, 행 수, 평균 행 길이 다양한 메타데이터를 제공

방법 2) INFORMATION_SCHEMA.TABLES

SELECT AUTO_INCREMENT 
   FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_SCHEMA = '데이터베이스명' 
   AND TABLE_NAME = '테이블명'
;

INFORMATION_SCHEMA.TABLES 뷰를 통해 특정 데이터베이스와 테이블에 대한 정보를 쿼리

방법 3) SELECT MAX() 

SELECT MAX(컬럼명) + 1 AS next_auto_increment FROM 테이블명;

현재 존재하는 최대 값을 기반으로 다음 AUTO_INCREMENT 값을 추정
데이터가 삭제되거나 수동으로 삽입된 경우 정확하지 않을 수 있다.

* 이전 버전에서는 auto_increment 값을 메모리에서 관리했지만(서버 재시작시 초기화), 8.0부터는 디스크에 저장하여 영속성을 보장한다.

mysql 파티션 테이블 

# 일반테이블의 파티션 전환시 유의점 
1. 인덱스 제약:
   - MySQL의 파티션 테이블에서는 모든 인덱스가 로컬 인덱스로 처리된다.
   - 글로벌 인덱스를 지원하지 않으므로, 기존 인덱스 구조를 재검토해야 함.

2. 유니크 키와 프라이머리 키 제약:
   - 파티션 키는 모든 유니크 인덱스(프라이머리 키 포함)의 일부 또는 모든 칼럼을 포함해야 함. 아닐경우 에러남
   - 기존 테이블의 키 구조가 이 요구사항을 충족하는지 확인 필수 

3. 외래 키 제약:
   - 파티션 테이블에는 외래 키를 설정 불가 (성능, 관리적 측면의 설계결정)
   - 기존 테이블에 외래 키가 있다면 이를 제거하거나 대체 방안을 마련 필요.

4. 파일 제한:
   - 파티션 테이블은 각 파티션마다 별도의 파일을 사용하므로, 서버의 open-files-limit 설정을 적절히 조정필요.

5. 성능 고려:
   - 파티션 개수가 많아질수록 오히려 성능이 저하될 수 있다.
   - 특히 INSERT 작업의 경우, 파티션 테이블이 일반 테이블보다 느려질 수 있다.

6. 잠금 처리:
   - 파티션 테이블에 쿼리 실행 시 모든 파티션에 대해 잠금이 걸린다.
   - 이는 동시성 처리에 영향을 줄 수 있으므로 주의 필요.

7. 파티션 키 선택:
   - 효과적인 파티션 프루닝을 위해 적절한 파티션 키를 선택해야 함.
   - 주로 조회 조건에 자주 사용되는 칼럼을 파티션 키로 선택하는 것이 유리 

8. 데이터 마이그레이션:
   - 기존 데이터를 새로운 파티션 구조로 마이그레이션하는 과정에서 서비스 중단이 필요한지 확인 필요
   - 대량의 데이터 이동이 필요하므로 충분한 시간과 리소스를 확보해야 함.

9. 쿼리 최적화:
   - 기존 쿼리들이 파티션 구조에서도 효율적으로 동작하는지 검토, 필요시 최적화 필요.

이러한 점들을 고려하여 신중하게 파티션 전환을 계획하고 실행해야 한다. 또한 전환 전후로 충분한 테스트를 수행하여 성능과 기능에 문제가 없는지 확인해야 합니다.
 
# 파티션 테이블 샘플 스크립트 
-- 1. 날짜 기반 레인지 파티션 테이블 생성
CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION future VALUES LESS THAN MAXVALUE
);

-- 2. 파티션 관리: 새 파티션 추가
ALTER TABLE sales ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));

-- 3. 파티션 최적화
ALTER TABLE sales OPTIMIZE PARTITION p2021, p2022, p2023;

-- 4. 특정 파티션 데이터 삭제
ALTER TABLE sales TRUNCATE PARTITION p2021;

-- 5. 파티션 삭제
ALTER TABLE sales DROP PARTITION p2021;

-- 6. 파티션 분할
ALTER TABLE sales REORGANIZE PARTITION p2023 INTO (
    PARTITION p2023_h1 VALUES LESS THAN ('2023-07-01'),
    PARTITION p2023_h2 VALUES LESS THAN (2024)
);
-- maxvalue 파티션 분할 
ALTER TABLE sales
REORGANIZE PARTITION future INTO (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION future VALUES LESS THAN MAXVALUE
);

-- 7. 파티션 정보 확인
SELECT PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'sales';

-- 8. 파티션을 고려한 쿼리 예시
SELECT * FROM sales PARTITION (p2023)
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

 

MySQL의 innodb_read_only와 read_only 파라미터는 비슷한 목적으로 사용되지만 몇 가지 중요한 차이점이 있다

1. 적용 범위:
   - innodb_read_only: InnoDB 스토리지 엔진을 사용하는 테이블에만 적용됩니다.
   - read_only: 모든 스토리지 엔진에 적용됩니다.

2. 권한 처리:
   - innodb_read_only: 모든 사용자(SUPER 권한 포함)의 쓰기 작업을 금지합니다.
   - read_only: SUPER 권한(또는 CONNECTION_ADMIN)을 가진 사용자는 여전히 쓰기 작업이 가능합니다.

3. 설정 변경:
   - innodb_read_only: 서버 시작 시에만 설정 가능한 정적 변수입니다.
   - read_only: 동적 변수로, 서버 운영 중에도 변경 가능합니다.

4. 작업 제한:
   - innodb_read_only: InnoDB 관련 모든 쓰기 작업을 금지합니다.
   - read_only: 일반적인 데이터 변경 작업을 금지하지만, TEMPORARY 테이블 작업이나 복제 관련 작업 등은 허용합니다.

5. 사용 목적:
   - innodb_read_only: 주로 읽기 전용 미디어에서 MySQL을 실행하거나, 데이터 무결성을 위해 사용됩니다.
   - read_only: 주로 복제 설정에서 슬레이브 서버를 읽기 전용으로 만들기 위해 사용됩니다.

6. 시스템 테이블 영향:
   - innodb_read_only: mysql 시스템 데이터베이스의 InnoDB 테이블에도 영향을 미칩니다.
   - read_only: 일부 시스템 작업(예: FLUSH TABLES WITH READ LOCK)은 여전히 허용됩니다.

따라서 innodb_read_only는 더 엄격한 읽기 전용 모드를 제공하며, read_only는 일반적인 운영 상황에서 더 유연한 읽기 전용 모드를 제공합니다.

mysql 에서 table 을 복사하는 몇가지 방법 및 특징을 알아보자

1. CTAS 

 CREATE TABLE NEW_TAB  AS  SELECT * FROM OLD_TAB ;

테이블 구조와 모든 데이터를 복사하지만, 인덱스, 기본 키, 외래 키 등은 복사되지 않음

2. LIKE 

 CREATE TABLE NEW_TAB LIKE OLD_TAB ;

원본 테이블의 구조(컬럼, 인덱스, 제약 조건 등)를 그대로 복사

3. SHOW CREATE TABLE 

 SHOW CREATE TABLE OLD_TAB; => DDL 추출해서 생성

Remote Dictionary Server

1. cache
cache 구현 방법 : memcached, redis, local memory cache 

 cache 전략
 - 읽기 전략
1) look aside : cache 에 없으면 db조회 
  장) 캐시 문제생기면 db 로 위임
  단) 캐시,db간 정합성 유지 어려움 
 
2) read through : 항상 캐시를 통해 
  장) 캐시,db간 정합성 유지 
  단) 캐시 죽으면 장애 
 
 - 쓰기 전략
1) write around : 캐시 우회해서 db에 바로 write  
   장) 성능 이점
   단) 캐시, db간 정합성 유지 어려움
2) write back : 캐시에 미리 write, 나중에 db에 모아서 write  
   장) 쓰기 비용 줄임
   단) 캐시 data loss 가능성

3) writh through : 항상 캐시 먼저 write , 그리고 db에 write  
장) 정합성 유지
단) 항상 두번 쓰는 성능 불리 

2. redis 

redis 용도 : 캐싱, 임시작업 큐, 실시간 채팅, 메시지 브로커 

 특징 
 1) performance
    : 메모리에 저장, get/set 초당 10만 tps
 2) single thread
   : 한번에 하나의 명령만 처리. race condition 발생 X
 3) data structure
   : 다양한 자료구조 제공 
 4) persistence 
   : 메모리 저장 데이터를 디스크 영속화. 복구 가능 
  - RDB (Redis Database Backup) 
   => 특정 간격 메모리 데이터 스냅샷 
  장) 압축,저장 -> 크기 작고 로딩/복구 빨라
  단) 백업 중 다운 되면 최신 데이터 유실 가능성 
 - AOF (Append Only File) 
 => dml 실행시 log파일에 기록 
  장) 실시간 데이터 백업, 데이터 손실 거의 없음
  단) 명령실행 모두 기록 -> 파일크기, 복원시 긴 시간
 

MySQL에서 테이블 이름의 대소문자 구분은 lower_case_table_names 시스템 변수로 제어된다.

이 설정은 운영 체제에 따라 기본값이 다르며, 주의해야 할 점들이 있다. 

1. lower_case_table_names 옵션
 - 0: 테이블 이름을 대소문자 구분 (기본값: Unix/Linux)
 - 1: 테이블 이름을 소문자로 저장하고 비교 시 대소문자 구분 안 함 (기본값: Windows, macOS)
 - 2: 테이블 이름을 지정한 대로 저장하지만 비교 시 소문자로 변환 (macOS에서 권장)

2. 운영 체제별 기본 설정
 Unix/Linux: 0 (대소문자 구분)
 Windows: 1 (대소문자 구분 안 함)
 macOS: 2 (저장은 지정한 대로, 비교는 소문자로)

3. 설정 방법
my.cnf 또는 my.ini 파일의 [mysqld] 섹션에 추가:

[mysqld]
lower_case_table_names=1

4. 주의점
 MySQL 8.0부터는 데이터 디렉토리 초기화 후 이 값을 변경할 수 없다
일관성 유지: 모든 환경에서 동일한 설정을 사용하는 것이 좋습니다
복제 고려: 복제 환경에서는 모든 서버가 동일한 설정을 사용해야 합니다
이식성: 대소문자를 구분하지 않는 설정(1)이 가장 이식성이 좋다
기존 데이터: 설정 변경 시 기존 테이블 이름과 충돌할 수 있으므로 주의가 필요
성능: 대소문자를 구분하지 않는 설정은 약간의 성능 저하를 일으킬 수 있다
파일 시스템 고려: 대소문자를 구분하는 파일 시스템(unix/linux)에서는 0 또는 2 설정을 권장

안전하고 일관된 운영을 위해서는 모든 환경에서 lower_case_table_names=1을 사용하는 것이 좋다.
이미 운영 중인 시스템이라면 변경 시 주의가 필요하며, 새로운 시스템 구축 시 이 설정을 고려해야 한다.

mysql 터미널에서 데이터를 dump & load 하는 방법 알아보자 

아래의 환경에서 테이블을 1개 dump & load 한다고 가정하자 
source db : dumpdb
source table : dumpt
source user : mydba

1. mysqldump

 # dump 
 
    mysqldump -u유저명 -p  -h 소스디비IP -P포트번호 디비명 테이블명 > 타겟파일명.sql
ex) mysqldump -umydba -p  -h 소스디비IP -P포트번호 --no-create-db --no-create-info --set-gtid-purged-OFF --single-transaction dumpdb dumpt > dumpt_bak.sql
덤프 범위 지정 옵션
--databases: 덤프할 데이터베이스들을 지정
--all-databases: 모든 데이터베이스를 덤프
--ignore-table: 특정 테이블을 덤프에서 제외

덤프 내용 제어 옵션
--no-create-info: CREATE TABLE 문을 포함하지 않음 (데이터만 덤프)
--no-data: 테이블 구조만 덤프하고 데이터는 포함하지 않음
--add-drop-database: 덤프 파일에 DROP DATABASE 문 포함
--add-drop-table: 덤프 파일에 DROP TABLE 문 포함

성능 및 일관성 관련 옵션
--single-transaction: InnoDB 테이블에 대해 일관된 덤프를 생성 (락 없이)
--quick (-q): 대용량 테이블 덤프 시 메모리 사용을 줄임
--opt: 여러 유용한 옵션들을 한 번에 활성화 (기본값)

출력 형식 관련 옵션
--extended-insert (-e): 다중 행 INSERT 문 사용 (로드 속도 향상)
--complete-insert: INSERT 문에 컬럼명 포함

특수 목적 옵션
--master-data: 복제 설정을 위한 CHANGE MASTER TO 문 포함
--where: 특정 조건에 맞는 데이터만 덤프
--set-gtid-purged=OFF 옵션의 의미와 용도
GTID 정보 제외: 이 옵션을 사용하면 덤프 파일에 GTID 정보를 포함시키지 않습니다
데이터 이동 시 유용: 서로 다른 GTID 환경 간에 데이터를 이동할 때 유용합니다
복제 토폴로지 유지: 기존의 복제 토폴로지를 유지하면서 테이블을 복사하거나 복구할 때 사용합니다
바이너리 로깅 영향: 이 옵션을 사용하면 덤프 파일을 다시 로드할 때 바이너리 로깅을 비활성화하는 명령문(SET @@SESSION.SQL_LOG_BIN = 0;)이 추가되지 않습니다

 # load 
 
1-1) 타겟디비에 접속 후 load

mysql -u유저명 -p  -h 타겟디비IP -P포트번호 타겟디비명 테이블명
source 타겟파일명.sql

1-2) 디비접속과 load 를 한줄로 실행 

mysql -u유저명 -p  -h 타겟디비IP -P포트번호 타겟디비명 테이블명 < 타겟파일명.sql

2. UTIL (mysqlsh) 사용

2-1) mysqlsh 에 붙어서 작업

 # dump

mysqlsh mysql://유저명@소스디비IP:포트
JS > util.dumpTables(schema, tables, outputUrl[, options])
 ex) util.dumpTables (" dumpdb", ["dumpt"], "/data01/work/dumpdir", {dataOnly: true, threads: 4})

* 한줄로 실행하기
mysqlsh -h 디비ip -P포트 -u 유저명 -p --log-file=/경로/파일명.log -e "util.dumpTable(...상동)"

 - 두번째 테이블 항목에 여러개를 추가하는 것이 가능
 - 멀티스레드로 dump 가능. default 는 1이다.

 # load

mysqlsh mysql://유저명@타겟디비IP:포트
JS > util.loadDump(url[, options]) 
 ex) util.loadDump('/data01/work/dumpdir', {schema:"dumpdb", threads: 4, ignoreExistingObjects: true})

 - load 시 thread 는 디폴트가 4로 생략해도 무방하다. 
 - 타겟에 이미 테이블이 존재한다면 ignoreExistingObjects 를 사용한다. dump시 dataOnly 를 사용하면 안써도됨

3. 정리..

mysqldump 는 결과파일을 사람이 편집도 가능한 sql문을 생성하는 로지컬 백업이다. 
mysqlsh 과는 호환이 되지 않는다. 

mysql쉘의 장점 : 

 - 멀티스레드로 병렬실행이 가능 
 - 파일압축
 - 진행률 확인가능 
 
백업을 지원하는 또 다른 유틸인 util.exportTable 의 경우는 CSV, TSV 등 다양한 텍스트 형식으로 백업이 가능하지만 
단일 테이블 작업만 가능하고, util.importTable 로 로드할수 있다. 


 출처 : https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html 

AWS 프리티어로 postgresql RDS 인스턴스 생성하기 

1. 엔진

엔진버전은 암거나 해도 되는데 나는 15.6 -R3 을 선택했다.

2. 설정 

DB 인스턴스 식별자는 pgdb , 마스터 계정은 pgdba 로 설정했다.

3. 연결

 EC2 로 붙지 않고 바로 접속할거라 퍼블릭액세스 '예' 를 선택한다. 

 퍼블릭이면 누구나 들어올수 있기에 well-known port 가 아닌 나만의 포트로 수정해준다. 

보안그룹의 인바운드 규칙도 편집해준다. 

4. 원격접속하기 

  엔드포인트를 확인하고 디비버에서 접속해보자 

 잘된다. 

AWS RDS 로 Postgresql 생성하고 로컬에서 psql 로 접속하는 테스트 해보자 

1. Postgresql 설치

  AWS RDS 로 postgressql 인스턴스 생성하기 

2. client 설치

아래 링크에서 postgresql 을 다운받자

https://www.postgresql.org/download/

 DB서버를 로컬에 설치하진 않을거라 제외하고 나머지들만 선택해줬다. 

다운받고 설치가 끝나면 (재부팅이 요구된다. 조심하자) 환경변수를 편집해서 psql 을 사용할수 있게 수정하자.

3. psql 로 DB 원격 접속 

cmd 창을 열어 psql 명령어로 접속한다. 

psql -U 유저명 -d 디비명 -p 포트넘버 -h 호스트명 

4. psql 사용법 

## 데이터베이스 관련

- `\l` 또는 `\list`: 모든 데이터베이스 목록 표시
- `\c dbname` 또는 `\connect dbname`: 특정 데이터베이스로 연결

## 테이블 관련

- `\dt`: 현재 데이터베이스의 모든 테이블 목록 표시
- `\d tablename`: 특정 테이블의 구조 확인

## 사용자 관련

- `\du`: 모든 사용자(롤) 목록 표시

## 기타 유용한 명령어

- `\?`: psql 명령어 도움말 표시
- `\q`: psql 종료
- `\g`: 이전에 실행한 쿼리 다시 실행
- `\s`: 명령어 실행 기록 표시

## SQL 실행

- 일반적인 SQL 문은 그대로 입력하고 세미콜론(;)으로 끝내면 실행됩니다.

## 파일 실행

- `\i filename`: 파일에서 SQL 명령 실행

이 명령어들을 사용하여 PostgreSQL 데이터베이스를 효과적으로 탐색하고 관리할 수 있습니다. 필요에 따라 `\?` 명령어를 통해 더 많은 psql 메타 명령어를 확인할 수 있습니다.