MySQL Flush

FLUSH [NO_WRITE_TO_BINLOG | LOCAL] {
    flush_option [, flush_option] ...
  | tables_option
}

flush_option: {
    BINARY LOGS
  | ENGINE LOGS
  | ERROR LOGS
  | GENERAL LOGS
  | HOSTS
  | LOGS
  | PRIVILEGES
  | OPTIMIZER_COSTS
  | RELAY LOGS [FOR CHANNEL channel]
  | SLOW LOGS
  | STATUS
  | USER_RESOURCES
}

tables_option: {
    table_synonym
  | table_synonym tbl_name [, tbl_name] ...
  | table_synonym WITH READ LOCK
  | table_synonym tbl_name [, tbl_name] ... WITH READ LOCK
  | table_synonym tbl_name [, tbl_name] ... FOR EXPORT
}

table_synonym: {
    TABLE
  | TABLES
}

 

---

1. MySQL flush란

MySQL 서버에서 다양한 유형의 데이터를 디스크로 플러시하는 작업을 의미합니다.
다음과 같은 두 가지 유형으로 나눌 수 있습니다.

참고로 사용자는 reload 권한이 필요하고 때때로 FLUSH_TABLES이 필요할 수 있습니다.

 

1) 로그 플러시:

• 변경된 데이터의 로그 레코드를 디스크로 플러시합니다.
• sync_binlog 옵션을 사용하여 제어할 수 있습니다.

2) 데이터 플러시:

• 변경된 데이터 페이지를 디스크로 플러시합니다.
• innodb_flush_method 옵션을 사용하여 제어할 수 있습니다.(buffer pool flush가 포함)

2. MySQL flush privileges란

현재 사용중인 MySql의 캐시를 지우고 새로운 설정을 적용하기 위해서 사용되는 명령어
MySQL의 환경설정을 변경하셨을 경우, MySQL의 재시작 없이 변경한 설정을 적용시키고자할 때 사용
즉, MySQL의 데이터(DML) 변경사항을 적용하기 위해서 사용하는 명령어가 flush privileges
DB 또는 TABLE의 추가, 삭제, 변경인 DDL의 경우에는 설명이 변경된게 아니므로 flush privileges 명령어 쓸 필요가 없음
따라서 SQL문이 아닌 Grant 명령어 사용하여 사용자 추가, 권한 변경은 DDL이므로 flush privileges가 필요없음

 

1) MySQL 접속시

mysql>INSERT INTO 테이블 이름 (열1, 열2, ...) VALUE (값1, 값2 , ….)
Query OK, 1 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

 

2) 쉘프롬프트 사용시

// 비밀번호 없을때
mysqladmin reload
mysqladmin flush-privileges

// 비밀번호 있을때
mysqladmin -u root -p reload
Enter password:
mysqladmin flush-privileges

 

3. MySQL flush table이란

MySQL 테이블 플러시는 테이블 데이터를 메모리 버퍼에서 디스크로 강제로 이동하는 작업

FLUSH TABLES는 열려있는 테이블들을 닫는 동작
테이블을 닫기 전에 변경분은 디스크에 flush(강제이동) 되며 걸려있던 락도 모두 해제
사용되는 경우는 변경분까지 모두 flush하면서 백업까지 받고 싶을 경우 FLUSH TABLES를 실행하기도 함
당연하게도 Query Cache의 result도 메모리를 날려버리니 모두 삭제

InnoDB 엔진에서 사용하는 테이블에 적용되며, 다음 두 가지 유형으로 나눌 수 있음

 

1) 테이블 전체 플러시

FLUSH TABLES table_name;

 

 

2) 테이블 페이지 플러시

ALTER TABLE table_name FORCE INDEX;

 

페이블 페이지 플러시는 테이블 전체 플러시와 달리 특정 테이블의 변경된 페이지만 선택적으로 플러시하는 것을 말합니다.

InnoDB 엔진에서 사용하는 테이블의 변경된 페이지만 디스크로 강제로 이동시킵니다.

 

---

FLUSH TABLES의 동작에 맞춘 3가지 튜닝포인트

1. 잠금 해제 (Lock Release)

1.1) 개요

FLUSH TABLES 명령을 실행하면 모든 테이블에 대한 잠금이 해제됩니다. 쿼리 실행 중에 테이블에 잠금이 걸릴 수 있으며, 이는 여러 가지 이유로 발생합니다. 예를 들어, 다음과 같은 경우 잠금이 발생할 수 있습니다.

