따봉 구걸남'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';