MySQL:ALTER TALBE ADD COLUMN이 오래 걸리는 경우

ALGORITHM=INSTANT(MySQL 8.0.12+)

✅ ALGORITHM을 명시해야 하는 경우

1. 성능이 중요한 운영 환경에서

운영 중인 시스템에서는 ALTER로 인해 잠깐이라도 서비스 중단 또는 지연이 발생하면 안 됩니다. 이때:

• **ALGORITHM=INSTANT + LOCK=NONE**을 명시하면
  → 예측 가능한 성능 확보 가능
  → DML 차단 없이 변경 가능 여부 확실하게 판단 가능

2. 변경 작업이 어떤 방식으로 실행될지 확실히 알고 싶을 때

명시하지 않으면 MySQL은 자동으로 적절한 알고리즘을 선택 (ALGORITHM=DEFAULT)합니다. 이는 다음과 같은 문제가 발생할 수 있습니다:

• 원하지 않던 INPLACE나 COPY가 실행되어 성능 저하
• ALTER TABLE 명령이 수 초~수 분 이상 지연
• 예상치 못한 서비스 차단(locking) 발생

따라서 명시함으로써 다음을 보장할 수 있습니다:

• 성능 예측 가능성
• 잠금 수준 통제
• DDL 실행 실패 시 명확한 에러 발생

3. 배포 자동화 스크립트에서

CI/CD, Flyway, Liquibase 등 자동 배포 스크립트에서는:

• 예상하지 못한 DDL 수행이 서비스에 영향을 주지 않도록
• 명시적으로 ALGORITHM과 LOCK을 지정하는 것이 안정적

ALTER TABLE users ADD COLUMN timezone VARCHAR(50)
ALGORITHM=INSTANT LOCK=NONE;

이렇게 하면 조건이 맞지 않을 경우 DDL 실행이 에러로 중단되어 예측 불가능한 상태를 방지할 수 있습니다.

---

❌ 명시하지 않아도 되는 경우

• 테스트 환경에서의 실험용 DDL
• 작은 테이블에 대한 간단한 변경
• MySQL이 자동 선택해도 문제 없다고 확신할 때

이 경우는 다음처럼 간단히 작성해도 무방합니다:

ALTER TABLE logs ADD COLUMN archived TINYINT(1);

---

🧪 권장 사용 팁

1. 변경 전 확인:

EXPLAIN ALTER TABLE your_table ADD COLUMN new_col INT;

 

2. 예상 알고리즘 확인 후 명시적으로 실행:

ALTER TABLE your_table ADD COLUMN new_col INT
ALGORITHM=INSTANT LOCK=NONE;

 

3. 실패 시, ERROR 1846 같은 명확한 메시지로 어떤 조건이 위반되었는지 확인 가능

---

📌 결론

상황	ALGORITHM 명시 필요 여부	비고
운영 환경 (대규모 테이블)	✅ 필수	INSTANT 또는 INPLACE와 LOCK 조합
자동화 배포 스크립트	✅ 필수	실패 시 중단 보장
테스트 또는 소규모 DDL	❌ 선택	자동 선택 허용 가능
데이터 복사 여부를 제어하고 싶을 때	✅ 필수	COPY 사용 방지 목적 등

---

 

MySQL ALGORITHM 3가지

🔧 1. ALGORITHM=INSTANT

도입: MySQL 8.0.12+

✅ 장점

• 매우 빠름: 메타데이터만 수정되므로 거의 즉시 완료됨
• 데이터 복사 없음
• LOCK=NONE과 함께 사용 가능 → DML(SELECT/INSERT/UPDATE/DELETE) 모두 병행 가능
• 테이블 크기에 관계없이 작업 속도 일정

❌ 단점

• 제약 조건 많음:
  • 컬럼은 맨 끝에만 추가 가능 (AFTER 불가)
  • NOT NULL + DEFAULT 지정 불가
  • 일부 작업(인덱스 추가 등) 불가능
• 구형 버전에서는 지원되지 않음 (8.0.12 미만)

🔍 예시

ALTER TABLE users ADD COLUMN nickname VARCHAR(50) ALGORITHM=INSTANT;

---

⚙️ 2. ALGORITHM=INPLACE

도입: MySQL 5.6+

✅ 장점

• 많은 DDL 작업을 데이터 복사 없이 수행 가능
• 일부 인덱스 추가/삭제, 기본값 변경 등 비교적 다양한 작업 지원
• 대개 LOCK=NONE 또는 LOCK=SHARED와 함께 사용 가능 → 읽기는 병행 가능

❌ 단점