• 데이터 변경: INSERT, UPDATE, DELETE와 같은 데이터 변경 쿼리는 테이블에 대한 쓰기 잠금을 걸어 데이터 무결성을 유지합니다.
• 데이터 읽기: SELECT 쿼리는 일반적으로 테이블에 대한 읽기 잠금을 걸어 다른 사용자가 데이터를 변경하는 것을 방지합니다.
• DDL 작업: CREATE TABLE, ALTER TABLE와 같은 DDL 작업은 테이블 구조를 변경하기 때문에 테이블에 대한 전용 잠금을 걸어 다른 사용자가 테이블에 접근하는 것을 차단합니다.

1.2) 문제 해결:

FLUSH TABLES 명령을 실행하여 잠금을 해제하면 다음과 같은 문제를 해결할 수 있습니다.

• 데이터 삽입 오류: 테이블에 대한 쓰기 잠금이 걸려 있을 경우 데이터 삽입 오류가 발생할 수 있습니다. FLUSH TABLES 명령을 실행하면 잠금을 해제하여 삽입 오류를 해결할 수 있습니다.
• 데이터 읽기 오류: 테이블에 대한 읽기 잠금이 걸려 있을 경우 데이터 읽기 오류가 발생할 수 있습니다. FLUSH TABLES 명령을 실행하면 잠금을 해제하여 읽기 오류를 해결할 수 있습니다.
• DDL 작업 실패: 테이블에 대한 전용 잠금이 걸려 있을 경우 DDL 작업이 실패할 수 있습니다. FLUSH TABLES 명령을 실행하면 잠금을 해제하여 DDL 작업을 성공적으로 수행할 수 있습니다.

1.3) 잠금 정보 확인:

SHOW PROCESSLIST 명령을 사용하여 현재 실행 중인 모든 프로세스와 각 프로세스가 보유하고 있는 잠금 정보를 확인할 수 있습니다.

SHOW PROCESSLIST;

 

1.4) 주의 사항:

FLUSH TABLES 명령을 실행하면 모든 테이블에 대한 잠금이 해제되므로 데이터 무결성 문제가 발생할 수 있습니다.

• 예를 들어, 데이터 변경 쿼리가 실행 중인 경우 FLUSH TABLES 명령을 실행하면 데이터 손실이 발생할 수 있습니다.
• 따라서 FLUSH TABLES 명령을 실행하기 전에 현재 실행 중인 쿼리와 잠금 정보를 확인하고 주의해서 사용해야 합니다.

2. 파일 닫기 (File Close)

2.1) 개요:

FLUSH TABLES 명령을 실행하면 모든 테이블에 대한 열린 파일이 닫힙니다. 테이블 파일이 열려 있을 경우 다음과 같은 문제가 발생할 수 있습니다.

• 파일 핸들 부족: 운영 체제는 사용할 수 있는 파일 핸들의 수에 제한을 두고 있습니다. 테이블 파일이 너무 많이 열려 있을 경우 새로운 파일을 열 수 없게 되어 오류가 발생할 수 있습니다.
• 파일 접근 오류: 여러 사용자가 동시에 같은 테이블 파일에 접근하려고 하면 파일 접근 오류가 발생할 수 있습니다.

2.2) 문제 해결:

FLUSH TABLES 명령을 실행하여 모든 테이블 파일을 닫으면 다음과 같은 문제를 해결할 수 있습니다.

• 파일 핸들 부족: 테이블 파일을 닫아 사용 가능한 파일 핸들을 확보하여 새로운 파일을 열 수 있도록 합니다.
• 파일 접근 오류: 테이블 파일을 닫고 다시 열면 파일 접근 오류를 해결할 수 있습니다.

2.3) 관련 변수:

• table_open_cache: 테이블 캐시에 저장될 수 있는 테이블 개수를 설정합니다.
• open_file_limits: 운영 체제에서 사용할 수 있는 최대 파일 핸들 수를 설정합니다.
• max_connections: MySQL 서버에 동시에 연결할 수 있는 최대 사용자 수를 설정합니다.

2.4) 주의 사항:

FLUSH TABLES 명령을 실행하면 모든 테이블 파일이 닫히므로 다음과 같은 문제가 발생할 수 있습니다.

• 성능 저하: 테이블 파일을 다시 열 때 성능 저하가 발생할 수 있습니다.
• 데이터 손실: 테이블 파일이 닫히는 동안 데이터 손실 가능성이 있습니다.

3. 메모리 플러시 (Memory Flush)

3.1) 개요:

FLUSH TABLES 명령을 실행하면 테이블 데이터가 메모리 버퍼에서 디스크로 강제로 플러시됩니다. 메모리 버퍼는 성능 향상을 위해 테이블 데이터를 메모리에 저장하는 기능입니다.

3.2) 장점:

• 데이터 안전성 향상: 메모리 버퍼는 휘발성이므로 시스템 충돌이나 전원 장애 발생 시 데이터 손실 가능성이 있습니다. FLUSH TABLES 명령을 실행하면 데이터를 디스크로 플러시하여 데이터 안전성을 높입니다.
• 성능 향상: 버퍼 공간을 확보하여 새 데이터 삽입 속도를 높입니다. 변경된 데이터를 디스크에 적용하여 쿼리 실행 속도를 향상시킬 수 있습니다.

3.3) 주의 사항:

• FLUSH TABLES 명령은 디스크 I/O 작업을 증가시켜 성능 저하를 초래할 수 있습니다.
• 테이블 크기가 클 경우 플러시 작업에 시간이 오래 걸릴 수 있습니다.
• 플러시 작업은 백그라운드에서 실행되므로 다른 작업에 영향을 줄 수 있습니다.

 

---

FLUSH TABLES 명령 작동 방식 

1. 개요:

FLUSH TABLES 명령은 MySQL 서버에서 열려 있는 모든 테이블을 강제로 닫고 변경 사항을 디스크에 플러시하는 명령입니다.

 

2. 작동 방식:

1. 테이블 닫기:
   • MySQL 서버는 sql/sql_base.cc::close_cached_tables() 함수를 사용하여 열려 있는 모든 테이블을 닫습니다.
   • MySQL 외부에서 복사된 테이블은 예외로 닫히지 않습니다.
   • 테이블을 닫기 전에 모든 변경 사항은 디스크에 플러시됩니다.
2. 버전 관리:
   • refresh_version이라는 내부 변수가 증가합니다.
   • 테이블을 열 때 테이블의 refresh_version이 업데이트됩니다.
   • 테이블을 닫을 때 테이블의 refresh_version과 현재 refresh_version을 비교합니다.
   • 버전이 일치하지 않으면 테이블을 닫고 COND_refresh 시그널을 브로드캐스트합니다.
3. 잠금 해제 및 재획득:
   • 쓰레드가 테이블 잠금을 획득하면 현재 refresh_version과 열 때의 refresh_version을 비교합니다.
   • 버전이 일치하지 않으면 쓰레드는 잠금을 해제하고 테이블을 다시 엽니다.
   • 테이블을 다시 열 때 다시 잠금을 획득하려고 시도합니다.
4. 클라이언트 응답:
   • 모든 테이블이 닫힌 후 클라이언트에게 OK 신호를 보냅니다.

 

---

명령을 실행한 쓰레드가 테이블 잠금을 보유하고 있는 경우

 

1. 잠금 유지:

FLUSH TABLES 명령은 모든 테이블에 대한 잠금을 해제하지만, 명령을 실행한 쓰레드가 이미 보유하고 있는 잠금은 해제하지 않습니다. 이는 명령 실행 중에 테이블 데이터의 일관성을 유지하기 위해서입니다.

 

2. 순서적 처리:

FLUSH TABLES 명령은 다음과 같은 순서로 처리됩니다.

1. 모든 테이블에 대한 변경 사항을 디스크에 플러시합니다.
2. 모든 테이블에 대한 잠금을 해제합니다.

따라서 명령 실행 중에 테이블 잠금을 보유하고 있는 쓰레드는 먼저 1단계에서 플러시 작업을 완료해야 합니다. 플러시 작업이 완료되면 2단계에서 잠금이 해제됩니다.

 

3. 예외 상황:

FLUSH TABLES WITH READ LOCK 옵션을 사용하면 명령 실행 중에 테이블에 대한 읽기 잠금을 유지합니다. 이 경우에도 명령 실행 쓰레드는 테이블 잠금을 보유하고 있는 상태가 됩니다.

 

4. 테이블 닫기:

FLUSH TABLES 명령은 테이블을 닫지만, 잠금을 해제하지는 않습니다. 테이블 잠금은 명령 실행 후에도 유지됩니다.

 

테이블 잠금을 보유한 경우 처리 과정은 아래와 같습니다.

• 먼저 잠금된 테이블을 닫습니다.
• 다른 쓰레드가 모든 테이블을 닫을 때까지 기다립니다.
• 해당 테이블을 다시 열고 잠금을 획득하려고 시도합니다.
• 모든 작업이 완료되면 다른 쓰레드가 해당 테이블을 열 수 있습니다.

 

---

buffer pool flush : https://dev.mysql.com/doc/refman/8.3/en/innodb-buffer-pool-flushing.html

flush statement : https://dev.mysql.com/doc/refman/8.3/en/flush.html

flush tables : https://blog.naver.com/sory1008/220608352263