• 컬럼 변경 등의 작업에서는 일부 DML 차단 발생 가능
• 테이블 크기에 따라 시간이 다소 걸릴 수 있음
• INSTANT보다는 느림

🔍 예시

ALTER TABLE users ADD INDEX idx_email (email) ALGORITHM=INPLACE;

---

🛠️ 3. ALGORITHM=COPY

도입: MySQL 기본 알고리즘 (모든 버전에서 지원)

✅ 장점

• 제약 없음: 거의 모든 종류의 테이블 변경이 가능
• 오래된 MySQL 버전에서도 사용 가능

❌ 단점

• 전체 테이블을 복사 → 매우 느리고 디스크 I/O 많음
• LOCK=EXCLUSIVE만 가능 → DML 및 SELECT 모두 차단됨
• 대용량 테이블에서는 매우 위험

🔍 예시

ALTER TABLE users MODIFY COLUMN age SMALLINT ALGORITHM=COPY;

---

📊 정리 비교표

항목	INSTANT	INPLACE	COPY
속도	🚀 매우 빠름	✅ 빠름	🐢 느림
데이터 복사	❌ 없음	❌ 없음	✅ 있음
DML 차단	❌ 없음	🔸 경우에 따라 있음	✅ 있음
가능한 작업	제한적	대부분	거의 모두 가능
LOCK 수준	NONE	NONE/SHARED	EXCLUSIVE
지원 버전	MySQL 8.0.12+	5.6+	모든 버전
사용 예	단순 컬럼 추가	인덱스 추가	컬럼 위치 변경 등

---

🧠 결론

사용 목적	추천 알고리즘
빠른 컬럼 추가 (끝에만)	INSTANT
인덱스 추가/삭제, 기본값 변경 등	INPLACE
컬럼 위치 바꾸기, 복잡한 구조 변경	COPY

---

ALGORITHM을 명시해도 느린 경우

🛠️ 해결 방법 및 조치

• 시스템 설정 조정: my.cnf 파일에서 다음과 같은 설정을 조정할 것을 제안합니다

innodb_open_files = 8000
table_open_cache = 8000
read_rnd_buffer_size = 16384
read_buffer_size = 524288

이러한 조정을 통해 테이블 오픈 캐시를 늘리고, 버퍼 크기를 조정하여 성능을 향상시킬 수 있습니다.

• 파일 디스크립터 제한 증가: 운영 체제의 ulimit 설정을 통해 열 수 있는 파일 수를 늘리는 것도 도움이 됩니다:

ulimit -n 20000

이 설정은 시스템이 동시에 열 수 있는 파일 수를 증가시켜, 테이블 오픈 시 발생하는 지연을 줄일 수 있습니다.

• 테스트 결과: 이러한 설정 변경 후, 20억 건 이상의 레코드를 가진 테이블에서 DROP TABLE 명령이 0.14초 만에 완료되었습니다.

---

✅ 결론 및 권장 사항

• ALGORITHM=INSTANT의 한계 인식: INSTANT 알고리즘은 메타데이터만 변경하므로 빠르게 실행되어야 하지만, 실제로는 메타데이터 락이 예상보다 오래 유지되어 전체 데이터베이스의 응답성이 저하될 수 있습니다.
• 시스템 설정 최적화: 테이블 오픈 캐시 및 버퍼 크기 등의 시스템 설정을 조정하여 성능을 향상시키고, 메타데이터 락으로 인한 지연을 최소화할 수 있습니다.
• 운영 시간 고려: 고트래픽 환경에서는 이러한 DDL 작업을 시스템 부하가 적은 시간대에 수행하는 것이 바람직합니다.
• 사전 시뮬레이션: EXPLAIN ALTER TABLE 명령을 사용하여 변경 작업의 영향을 사전에 평가하고, 예상치 못한 지연을 방지할 수 있습니다.

---

🔍 설정의 효과와 동작 원리

1. innodb_open_files = 8000

• 기능: InnoDB가 테이블마다 사용하는 내부 파일(.ibd) 수 제한
• 효과: 많은 수의 InnoDB 테이블이 있고 이를 동시에 열어야 할 때, 열린 파일 핸들 부족 문제 방지
• 언제 효과 있음?
  • 수천 개 이상의 테이블을 가진 시스템
  • 대용량 서버에서 병렬 처리 많을 때

---

2. table_open_cache = 8000

• 기능: MySQL이 캐시에 유지할 수 있는 테이블 핸들의 수
• 효과: 캐시에 열려 있는 테이블 수가 많을수록 디스크 I/O 줄어듦 → 쿼리 성능 향상
• 언제 효과 있음?
  • 많은 테이블이 자주 열리고 닫히는 환경
  • 다수의 동시 접속자가 다른 테이블을 사용하는 경우

---

3. read_buffer_size, read_rnd_buffer_size

• 기능: 테이블 스캔 또는 정렬된 읽기 시 사용하는 버퍼
• 효과: 정렬, 풀스캔 등의 작업 속도 향상
• 언제 효과 있음?
  • 정렬(ORDER BY), GROUP BY, LIKE '%xxx%' 등이 많을 때

---

4. ulimit -n 20000 (nofile)

• 기능: 리눅스에서 프로세스당 열 수 있는 파일 디스크립터 수
• MySQL 프로세스는 테이블, 인덱스, 로그 등 모든 파일마다 디스크립터를 사용
• 효과: 파일 핸들 부족으로 인한 오류(Too many open files) 방지, 속도 개선

---

📊 실제 효과는 언제 큰가?

조건	효과
수천 개 테이블	✅ 매우 큼 (file handle 문제 직접 타격)
많은 동시 연결 수	✅ 연결마다 테이블 오픈 시 병목 방지
FULLTEXT나 대규모 ALTER 후 MDL 발생	✅ 메타데이터 락 지연 줄임
대용량 정렬/풀스캔	✅ read_buffer_size / read_rnd_buffer_size 적용 가능

 

즉, "큰 테이블"이 아니라 "많은 테이블" + "많은 연결 수" + "파일 핸들 제약"이 있는 시스템이라면 효과가 큽니다.

---

🚫 효과가 거의 없는 경우

• 테이블 수가 적고 (수십~수백 개)
• 대용량 작업이 아닌 경우
• 이미 시스템 리소스(메모리/파일 핸들 등)가 넉넉한 경우

이런 경우 설정을 높여도 눈에 띄는 성능 차이는 없을 수 있습니다.

---

✅ 결론

설정	효과 있음?	요약
innodb_open_files	✅ (테이블 수 많을 때)	파일 핸들 부족 방지
table_open_cache	✅ (다중 테이블 동시 사용 시)	테이블 오픈/닫기 성능
read_rnd_buffer_size	🔸 (정렬/랜덤 읽기 많을 때)	버퍼 성능 향상
read_buffer_size	🔸 (풀스캔 많을 때)	읽기 성능 개선
ulimit -n	✅ (전체적 안정성)	오픈 파일 제한 해제

✅ 1. ulimit -n (열 수 있는 파일 수)

기본 개념:

• MySQL은 테이블, 인덱스, 로그 등 모든 파일에 대해 FD(File Descriptor)를 사용
• 너무 낮으면 "Too many open files" 에러 발생

계산 기준:

ulimit -n ≥ (table_open_cache * 2) + 연결 수 여유분

실무 권장:

• 최소 10,000 이상 설정 권장 (ulimit -n 10000)
• 대형 서비스라면 20,000~65,535 수준까지 확장 가능

설정 예:

ulimit -n 20000  # Linux shell

/etc/security/limits.conf

---

✅ 2. table_open_cache

기본 개념:

• MySQL이 동시에 열어둘 수 있는 테이블 수
• 세션마다 테이블을 공유하므로, 많은 동시 접속자 + 많은 테이블이 있는 경우 캐시 부족이 성능 저하 원인이 됨

계산 기준:

table_open_cache ≥ (동시 연결 수 * 평균 사용하는 테이블 수) + 25%

예:

• 200개의 동시 연결, 평균 10개의 테이블 사용
• → table_open_cache ≈ 200 * 10 * 1.25 = 2500

실무 권장:

상황	추천 값
소규모 개발용	400~800
일반 서비스	2,000~8,000
대규모	10,000+ (필요 시)

 

튜닝 확인:

SHOW GLOBAL STATUS LIKE 'Open_tables';
SHOW GLOBAL STATUS LIKE 'Opened_tables';

• Opened_tables가 지속적으로 증가 → 캐시 부족

---

✅ 3. innodb_open_files

기본 개념:

• InnoDB가 관리할 수 있는 .ibd 파일 수 (테이블당 1개)
• file-per-table 사용 시 테이블 수만큼 필요

계산 기준:

innodb_open_files ≥ 실제 테이블 수 + 여유 20~30%

예:

• 3,000개 테이블 보유 → 최소 4,000 이상 설정

실무 권장:

• 4,000~10,000 설정 일반적
• 너무 낮으면 테이블 열기/닫기 반복으로 오버헤드 증가

---

✅ 4. read_buffer_size / read_rnd_buffer_size

기본 개념:

• 각 세션에서 사용하는 읽기 버퍼
• 너무 크면 메모리 소모 심함 (사용자 연결 수 고려)

실무 권장:

항목	추천값	설명
read_buffer_size	128KB ~ 512KB	테이블 풀스캔 속도 향상
read_rnd_buffer_size	256KB ~ 1MB	ORDER BY 등 랜덤 읽기 속도 향상

 

단, 이 값은 연결마다 할당됨 → 동시 접속 수가 많다면 과도한 설정은 OOM 위험 있음

---

🧪 모니터링 팁

항목	쿼리	의미
테이블 캐시 적중률	Opened_tables / Open_tables	높으면 캐시 부족
열려 있는 테이블 수	SHOW OPEN TABLES;	빈번한 테이블 오픈 여부 파악
파일 핸들 사용량	`lsof -p $(pidof mysqld)	wc -l`

 

---

✅ 결론

항목	계산	기준권장 범위
ulimit -n	(table_open_cache * 2) + margin	10,000~65,535
table_open_cache	(연결 수 * 평균 테이블 수)	2,000~10,000
innodb_open_files	테이블 수 + 여유	4,000~10,000
read_buffer_size	세션당 128KB~512KB	DML 많을 때
read_rnd_buffer_size	세션당 256KB~1MB	정렬 많은 쿼리

 

⚠ 주의: 너무 높은 값은 오히려 메모리 부족(OOM)을 유발할 수 있으므로, 서버 사양 (RAM, 연결 수 등)에 따라 조정하고, 모니터링 후 튜닝 반복하는 것이 중요합니다.

 

🧠 1. InnoDB 관련 핵심 설정

InnoDB는 MySQL의 기본 스토리지 엔진입니다.

🔹 innodb_buffer_pool_size

• 역할: 디스크 읽기를 줄이기 위한 캐시 역할 (InnoDB 데이터+인덱스 보관)
• 설정: 전체 메모리의 60~80% 권장

innodb_buffer_pool_size = 96G   # (128GB RAM 기준)

---

🔹 innodb_buffer_pool_instances

• 역할: 버퍼 풀을 나눠 동시 접근 성능 향상
• 설정: RAM이 1GB 이상일 경우 8~16 추천

innodb_buffer_pool_instances = 8

---

🔹 innodb_log_file_size

• 역할: InnoDB redo log 크기. 너무 작으면 쓰기 빈번, 너무 크면 복구 지연
• 설정: 512MB ~ 2GB 권장

innodb_log_file_size = 1G

---

🔹 innodb_flush_log_at_trx_commit

• 역할: 트랜잭션 로그 디스크 반영 방식
• 값:
  • 1: 안정성 우선 (기본값)
  • 2: 버퍼에 먼저, 일정 시간마다 플러시 (성능 우선)
• 설정 예시 (성능과 안정성 절충)

innodb_flush_log_at_trx_commit = 2

---

⚙️ 2. 쿼리 캐시 / 스레드 설정

🔹 query_cache_size / query_cache_type (주의: 8.0에서는 제거됨)

• MySQL 5.7 이하에서만 사용 가능

---

🔹 thread_cache_size

• 역할: MySQL 스레드 재사용 캐시
• 설정: max_connections의 5~10% 권장

thread_cache_size = 200

---

📋 3. 로그 및 모니터링 관련 옵션

🔹 slow_query_log

• 느린 쿼리 추적

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

---

🔹 log_error

• 에러 로그 위치 설정

log_error = /var/log/mysql/error.log

---

🚀 4. 접속 처리 및 타임아웃

🔹 max_connections

• 동시 접속자 수에 맞게 설정 (예: 2,000)

max_connections = 2000

---

🔹 wait_timeout, interactive_timeout

• 유휴 세션의 연결 유지 시간을 제한
• 과도한 세션 유지 방지

wait_timeout = 600
interactive_timeout = 600

---

📦 5. 기타 실무에서 자주 쓰는 옵션

🔹 skip-name-resolve

• DNS 역조회 방지 → 접속 속도 향상

skip-name-resolve

---

🔹 sql_mode

• 엄격한 모드 설정 가능 (STRICT, NO_ZERO_DATE, 등)

sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

---

✅ 결론

범주	핵심 옵션
성능 캐시	innodb_buffer_pool_size, table_open_cache
로그 설정	innodb_log_file_size, flush_log_at_trx_commit
동시 처리	thread_cache_size, max_connections
쿼리 분석	slow_query_log, long_query_time
보조	skip-name-resolve, sql_mode